0

Have a heavily trafficed database running mostly innodb and would like to better optimize the mysql config. Server has 32Gb ram and the biggest tables are ~18Gb innodb. I think I have done all the obvious things to try and improve performance, but wondering what others would recommend.

[mysqld]
datadir=/mysql/data
socket=/var/lib/mysql/mysql.sock
pid-file=/var/run/mysqld/mysqld.pid
key_buffer_size=1G
read_buffer_size=16M
sort_buffer_size=16M
innodb_data_home_dir=/mysql/innodb
innodb_log_group_home_dir = /mysql/log
innodb_file_format=barracuda
innodb_file_per_table=true
innodb_thread_concurrency=8
innodb_buffer_pool_size=20G
innodb_additional_mem_pool_size=128M
innodb_log_buffer_size=512M
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=2
innodb_lock_wait_timeout=50
myisam_sort_buffer_size=64M
read_rnd_buffer_size=32M
max_connections=125
max_user_connections=90
sql-mode=traditional
tmpdir=/mysql/tmp
slow-query-log=1
slow-query-log-file=/mysql/tmp/mysql_slow_queries.log
max_allowed_packet=32M
tmp_table_size=128M
max_heap_table_size=128M
open_files_limit=8096
join_buffer_size=256M
thread_cache_size=150
table_cache=8096
query_cache_size=512M
query_cache_limit=32M
expire-logs-days=3
log-error = /mysql/log/mysqlderror.log
max_binlog_size=1G
interactive_timeout=3600
wait_timeout=28800

[mysql.server]
user=mysql

[safe_mysqld]
err-log=/mysql/log/mysqld-err.log
pid-file=/var/run/mysqld/mysqld.pid

[mysqldump]
max_allowed_packet=1024M

mysqltuner is showing this

 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.21-enterprise-commercial-advanced-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 4G (Tables: 88)
[--] Data in InnoDB tables: 61G (Tables: 114)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 83

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 27d 18h 19m 53s (1B q [487.302 qps], 5M conn, TX: 751B, RX: 326B)
[--] Reads / Writes: 60% / 40%
[--] Total buffers: 22.2G global + 320.2M per thread (125 max threads)
[!!] Maximum possible memory usage: 61.3G (195% of installed RAM)
[OK] Slow queries: 0% (13K/1B)
[OK] Highest usage of available connections: 76% (95/125)
[OK] Key buffer size / total MyISAM indexes: 1.0G/625.9M
[OK] Key buffer hit rate: 100.0% (18B cached / 2M reads)
[OK] Query cache efficiency: 50.2% (433M cached / 863M selects)
[!!] Query cache prunes per day: 2389385
[OK] Sorts requiring temporary tables: 0% (13K temp sorts / 30M sorts)
[!!] Joins performed without indexes: 4230069
[OK] Temporary tables created on disk: 3% (964K on disk / 30M total)
[OK] Thread cache hit rate: 99% (95 created / 5M connections)
[!!] Table cache hit rate: 4% (836 open / 17K opened)
[OK] Open file limit used: 2% (418/16K)
[OK] Table locks acquired immediately: 99% (912M immediate / 912M locks)
[!!] InnoDB data size / buffer pool: 61.6G/20.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
    Increasing the query_cache size over 128M may reduce performance
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (> 512M) [see warning above]
    join_buffer_size (> 256.0M, or always use indexes with joins)
    table_cache (> 8096)
    innodb_buffer_pool_size (>= 61G)

1 Answer 1

1

MySQLtuner is suggesting you add ram to the box or reduce the memory settings. does this box use its hard disk swap file?

* MySQL's maximum memory usage is dangerously high Add RAM before increasing MySQL buffer variables * query_cache_size (> 512M) [see warning above] join_buffer_size (> 256.0M, or always use indexes with joins) table_cache (> 8096) innodb_buffer_pool_size (>= 61G)

2
  • I cannot add any more ram and cannot reduce the size of the database. There have been some problems recently with swapping and that is how I have the settings it has now (it has not been swapping with these). Trying to work with what I have and was hoping someone had some insight to squeeze a bit more out of this box. Mar 17, 2013 at 21:55
  • I would suggest that you set the memory settings to where everything will fit in actual ram, except innodb. Probably not what you want to hear, but it will yield you the most performance your going to get from this hardware. It does no good to put tables into a swap.
    – 300c
    Mar 18, 2013 at 22:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .