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