Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
share|improve this question

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.