MySQL my.cnf modifications - Assistance
Hi everyone,
I pledge your assistance in configuring MySQL my.cnf file on my server to best use my resources:
(I have tried many optimization tools and methods, and at times I found it frustrating to attain any better result).
My current setup is:
My.cnf (currently in use):
My current load: I have three Huge websites. 1. E-Commerce website with Stock market and news. 2. a child clinic website with visitors up to 1 mil a month. 3. a sports website, which always busy with visitors for sport news. Current Server's mysqltuner.pl statues:
My CPU / MEM load on server (almost all time):
Can you tell me what i have wrong in My.cnf and what I should change/remove ? I really appreciate your assistance. thanks everyone
[COLOR="#0000FF">Dedicated Server
Intel(R) Xeon(R) CPU E3-1230 V2 @ 3.30GHz ( 8 CPUs )
Cache 8192 KB
8GB RAM
100Mbps port
1,000GB SATA2 x1 (No RAID)
Enterprise Linux - CentOS 6.4 - 64 bit - WHM Cpanel 11.38.2 (Build 6)
My.cnf (currently in use):
[mysqld]
max_connections = 500
key_buffer = 220M
myisam_sort_buffer_size = 32M
join_buffer_size = 1M
read_buffer_size = 256K
sort_buffer_size = 256K
table_cache = 128M
thread_cache_size = 16M
interactive_timeout = 25
wait_timeout = 10
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 8M
query_cache_size = 64M
query_cache_type = 1
tmp_table_size = 16M
max_heap_table_size = 64M
open_files_limit=13850
[mysqld_safe]
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 220M
sort_buffer = 256K
read_buffer = 256K
write_buffer = 256K
[mysqlhotcopy]
interactive-timeoutMy current load: I have three Huge websites. 1. E-Commerce website with Stock market and news. 2. a child clinic website with visitors up to 1 mil a month. 3. a sports website, which always busy with visitors for sport news. Current Server's mysqltuner.pl statues:
-------- Storage Engine Statistics -------------------------------------------
[--] Status: [COLOR="#00FF00">+Archive[COLOR="#FF0000"> -BDB -Federated [COLOR="#00FF00">+InnoDB [COLOR="#FF0000">-ISAM -NDBCluster
[--] Data in MyISAM tables: 8G (Tables: 5255)
[--] Data in InnoDB tables: 4M (Tables: 160)
[--] Data in MEMORY tables: 1M (Tables: 22)
[!!] Total fragmented tables: 216
-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 23h 0m 19s (9M q [56.387 qps], 175K conn, TX: 66B, RX: 2B)
[--] Reads / Writes: 38% / 62%
[--] Total buffers: 1.0G global + 2.7M per thread (300 max threads)
[OK] Maximum possible memory usage: 1.8G (5% of installed RAM)
[OK] Slow queries: 0% (69/9M)
[OK] Highest usage of available connections: 4% (12/300)
[OK] Key buffer size / total MyISAM indexes: 1000.0M/1.4G
[OK] Key buffer hit rate: 99.8% (198M cached / 312K reads)
[OK] Query cache efficiency: 89.5% (6M cached / 6M selects)
[[COLOR="#FF0000">!!] Query cache prunes per day: 115508
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 238K sorts)
[[COLOR="#FF0000">!!] Joins performed without indexes: 3734
[[COLOR="#FF0000">!!] Temporary tables created on disk: 29% (123K on disk / 419K total)
[OK] Thread cache hit rate: 98% (1K created / 175K connections)
[[COLOR="#FF0000">!!] Table cache hit rate: 0% (10K open / 1M opened)
[OK] Open file limit used: 5% (15K/262K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
[OK] InnoDB data size / buffer pool: 4.7M/8.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
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
Variables to adjust:
query_cache_size (> 16M)
join_buffer_size (> 128.0K, or always use indexes with joins)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
table_cache (> 131072)My CPU / MEM load on server (almost all time):
mysql - CPU 55% ~ 107% Mem 1.7% ~ 5%Can you tell me what i have wrong in My.cnf and what I should change/remove ? I really appreciate your assistance. thanks everyone
-
Hello :) I moved this thread over to the "Optimization" forum. You will likely get more user feedback on MySQL configurations here. Thank you. 0 -
Hi, thank you very much, I was actually looking for this section earlier, but didn't find it. 0 -
Correct those: key_buffer = 2000M table_cache = 5000 thread_cache_size = 50 and add slow queries tracking slow_query_log=1 slow_query_log_file=mysql-slow.log long_query_time=0.1 log-queries-not-using-indexes and 50-107% its frop top ? couse there it means, in very simplistic description: 50% - 100% of 1 core + 7% of next core you got 8 cores (inc virtual), so 1/8 is used so it's quite good actually anyways please correcy my.cnf with values above and restart then please provide mysqltuner.pl after few hours 0 -
Thank you very much, I will edit now, and let you know the result after 24 HR, to make sure sqltuner get's a good result log. [quote="thinkbot, post: 1461912">Correct those: key_buffer = 2000M table_cache = 5000 thread_cache_size = 50 and add slow queries tracking slow_query_log=1 slow_query_log_file=mysql-slow.log long_query_time=0.1 log-queries-not-using-indexes and 50-107% its frop top ? couse there it means, in very simplistic description: 50% - 100% of 1 core + 7% of next core you got 8 cores (inc virtual), so 1/8 is used so it's quite good actually anyways please correcy my.cnf with values above and restart then please provide mysqltuner.pl after few hours 0 -
Hi, Just a question, did you mean thread_cache_size = 50 or thread_cache_size = 50M ? 0 -
thread_cache_size = 50 without M same for table_cache = 5000 without M :) 0 -
Thank you, i will hopefully post results later. thanks again. [quote="thinkbot, post: 1462091">thread_cache_size = 50 without M same for table_cache = 5000 without M :) 0 -
Here's 24 Hour SQLTuner readings: [QUOTE]-------- Storage Engine Statistics ------------------------------------------- [--] Status: [COLOR="#00FF00">+Archive [COLOR="#FF0000">-BDB -Federated [COLOR="#00FF00">+InnoDB [COLOR="#FF0000">-ISAM -NDBCluster [--] Data in MyISAM tables: 4G (Tables: 2157) [--] Data in InnoDB tables: 17M (Tables: 167) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [--] Data in MEMORY tables: 0B (Tables: 26) [[COLOR="#FF0000">!!] Total fragmented tables: 190 -------- Security Recommendations ------------------------------------------- [!!] User 'link2ad_new@localhost' has no password set. -------- Performance Metrics ------------------------------------------------- [--] Up for: 1d 5h 25m 10s (29M q [277.314 qps], 130K conn, TX: 10923B, RX: 1B) [--] Reads / Writes: 92% / 8% [--] Total buffers: 2.4G global + 8.2M per thread (500 max threads) [[COLOR="#FF0000">!!] Maximum possible memory usage: 6.4G (86% of installed RAM) [[COLOR="#FF0000">!!] Slow queries: 13% (3M/29M) [OK] Highest usage of available connections: 7% (38/500) [OK] Key buffer size / total MyISAM indexes: 2.0G/1.1G [OK] Key buffer hit rate: 99.9% (44M cached / 51K reads) [OK] Query cache efficiency: 83.2% (23M cached / 28M selects) [[COLOR="#FF0000">!!] Query cache prunes per day: 166433 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2M sorts) [[COLOR="#FF0000">!!] Joins performed without indexes: 13140 [[COLOR="#FF0000">!!] Temporary tables created on disk: 37% (1M on disk / 3M total) [OK] Thread cache hit rate: 99% (38 created / 130K connections) [OK] Table cache hit rate: 38% (4K open / 12K opened) [OK] Open file limit used: 48% (6K/13K) [OK] Table locks acquired immediately: 99% (5M immediate / 5M locks) [OK] InnoDB data size / buffer pool: 17.8M/128.0M -------- 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 Variables to adjust: query_cache_size (> 32M) join_buffer_size (> 2.0M, or always use indexes with joins)
[QUOTE]CPU Usage for MYSQL : 89.6% MEM: 6.50 -
Slow.txt Here's the slow.txt :) So, do you believe the whole issue is from slow queries (bad written sql queries) and I should let the client fix them ? # 600.2s user time, 580ms system time, 106.82M rss, 260.20M vsz # Current date: Sun Sep 15 23:42:52 2013 # Hostname: server.noblesintl.com # Files: /var/lib/mysql/mysql-slow.log # Overall: 4.21M total, 572 unique, 0.58 QPS, 0.01x concurrency __________ # Time range: 2013-06-24 13:34:00 to 2013-09-15 23:42:50 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 102131s 29us 186s 24ms 100ms 243ms 4ms # Lock time 973s 0 96s 231us 52us 96ms 38us # Rows sent 910.56M 0 9.30M 227.05 1012.63 4.83k 0.99 # Rows examine 16.78G 0 381.26M 4.18k 3.35k 692.75k 1.39k # Query size 244.40M 14 27.06k 60.94 118.34 43.86 51.63 # Profile # Rank Query ID Response time Calls R/Call V/M Item # ==== ================== ================ ======= ======== ===== ======== # 1 0xD5DBA4FF14F318CD 85407.9630 83.6% 833023 0.1025 0.00 SELECT pages # 2 0xBF1709884D047149 5547.6923 5.4% 833023 0.0067 0.00 SELECT videocontent # 3 0xBCE087184A1D06D9 2052.1737 2.0% 833023 0.0025 0.00 SELECT signatures # 4 0x7C4EDE3BEFD0010D 1828.9393 1.8% 15 121.9293 11.53 SELECT t_news news_cats # 5 0x24E8F807B04FC9A7 1697.9560 1.7% 60001 0.0283 0.00 SELECT pages # 6 0x801BBD3669FBF8BF 1184.1595 1.2% 68463 0.0173 0.00 SELECT ips_countries # 31 0xD7BF21F1A60B501B 25.2908 0.0% 15 1.6861 2.51 UPDATE t_news # MISC 0xMISC 4386.9140 4.3% 1577703 0.0028 0.0 <565 ITEMS> # Query 1: 0.12 QPS, 0.01x concurrency, ID 0xD5DBA4FF14F318CD at byte 919210452 # Scores: V/M = 0.00 # Time range: 2013-06-24 13:34:00 to 2013-09-15 23:42:48 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 19 833023 # Exec time 83 85408s 95ms 2s 103ms 105ms 17ms 100ms # Lock time 20 197s 12us 652ms 236us 36us 3ms 23us # Rows sent 89 819.06M 1.01k 1.01k 1.01k 1.01k 0 1.01k # Rows examine 4 819.06M 1.01k 1.01k 1.01k 1.01k 0 1.01k # Query size 11 27.01M 34 34 34 34 0 34 # String: # Databases childcli_ccs # Hosts localhost # Users childcli_db # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # # 100ms ################################################################ # 1s # # 10s+ # Tables # SHOW TABLE STATUS FROM `childcli_ccs` LIKE 'pages'\G # SHOW CREATE TABLE `childcli_ccs`.`pages`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT * FROM pages where active=1\G # Query 2: 0.12 QPS, 0.00x concurrency, ID 0xBF1709884D047149 at byte 919209135 # Scores: V/M = 0.00 # Time range: 2013-06-24 13:34:00 to 2013-09-15 23:42:48 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 19 833023 # Exec time 5 5548s 5ms 2s 7ms 8ms 5ms 6ms # Lock time 4 40s 19us 58ms 47us 54us 84us 47us # Rows sent 0 813.50k 1 1 1 1 0 1 # Rows examine 6 1.13G 1.42k 1.42k 1.42k 1.42k 0 1.42k # Query size 21 53.23M 67 67 67 67 0 67 # String: # Databases childcli_ccs # Hosts localhost # Users childcli_db # Query_time distribution # 1us # 10us # 100us # 1ms ################################################################ # 10ms # # 100ms # # 1s # # 10s+ # Tables # SHOW TABLE STATUS FROM `childcli_ccs` LIKE 'videocontent'\G # SHOW CREATE TABLE `childcli_ccs`.`videocontent`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT * FROM videocontent where active=1 order by RAND(15) limit 1\G # Query 3: 0.12 QPS, 0.00x concurrency, ID 0xBCE087184A1D06D9 at byte 275949673 # Scores: V/M = 0.00 # Time range: 2013-06-24 13:34:00 to 2013-09-15 23:42:48 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 19 833023 # Exec time 2 2052s 2ms 242ms 2ms 3ms 598us 2ms # Lock time 3 37s 17us 15ms 44us 52us 41us 42us # Rows sent 0 813.50k 1 1 1 1 0 1 # Rows examine 7 1.19G 1.50k 1.50k 1.50k 1.50k 0 1.50k # Query size 16 39.72M 50 50 50 50 0 50 # String: # Databases childcli_ccs # Hosts localhost # Users childcli_db # Query_time distribution # 1us # 10us # 100us # 1ms ################################################################ # 10ms # # 100ms # # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `childcli_ccs` LIKE 'signatures'\G # SHOW CREATE TABLE `childcli_ccs`.`signatures`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT * FROM signatures order by RAND(14) limit 1\G # Query 4: 0.00 QPS, 0.08x concurrency, ID 0x7C4EDE3BEFD0010D at byte 367802407 # Scores: V/M = 11.53 # Time range: 2013-09-14 23:55:12 to 2013-09-15 05:59:14 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 15 # Exec time 1 1829s 82s 186s 122s 184s 37s 97s # Lock time 46 451s 38us 96s 30s 93s 39s 6s # Rows sent 0 653 14 50 43.53 49.17 9.85 49.17 # Rows examine 31 5.27G 319.18M 381.26M 359.98M 379.16M 31.04M 379.16M # Query size 0 2.78k 189 190 189.67 183.58 0 183.58 # String: # Databases b2bsy_b2b # Hosts localhost # Users b2bsy_root # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms # 1s # 10s+ ################################################################ # Tables # SHOW TABLE STATUS FROM `b2bsy_b2b` LIKE 't_news'\G # SHOW CREATE TABLE `b2bsy_b2b`.`t_news`\G # SHOW TABLE STATUS FROM `b2bsy_b2b` LIKE 'news_cats'\G # SHOW CREATE TABLE `b2bsy_b2b`.`news_cats`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT * FROM `t_news` WHERE approved = 1 and deleted = 0 and id in (select DISTINCT n_id from news_cats where c_id = 652 order by n_id desc) ORDER BY `sort_date` DESC,`id` DESC limit 0, 50\G # Query 5: 0.01 QPS, 0.00x concurrency, ID 0x24E8F807B04FC9A7 at byte 278543846 # Scores: V/M = 0.00 # Time range: 2013-06-24 13:34:00 to 2013-09-15 23:42:46 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 1 60001 # Exec time 1 1698s 22ms 2s 28ms 34ms 10ms 26ms # Lock time 1 15s 22us 178ms 257us 47us 4ms 25us # Rows sent 0 292.97k 5 5 5 5 0 5 # Rows examine 0 113.01M 1.93k 1.93k 1.93k 1.93k 0 1.93k # Query size 3 8.41M 147 147 147 147 0 147 # String: # Databases childcli_ccs # Hosts localhost # Users childcli_db # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms # # 1s # # 10s+ # Tables # SHOW TABLE STATUS FROM `childcli_ccs` LIKE 'pages'\G # SHOW CREATE TABLE `childcli_ccs`.`pages`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT * FROM pages WHERE catgoryid <> '11' AND catgoryid <> '25' AND catgoryid <> '26' AND catgoryid <> '29' AND active=1 order by rand() limit 5\G # Query 6: 0.01 QPS, 0.00x concurrency, ID 0x801BBD3669FBF8BF at byte 904376402 # Scores: V/M = 0.00 # Time range: 2013-06-24 13:34:40 to 2013-09-15 23:42:41 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 1 68463 # Exec time 1 1184s 15ms 105ms 17ms 19ms 3ms 16ms # Lock time 0 3s 16us 719us 40us 54us 10us 40us # Rows sent 0 31.42k 0 1 0.47 0.99 0.50 0 # Rows examine 24 4.08G 62.56k 62.56k 62.56k 62.56k 0 62.56k # Query size 2 6.77M 100 104 103.74 102.22 1.45 102.22 # String: # Databases soccer_dbnew # Hosts localhost # Users soccer_deqaq # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms # # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `soccer_dbnew` LIKE 'ips_countries'\G # SHOW CREATE TABLE `soccer_dbnew`.`ips_countries`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT * FROM ips_countries WHERE 1603907757 >= Beginning_IP_Number AND 1603907757 <= Ending_IP_Number\G # Query 31: 0.00 QPS, 0.00x concurrency, ID 0xD7BF21F1A60B501B at byte 268339734 # Scores: V/M = 2.51 # Time range: 2013-09-14 22:34:24 to 2013-09-15 10:22:46 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 15 # Exec time 0 25s 104ms 6s 2s 6s 2s 993ms # Lock time 0 2s 18us 701ms 126ms 672ms 233ms 40us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 15 1 1 1 1 0 1 # Query size 0 888 58 60 59.20 59.77 1 56.92 # String: # Databases b2bsy_b2b # Hosts localhost # Users b2bsy_root # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s ######################################################## # 10s+ # Tables # SHOW TABLE STATUS FROM `b2bsy_b2b` LIKE 't_news'\G # SHOW CREATE TABLE `b2bsy_b2b`.`t_news`\G update `t_news` set `counter` = '144' where id = 904\G # Converted for EXPLAIN # EXPLAIN /*!50100 PARTITIONS*/ select `counter` = '144' from `t_news` where id = 904\G0 -
Yes, Yes, your problems are becouse : Query 1: SELECT * FROM pages where active=1\G it takes most of the time in mysql 83.6% of all queries what is this query trying to do ? select all elements, or only count ? if count use SELECT COUNT(*) as total FROM pages where active=1\G if you really want to select all elements, you should select only columns you need example SELECT id, user, content FROM pages where active=1 not SELECT * FROM also make sure, you got index on active column in pages Query 2: second one, queries with rand() (especially on bigger tables): SELECT * FROM videocontent where active=1 order by RAND(15) limit 1\G with RAND() [url=http://jan.kneschke.de/projects/mysql/order-by-rand/]~jk - ORDER BY RAND() 0 -
I see, Seems my suspicious were true. That domain is giving me a headache, i will let my programmer check this topic and see if she can correct those queries to solve the issue, thanks . [quote="thinkbot, post: 1462392">Yes, Yes, your problems are becouse : Query 1: SELECT * FROM pages where active=1\G it takes most of the time in mysql 83.6% of all queries what is this query trying to do ? select all elements, or only count ? if count use SELECT COUNT(*) as total FROM pages where active=1\G if you really want to select all elements, you should select only columns you need example SELECT id, user, content FROM pages where active=1 not SELECT * FROM also make sure, you got index on active column in pages Query 2: second one, queries with rand() (especially on bigger tables): SELECT * FROM videocontent where active=1 order by RAND(15) limit 1\G with RAND() [url=http://jan.kneschke.de/projects/mysql/order-by-rand/]~jk - ORDER BY RAND() 0 -
hi, I have made the changes to SQL Queries as you mentioned, and BAM! Down from 89% + to 8~10%, I mean you are the man. Do you think I should fine tune anything else now? Here's plsqltuner report: -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 4G (Tables: 2157) [--] Data in InnoDB tables: 17M (Tables: 168) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [--] Data in MEMORY tables: 0B (Tables: 26) [!!] Total fragmented tables: 196 -------- Security Recommendations ------------------------------------------- [!!] User 'link2ad_new@localhost' has no password set. -------- Performance Metrics ------------------------------------------------- [--] Up for: 23m 6s (460K q [332.227 qps], 1K conn, TX: 9B, RX: 27M) [--] Reads / Writes: 91% / 9% [--] Total buffers: 2.4G global + 8.2M per thread (500 max threads) [!!] Maximum possible memory usage: 6.4G (86% of installed RAM) [!!] Slow queries: 10% (49K/460K) [OK] Highest usage of available connections: 1% (5/500) [OK] Key buffer size / total MyISAM indexes: 2.0G/1.1G [OK] Key buffer hit rate: 98.6% (565K cached / 7K reads) [OK] Query cache efficiency: 86.1% (387K cached / 449K selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 36K sorts) [!!] Joins performed without indexes: 197 [!!] Temporary tables created on disk: 35% (19K on disk / 53K total) [OK] Thread cache hit rate: 99% (5 created / 1K connections) [OK] Table cache hit rate: 66% (193 open / 289 opened) [OK] Open file limit used: 2% (334/13K) [OK] Table locks acquired immediately: 99% (68K immediate / 68K locks) [OK] InnoDB data size / buffer pool: 17.8M/128.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate 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 Variables to adjust: join_buffer_size (> 2.0M, or always use indexes with joins)0 -
can you generate slow.txt again with pt-query-digest, with new stats ? and can you tell how did you correct your queries, how does it look now ? 0 -
Ok, But it will take time untill tomorrow, as I removed old slow log to make sure i get new readings, i will post new results tomorrow after 24 HR. Also, I didn't do the changes my self, I handed your Notes to the programmer of the website and he checked them out and made the necessary changes. [quote="thinkbot, post: 1465531">can you generate slow.txt again with pt-query-digest, with new stats ? and can you tell how did you correct your queries, how does it look now ? 0 -
you dont have to wait 24h to generate slowlog, can be only few hours 0 -
[quote="thinkbot, post: 1465612">you dont have to wait 24h to generate slowlog, can be only few hours
# 129.5s user time, 130ms system time, 43.58M rss, 193.03M vsz # Current date: Sat Sep 21 03:04:44 2013 # Hostname: server.noblesintl.com # Files: /var/lib/mysql/mysql-slow.log # Overall: 911.63k total, 406 unique, 41.03 QPS, 0.12x concurrency _______ # Time range: 2013-09-20 20:54:22 to 2013-09-21 03:04:42 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 2759s 29us 100s 3ms 5ms 246ms 1ms # Lock time 65s 0 6s 71us 49us 12ms 22us # Rows sent 41.95M 0 9.30M 48.25 9.83 10.34k 0.99 # Rows examine 4.43G 0 396.46M 5.09k 3.35k 974.87k 1.39k # Query size 69.31M 14 106.31k 79.72 118.34 125.81 69.19 # Profile # Rank Query ID Response time Calls R/Call V/M Item # ==== ================== ============== ====== ======= ===== ============ # 1 0x7C4EDE3BEFD0010D 581.2365 21.1% 6 96.8727 0.25 SELECT t_news news_cats # 2 0xCEFAD6A90BFF75CD 436.5478 15.8% 237952 0.0018 0.00 SELECT videocontent # 3 0x253746F4D0305402 358.7444 13.0% 15879 0.0226 0.00 SELECT pages # 4 0x36ED1CBF7ACA2E3B 243.0325 8.8% 237952 0.0010 0.00 SELECT signatures # 5 0x95A32AF22D1B8058 202.1475 7.3% 49707 0.0041 0.00 SELECT pages # 6 0x801BBD3669FBF8BF 166.7257 6.0% 9939 0.0168 0.00 SELECT ips_countries # 7 0x67A347A2812914DF 103.5395 3.8% 1914 0.0541 7.64 SELECT post # 8 0x86251B43B9DDF65A 60.5789 2.2% 15879 0.0038 0.00 SELECT pages # 9 0x043FF6692C1BC226 44.5401 1.6% 86429 0.0005 0.34 SELECT ads # 10 0x62F0C89E29502FE3 40.5223 1.5% 1116 0.0363 0.00 SELECT add_comment # 11 0xBD961FCD0E8FE879 34.3455 1.2% 16274 0.0021 3.08 SELECT online # 12 0xC32B386D3651DF02 28.2047 1.0% 8462 0.0033 0.00 SELECT news # 13 0xA04424D8B00ECAC4 22.9826 0.8% 18757 0.0012 0.00 SELECT comp_per # 14 0xBCD934C7A7981E7E 21.9524 0.8% 1357 0.0162 0.00 SELECT t_news # 15 0xD7BF21F1A60B501B 21.6663 0.8% 2 10.8331 4.16 UPDATE t_news # 16 0x5E0E2E4D89C8B96D 21.5775 0.8% 1357 0.0159 0.00 SELECT t_news # 17 0x49649CDCE6DA22B3 20.5867 0.7% 5292 0.0039 0.00 SELECT pages # 18 0x046457F0CE3E8295 20.0790 0.7% 5300 0.0038 0.00 SELECT pages # 19 0x04D731CE69EB754C 19.8876 0.7% 4254 0.0047 0.00 SELECT pages # 20 0xCF8D24A96B4A4B96 18.7486 0.7% 7 2.6784 4.33 UPDATE smtp sends # 30 0x55F1CC51FAEEAE8D 11.0347 0.4% 29 0.3805 0.33 INSERT av_cat # MISC 0xMISC 280.3069 10.2% 193766 0.0014 0.0 <385 ITEMS> # Query 1: 0.00 QPS, 0.04x concurrency, ID 0x7C4EDE3BEFD0010D at byte 206163548 # This item is included in the report because it matches --limit. # Scores: V/M = 0.25 # Time range: 2013-09-20 23:05:31 to 2013-09-21 02:53:00 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 6 # Exec time 21 581s 87s 100s 97s 97s 5s 97s # Lock time 0 333us 42us 66us 55us 63us 8us 60us # Rows sent 0 228 6 50 38 49.17 16.47 49.17 # Rows examine 50 2.24G 331.86M 396.46M 382.48M 379.16M 19.24M 379.16M # Query size 0 1.11k 189 190 189.67 183.58 0 183.58 # String: # Databases b2bsy_b2b # Hosts localhost # Users b2bsy_root # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms # 1s # 10s+ ################################################################ # Tables # SHOW TABLE STATUS FROM `b2bsy_b2b` LIKE 't_news'\G # SHOW CREATE TABLE `b2bsy_b2b`.`t_news`\G # SHOW TABLE STATUS FROM `b2bsy_b2b` LIKE 'news_cats'\G # SHOW CREATE TABLE `b2bsy_b2b`.`news_cats`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT * FROM `t_news` WHERE approved = 1 and deleted = 0 and id in (select DISTINCT n_id from news_cats where c_id = 626 order by n_id desc) ORDER BY `sort_date` DESC,`id` DESC limit 0, 50\G # Query 2: 10.71 QPS, 0.02x concurrency, ID 0xCEFAD6A90BFF75CD at byte 190392332 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2013-09-20 20:54:23 to 2013-09-21 03:04:42 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 26 237952 # Exec time 15 437s 1ms 341ms 2ms 2ms 1ms 2ms # Lock time 9 6s 19us 196us 26us 38us 6us 23us # Rows sent 0 232.38k 1 1 1 1 0 1 # Rows examine 7 330.18M 1.42k 1.42k 1.42k 1.42k 0 1.42k # Query size 29 20.42M 90 90 90 90 0 90 # String: # Databases childcli_ccs # Hosts localhost # Users childcli_db # Query_time distribution # 1us # 10us # 100us # 1ms ################################################################ # 10ms # # 100ms # # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `childcli_ccs` LIKE 'videocontent'\G # SHOW CREATE TABLE `childcli_ccs`.`videocontent`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT id,active,btextcode,name FROM videocontent where active=1 order by RAND(20) limit 1\G # Query 3: 0.71 QPS, 0.02x concurrency, ID 0x253746F4D0305402 at byte 185890808 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2013-09-20 20:54:23 to 2013-09-21 03:04:41 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 1 15879 # Exec time 13 359s 19ms 213ms 23ms 24ms 3ms 22ms # Lock time 0 580ms 27us 18ms 36us 36us 249us 30us # Rows sent 0 77.53k 5 5 5 5 0 5 # Rows examine 0 29.97M 1.93k 1.93k 1.93k 1.93k 0 1.93k # Query size 4 2.89M 191 191 191 191 0 191 # String: # Databases childcli_ccs # Hosts localhost # Users childcli_db # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms # # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `childcli_ccs` LIKE 'pages'\G # SHOW CREATE TABLE `childcli_ccs`.`pages`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT id,title,shortdesc,thumbs,date,text,catgoryid FROM pages WHERE catgoryid <> '11' AND catgoryid <> '25' AND catgoryid <> '26' AND catgoryid <> '29' AND active=1 order by rand() limit 5\G # Query 4: 10.71 QPS, 0.01x concurrency, ID 0x36ED1CBF7ACA2E3B at byte 93772805 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2013-09-20 20:54:23 to 2013-09-21 03:04:42 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 26 237952 # Exec time 8 243s 744us 18ms 1ms 1ms 174us 1ms # Lock time 8 6s 17us 489us 23us 28us 4us 21us # Rows sent 0 232.38k 1 1 1 1 0 1 # Rows examine 7 349.24M 1.50k 1.50k 1.50k 1.50k 0 1.50k # Query size 23 16.34M 72 72 72 72 0 72 # String: # Databases childcli_ccs # Hosts localhost # Users childcli_db # Query_time distribution # 1us # 10us # 100us ###################### # 1ms ################################################################ # 10ms # # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `childcli_ccs` LIKE 'signatures'\G # SHOW CREATE TABLE `childcli_ccs`.`signatures`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT id,name_image,url_image FROM signatures order by RAND(20) limit 1\G # Query 5: 2.24 QPS, 0.01x concurrency, ID 0x95A32AF22D1B8058 at byte 93779330 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2013-09-20 20:54:23 to 2013-09-21 03:04:41 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 5 49707 # Exec time 7 202s 3ms 47ms 4ms 6ms 1ms 4ms # Lock time 3 2s 15us 23ms 45us 49us 155us 42us # Rows sent 0 258.42k 0 57 5.32 19.46 6.76 2.90 # Rows examine 1 48.97M 1.01k 1.01k 1.01k 1.01k 0 1.01k # Query size 3 2.51M 52 54 52.90 51.63 0 51.63 # String: # Databases childcli_ccs # Hosts localhost # Users childcli_db # Query_time distribution # 1us # 10us # 100us # 1ms ################################################################ # 10ms # # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `childcli_ccs` LIKE 'pages'\G # SHOW CREATE TABLE `childcli_ccs`.`pages`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT * FROM pages where catgoryid='13' AND active=1\G # Query 6: 0.45 QPS, 0.01x concurrency, ID 0x801BBD3669FBF8BF at byte 192524714 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2013-09-20 20:54:35 to 2013-09-21 03:04:35 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 1 9939 # Exec time 6 167s 15ms 36ms 17ms 19ms 1ms 16ms # Lock time 0 331ms 18us 135us 33us 47us 7us 30us # Rows sent 0 4.95k 0 1 0.51 0.99 0.50 0.99 # Rows examine 13 607.21M 62.56k 62.56k 62.56k 62.56k 0 62.56k # Query size 1 1007.27k 100 104 103.78 102.22 1.36 102.22 # String: # Databases soccer_dbnew # Hosts localhost # Users soccer_deqaq # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `soccer_dbnew` LIKE 'ips_countries'\G # SHOW CREATE TABLE `soccer_dbnew`.`ips_countries`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT * FROM ips_countries WHERE 1593212649 >= Beginning_IP_Number AND 1593212649 <= Ending_IP_Number\G # Query 7: 0.71 QPS, 0.04x concurrency, ID 0x67A347A2812914DF at byte 176036063 # This item is included in the report because it matches --limit. # Scores: V/M = 7.64 # Time range: 2013-09-21 01:01:04 to 01:46:08 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 1914 # Exec time 3 104s 29us 20s 54ms 87ms 643ms 167us # Lock time 0 0 0 0 0 0 0 0 # Rows sent 52 21.90M 0 9.30M 11.72k 13.13k 225.41k 4.96 # Rows examine 0 21.97M 0 9.30M 11.75k 13.13k 225.41k 13.83 # Query size 0 149.76k 43 4.53k 80.12 124.25 110.07 56.92 # String: # Databases horde (617/32%), shabable_2... (128/6%)... 60 more # Hosts localhost # Users root # Query_time distribution # 1us # 10us ##################################### # 100us ################################################################ # 1ms ################## # 10ms ############ # 100ms ##### # 1s # # 10s+ # # Tables # SHOW TABLE STATUS FROM `shabable_vbnew1` LIKE 'post'\G # SHOW CREATE TABLE `shabable_vbnew1`.`post`\G SELECT /*!40001 SQL_NO_CACHE */ * FROM `post`\G # Converted for EXPLAIN # EXPLAIN /*!50100 PARTITIONS*/ SELECT /*!40001 SQL_NO_CACHE */ * FROM `post`\G # Query 8: 0.71 QPS, 0.00x concurrency, ID 0x86251B43B9DDF65A at byte 93728433 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2013-09-20 20:54:23 to 2013-09-21 03:04:41 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 1 15879 # Exec time 2 61s 3ms 21ms 4ms 4ms 532us 4ms # Lock time 1 827ms 20us 9ms 52us 54us 124us 49us # Rows sent 0 46.52k 3 3 3 3 0 3 # Rows examine 0 18.41M 1.19k 1.19k 1.19k 1.19k 0 1.19k # Query size 1 1.33M 88 88 88 88 0 88 # String: # Databases childcli_ccs # Hosts localhost # Users childcli_db # Query_time distribution # 1us # 10us # 100us # 1ms ################################################################ # 10ms # # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `childcli_ccs` LIKE 'pages'\G # SHOW CREATE TABLE `childcli_ccs`.`pages`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT id,title,bimage FROM pages WHERE bimage !='' AND active=1 order by RAND() limit 3\G # Query 9: 3.89 QPS, 0.00x concurrency, ID 0x043FF6692C1BC226 at byte 167526754 # This item is included in the report because it matches --limit. # Scores: V/M = 0.34 # Time range: 2013-09-20 20:54:22 to 2013-09-21 03:04:42 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 9 86429 # Exec time 1 45s 182us 2s 515us 799us 13ms 260us # Lock time 6 4s 18us 2s 47us 44us 6ms 22us # Rows sent 0 77.32k 0 1 0.92 0.99 0.28 0.99 # Rows examine 0 1.43M 15 20 17.38 19.46 1.28 16.81 # Query size 8 5.77M 70 70 70 70 0 70 # String: # Databases childcli_ccs # Hosts localhost # Users childcli_db # Query_time distribution # 1us # 10us # 100us ################################################################ # 1ms # # 10ms # # 100ms # # 1s # # 10s+ # Tables # SHOW TABLE STATUS FROM `childcli_ccs` LIKE 'ads'\G # SHOW CREATE TABLE `childcli_ccs`.`ads`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT * FROM ads WHERE cat='0' AND active='1' ORDER BY rand() LIMIT 1\G # Query 10: 0.05 QPS, 0.00x concurrency, ID 0x62F0C89E29502FE3 at byte 83679032 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2013-09-20 20:54:33 to 2013-09-21 03:04:33 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 1116 # Exec time 1 41s 35ms 55ms 36ms 38ms 1ms 36ms # Lock time 0 35ms 18us 82us 31us 42us 8us 33us # Rows sent 0 19.50k 0 895 17.89 65.89 44.68 5.75 # Rows examine 2 128.61M 118.01k 118.01k 118.01k 118.01k 0 118.01k # Query size 0 69.70k 61 64 63.96 62.76 0.25 62.76 # String: # Databases soccer_dbnew # Hosts localhost # Users soccer_deqaq # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `soccer_dbnew` LIKE 'add_comment'\G # SHOW CREATE TABLE `soccer_dbnew`.`add_comment`\G # EXPLAIN /*!50100 PARTITIONS*/ select * from add_comment where `id_rel`='6456' and `appear`='1'\G # Query 11: 0.73 QPS, 0.00x concurrency, ID 0xBD961FCD0E8FE879 at byte 176178434 # This item is included in the report because it matches --limit. # Scores: V/M = 3.08 # Time range: 2013-09-20 20:54:22 to 2013-09-21 03:04:41 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 1 16274 # Exec time 1 34s 71us 6s 2ms 176us 81ms 98us # Lock time 50 33s 17us 6s 2ms 31us 81ms 21us # Rows sent 0 10.50k 0 2 0.66 0.99 0.47 0.99 # Rows examine 0 2.90M 86 249 186.62 234.30 44.76 192.76 # Query size 1 792.40k 46 52 49.86 51.63 1.43 49.17 # String: # Databases childcli_ccs # Hosts localhost # Users childcli_db # Query_time distribution # 1us # 10us ################################################# # 100us ################################################################ # 1ms # # 10ms # # 100ms # # 1s # # 10s+ # Tables # SHOW TABLE STATUS FROM `childcli_ccs` LIKE 'online'\G # SHOW CREATE TABLE `childcli_ccs`.`online`\G # EXPLAIN /*!50100 PARTITIONS*/ select xtime from online where xip='90.148.19.3'\G # Query 12: 0.38 QPS, 0.00x concurrency, ID 0xC32B386D3651DF02 at byte 183579739 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2013-09-20 20:54:33 to 2013-09-21 03:04:36 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 8462 # Exec time 1 28s 3ms 21ms 3ms 4ms 529us 3ms # Lock time 0 372ms 21us 1ms 43us 54us 24us 44us # Rows sent 0 9.45k 1 2 1.14 1.96 0.34 0.99 # Rows examine 0 28.02M 3.39k 3.39k 3.39k 3.35k 0 3.35k # Query size 1 1.06M 130 133 131.14 130.47 1.50 130.47 # String: # Databases soccer_dbnew # Hosts localhost # Users soccer_deqaq # Query_time distribution # 1us # 10us # 100us # 1ms ################################################################ # 10ms # # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `soccer_dbnew` LIKE 'news'\G # SHOW CREATE TABLE `soccer_dbnew`.`news`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT title,image1,page,id FROM news WHERE Rel_Window = '1' AND issue_id='1' AND page = '6' order by issue_id desc, details_date\G # Query 13: 0.85 QPS, 0.00x concurrency, ID 0xA04424D8B00ECAC4 at byte 203189436 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2013-09-20 20:54:27 to 2013-09-21 03:03:44 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 2 18757 # Exec time 0 23s 866us 4ms 1ms 2ms 262us 1ms # Lock time 0 386ms 12us 523us 20us 31us 7us 18us # Rows sent 0 18.31k 0 1 1.00 0.99 0.02 0.99 # Rows examine 5 263.43M 14.20k 16.62k 14.38k 15.96k 580.27 13.78k # Query size 1 788.61k 39 44 43.05 42.48 0.68 42.48 # String: # Databases industry_a... (17380/92%)... 1 more # Hosts localhost # Users industry_a... (17380/92%)... 1 more # Query_time distribution # 1us # 10us # 100us ########################## # 1ms ################################################################ # 10ms # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `medicalg_medical` LIKE 'comp_per'\G # SHOW CREATE TABLE `medicalg_medical`.`comp_per`\G # EXPLAIN /*!50100 PARTITIONS*/ select * from comp_per where comp_id="5880"\G # Query 14: 0.06 QPS, 0.00x concurrency, ID 0xBCD934C7A7981E7E at byte 146775366 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2013-09-20 20:54:48 to 2013-09-21 03:04:10 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 1357 # Exec time 0 22s 15ms 71ms 16ms 17ms 2ms 16ms # Lock time 0 49ms 22us 3ms 36us 44us 84us 30us # Rows sent 0 13.25k 10 10 10 10 0 10 # Rows examine 0 18.47M 13.94k 13.94k 13.94k 13.94k 0 13.94k # Query size 0 161.67k 122 122 122 122 0 122 # String: # Databases b2bsy_b2b # Hosts localhost # Users b2bsy_root # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `b2bsy_b2b` LIKE 't_news'\G # SHOW CREATE TABLE `b2bsy_b2b`.`t_news`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT id, title, text, img FROM t_news where approved = '1' and deleted = '0' order by sort_date desc, id desc limit 0,10\G # Query 15: 0.18 QPS, 1.97x concurrency, ID 0xD7BF21F1A60B501B at byte 167749136 # This item is included in the report because it matches --limit. # Scores: V/M = 4.16 # Time range: 2013-09-21 01:02:55 to 01:03:06 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 2 # Exec time 0 22s 6s 16s 11s 16s 7s 11s # Lock time 0 87us 43us 44us 43us 44us 0 43us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 2 1 1 1 1 0 1 # Query size 0 119 59 60 59.50 60 0.71 59.50 # String: # Databases b2bsy_b2b # Hosts localhost # Users b2bsy_root # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms # 1s ################################################################ # 10s+ ################################################################ # Tables # SHOW TABLE STATUS FROM `b2bsy_b2b` LIKE 't_news'\G # SHOW CREATE TABLE `b2bsy_b2b`.`t_news`\G update `t_news` set `counter` = '184' where id = 7160\G # Converted for EXPLAIN # EXPLAIN /*!50100 PARTITIONS*/ select `counter` = '184' from `t_news` where id = 7160\G # Query 16: 0.06 QPS, 0.00x concurrency, ID 0x5E0E2E4D89C8B96D at byte 74994399 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2013-09-20 20:54:48 to 2013-09-21 03:04:10 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 1357 # Exec time 0 22s 15ms 25ms 16ms 17ms 1ms 16ms # Lock time 0 31ms 16us 54us 22us 27us 3us 21us # Rows sent 0 6.63k 5 5 5 5 0 5 # Rows examine 0 18.47M 13.93k 13.93k 13.93k 13.93k 0 13.93k # Query size 0 117.94k 89 89 89 89 0 89 # String: # Databases b2bsy_b2b # Hosts localhost # Users b2bsy_root # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms ################################################################ # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `b2bsy_b2b` LIKE 't_news'\G # SHOW CREATE TABLE `b2bsy_b2b`.`t_news`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT * FROM t_news where approved = 1 and deleted = 0 ORDER BY counter DESC LIMIT 0, 5\G # Query 17: 0.24 QPS, 0.00x concurrency, ID 0x49649CDCE6DA22B3 at byte 93769387 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2013-09-20 20:54:23 to 2013-09-21 03:04:41 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 5292 # Exec time 0 21s 3ms 44ms 4ms 4ms 1ms 4ms # Lock time 0 265ms 19us 4ms 49us 54us 101us 44us # Rows sent 0 51.68k 10 10 10 10 0 10 # Rows examine 0 5.26M 1.02k 1.02k 1.02k 1.02k 0 1.02k # Query size 0 439.28k 85 85 85 85 0 85 # String: # Databases childcli_ccs # Hosts localhost # Users childcli_db # Query_time distribution # 1us # 10us # 100us # 1ms ################################################################ # 10ms # # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `childcli_ccs` LIKE 'pages'\G # SHOW CREATE TABLE `childcli_ccs`.`pages`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT id,title,date,visitor FROM pages where active=1 order by visitor DESC LIMIT 10\G # Query 18: 0.24 QPS, 0.00x concurrency, ID 0x046457F0CE3E8295 at byte 93752012 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2013-09-20 20:54:23 to 2013-09-21 03:04:41 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 5300 # Exec time 0 20s 3ms 50ms 4ms 4ms 978us 4ms # Lock time 0 152ms 18us 175us 28us 42us 8us 26us # Rows sent 0 25.88k 5 5 5 5 0 5 # Rows examine 0 5.25M 1.01k 1.01k 1.01k 1.01k 0 1.01k # Query size 0 496.88k 96 96 96 96 0 96 # String: # Databases childcli_ccs # Hosts localhost # Users childcli_db # Query_time distribution # 1us # 10us # 100us # 1ms ################################################################ # 10ms # # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `childcli_ccs` LIKE 'pages'\G # SHOW CREATE TABLE `childcli_ccs`.`pages`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT id,title,rate,votes FROM pages where active=1 and votes != '1' order by rate DESC LIMIT 5\G # Query 19: 0.19 QPS, 0.00x concurrency, ID 0x04D731CE69EB754C at byte 93821858 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2013-09-20 20:54:27 to 2013-09-21 03:04:41 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 4254 # Exec time 0 20s 4ms 14ms 5ms 5ms 395us 5ms # Lock time 0 135ms 27us 85us 31us 36us 2us 30us # Rows sent 0 20.00k 0 5 4.81 4.96 0.79 4.96 # Rows examine 0 4.21M 1.01k 1.01k 1.01k 1012.63 0.00 1012.63 # Query size 1 1.01M 228 293 248.67 271.23 11.68 234.30 # String: # Databases childcli_ccs # Hosts localhost # Users childcli_db # Query_time distribution # 1us # 10us # 100us # 1ms ################################################################ # 10ms # # 100ms # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `childcli_ccs` LIKE 'pages'\G # SHOW CREATE TABLE `childcli_ccs`.`pages`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT id,title,active FROM pages WHERE catgoryid <> '11' AND catgoryid <> '25' AND catgoryid <> '26' AND catgoryid <> '29' AND id != 1052 AND active=1 AND MATCH (title) AGAINST ('????? ?????? ??? ??????? :????? ,?????,?????, ????' IN BOOLEAN MODE) order by title LIMIT 0,5\G # Query 20: 0.21 QPS, 0.55x concurrency, ID 0xCF8D24A96B4A4B96 at byte 193126382 # This item is included in the report because it matches --limit. # Scores: V/M = 4.33 # Time range: 2013-09-21 02:00:43 to 02:01:17 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 7 # Exec time 0 19s 153ms 10s 3s 10s 3s 900ms # Lock time 0 0 0 0 0 0 0 0 # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 31.59k 11 26.75k 4.51k 25.99k 8.88k 652.75 # Query size 0 1.07k 154 157 156.29 151.03 0 151.03 # String: # Databases eximstats # Hosts localhost # Users eximstats # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s ################################ # 10s+ ################ # Tables # SHOW TABLE STATUS FROM `eximstats` LIKE 'smtp'\G # SHOW CREATE TABLE `eximstats`.`smtp`\G # SHOW TABLE STATUS FROM `eximstats` LIKE 'sends'\G # SHOW CREATE TABLE `eximstats`.`sends`\G update smtp INNER JOIN sends ON (sends.msgid=smtp.msgid) set smtp.processed=1 where smtp.transport_is_remote=1 and sends.user='childcli' and smtp.processed=0\G # Converted for EXPLAIN # EXPLAIN /*!50100 PARTITIONS*/ select smtp.processed=1 from smtp INNER JOIN sends ON (sends.msgid=smtp.msgid) where smtp.transport_is_remote=1 and sends.user='childcli' and smtp.processed=0\G # Query 30: 0.00 QPS, 0.00x concurrency, ID 0x55F1CC51FAEEAE8D at byte 175977888 # This item is included in the report because it matches --outliers. # Scores: V/M = 0.33 # Time range: 2013-09-20 22:01:01 to 2013-09-21 02:03:51 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 29 # Exec time 0 11s 102ms 1s 381ms 1s 354ms 155ms # Lock time 0 9ms 65us 2ms 317us 490us 322us 185us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 0 0 0 0 0 0 0 # Query size 0 212.24k 139 106.31k 7.32k 27.29k 19.63k 284.79 # String: # Databases b2bsy_b2b # Hosts localhost # Users b2bsy_root # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s ########## # 10s+ # Tables # SHOW TABLE STATUS FROM `b2bsy_b2b` LIKE 'av_cat'\G # SHOW CREATE TABLE `b2bsy_b2b`.`av_cat`\G INSERT INTO `av_cat` ( `id` ) VALUES ( '14312' ),( '14030' ),( '13578' ),( '11630' ),( '11535' ),( '11350' ),( '11165' ),( '10937' )\G0 -
childcli_ccs SELECT * FROM `t_news` WHERE approved = 1 and deleted = 0 and id in (select DISTINCT n_id from news_cats where c_id = 626 order by n_id desc) ORDER BY `sort_date` DESC,`id` DESC limit 0, 50\G 97 seconds, each execution check if you have indexes (id) or/and (sort_date) on t_news table and (n_id, c_id) on news_cats table the rest is fine, altough its good to replace rand() with something else there are many solutions for faster rand results, not using temporary table, you can check the link that I send in the first page of the topic 0 -
Thanks, I have also sent this query modification to the programmer. But I have a question, even thought we have tweaked the settings and the CPU usage went down a lot, what can we do to improve speed? some sites still feel sluggish while loading . thanks a head for your help 0 -
what do you mean by sluggish ? you mean page generation time or static elements loading ? use tools.pingdom.com to test your site, and let me know the results link to check 0 -
Try using : /http://mysqlcalculator.com This calculates in accordance with your Server RAM available. Please note that make sure you have 45% of RAM When comparing it with mysqlcalculator. For example, If you have 8GB, consider allocating the MySQL memory to be 4GB (This depends upon your requirement) and calculate the mysql variables with /http://mysqlcalculator.com so that the total RAM usage in mysqlcalculator should be 4GB. This is to make sure that you have available free RAM Space for your other PHP files and other files. Let me know if you need any further assistance 0 -
thanks, i will use the tools and report when done. ps: I meant with "Sluggish" the website loading time is slow. Means some elements load before others, and the ones that require MySQL Tables are left till last. :) 0 -
heres few big sites results: B2b-sy.com : Tested from New York City, New York, USA on September 22 at 20:28:54 Page size 2.9MB Load time 6.47s Requests 159 Perf. grade 74/100 childclinic.net : Tested from Amsterdam, Netherlands on September 22 at 20:30:13 Page size 1.9MB Load time 4.54s Requests 147 Perf. grade 70/100 And for Mr Aaron, heres the result I got: 6396 MB Total - I have 8GB RAM . Good/bad ? 0 -
You can call me Just "Aaron". Try the values for 5GB and alter the mysql values accordingly, this is to make space for your webserver in RAM. Can you post the values which you obtained through [url=http://mysqlcalculator.com]MySQL Memory Calculator ? 0 -
I used the same values posted on the First page on My.CNF File. with editing Key Buffer to 2000MB of course as Thinkbot told me. Those values gave me a whopping 6400MB Total for MySQL on MySQL Calculator. Im trying to tune down the values to balance things out 0 -
[quote="nourjabi, post: 1466451">heres few big sites results: B2b-sy.com : Tested from New York City, New York, USA on September 22 at 20:28:54 Page size 2.9MB Load time 6.47s Requests 159 Perf. grade 74/100 childclinic.net : Tested from Amsterdam, Netherlands on September 22 at 20:30:13 Page size 1.9MB Load time 4.54s Requests 147 Perf. grade 70/100 And for Mr Aaron, heres the result I got: 6396 MB Total - I have 8GB RAM . Good/bad ?
Im asking for link to results to see which requests take time "Means some elements load before others, and the ones that require MySQL Tables are left till last." mysql tables ? only page generation time (php) requres mysql tables/connections, the rest is static content don't change mysql settings couse those are good, check the rest provide tools.pingdom.com full result (link) and post your web server config, keepalive and things like that0 -
Ok, i will provide all needed information today. 0
Please sign in to leave a comment.
Comments
27 comments