Revisit MySQL configuration

[kristjanjansen]

sudo apt-get install mysqltuner

Make sure innodb_buffer_pool_size is right for our table data size and server memory size (2 or 4 GB)

/var/lib/mysql# ls -lh
-rw-r----- 1 mysql mysql  76M Jul 12 21:02 ibdata1

See::

http://stackoverflow.com/questions/8062496/how-to-change-max-allowed-packet-size http://dba.stackexchange.com/questions/124128/mysql-server-5-6-high-memory-consumption?rq=1 http://dba.stackexchange.com/questions/92074/can-i-reduce-mysql-memory-usage-or-do-i-need-to-add-more-ram

For further optimizations run http://mysqltuner.com and optionally Percona Configuration Wizard for optimal mysql configuration.

[kristjanjansen]

See also #842

[kristjanjansen]

Results

-------- Performance Metrics -------------------------------------------------
[--] Up for: 30m 20s (109K q [60.123 qps], 2K conn, TX: 423M, RX: 24M)
[--] Reads / Writes: 99% / 1%
[--] Total buffers: 169.0M global + 1.1M per thread (151 max threads)
[OK] Maximum possible memory usage: 338.9M (8% of installed RAM)
[OK] Slow queries: 0% (664/109K)
[OK] Highest usage of available connections: 3% (6/151)
[!!] Key buffer size / total MyISAM indexes: 8.0M/75.6M
[!!] Key buffer hit rate: 50.0% (6 cached / 3 reads)
[!!] Query cache efficiency: 0.0% (0 cached / 99K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 8K sorts)
[!!] Temporary tables created on disk: 44% (953 on disk / 2K total)
[OK] Thread cache hit rate: 99% (6 created / 2K connections)
[OK] Table cache hit rate: 72% (304 open / 417 opened)
[OK] Open file limit used: 0% (7/1K)
[OK] Table locks acquired immediately: 100% (102 immediate / 102 locks)
[!!] InnoDB data size / buffer pool: 197.1M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    key_buffer_size (> 75.6M)
    query_cache_limit (> 1M, or use smaller result sets)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    innodb_buffer_pool_size (>= 197M)

[kristjanjansen]

Proposal:

key_buffer_size=128M
query_cache_limit=128M
tmp_table_size=128M
max_heap_table_size=128M
innodb_buffer_pool_size=512M