I have a Dedicated Server running an php eshop.
The server is built on Debian 7.8 64Bit, and has 12 cores and 64GB RAM. I try to use the mysqltuner script to optimize my mysql database.
The current query_cache_size is set to 256MB. I am not sure I should continue increase this number as suggested or not.
A) could you tell me why the Query cache prunes per day(111209) is so high?
How can I to reduce this?
B) why the Table cache hit rate is only 56%
How can i achieve better cache hit rate?
MySQLTuner 1.4.0 – Major Hayden
Bug reports, feature requests, and downloads at http://mysqltuner.com/
Run with ‘–help’ for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.
[OK] Currently running supported MySQL version 5.5.43-0+deb7u1-log
[OK] Operating on 64-bit architecture
——– Storage Engine Statistics ——————————————-
[–] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[–] Data in MyISAM tables: 34M (Tables: 157)
[–] Data in InnoDB tables: 422M (Tables: 4)
[–] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 14
——– Security Recommendations ——————————————-
[OK] All database users have passwords assigned
——– Performance Metrics ————————————————-
[–] Up for: 12h 49m 55s (2M q [59.876 qps], 138K conn, TX: 6B, RX: 476M)
[–] Reads / Writes: 76% / 24%
[–] Total buffers: 32.3G global + 2.8M per thread (600 max threads)
[OK] Maximum possible memory usage: 33.9G (53% of installed RAM)
[OK] Slow queries: 0% (0/2M)
[OK] Highest usage of available connections: 1% (9/600)
[OK] Key buffer size / total MyISAM indexes: 16.0M/36.1M
[OK] Key buffer hit rate: 100.0% (1B cached / 17K reads)
[OK] Query cache efficiency: 70.2% (1M cached / 2M selects)
[!!] Query cache prunes per day: 111209
[OK] Sorts requiring temporary tables: 0% (223 temp sorts / 70K sorts)
[!!] Joins performed without indexes: 5298
[OK] Temporary tables created on disk: 18% (13K on disk / 72K total)
[OK] Thread cache hit rate: 99% (9 created / 138K connections)
[OK] Table cache hit rate: 56% (239 open / 423 opened)
[OK] Open file limit used: 0% (403/1M)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[OK] InnoDB buffer pool / data size: 32.0G/422.7M
[OK] InnoDB log waits: 0
——– Recommendations —————————————————–
Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Increasing the query_cache size over 128M may reduce performance Adjust your join queries to always utilize indexes
Variables to adjust:
query_cache_size (> 256M) [see warning above] join_buffer_size (> 256.0K, or always use indexes with joins)
this is what you have to use to understand whats going on with your database: https://www.percona.com/software/percona-toolkit
Percona Toolkit for MySQL is a collection of advanced command-line tools (which our own Percona MySQL Support staff uses) to perform a variety of MySQL server and system tasks that are too difficult or complex to perform manually, including: Verify MySQL replication integrity by checking master and replica data consistency Efficiently archive rows Find duplicate indexes Summarize MySQL servers Analyze queries from logs and tcpdump Collect vital system information when problems occur
anyway this was one of my favorite scripts back in old days:
Answered by ADM on November 14, 2021
You have to decrease
max_connections as far as you have only 9 simultaneous connections within 138K for 12 hours (and 600 is defined). Default 151 connections if enough.
You have to increase your
key_buffer_size to at least twice big as MYISAM indices (64MB, and 256MB prevents troubles in the future) to fit all the indices into the memory.
You can reduce InnoDB buffer pool to reasonable 2GB instead of 32GB, while only 422MB of data exists.
Answered by Kondybas on November 14, 2021
1 Asked on January 12, 2021 by viraptor
1 Asked on January 12, 2021 by pete
1 Asked on January 11, 2021 by mrhinsh-martin-hinshelwood
0 Asked on January 11, 2021 by server-fault
0 Asked on January 11, 2021 by sami-satti
0 Asked on January 10, 2021
1 Asked on January 10, 2021 by user306594
1 Asked on January 10, 2021 by johnklee
2 Asked on January 10, 2021 by renaud-bompuis
1 Asked on January 9, 2021 by chandima-jayawickrema
1 Asked on January 9, 2021 by ajprames
1 Asked on January 9, 2021
0 Asked on January 9, 2021 by abhishek-divekar
0 Asked on January 9, 2021 by casady
0 Asked on January 9, 2021 by selfishcrawler
0 Asked on January 9, 2021 by user2033464
0 Asked on January 8, 2021 by angelo-sossela
Get help from others!