Skip to main content

MYSQL optimization

tank
Hello all, I have read lots and lots of threads and just want to get some thoughts on tuning mysql settings. I do not have alot of sites but some of these are intense databases. Here is my conf
[mysqld] # SAFETY # low_priority_updates=1 concurrent_insert=ALWAYS local-infile=0 # CACHES AND LIMITS # query_cache_size=200M query_cache_limit=4M key_buffer_size=256M tmp_table_size=144M max_heap_table=144M table_definition_cache=4096 table_open_cache=11k open_files_limit=15020 max_connections=600 max_allowed_packet=268435456 read_buffer_size=1Mthread_cache_size=3M join_buffer_size=3M innodb_buffer_pool_size=300M max_user_connections=1000 default-storage-engine=MyISAM innodb_file_per_table=1 # LOGGING # long_query_time = 5 log-slow-queries=/var/lib/mysql/slow.log
Here is my tuning script.
>> MySQLTuner 1.2.0 - Major Hayden >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.5.46-cll [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 589M (Tables: 2032) [--] Data in InnoDB tables: 172M (Tables: 1496) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [--] Data in MEMORY tables: 1M (Tables: 74) [!!] Total fragmented tables: 185 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 4m 47s (3K q [13.645 qps], 339 conn, TX: 34M, RX: 660K) [--] Reads / Writes: 83% / 17% [--] Total buffers: 916.0M global + 6.5M per thread (600 max threads) [OK] Maximum possible memory usage: 4.7G (30% of installed RAM) [OK] Slow queries: 0% (2/3K) [OK] Highest usage of available connections: 0% (5/600) [OK] Key buffer size / total MyISAM indexes: 256.0M/158.1M [OK] Key buffer hit rate: 96.2% (45K cached / 1K reads) [OK] Query cache efficiency: 45.3% (1K cached / 2K selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 390 sorts) [!!] Temporary tables created on disk: 33% (146 on disk / 441 total) [OK] Thread cache hit rate: 98% (5 created / 339 connections) [OK] Table cache hit rate: 99% (3K open / 3K opened) [OK] Open file limit used: 17% (4K/23K) [OK] Table locks acquired immediately: 99% (2K immediate / 2K locks) [OK] InnoDB data size / buffer pool: 173.0M/300.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: tmp_table_size (> 144M) max_heap_table_size (> 144M)
I am thinking of changes these variables
max_connections= 500 # was 600 query_cache_limit=3M #was 4M tmp_table_size=256M # 144M max_heap_table=256M # 144M
How to I get the temporary tables to stop forming? I assume they are bad? Any other recommendations would be great.

Comments

4 comments

Date Votes
  • tank
    I did not make any changes and here is my mysql tuner again.
    >> MySQLTuner 1.6.1 - Major Hayden >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.5.46-cll [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM [--] Data in MyISAM tables: 747M (Tables: 2032) [--] Data in InnoDB tables: 238M (Tables: 1496) [--] Data in MEMORY tables: 5M (Tables: 74) [!!] Total fragmented tables: 181 -------- Security Recommendations ------------------------------------------- [OK] There is no anonymous account in all database users [OK] All database users have passwords assigned [!!] User 'munin@localhost' has user name as password. [!!] User *****' hasn't specific host restriction. [!!] User '*******' hasn't specific host restriction. [!!] User '*******' hasn't specific host restriction. [--] There is 605 basic passwords in the list. -------- Performance Metrics ------------------------------------------------- [--] Up for: 2d 0h 31m 22s (2M q [16.830 qps], 247K conn, TX: 29B, RX: 658M) [--] Reads / Writes: 87% / 13% [--] Binary logging is disabled [--] Total buffers: 916.0M global + 6.5M per thread (600 max threads) [OK] Maximum reached memory usage: 981.0M (6.17% of installed RAM) [OK] Maximum possible memory usage: 4.7G (30.31% of installed RAM) [OK] Slow queries: 0% (2/2M) [OK] Highest usage of available connections: 1% (10/600) [OK] Aborted connections: 0.00% (2/247105) [OK] Query cache efficiency: 64.2% (1M cached / 2M selects) [!!] Query cache prunes per day: 4944 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 159K sorts) [!!] Temporary tables created on disk: 43% (47K on disk / 108K total) [OK] Thread cache hit rate: 99% (10 created / 247K connections) [OK] Table cache hit rate: 98% (6K open / 6K opened) [OK] Open file limit used: 24% (5K/23K) [OK] Table locks acquired immediately: 99% (963K immediate / 964K locks) -------- MyISAM Metrics ----------------------------------------------------- [!!] Key buffer used: 27.7% (74M used / 268M cache) [OK] Key buffer size / total MyISAM indexes: 256.0M/158.3M [OK] Read Key buffer hit rate: 99.9% (28M cached / 25K reads) [!!] Write Key buffer hit rate: 26.6% (218K cached / 160K writes) -------- InnoDB Metrics ----------------------------------------------------- [--] InnoDB is enabled. [OK] InnoDB buffer pool / data size: 300.0M/238.2M [OK] InnoDB buffer pool instances: 1 [OK] InnoDB Used buffer: 99.99% (19198 used/ 19199 total) [OK] InnoDB Read buffer efficiency: 99.91% (24618464 hits/ 24640528 total) [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total) [OK] InnoDB log waits: 0.00% (0 waits / 2869 writes) -------- AriaDB Metrics ----------------------------------------------------- [--] AriaDB is disabled. -------- Replication Metrics ------------------------------------------------- [--] No replication slave(s) for this server. [--] This is a standalone server.. -------- Recommendations ----------------------------------------------------- General recommendations: 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'); ) Restrict Host for user@% to user@SpecificDNSorIp Increasing the query_cache size over 128M may reduce performance When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries which have no LIMIT clause Variables to adjust: query_cache_size (> 200M) [see warning above] tmp_table_size (> 144M) max_heap_table_size (> 144M)
    0
  • cPanelMichael
    Hello :) The second result is likely more accurate because MySQL was running for a longer period of time. You can try modifying your /etc/my.cnf file with changes based on the "Variables to adjust" section of the tuner results to see if that helps. Thank you.
    0
  • tank
    Hello :) The second result is likely more accurate because MySQL was running for a longer period of time. You can try modifying your /etc/my.cnf file with changes based on the "Variables to adjust" section of the tuner results to see if that helps. Thank you.

    Yea i understand that. I was hoping for more help than that. We all know that this is a simple program that does not do everything we need to optimize a MYSQL setting. Specifically it does not address to issues i am having. 1. Query cache prunes per day: 4944 2. [!!] Temporary tables created on disk: 43% (47K on disk / 108K total) Again I was looking for some insight to solving these. Thanks
    0
  • cPanelMichael
    You may need to consult with a qualified system administrator, or post to another forum such as StackOverflow if you do not receive sufficient user-feedback on this thread. Thank you.
    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post