My MySQL server optimization progress, history

[Fli]

As a MySQL newbie i would like to share how my mysql server optimization goes thru the history:


-------- Performance Metrics -------------------------------------------------
[--] Up for: 23d 2h 29m 34s (193M q [97.175 qps], 9M conn, TX: 8405B, RX: 37B)
[--] Reads / Writes: 74% / 26%
[--] Total buffers: 2.3G global + 34.5M per thread (200 max threads)
[OK] Maximum possible memory usage: 9.0G (52% of installed RAM)
[OK] Slow queries: 0% (105K/193M)
[OK] Highest usage of available connections: 19% (39/200)
[!!] Key buffer size / total MyISAM indexes: 0B/1.1G
[!!] Key buffer hit rate: 0.0% (2B cached / 2B reads)
[OK] Query cache efficiency: 70.2% (94M cached / 133M selects)
[!!] Query cache prunes per day: 558587
[OK] Sorts requiring temporary tables: 1% (108K temp sorts / 9M sorts)
[!!] Joins performed without indexes: 1214820
[!!] Temporary tables created on disk: 32% (4M on disk / 15M total)
[OK] Thread cache hit rate: 99% (11K created / 9M connections)
[!!] Table cache hit rate: 0% (1K open / 75M opened)
[OK] Open file limit used: 3% (1K/43K)
[OK] Table locks acquired immediately: 99% (73M immediate / 73M locks)
[OK] InnoDB buffer pool / data size: 760.0M/714.0M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Increasing the query_cache size over 128M may reduce performance
Adjust your join queries to always utilize indexes
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
key_buffer_size (> 1.1G)
query_cache_size (> 512M) [see warning above]
join_buffer_size (> 16.0M, or always use indexes with joins)
table_cache (> 1024)


Report Complete:
Wed Apr 13 09:02:15 UTC 2016

Adjustments i made after above report:
table_open_cache=1024 -> 512M (lowered)
tmp_table_size=1024M -> 512M (lowered, average maximum size of mysql table is 45M)
join_buffer_size=16M -> 64M (increase)
read_buffer_size=16M -> 64M (increase)
sort_buffer_size=64M <- NEW variable

Decision based on this (http://stackoverflow.com/questions/13259275/mysql-tmp-table-size-max-heap-table-size) article
Lets see next run if tmp_tables % reduce

[Fli]

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 2h 3m 34s (8M q [94.798 qps], 453K conn, TX: 337B, RX: 6B)
[--] Reads / Writes: 69% / 31%
[--] Total buffers: 1.8G global + 192.5M per thread (200 max threads)
[!!] Maximum possible memory usage: 39.4G (168% of installed RAM)
[OK] Slow queries: 0% (9K/8M)
[OK] Highest usage of available connections: 27% (54/200)
[!!] Key buffer size / total MyISAM indexes: 0B/1.2G
[!!] Key buffer hit rate: 0.0% (68M cached / 68M reads)
[OK] Query cache efficiency: 63.6% (3M cached / 5M selects)
[!!] Query cache prunes per day: 455500
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 379K sorts)
[!!] Joins performed without indexes: 57515
[!!] Temporary tables created on disk: 30% (204K on disk / 661K total)
[OK] Thread cache hit rate: 99% (475 created / 453K connections)
[!!] Table cache hit rate: 0% (1K open / 4M opened)
[OK] Open file limit used: 3% (1K/43K)
[OK] Table locks acquired immediately: 99% (4M immediate / 4M locks)
[!!] Connections aborted: 16%
[!!] InnoDB buffer pool / data size: 760.0M/832.4M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Increasing the query_cache size over 128M may reduce performance
Adjust your join queries to always utilize indexes
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C
Your applications are not closing MySQL connections properly
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
key_buffer_size (> 1.2G)
query_cache_size (> 512M) [see warning above]
join_buffer_size (> 64.0M, or always use indexes with joins)
table_cache (> 1024)
innodb_buffer_pool_size (>= 832M)

Changes made after this report:
key_buffer_size=1400 -> #key_buffer_size=1400
query_cache_size=512M -> query_cache_size=96M
query_cache_limit=512M -> query_cache_limit=96M

[Fli]

-------- Performance Metrics -------------------------------------------------
[--] Up for: 26d 20h 44m 31s (214M q [92.516 qps], 8M conn, TX: 7566B, RX: 92B)
[--] Reads / Writes: 74% / 26%
[--] Total buffers: 1.4G global + 192.5M per thread (200 max threads)
[!!] Maximum possible memory usage: 39.0G (166% of installed RAM)
[OK] Slow queries: 0% (165K/214M)
[OK] Highest usage of available connections: 28% (58/200)
[OK] Key buffer size / total MyISAM indexes: 8.0M/1.2G
[OK] Key buffer hit rate: 97.1% (2B cached / 69M reads)
[OK] Query cache efficiency: 62.8% (94M cached / 151M selects)
[!!] Query cache prunes per day: 902001
[OK] Sorts requiring temporary tables: 0% (3 temp sorts / 78M sorts)
[!!] Joins performed without indexes: 1597832
[!!] Temporary tables created on disk: 31% (5M on disk / 16M total)
[OK] Thread cache hit rate: 99% (3K created / 8M connections)
[!!] Table cache hit rate: 0% (1K open / 109M opened)
[OK] Open file limit used: 4% (1K/43K)
[OK] Table locks acquired immediately: 99% (103M immediate / 103M locks)
[!!] InnoDB buffer pool / data size: 760.0M/810.5M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Adjust your join queries to always utilize indexes
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (> 96M)
join_buffer_size (> 64.0M, or always use indexes with joins)
table_cache (> 1024)
innodb_buffer_pool_size (>= 810M)

changes made after this report:

sort_buffer_size=64M -> sort_buffer_size=4M
read_buffer_size=64M -> read_buffer_size=2M
(above changes per advice on https://dev.mysql.com/doc/refman/5.5/en/server-parameters.html)
table_cache=1024 -> (removed, is named table_open_cache now)
table_open_cache=1024 -> table_open_cache=2048 (https://mariadb.com/kb/en/mariadb/optimizing-table_open_cache/)
"The overall goal should be to prevent temp table creation as much as possible." (tmp_table_size increase will not help) watch join and sort buffer

# free -mt|grep buffers
-/+ buffers/cache: 3167 20794

[Fli]

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 12d 17h 51m 28s (87M q [79.294 qps], 3M conn, TX: 3611G, RX: 21G)
[--] Reads / Writes: 77% / 23%
[--] Binary logging is disabled
[--] Physical Memory : 18.0G
[--] Max MySQL memory : 15.1G
[--] Other process memory: 1.9G
[--] Total buffers: 1.4G global + 70.5M per thread (200 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 13.9G (77.16% of installed RAM)
[OK] Maximum possible memory usage: 15.1G (84.05% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (26K/87M)
[!!] Highest connection usage: 91% (182/200)
[OK] Aborted connections: 0.15% (5761/3957389)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Sorts requiring temporary tables: 0% (3K temp sorts / 4M sorts)
[!!] Joins performed without indexes: 734841
[!!] Temporary tables created on disk: 44% (2M on disk / 5M total)
[!!] Table cache hit rate: 0% (2K open / 48M opened)
[OK] Open file limit used: 7% (3K/43K)
[OK] Table locks acquired immediately: 99% (42M immediate / 42M locks)

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 79.6% (6M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/1.3G
[OK] Read Key buffer hit rate: 97.3% (982M cached / 26M reads)
[!!] Write Key buffer hit rate: 56.1% (15M cached / 6M writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 760.0M/1.1G
[OK] InnoDB buffer pool instances: 1
[OK] InnoDB Used buffer: 100.00% (48639 used/ 48640 total)
[OK] InnoDB Read buffer efficiency: 100.00% (10499341540 hits/ 10499681327 total)
[OK] InnoDB Write log efficiency: 92.21% (11632093 hits/ 12614218 total)
[OK] InnoDB log waits: 0.00% (0 waits / 982125 writes)


-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
OPTIMIZE TABLE mpellzmp_fh.sessions; -- can free 51 MB
Total freed space after theses OPTIMIZE TABLE : 51 Mb
Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
Restrict Host for user@% to user@SpecificDNSorIp
Reduce or eliminate persistent connections to reduce connection usage
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Adjust your join queries to always utilize indexes
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Beware that open_files_limit (43762) variable
should be greater than table_open_cache ( 2048)
Variables to adjust:
max_connections (> 200)
wait_timeout (< 28800)
interactive_timeout (< 28800)
query_cache_type (=0)
join_buffer_size (> 64.0M, or always use indexes with joins)
table_open_cache (> 2048)
innodb_buffer_pool_size (>= 1G) if possible.

There was problems with high MySQL memory usage (this and last run), i think next run might be solved thanks to following adjustments

OLD:
[!!] Temporary tables created on disk: 31% (5M on disk / 16M total)

NEW:
[!!] Temporary tables created on disk: 44% (2M on disk / 5M total)

Changes i made after this report:
innodb_buffer_pool_size=760M -> innodb_buffer_pool_size=2G
-> wait_timeout=300
-> interactive_timeout=300
-> query_cache_type=0 (Don't cache results or retrieve from query cache)
tmp_table_size=512M -> tmp_table_size=192M
max_heap_table_size=512M -> max_heap_table_size=292M

Later maybe increase table_open_cache (not sure if safe, im low on RAM)

[Fli]

There are no longer memory issues "Unable to fork". Following is the last 30 days 26.7.2016 - 26.8.2016

1. privvmpages openvz was set to unlimited and physpages was activated and set to 18GB. vSWAP was set to 2GB
2. i made MySQL adjustments mentioned in above post in this thread and restarted MySQL
3. im temporarily running certain bash scripts that is consuming more and more RAM.

283

[Fli]

Still no issues.

# mysqltunerenhanced_outdated_but_whm_focus

-------- Performance Metrics -------------------------------------------------
[--] Up for: 48d 19h 5m 42s (371M q [88.084 qps], 21M conn, TX: 13075G, RX: 61G)
[--] Reads / Writes: 88% / 12%
[--] Binary logging is disabled
[--] Total buffers: 2.3G global + 70.5M per thread (200 max threads)
[OK] Maximum reached memory usage: 9.5G (52.96% of installed RAM)
[!!] Maximum possible memory usage: 16.1G (89.30% of installed RAM)
[OK] Slow queries: 0% (35K/371M)
[OK] Highest usage of available connections: 52% (105/200)
[!!] Aborted connections: 9.17% (1942459/21190294)
[OK] Query cache is disabled by default due to mutex contention.
[OK] Sorts requiring temporary tables: 0% (889 temp sorts / 55M sorts)
[!!] Joins performed without indexes: 7015399
[!!] Temporary tables created on disk: 42% (20M on disk / 47M total)
[OK] Thread cache hit rate: 99% (28K created / 21M connections)
[!!] Table cache hit rate: 0% (2K open / 177M opened)
[OK] Open file limit used: 8% (3K/43K)
[OK] Table locks acquired immediately: 99% (375M immediate / 375M locks)

-------- MyISAM Metrics ------------------------------------------------------
[!!] Key buffer used: 89.0% (7M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/1.2G
[OK] Read Key buffer hit rate: 98.5% (7B cached / 106M reads)
[!!] Write Key buffer hit rate: 70.9% (118M cached / 34M writes)

-------- InnoDB Metrics ------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 2.0G/856.2M
[!!] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 73.22% (95972 used/ 131072 total)
[OK] InnoDB Read buffer efficiency: 100.00% (2143288492 hits/ 2143348268 total)
[!!] InnoDB Write Log efficiency: 78.63% (1858146 hits/ 2363135 total)
[OK] InnoDB log waits: 0.00% (0 waits / 504989 writes)

-------- Recommendations -----------------------------------------------------
General recommendations:
Consider dedicating a server for your database installation with less services running on !
Consider dedicating a server for Web server in production !
Run OPTIMIZE TABLE to defragment tables for better performance
Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
Reduce your overall MySQL memory footprint for system stability
See notes on accuracy of this recommendation below
Reduce or eliminate unclosed connections and network issues
Adjust your join queries to always utilize indexes. Please note this
calculation is made by adding Select_full_join + Select_range_check
status values and triggered when the total >250
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
(exception MariaDB 10.1.x mariadb.com/kb/en/mariadb/optimizing-table_open_cache/)
Beware that open_files_limit (43762) variable
should be greater than table_open_cache ( 2048)
Variables to adjust:
join_buffer_size (> 64.0M, or always use indexes with joins)
tmp_table_size (> 192M)
max_heap_table_size (> 192M)
table_open_cache (> 2048)
innodb_buffer_pool_instances(=2)

Report Complete:
Wed Dec 21 10:31:39 UTC 2016
----------------------------


# cat /etc/my.cnf
[mysqld]

#tmpdir=/dev/shm
tmpdir=/tmp/mysqltmp/

slow_query_log=1
slow_query_log_file=/var/log/mysql/slow.log
long_query_time=1
#log_queries_not_using_indexes=1

max_connections=150
max_user_connections=18
wait_timeout=300
interactive_timeout=300

innodb_file_per_table=1
local-infile=0
max_allowed_packet=268435456
open_files_limit=43762
ft_min_word_len=3

# was not there, was default 8M
#key_buffer_size=1400

thread_cache_size=12
max_connections=200
innodb_buffer_pool_size=2G
join_buffer_size=64M
sort_buffer_size=4M

#was not there, was default 128k
read_buffer_size=2M

# was 512M
query_cache_size=96M
query_cache_limit=96M
query_cache_type=0
table_cache=1024
table_open_cache=2048
tmp_table_size=192M
max_heap_table_size=192M

default-storage-engine=MyISAM
[myisamchk]
ft_min_word_len=3

Possible issues: [!!] Aborted connections: 9.17% (1942459/21190294)
Legend: Too many connections are aborted. - Connections are usually aborted when they cannot be authorized.
My opinion: I am limiting concurrent mysql connections on several sites and one of them is getting flooded by the robots, unsure if this counts towards aborted connections. example: mysql_connect() [<a href='function.mysql-connect'>function.mysql-connect</a>]: User *_* already has more than 'max_user_connections' active connections
/home/*/public_html/*.php on line *

Key buffer used: 89.0% , previously was 79%
Write Key buffer hit rate: 70.9%, previously 56%

[Fli]

Because of some unknown issue with server overloading (seems to be RAM/HDD issue and the Mysql is TOP process in this matter) i am trying to reduce my.cnf variables. Changes:
read_buffer_size=128K (was 2M)
sort_buffer_size=256K (was 4M)
join_buffer_size=256K (was 64M)
(values recommended at: https://haydenjames.io/my-cnf-tuning-avoid-this-common-pitfall/)

- based on advice: "For more than a decade the default remains at 128K. Increasing the default is not only a waste of server memory, but often does not help performance"

added following below [mysqld]
# Skip MYSQL Reverse DNS lookup
skip-name-resolve

UPDATE: skip-name-resolve caused an issue (i do not remember which, i had to disable it)

UPDATE2: i got some advices regarding my config and this is what i followed/changed:

InnoDB buffer pool size to 1G (from 2G)
Dropped max_connections to 150, max_user_connections to 30
Increased join_buffer_size to 1M
Set query_cache_size=32M