My VPS host is complaining that I am using by far the highest IO on the host. Im running a Zabbix monitoring server with a MySQL innodb database. The VPS has 512MB ram. I dont understand why the mysqltuner.pl script is recommending innodb_buffer_pool_size > 1G when I only have 512MB. Any advice on where I should start would be greatly appreciated. I contacted a Zabbix expert and was told 'tmp tables should not end on disk and innodb buffer pool should be as large a possible in most cases' but Im not sure exactly how to implement that in terms of numbers.
Here is the output of the mysqltuner.pl script.
-------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.0.77 [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: -Archive +BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in InnoDB tables: 1G (Tables: 144) [!!] BDB is enabled but isn't being used [OK] Total fragmented tables: 0 -------- Performance Metrics ------------------------------------------------- [--] Up for: 129d 6h 54m 3s (342M q [30.677 qps], 1M conn, TX: 112B, RX: 46B) [--] Reads / Writes: 57% / 43% [--] Total buffers: 34.0M global + 2.7M per thread (100 max threads) [OK] Maximum possible memory usage: 309.0M (62% of installed RAM) [OK] Slow queries: 0% (134/342M) [OK] Highest usage of available connections: 33% (33/100) [OK] Key buffer size / total MyISAM indexes: 8.0M/67.0K [OK] Key buffer hit rate: 96.3% (31M cached / 1M reads) [!!] Query cache is disabled [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 5M sorts) [OK] Temporary tables created on disk: 13% (1M on disk / 10M total) [!!] Thread cache is disabled [!!] Table cache hit rate: 0% (64 open / 46K opened) [OK] Open file limit used: 0% (0/1K) [OK] Table locks acquired immediately: 100% (406M immediate / 406M locks) [!!] InnoDB data size / buffer pool: 1.8G/8.0M -------- Recommendations ----------------------------------------------------- General recommendations: Add skip-bdb to MySQL configuration to disable BDB Enable the slow query log to troubleshoot bad queries Set thread_cache_size to 4 as a starting value Increase table_cache gradually to avoid file descriptor limits Variables to adjust: query_cache_size (>= 8M) thread_cache_size (start at 4) table_cache (> 64) innodb_buffer_pool_size (>= 1G)
And here is my current my.cnf
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 # Disabling symbolic-links is recommended to prevent assorted security risks; # to do so, uncomment this line: # symbolic-links=0 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
Output of free -m
total used free shared buffers cached Mem: 492 486 6 0 2 44 -/+ buffers/cache: 439 53 Swap: 255 112 143
Output from iostat
avg-cpu: %user %nice %system %iowait %steal %idle 0.24 0.03 0.12 0.57 0.01 99.03 Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn xvda 24.47 36.26 748.69 405085498 8365183264 xvdb 1.16 9.50 6.87 106190600 76708192 xvdc 0.00 0.00 0.00 5961 1416
Best Answer
innodb_buffer_pool_size > 1G
, because your dataset size is 1,8GB.To decrease number of reads you need to increase
innodb_buffer_pool_size
. To decrease number of writes you need to edit your zabbix templates (disabling some unnecessary items like free inodes, increase intervals between checks).You have Reads / Writes ratio at 57% / 43%, so enabling Query Cache will not help (it may make things worse, because writes to tables invalidates cache).
Think about increasing tmp_table_size and max_heap_table_size to avoid creating tmp tables on disk (13% of tmp tables). Temporary tables are in MB? it is count? If it is counter, its too high.
Decrease number of connection to 50 (your highest number was 33).
Usefull link about InnoDB optimization.