TransWikia.com

mysqltuner script to optimize my mysql database

Server Fault Asked by MaryB on November 14, 2021

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.

Also,
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 —————————————————–

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

2 Answers

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:
https://github.com/daniel-nichter/hackmysql.com/blob/master/mysqlreport/mysqlreport

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

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP