2

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

2 Answers 2

2

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).

innodb_support_xa = false
innodb_buffer_pool_size = 256M # It depends how many memory is available to MySQL, more is better.
innodb_flush_log_at_trx_commit = 0 # disable writing to logs on every commit and disable fsync on each write
innodb_max_dirty_pages_pct = 90 # avoid flushing dirty pages to disk
innodb_flush_method = O_DIRECT # direct access to disk without OS cache
thread_cache_size = 4
query_cache_size = 0
table_cache = 80? # a little more than number_of_tables_in_zabbix_database

Usefull link about InnoDB optimization.

0

The InnoDB buffer pool is REALLY too small (try at least 64M or 128M). Think to enable Thread Cache, Query Cache and get a bigger table_cache, it could help a lot.

You must log in to answer this question.

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