1

I recently took over administration responsibilities for one of our databases. The existing my.cnf has some crazy values. I should mention that one table has 1.8 billion rows of mostly unneeded historical data, so one of my top priorities is removing 1.5 billion rows. Some values that jump out: read_rnd_buffer, sort_buffer_size are orders of magnitude larger than the sample my.cnf values and thread_stack looks too small.

system info: OpenBSD 5.4 amd64

# sysctl -a | egrep -i 'hw.machine|hw.model|hw.ncpu'
hw.machine=amd64
hw.model=Intel(R) Xeon(R) CPU X3430 @ 2.40GHz
hw.ncpu=4
hw.ncpufound=4


# dmesg | grep mem
real mem = 34334113792 (32743MB)
avail mem = 33412423680 (31864MB)

Here are the results from mysqltuner.pl

mysqltuner.pl
# perl mysqltuner.pl   

 >>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: ******
Please enter your MySQL administrative password: 
[OK] Currently running supported MySQL version 5.1.62-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
[--] Data in InnoDB tables: 198G (Tables: 94)
[!!] Total fragmented tables: 94

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 55m 40s (411K q [123.263 qps], 713 conn, TX: 446M, RX: 59M)
[--] Reads / Writes: 23% / 77%
[--] Total buffers: 4.8G global + 736.1M per thread (300 max threads)
[!!] Maximum possible memory usage: 220.4G (689% of installed RAM)
[OK] Slow queries: 0% (14/411K)
[OK] Highest usage of available connections: 73% (221/300)
[OK] Key buffer size / total MyISAM indexes: 64.0M/91.0K
[OK] Key buffer hit rate: 100.0% (4K cached / 2 reads)
[!!] Query cache efficiency: 8.9% (8K cached / 96K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 4K sorts)
[OK] Temporary tables created on disk: 1% (74 on disk / 4K total)
[OK] Thread cache hit rate: 69% (221 created / 713 connections)
[OK] Table cache hit rate: 98% (348 open / 355 opened)
[OK] Open file limit used: 0% (48/8K)
[OK] Table locks acquired immediately: 100% (397K immediate / 397K locks)
[!!] InnoDB  buffer pool / data size: 4.0G/198.7G
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Enable the slow query log to troubleshoot bad queries
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_limit (> 16M, or use smaller result sets)
    innodb_buffer_pool_size (>= 198G)

Here is the info from my.cnf

# grep -e '^[a-zA-z]' /etc/my.cnf                                                                        
[client]
port            = 3306
socket          = /var/www/var/run/mysql/mysql.sock
[mysqld]
port            = 3306
socket          = /var/www/var/run/mysql/mysql.sock
max_connections = 300
max_connect_errors = 10
table_open_cache = 2048
max_allowed_packet = 32M
binlog_cache_size = 32M
max_heap_table_size = 64M
read_buffer_size = 64M
read_rnd_buffer_size = 384M
sort_buffer_size = 256M
join_buffer_size = 32M
thread_cache_size = 8
thread_concurrency = 4
query_cache_size = 256M
query_cache_limit = 16M
ft_min_word_len = 4
default-storage-engine = MYISAM
thread_stack = 128K
transaction_isolation = READ-UNCOMMITTED
tmp_table_size = 128M
log-bin=mysql-bin
binlog_format=mixed
long_query_time = 10
server-id = 1
key_buffer_size = 64M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 4G
myisam_repair_threads = 1
myisam_recover
innodb_additional_mem_pool_size = 256M
innodb_buffer_pool_size = 4G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 128M
innodb_log_file_size = 1024M
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 50
innodb_lock_wait_timeout = 120
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
sheepdog
  • 345
  • 2
  • 11

2 Answers2

0

A few ideas:

Turn query cache off (reduces that usage to 0). Doesn't appear to be a big win for you, so it's an unnecessary performance hit (in addition to the memory usage).

Unless I’m reading the report wrong, you don’t appear to have a lot of MyISAM tables. I’d reduce read_buffer_size to the default (128K) and read_rnd_buffer_size to the default (256K). But check the MyISAM count carefully, because somebody must have had a reason to tune these so high.

You could probably get by with setting the sort_buffer_size to the default (2MB), too.

That gets you a little closer to your available memory.

Valerie Parham-Thompson
  • 716
  • 5
  • 9
0

Your per-connection buffers are too high. Look at the mysqltuner.pl output. It says 736.1M per thread. This has the greatest impact on the 220GB Estimate. How ?

You are asking MySQL to allocate 736MB each time you open just one DB connection. Please see my post How costly is opening and closing of a DB connection? on the cost of opening and closing DB Connections.

What you should be focusing on is lowering the following settings:

For a 32 GB DB Server, these numbers are far more reasonable.

Give it a Try !!!

Community
  • 1
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520