THL Posted July 14, 2010 Posted July 14, 2010 Hello Guys, just need some optimising advice..My current server spec is Processor: Intel Q9400 QUAD CORE » Memory: 8GB DDR2 SDRAM » Hd1: Dual 500GB SATA / Hardware Raid 1 » Hd2: 500GB 7200RPM SATA / 8MB Cache My.CNF Settings[mysqld] max_connections = 1000 safe-show-database skip-locking key_buffer = 256M max_allowed_packet = 64M table_cache = 256 sort_buffer_size = 128M read_buffer_size = 256M read_rnd_buffer_size = 128M myisam_sort_buffer_size = 256M thread_cache_size = 128 query_cache_size= 128M thread_concurrency = 64 wait_timeout = 30 innodb_file_per_table innodb_log_file_size = 10485760 open_files_limit = 8192 max_heap_table_size = 512M tmp_table_size = 128M [mysqldump] quick max_allowed_packet = 32M [mysql] no-auto-rehash [isamchk] key_buffer = 128M sort_buffer_size = 128M read_buffer = 64M write_buffer = 64M [myisamchk] key_buffer = 128M sort_buffer_size = 128M read_buffer = 64M write_buffer = 64M Whats the best way of optimising for my forum, should I run the tuning primer thats mentioned on here a lot (can I run it anytime or when I'm suffering from slowdown on the server?) Also do I need these lines adding to my.cnf ???? innodb_buffer_pool_size = 2048M innodb_additional_mem_pool_size = 20M thread_cache_size = 16 The server serves some static html and the IPB suite, the forum being the main user of resources...
AlexJ Posted July 15, 2010 Posted July 15, 2010 Do you really reach 1000 mysql connections or at least even 500? Also, might want to increase tmp_table_size to 256MB depending on how much memory you are left with on server. Also, table_cache looks too small. If you post output result of mysqltunner http://blog.mysqltuner.com/ it will be helpful.
THL Posted July 15, 2010 Author Posted July 15, 2010 Results of mysqltuner.>> MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net> >> 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.0.91-community [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 1G (Tables: 346) [--] Data in InnoDB tables: 96K (Tables: 6) [--] Data in MEMORY tables: 49M (Tables: 9) [!!] Total fragmented tables: 53 -------- Performance Metrics ------------------------------------------------- [--] Up for: 14d 1h 4m 59s (83M q [68.937 qps], 4M conn, TX: 768B, RX: 97B) [--] Reads / Writes: 31% / 69% [--] Total buffers: 522.0M global + 512.4M per thread (1000 max threads) [!!] Maximum possible memory usage: 500.9G (6431% of installed RAM) [OK] Slow queries: 0% (122/83M) [OK] Highest usage of available connections: 30% (306/1000) [OK] Key buffer size / total MyISAM indexes: 256.0M/681.8M [OK] Key buffer hit rate: 100.0% (28B cached / 2M reads) [OK] Query cache efficiency: 47.6% (16M cached / 35M selects) [!!] Query cache prunes per day: 38626 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 880K sorts) [!!] Temporary tables created on disk: 48% (229K on disk / 472K total) [OK] Thread cache hit rate: 99% (552 created / 4M connections) [!!] Table cache hit rate: 0% (255 open / 791K opened) [OK] Open file limit used: 4% (358/8K) [!!] Table locks acquired immediately: 76% [OK] InnoDB data size / buffer pool: 96.0K/8.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Reduce your overall MySQL memory footprint for system stability Enable the slow query log to troubleshoot bad queries When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries without LIMIT clauses Increase table_cache gradually to avoid file descriptor limits Optimize queries and/or use InnoDB to reduce lock wait Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** query_cache_size (> 128M) tmp_table_size (> 128M) max_heap_table_size (> 512M) table_cache (> 256) Heres also a mysqlreport, can anyone with more knowledge pickout suggested bottleknecks and what should be adjusted... Use of uninitialized value in multiplication (*) at mysqlreport line 829. Use of uninitialized value in formline at mysqlreport line 1227. MySQL 5.0.91-community uptime 14 2:13:13 Thu Jul 15 06:28:04 2010 __ Key _________________________________________________________________ Buffer used 163.76M of 256.00M %Used: 63.97 Current 79.84M %Usage: 31.19 Write hit 23.61% Read hit 99.99% __ Questions ___________________________________________________________ Total 83.87M 68.9/s DMS 57.87M 47.5/s %Total: 68.99 QC Hits 16.81M 13.8/s 20.04 Com_ 4.82M 4.0/s 5.75 COM_QUIT 4.51M 3.7/s 5.38 -Unknown 129.63k 0.1/s 0.15 Slow 10 s 122 0.0/s 0.00 %DMS: 0.00 Log: OFF DMS 57.87M 47.5/s 68.99 INSERT 22.08M 18.1/s 26.33 38.16 SELECT 18.47M 15.2/s 22.02 31.91 UPDATE 16.08M 13.2/s 19.18 27.79 DELETE 1.23M 1.0/s 1.47 2.13 REPLACE 0 0/s 0.00 0.00 Com_ 4.82M 4.0/s 5.75 set_option 4.13M 3.4/s 4.93 change_db 387.14k 0.3/s 0.46 admin_comma 127.14k 0.1/s 0.15 __ SELECT and Sort _____________________________________________________ Scan 1.08M 0.9/s %SELECT: 5.87 Range 4.50M 3.7/s 24.37 Full join 1.69k 0.0/s 0.01 Range check 0 0/s 0.00 Full rng join 0 0/s 0.00 Sort scan 221.92k 0.2/s Sort range 661.47k 0.5/s Sort mrg pass 0 0/s __ Query Cache _________________________________________________________ Memory usage 67.75M of 128.00M %Used: 52.93 Block Fragmnt 21.06% Hits 16.81M 13.8/s Inserts 18.00M 14.8/s Insrt:Prune 33.18:1 14.3/s Hit:Insert 0.93:1 __ Table Locks _________________________________________________________ Waited 20.22M 16.6/s %Total: 23.80 Immediate 64.75M 53.2/s __ Tables ______________________________________________________________ Open 255 of 256 %Cache: 99.61 Opened 793.22k 0.7/s __ Connections _________________________________________________________ Max used 306 of 1000 %Max: 30.60 Total 4.51M 3.7/s __ Created Temp ________________________________________________________ Disk table 229.21k 0.2/s Table 243.33k 0.2/s Size: 128.0M File 6 0.0/s __ Threads _____________________________________________________________ Running 1 of 1 Cached 127 of 128 %Hit: 99.99 Created 552 0.0/s Slow 0 0/s __ Aborted _____________________________________________________________ Clients 8.93k 0.0/s Connects 3.79k 0.0/s __ Bytes _______________________________________________________________ Sent 771.22G 633.4k/s Received 98.05G 80.5k/s __ InnoDB Buffer Pool __________________________________________________ Usage 896.00k of 8.00M %Used: 10.94 Read hit 98.65% Pages Free 456 %Total: 89.06 Data 56 10.94 %Drty: 0.00 Misc 0 0.00 Latched 0.00 Reads 3.62k 0.0/s From file 49 0.0/s 1.35 Ahead Rnd 1 0.0/s Ahead Sql 0 0/s Writes 1 0.0/s Flushes 1 0.0/s Wait Free 0 0/s __ InnoDB Lock _________________________________________________________ Waits 0 0/s Current 0 Time acquiring Total 0 ms Average 0 ms Max 0 ms __ InnoDB Data, Pages, Rows ____________________________________________ Data Reads 68 0.0/s Writes 7 0.0/s fsync 7 0.0/s Pending Reads 0 Writes 0 fsync 0 Pages Created 0 0/s Read 56 0.0/s Written 1 0.0/s Rows Deleted 0 0/s Inserted 0 0/s Read 0 0/s Updated 0 0/s
THL Posted July 15, 2010 Author Posted July 15, 2010 I've increased table_cache to 512 , tmp_table_size to 256m and Max Connections reduced to 500.
AlexJ Posted July 15, 2010 Posted July 15, 2010 Here is what I would use: table_cache - 1400 (Considering you have IP.Content) tmp_table_size - 256MB max_heap_table_size - 256MB Max connection - 500 query_cache_size - 128MB I would then increase it gradually when needed. I am not quite sure, but it looks like you have set super high buffer size. sort_buffer_size read_buffer_size read_rnd_buffer_size myisam_sort_buffer_size Do you really need 256MB for read_buffer_size? I think with just 8MB it should be fine. (Confirm with someone else) Paste the output of : free -m Also, optimize your xcache. If you get too many OOM's then your site will be always slow.
THL Posted July 18, 2010 Author Posted July 18, 2010 I don't understand this line [!!] Maximum possible memory usage: 500.9G (6431% of installed RAM) How do I go about adjusting whats required here ??
AlexJ Posted July 18, 2010 Posted July 18, 2010 read_rnd_buffer_size change from u uber high value of 128MB to 8MB.
THL Posted July 18, 2010 Author Posted July 18, 2010 New Settings... I'll monitor these..[mysqld] max_connections = 500 safe-show-database skip-locking key_buffer = 256M max_allowed_packet = 64M table_cache = 1400 sort_buffer_size = 8M read_buffer_size = 8M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 256M thread_cache_size = 128 query_cache_size= 256M thread_concurrency = 64 wait_timeout = 30 innodb_file_per_table innodb_log_file_size = 10485760 open_files_limit = 8192 max_heap_table_size = 512M tmp_table_size = 512M [mysqldump] quick max_allowed_packet = 32M [mysql] no-auto-rehash [isamchk] key_buffer = 64M sort_buffer_size = 64M read_buffer = 16M write_buffer = 16M [myisamchk] key_buffer = 64M sort_buffer_size = 64M read_buffer = 16M write_buffer = 16M
AlexJ Posted July 18, 2010 Posted July 18, 2010 Might want to run mysqltunner every 3 days and post the output log. Keep us updated. Your forum speed looks fine to me. :)
THL Posted July 21, 2010 Author Posted July 21, 2010 Heres my latest reports..Mysqltuner >> MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net> >> 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.0.91-community [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 1G (Tables: 384) [--] Data in InnoDB tables: 96K (Tables: 6) [--] Data in MEMORY tables: 8M (Tables: 9) [!!] Total fragmented tables: 63 -------- Performance Metrics ------------------------------------------------- [--] Up for: 3d 10h 16m 15s (25M q [84.925 qps], 1M conn, TX: 201B, RX: 28B) [--] Reads / Writes: 26% / 74% [--] Total buffers: 1.0G global + 24.4M per thread (500 max threads) [!!] Maximum possible memory usage: 12.9G (165% of installed RAM) [OK] Slow queries: 0% (20/25M) [OK] Highest usage of available connections: 31% (157/500) [OK] Key buffer size / total MyISAM indexes: 256.0M/660.4M [OK] Key buffer hit rate: 99.9% (1B cached / 1M reads) [OK] Query cache efficiency: 47.2% (4M cached / 9M selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (1K temp sorts / 183K sorts) [!!] Temporary tables created on disk: 41% (10K on disk / 24K total) [OK] Thread cache hit rate: 99% (158 created / 1M connections) [OK] Table cache hit rate: 52% (1K open / 2K opened) [OK] Open file limit used: 18% (1K/8K) [!!] Table locks acquired immediately: 66% [OK] InnoDB data size / buffer pool: 96.0K/8.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Reduce your overall MySQL memory footprint for system stability Enable the slow query log to troubleshoot bad queries Temporary table size is already large - reduce result set size Reduce your SELECT DISTINCT queries without LIMIT clauses Optimize queries and/or use InnoDB to reduce lock waitMysqlreportMySQL 5.0.91-community uptime 3 10:18:50 Wed Jul 21 17:18:58 2010 __ Key _________________________________________________________________ Buffer used 209.32M of 256.00M %Used: 81.76 Current 219.54M %Usage: 85.76 Write hit 47.94% Read hit 99.93% __ Questions ___________________________________________________________ Total 25.17M 84.9/s DMS 18.40M 62.1/s %Total: 73.12 QC Hits 4.29M 14.5/s 17.06 Com_ 1.30M 4.4/s 5.16 COM_QUIT 1.21M 4.1/s 4.81 -Unknown 37.92k 0.1/s 0.15 Slow 10 s 20 0.0/s 0.00 %DMS: 0.00 Log: OFF DMS 18.40M 62.1/s 73.12 INSERT 7.59M 25.6/s 30.15 41.23 UPDATE 5.73M 19.3/s 22.75 31.11 SELECT 4.80M 16.2/s 19.05 26.06 DELETE 293.19k 1.0/s 1.16 1.59 REPLACE 3 0.0/s 0.00 0.00 Com_ 1.30M 4.4/s 5.16 set_option 1.12M 3.8/s 4.46 change_db 90.21k 0.3/s 0.36 admin_comma 37.02k 0.1/s 0.15 __ SELECT and Sort _____________________________________________________ Scan 198.52k 0.7/s %SELECT: 4.14 Range 1.15M 3.9/s 24.06 Full join 460 0.0/s 0.01 Range check 30 0.0/s 0.00 Full rng join 2 0.0/s 0.00 Sort scan 10.37k 0.0/s Sort range 173.54k 0.6/s Sort mrg pass 1.13k 0.0/s __ Query Cache _________________________________________________________ Memory usage 119.61M of 256.00M %Used: 46.72 Block Fragmnt 20.80% Hits 4.29M 14.5/s Inserts 4.72M 15.9/s Insrt:Prune 4.72M:1 15.9/s Hit:Insert 0.91:1 __ Table Locks _________________________________________________________ Waited 8.52M 28.7/s %Total: 33.45 Immediate 16.94M 57.2/s __ Tables ______________________________________________________________ Open 1156 of 1400 %Cache: 82.57 Opened 2.21k 0.0/s __ Connections _________________________________________________________ Max used 157 of 500 %Max: 31.40 Total 1.21M 4.1/s __ Created Temp ________________________________________________________ Disk table 10.42k 0.0/s Table 14.44k 0.0/s Size: 512.0M File 2.26k 0.0/s __ Threads _____________________________________________________________ Running 1 of 1 Cached 127 of 128 %Hit: 99.99 Created 158 0.0/s Slow 0 0/s __ Aborted _____________________________________________________________ Clients 2.32k 0.0/s Connects 914 0.0/s __ Bytes _______________________________________________________________ Sent 201.90G 681.3k/s Received 28.17G 95.1k/s __ InnoDB Buffer Pool __________________________________________________ Usage 896.00k of 8.00M %Used: 10.94 Read hit 99.47% Pages Free 456 %Total: 89.06 Data 56 10.94 %Drty: 0.00 Misc 0 0.00 Latched 0.00 Reads 9.32k 0.0/s From file 49 0.0/s 0.53 Ahead Rnd 1 0.0/s Ahead Sql 0 0/s Writes 1 0.0/s Flushes 1 0.0/s Wait Free 0 0/s __ InnoDB Lock _________________________________________________________ Waits 0 0/s Current 0 Time acquiring Total 0 ms Average 0 ms Max 0 ms __ InnoDB Data, Pages, Rows ____________________________________________ Data Reads 68 0.0/s Writes 7 0.0/s fsync 7 0.0/s Pending Reads 0 Writes 0 fsync 0 Pages Created 0 0/s Read 56 0.0/s Written 1 0.0/s Rows Deleted 0 0/s Inserted 0 0/s Read 0 0/s Updated 0 0/sTuning Primer -- MYSQL PERFORMANCE TUNING PRIMER -- - By: Matthew Montgomery - MySQL Version 5.0.91-community x86_64 Uptime = 3 days 10 hrs 20 min 6 sec Avg. qps = 84 Total Questions = 25175816 Threads Connected = 1 Server has been running for over 48hrs. It should be safe to follow these recommendations To find out more information on how each of these runtime variables effects performance visit: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html Visit http://www.mysql.com/products/enterprise/advisors.html for info about MySQL's Enterprise Monitoring and Advisory Service SLOW QUERIES The slow query log is NOT enabled. Current long_query_time = 10 sec. You have 20 out of 25175837 that take longer than 10 sec. to complete Your long_query_time seems to be fine BINARY UPDATE LOG The binary update log is NOT enabled. You will not be able to do point in time recovery See http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html WORKER THREADS Current thread_cache_size = 128 Current threads_cached = 127 Current threads_per_sec = 0 Historic threads_per_sec = 0 Your thread_cache_size is fine MAX CONNECTIONS Current max_connections = 500 Current threads_connected = 1 Historic max_used_connections = 157 The number of used connections is 31% of the configured maximum. Your max_connections variable seems to be fine. INNODB STATUS Current InnoDB index space = 160 K Current InnoDB data space = 96 K Current InnoDB buffer pool free = 89 % Current innodb_buffer_pool_size = 8 M Depending on how much space your innodb indexes take up it may be safe to increase this value to up to 2 / 3 of total system memory MEMORY USAGE Max Memory Ever Allocated : 4.24 G Configured Max Per-thread Buffers : 11.90 G Configured Max Global Buffers : 522 M Configured Max Memory Limit : 12.41 G Physical Memory : 7.78 G nMax memory limit exceeds 90% of physical memory KEY BUFFER Current MyISAM index space = 660 M Current key_buffer_size = 256 M Key cache miss rate is 1 : 1337 Key buffer free ratio = 13 % You could increase key_buffer_size It is safe to raise this up to 1/4 of total system memory; assuming this is a dedicated database server. QUERY CACHE Query cache is enabled Current query_cache_size = 256 M Current query_cache_used = 120 M Current query_cache_limit = 1 M Current Query cache Memory fill ratio = 46.94 % Current query_cache_min_res_unit = 4 K MySQL won't cache query results that are larger than query_cache_limit in size SORT OPERATIONS Current sort_buffer_size = 8 M Current read_rnd_buffer_size = 8 M Sort buffer seems to be fine JOINS Current join_buffer_size = 132.00 K You have had 462 queries where a join could not use an index properly You have had 30 joins without keys that check for key usage after each row You should enable "log-queries-not-using-indexes" Then look for non indexed joins in the slow query log. If you are unable to optimize your queries you may want to increase your join_buffer_size to accommodate larger joins in one pass. Note! This script will still suggest raising the join_buffer_size when ANY joins not using indexes are found. OPEN FILES LIMIT Current open_files_limit = 8192 files The open_files_limit should typically be set to at least 2x-3x that of table_cache if you have heavy MyISAM usage. Your open_files_limit value seems to be fine TABLE CACHE Current table_cache value = 1400 tables You have a total of 416 tables You have 1156 open tables. The table_cache value seems to be fine TEMP TABLES Current max_heap_table_size = 512 M Current tmp_table_size = 512 M Of 14608 temp tables, 41% were created on disk Perhaps you should increase your tmp_table_size and/or max_heap_table_size to reduce the number of disk-based temporary tables Note! BLOB and TEXT columns are not allow in memory tables. If you are using these columns raising these values might not impact your ratio of on disk temp tables. TABLE SCANS Current read_buffer_size = 8 M Current table scan ratio = 499 : 1 read_buffer_size seems to be fine TABLE LOCKING Current Lock Wait ratio = 1 : 1 You may benefit from selective use of InnoDB. If you have long running SELECT's against MyISAM tables and perform frequent updates consider setting 'low_priority_updates=1' If you have a high concurrency of inserts on Dynamic row-length tables consider setting 'concurrent_insert=2'. What settings actually affect this ([!!] Maximum possible memory usage: 12.9G (165% of installed RAM)) and how do I adjust them...? Is there anything obvious or not so obvious from the reports, in regard to settings to adjust? Looks like key buffer and tmp tables could be adjusted I think??? With the setting below for memory am I only utilising 4GB of the memory available or over in size for my settings?? MEMORY USAGE Max Memory Ever Allocated : 4.24 G Configured Max Per-thread Buffers : 11.90 G Configured Max Global Buffers : 522 M Configured Max Memory Limit : 12.41 G Physical Memory : 7.78 G
AlexJ Posted July 21, 2010 Posted July 21, 2010 Repost your memory usage and my.cnf Shell command free -m
THL Posted July 21, 2010 Author Posted July 21, 2010 Memory Usage total used free shared buffers cached Mem: 7974 7299 675 0 395 5216 -/+ buffers/cache: 1687 6287 Swap: 9977 0 9977 My CNF [mysqld] max_connections = 500 safe-show-database skip-locking key_buffer = 256M max_allowed_packet = 64M table_cache = 1400 sort_buffer_size = 8M read_buffer_size = 8M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 256M thread_cache_size = 128 query_cache_size= 256M thread_concurrency = 64 wait_timeout = 30 innodb_file_per_table innodb_log_file_size = 10485760 open_files_limit = 8192 max_heap_table_size = 512M tmp_table_size = 512M [mysqldump] quick max_allowed_packet = 32M [mysql] no-auto-rehash [isamchk] key_buffer = 64M sort_buffer_size = 64M read_buffer = 16M write_buffer = 16M [myisamchk] key_buffer = 64M sort_buffer_size = 64M read_buffer = 16M write_buffer = 16M Ps I've seen other mycnf files on here and they have a lot more info on them too as in memory allocation.
THL Posted July 23, 2010 Author Posted July 23, 2010 How do I reduce this [!!] Maximum possible memory usage: 12.9G (165% of installed RAM)
AlexJ Posted July 23, 2010 Posted July 23, 2010 I don't understand this line [!!] Maximum possible memory usage: 500.9G (6431% of installed RAM) How do I go about adjusting whats required here ?? How do I reduce this [!!] Maximum possible memory usage: 12.9G (165% of installed RAM) Sudden wake up? You are much better in shape compared to your old config and your estimated memory usage. You PM'd me your other mysql parameters yesterday. I will look at tonight and will suggest new settings. Have some patience. FYI You still have plenty of free RAM on your dedicated box, so need to worry.
THL Posted July 23, 2010 Author Posted July 23, 2010 Thanks Alex, so many questions ...better to have a good grounding of things... Sorry to be a pain... I'd like to know how its 165% of installed ram and how I could reduce this using what settings... sorry to be impatient... :blush: :huh:
THL Posted July 24, 2010 Author Posted July 24, 2010 Latest Reports for Alex.Mysql Tuner __ Key _________________________________________________________________ Buffer used 209.32M of 256.00M %Used: 81.76 Current 256.00M %Usage: 100.00 Write hit 40.42% Read hit 99.96% __ Questions ___________________________________________________________ Total 39.01M 71.4/s DMS 27.71M 50.7/s %Total: 71.05 QC Hits 7.15M 13.1/s 18.34 Com_ 2.17M 4.0/s 5.57 COM_QUIT 2.03M 3.7/s 5.19 -Unknown 61.35k 0.1/s 0.16 Slow 10 s 35 0.0/s 0.00 %DMS: 0.00 Log: OFF DMS 27.71M 50.7/s 71.05 INSERT 10.91M 20.0/s 27.96 39.35 UPDATE 8.18M 15.0/s 20.97 29.51 SELECT 8.10M 14.8/s 20.76 29.21 DELETE 533.91k 1.0/s 1.37 1.93 REPLACE 3 0.0/s 0.00 0.00 Com_ 2.17M 4.0/s 5.57 set_option 1.88M 3.4/s 4.81 change_db 153.25k 0.3/s 0.39 admin_comma 60.05k 0.1/s 0.15 __ SELECT and Sort _____________________________________________________ Scan 352.92k 0.6/s %SELECT: 4.36 Range 2.00M 3.7/s 24.68 Full join 810 0.0/s 0.01 Range check 60 0.0/s 0.00 Full rng join 2 0.0/s 0.00 Sort scan 18.76k 0.0/s Sort range 298.17k 0.5/s Sort mrg pass 2.15k 0.0/s __ Query Cache _________________________________________________________ Memory usage 125.09M of 256.00M %Used: 48.86 Block Fragmnt 22.49% Hits 7.15M 13.1/s Inserts 7.96M 14.6/s Insrt:Prune 275.08:1 14.5/s Hit:Insert 0.90:1 __ Table Locks _________________________________________________________ Waited 10.80M 19.8/s %Total: 27.29 Immediate 28.78M 52.7/s __ Tables ______________________________________________________________ Open 1142 of 1400 %Cache: 81.57 Opened 2.85k 0.0/s __ Connections _________________________________________________________ Max used 157 of 500 %Max: 31.40 Total 2.03M 3.7/s __ Created Temp ________________________________________________________ Disk table 20.58k 0.0/s Table 27.52k 0.1/s Size: 512.0M File 4.30k 0.0/s __ Threads _____________________________________________________________ Running 1 of 2 Cached 126 of 128 %Hit: 99.99 Created 158 0.0/s Slow 0 0/s __ Aborted _____________________________________________________________ Clients 4.09k 0.0/s Connects 1.70k 0.0/s __ Bytes _______________________________________________________________ Sent 330.19G 604.2k/s Received 44.19G 80.9k/s __ InnoDB Buffer Pool __________________________________________________ Usage 896.00k of 8.00M %Used: 10.94 Read hit 99.54% Pages Free 456 %Total: 89.06 Data 56 10.94 %Drty: 0.00 Misc 0 0.00 Latched 0.00 Reads 10.68k 0.0/s From file 49 0.0/s 0.46 Ahead Rnd 1 0.0/s Ahead Sql 0 0/s Writes 1 0.0/s Flushes 1 0.0/s Wait Free 0 0/s __ InnoDB Lock _________________________________________________________ Waits 0 0/s Current 0 Time acquiring Total 0 ms Average 0 ms Max 0 ms __ InnoDB Data, Pages, Rows ____________________________________________ Data Reads 68 0.0/s Writes 7 0.0/s fsync 7 0.0/s Pending Reads 0 Writes 0 fsync 0 Pages Created 0 0/s Read 56 0.0/s Written 1 0.0/s Rows Deleted 0 0/s Inserted 0 0/s Read 0 0/s Updated 0 0/s Tuning Primer -- MYSQL PERFORMANCE TUNING PRIMER -- - By: Matthew Montgomery - MySQL Version 5.0.91-community x86_64 Uptime = 6 days 7 hrs 48 min 54 sec Avg. qps = 71 Total Questions = 39009573 Threads Connected = 3 Server has been running for over 48hrs. It should be safe to follow these recommendations To find out more information on how each of these runtime variables effects performance visit: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html Visit http://www.mysql.com/products/enterprise/advisors.html for info about MySQL's Enterprise Monitoring and Advisory Service SLOW QUERIES The slow query log is NOT enabled. Current long_query_time = 10 sec. You have 35 out of 39009627 that take longer than 10 sec. to complete Your long_query_time seems to be fine BINARY UPDATE LOG The binary update log is NOT enabled. You will not be able to do point in time recovery See http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html WORKER THREADS Current thread_cache_size = 128 Current threads_cached = 127 Current threads_per_sec = 0 Historic threads_per_sec = 0 Your thread_cache_size is fine MAX CONNECTIONS Current max_connections = 500 Current threads_connected = 2 Historic max_used_connections = 157 The number of used connections is 31% of the configured maximum. Your max_connections variable seems to be fine. INNODB STATUS Current InnoDB index space = 160 K Current InnoDB data space = 96 K Current InnoDB buffer pool free = 89 % Current innodb_buffer_pool_size = 8 M Depending on how much space your innodb indexes take up it may be safe to increase this value to up to 2 / 3 of total system memory MEMORY USAGE Max Memory Ever Allocated : 4.24 G Configured Max Per-thread Buffers : 11.90 G Configured Max Global Buffers : 522 M Configured Max Memory Limit : 12.41 G Physical Memory : 7.78 G nMax memory limit exceeds 90% of physical memory KEY BUFFER Current MyISAM index space = 669 M Current key_buffer_size = 256 M Key cache miss rate is 1 : 2275 Key buffer free ratio = 0 % You could increase key_buffer_size It is safe to raise this up to 1/4 of total system memory; assuming this is a dedicated database server. QUERY CACHE Query cache is enabled Current query_cache_size = 256 M Current query_cache_used = 125 M Current query_cache_limit = 1 M Current Query cache Memory fill ratio = 49.01 % Current query_cache_min_res_unit = 4 K Query Cache is 22 % fragmented Run "FLUSH QUERY CACHE" periodically to defragment the query cache memory If you have many small queries lower 'query_cache_min_res_unit' to reduce fragmentation. MySQL won't cache query results that are larger than query_cache_limit in size SORT OPERATIONS Current sort_buffer_size = 8 M Current read_rnd_buffer_size = 8 M Sort buffer seems to be fine JOINS Current join_buffer_size = 132.00 K You have had 810 queries where a join could not use an index properly You have had 60 joins without keys that check for key usage after each row You should enable "log-queries-not-using-indexes" Then look for non indexed joins in the slow query log. If you are unable to optimize your queries you may want to increase your join_buffer_size to accommodate larger joins in one pass. Note! This script will still suggest raising the join_buffer_size when ANY joins not using indexes are found. OPEN FILES LIMIT Current open_files_limit = 8192 files The open_files_limit should typically be set to at least 2x-3x that of table_cache if you have heavy MyISAM usage. Your open_files_limit value seems to be fine TABLE CACHE Current table_cache value = 1400 tables You have a total of 416 tables You have 1142 open tables. The table_cache value seems to be fine TEMP TABLES Current max_heap_table_size = 512 M Current tmp_table_size = 512 M Of 27692 temp tables, 42% were created on disk Perhaps you should increase your tmp_table_size and/or max_heap_table_size to reduce the number of disk-based temporary tables Note! BLOB and TEXT columns are not allow in memory tables. If you are using these columns raising these values might not impact your ratio of on disk temp tables. TABLE SCANS Current read_buffer_size = 8 M Current table scan ratio = 487 : 1 read_buffer_size seems to be fine TABLE LOCKING Current Lock Wait ratio = 1 : 2 You may benefit from selective use of InnoDB. If you have long running SELECT's against MyISAM tables and perform frequent updates consider setting 'low_priority_updates=1' If you have a high concurrency of inserts on Dynamic row-length tables consider setting 'concurrent_insert=2'. MySQL Tuner >> MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net> >> 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.0.91-community [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 1G (Tables: 384) [--] Data in InnoDB tables: 96K (Tables: 6) [--] Data in MEMORY tables: 8M (Tables: 9) [!!] Total fragmented tables: 60 -------- Performance Metrics ------------------------------------------------- [--] Up for: 6d 7h 50m 22s (39M q [71.374 qps], 2M conn, TX: 330B, RX: 44B) [--] Reads / Writes: 29% / 71% [--] Total buffers: 1.0G global + 24.4M per thread (500 max threads) [!!] Maximum possible memory usage: 12.9G (165% of installed RAM) [OK] Slow queries: 0% (35/39M) [OK] Highest usage of available connections: 31% (157/500) [OK] Key buffer size / total MyISAM indexes: 256.0M/669.1M [OK] Key buffer hit rate: 100.0% (2B cached / 1M reads) [OK] Query cache efficiency: 46.9% (7M cached / 15M selects) [!!] Query cache prunes per day: 4571 [OK] Sorts requiring temporary tables: 0% (2K temp sorts / 317K sorts) [!!] Temporary tables created on disk: 42% (20K on disk / 48K total) [OK] Thread cache hit rate: 99% (158 created / 2M connections) [OK] Table cache hit rate: 40% (1K open / 2K opened) [OK] Open file limit used: 17% (1K/8K) [!!] Table locks acquired immediately: 72% [OK] InnoDB data size / buffer pool: 96.0K/8.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Reduce your overall MySQL memory footprint for system stability Enable the slow query log to troubleshoot bad queries Increasing the query_cache size over 128M may reduce performance Temporary table size is already large - reduce result set size Reduce your SELECT DISTINCT queries without LIMIT clauses Optimize queries and/or use InnoDB to reduce lock wait Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** query_cache_size (> 256M) [see warning above]
Gary. Posted July 24, 2010 Posted July 24, 2010 Who ever gave you that my.cnf needs a brain test ! They obviously have no idea. 1 major problem:thread_concurrency = 64 so your machine has 64 CPU's ? LOL Ill reply in 15 mins after I stop wetting myself through laughing :P
AlexJ Posted July 26, 2010 Posted July 26, 2010 Other then that try below settings: max_connections = 300 key_buffer_size = 350MB myisam_sort_buffer_size = 96MB thread_cache_size = 192 thread_concurrency = 4 query_cache_limit = 2M Note: I personally think both key_buffer_size and key_buffer are quite same, but please confirm with someone else. You also might want to report to IPB which queries are not using indexes from your query log. PS It is always good to take back up of last working copy of my.cnf in case if something goes wrong. :)
Gary. Posted July 26, 2010 Posted July 26, 2010 I would personally go with the following:[mysqld] max_connections = 300 safe-show-database skip-locking key_buffer = 192M max_allowed_packet = 32M table_cache = 1024 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 2M myisam_sort_buffer_size = 92M thread_cache_size = 128 query_cache_size= 64M thread_concurrency = 64 wait_timeout = 30 innodb_file_per_table open_files_limit = 4028 max_heap_table_size = 64M tmp_table_size = 164M [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 92M sort_buffer_size = 92M read_buffer = 16M write_buffer = 16M [myisamchk] key_buffer = 92M sort_buffer_size = 92M read_buffer = 16M write_buffer = 16M Then restart mysql
Gary. Posted July 28, 2010 Posted July 28, 2010 Sorru I said this wrong:[mysqld] max_connections = 300 safe-show-database skip-locking key_buffer = 192M max_allowed_packet = 32M table_cache = 1024 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 2M myisam_sort_buffer_size = 92M thread_cache_size = 128 query_cache_size= 64M thread_concurrency = 4 wait_timeout = 30 innodb_file_per_table open_files_limit = 4028 max_heap_table_size = 64M tmp_table_size = 164M [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 92M sort_buffer_size = 92M read_buffer = 16M write_buffer = 16M [myisamchk] key_buffer = 92M sort_buffer_size = 92M read_buffer = 16M write_buffer = 16M
Recommended Posts
Archived
This topic is now archived and is closed to further replies.