Ohh, you should remove it first, then flush logs, to recreate the file 
Can you try again, remove old log, then flush logs
Can you try again, remove old log, then flush logs
Could use some help with mysql
- Thread starter eglwolf
- Start date
Here is the updated slow queries report
Code:
# 6.3s user time, 120ms system time, 31.30M rss, 173.67M vsz
# Current date: Mon Nov 18 20:39:07 2013
# Files: /var/lib/mysql/mysql-slow.log
# Overall: 2.66k total, 192 unique, 0.02 QPS, 0.01x concurrency __________
# Time range: 2013-11-17 13:35:08 to 2013-11-18 20:35:09
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 1500s 100ms 16s 565ms 2s 802ms 412ms
# Lock time 3s 0 592ms 1ms 657us 15ms 224us
# Rows sent 32.83k 0 768 12.65 38.53 69.75 0.99
# Rows examine 5.37M 0 48.59k 2.07k 13.78k 5.41k 13.83
# Query size 1.68M 30 15.60k 662.09 1.69k 1004.02 136.99
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ================== ============== ===== ====== ===== ==============
# 1 0xF228C91161D1EC7C 251.7372 16.8% 373 0.6749 0.47 SELECT tables
# 2 0x028D50B38C88D6B2 229.5124 15.3% 373 0.6153 0.20 SELECT tables
# 3 0x2CE3D349814704A5 182.5419 12.2% 524 0.3484 1.19 INSERT maillog
# 4 0xDBA82A339D2FB0E0 66.5901 4.4% 49 1.3590 1.42 UPDATE cscart_stat_requests
# 5 0x1543E6DFC49E1EF0 62.3062 4.2% 76 0.8198 0.06 SELECT cscart_product_feature_variants cscart_product_feature_variant_descriptions cscart_product_features_values cscart_product_features cscart_seo_names
# 6 0x5E9A5EC84A83CE6F 59.4456 4.0% 75 0.7926 1.70 REPLACE cscart_sessions
# 7 0x99AA0165670CE848 59.0842 3.9% 32 1.8464 1.60 ADMIN PREPARE
# 8 0xF64E6026FD36734A 51.4065 3.4% 67 0.7673 2.41 DELETE cscart_stored_sessions
# 9 0xAFC0DD278228530A 42.1812 2.8% 80 0.5273 1.45 REPLACE SELECT cscart_stored_sessions cscart_sessions
# 10 0xAAB1A4BB8262D565 26.8611 1.8% 14 1.9187 8.89 SELECT cscart_orders cscart_order_docs cscart_companies cscart_order_data
# 11 0xA818789EF5D1CA62 25.2133 1.7% 29 0.8694 2.34 SELECT nivxq?o?g_bwps_log
# 12 0xCB9082DBB121AAF6 19.3939 1.3% 30 0.6465 0.11 SELECT cscart_product_feature_variants cscart_product_feature_variant_descriptions cscart_seo_names
# 13 0x73B82FC190CB664A 17.5320 1.2% 24 0.7305 0.82 SELECT cscart_products cscart_product_descriptions cscart_product_prices cscart_products_categories cscart_categories cscart_seo_names
# 14 0xA640B9AD5FA8EE15 15.0605 1.0% 32 0.4706 2.71 REPLACE cscart_sessions
# 15 0xE08A720756D8F9A8 13.5942 0.9% 26 0.5229 0.83 SELECT cscart_products cscart_product_prices cscart_product_descriptions cscart_products_categories cscart_categories cscart_product_popularity cscart_seo_names cscart_product_point_prices cscart_product_sales cscart_discussion
# 16 0x54158123C19799B3 13.4935 0.9% 24 0.5622 1.12 SELECT cscart_bm_snapping cscart_bm_blocks cscart_bm_block_statuses cscart_bm_blocks_descriptions cscart_bm_blocks_content
# 17 0x0437334A74016DC2 13.4038 0.9% 33 0.4062 0.28 SELECT cscart_stat_sessions
# 18 0x9731D62624867446 13.3555 0.9% 25 0.5342 1.59 SELECT pqv?e?a_options
# 19 0x05F042979B03EE6E 12.8398 0.9% 13 0.9877 0.53 SELECT nivxq?o?g_posts nivxq?o?g_term_relationships
# 20 0x6FEF70E8A031CB42 12.4545 0.8% 30 0.4152 0.38 SELECT cscart_cache
# 22 0x2C916F3117CCC32F 9.2010 0.6% 16 0.5751 1.28 INSERT cscart_stat_requests
# 23 0x662017756F8121E1 8.2965 0.6% 11 0.7542 0.38 SELECT cscart_images_links cscart_images cscart_common_descriptions
# 25 0xCA357C524800ED6B 8.0090 0.5% 17 0.4711 0.58 SELECT cscart_stat_ips
# 30 0x0352A56577868FFE 6.8273 0.5% 10 0.6827 2.36 INSERT nivxq?o?g_bwps_log
# MISC 0xMISC 280.0588 18.7% 674 0.4155 0.0 <168 ITEMS>
# Query 1: 0.00 QPS, 0.00x concurrency, ID 0xF228C91161D1EC7C at byte 1199799
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.47
# Time range: 2013-11-17 13:35:09 to 2013-11-18 20:35:09
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 14 373
# Exec time 16 252s 532ms 10s 675ms 900ms 565ms 552ms
# Lock time 2 76ms 158us 381us 204us 287us 41us 185us
# Rows sent 1 373 1 1 1 1 0 1
# Rows examine 6 350.05k 961 961 961 961 0 961
# Query size 1 21.86k 60 60 60 60 0 60
# String:
# Databases information_schema
# Hosts localhost
# Users root
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s ##
# 10s+
# Tables
# SHOW TABLE STATUS FROM `information_schema` LIKE 'tables'\G
# SHOW CREATE TABLE `information_schema`.`tables`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT data_free FROM tables WHERE ENGINE = 'InnoDB' LIMIT 1\G
# Query 2: 0.00 QPS, 0.00x concurrency, ID 0x028D50B38C88D6B2 at byte 1693385
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.20
# Time range: 2013-11-17 13:35:08 to 2013-11-18 20:35:08
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 14 373
# Exec time 15 230s 483ms 4s 615ms 945ms 350ms 501ms
# Lock time 2 79ms 163us 1ms 211us 287us 62us 185us
# Rows sent 1 373 1 1 1 1 0 1
# Rows examine 23 1.29M 3.53k 3.53k 3.53k 3.53k 0 3.53k
# Query size 1 18.58k 51 51 51 51 0 51
# String:
# Databases information_schema
# Hosts localhost
# Users root
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s ###
# 10s+
# Tables
# SHOW TABLE STATUS FROM `information_schema` LIKE 'tables'\G
# SHOW CREATE TABLE `information_schema`.`tables`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT count(*) FROM tables WHERE ENGINE = 'InnoDB'\G
# Query 3: 0.01 QPS, 0.00x concurrency, ID 0x2CE3D349814704A5 at byte 1723799
# This item is included in the report because it matches --limit.
# Scores: V/M = 1.19
# Time range: 2013-11-17 14:10:35 to 2013-11-18 18:04:15
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 19 524
# Exec time 12 183s 102ms 6s 348ms 740ms 645ms 180ms
# Lock time 6 199ms 226us 961us 379us 490us 69us 366us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 43 751.23k 1.19k 5.69k 1.43k 3.35k 750.32 1.20k
# String:
# Databases mailscanner
# Hosts localhost
# Users mailwatch
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s ##
# 10s+
# Tables
# SHOW TABLE STATUS FROM `mailscanner` LIKE 'maillog'\G
# SHOW CREATE TABLE `mailscanner`.`maillog`\G
INSERT INTO maillog (timestamp, id, size, from_address, from_domain, to_address, to_domain, subject, clientip, archive, isspam, ishighspam, issaspam, isrblspam, spamwhitelisted, spamblacklisted, sascore, spamreport, virusinfected, nameinfected, otherinfected, report, ismcp, ishighmcp, issamcp, mcpwhitelisted, mcpblacklisted, mcpsascore, mcpreport, hostname, date, time, headers, quarantined) VALUES ('2013-11-18 13:43:40','1ViT6o-0002Q8-C4','2060','[email protected]','spa-boudoir.com','[email protected]','yahoo.com',' 09-28-2013','165.228.61.252','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'0','0','0','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'server.smbessentials.com','2013-11-18','13:43:40','Received: from mail.bethel.org.au ([165.228.61.252]:2307 helo=serverapps)\n by server.smbessentials.com with esmtpa (Exim 4.80.1)\n (envelope-from <[email protected]>)\n id 1ViT6o-0002Q8-C4\n for [email protected]; Mon, 18 Nov 2013 09:59:39 -0800\nFrom: \"Smith Cole\" <[email protected]>\nSubject: 09-28-2013\nTo: [email protected]\nContent-Type: multipart/alternative; boundary=\"3kUhX5gnUf9tmIHMisg4wkSWuKdQ6a=_Lf0\"\nMIME-Version: 1.0\nReply-To: [email protected]\nDate: Tue, 19 Nov 2013 04:59:38 +1100','0')\G
# Query 4: 0.00 QPS, 0.00x concurrency, ID 0xDBA82A339D2FB0E0 at byte 1691665
# This item is included in the report because it matches --limit.
# Scores: V/M = 1.42
# Time range: 2013-11-17 14:30:52 to 2013-11-18 15:30:59
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 1 49
# Exec time 4 67s 101ms 6s 1s 4s 1s 1s
# Lock time 0 7ms 104us 221us 152us 185us 26us 138us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 23.90k 1 971 499.53 918.49 392.40 685.39
# Query size 0 4.35k 90 91 90.84 88.31 0 88.31
# String:
# Databases lotusm_cs2... (41/83%), plainjan_c... (8/16%)
# Hosts localhost
# Users lotusm_cs3... (41/83%), plainjan_c... (8/16%)
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms #############################################################
# 1s ################################################################
# 10s+
# Tables
# SHOW TABLE STATUS FROM `lotusm_cs2cart` LIKE 'cscart_stat_requests'\G
# SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_stat_requests`\G
UPDATE cscart_stat_requests SET request_type = request_type & 3001 WHERE sess_id = '106688'\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
select request_type = request_type & 3001 from cscart_stat_requests where sess_id = '106688'\G
# Query 5: 0.00 QPS, 0.00x concurrency, ID 0x1543E6DFC49E1EF0 at byte 1230282
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.06
# Time range: 2013-11-17 13:49:26 to 2013-11-18 12:08:53
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 2 76
# Exec time 4 62s 719ms 2s 820ms 1s 228ms 705ms
# Lock time 1 35ms 377us 701us 456us 596us 70us 424us
# Rows sent 9 2.97k 40 40 40 40 0 40
# Rows examine 25 1.37M 18.46k 18.56k 18.51k 18.47k 52 18.47k
# Query size 5 94.07k 1.24k 1.24k 1.24k 1.20k 0 1.20k
# String:
# Databases lotusm_cs2cart
# Hosts localhost
# Users lotusm_cs3admin
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s ##########
# 10s+
# Tables
# SHOW TABLE STATUS FROM `lotusm_cs2cart` LIKE 'cscart_product_feature_variants'\G
# SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_product_feature_variants`\G
# SHOW TABLE STATUS FROM `lotusm_cs2cart` LIKE 'cscart_product_feature_variant_descriptions'\G
# SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_product_feature_variant_descriptions`\G
# SHOW TABLE STATUS FROM `lotusm_cs2cart` LIKE 'cscart_product_features_values'\G
# SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_product_features_values`\G
# SHOW TABLE STATUS FROM `lotusm_cs2cart` LIKE 'cscart_product_features'\G
# SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_product_features`\G
# SHOW TABLE STATUS FROM `lotusm_cs2cart` LIKE 'cscart_seo_names'\G
# SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_seo_names`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT cscart_product_feature_variant_descriptions.*, cscart_product_feature_variants.*, cscart_product_features_values.variant_id as selected, cscart_product_features.feature_type, cscart_seo_names.name as seo_name FROM cscart_product_feature_variants LEFT JOIN cscart_product_feature_variant_descriptions ON cscart_product_feature_variant_descriptions.variant_id = cscart_product_feature_variants.variant_id AND cscart_product_feature_variant_descriptions.lang_code = 'EN' LEFT JOIN cscart_product_features_values ON cscart_product_features_values.variant_id = cscart_product_feature_variants.variant_id AND cscart_product_features_values.lang_code = 'EN' AND cscart_product_features_values.product_id = 246 LEFT JOIN cscart_product_features ON cscart_product_features.feature_id = cscart_product_feature_variants.feature_id LEFT JOIN cscart_seo_names ON cscart_seo_names.object_id = cscart_product_feature_variants.variant_id AND cscart_seo_names.type = 'e' AND cscart_seo_names.dispatch = '' AND cscart_seo_names.lang_code = 'EN' WHERE 1 AND cscart_product_feature_variants.feature_id IN ('14') GROUP BY cscart_product_feature_variants.variant_id ORDER BY cscart_product_feature_variants.position, cscart_product_feature_variant_descriptions.variant LIMIT 0, 40\G
# Query 6: 0.00 QPS, 0.00x concurrency, ID 0x5E9A5EC84A83CE6F at byte 1209724
# This item is included in the report because it matches --limit.
# Scores: V/M = 1.70
# Time range: 2013-11-17 14:02:34 to 2013-11-18 20:11:31
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 2 75
# Exec time 3 59s 101ms 5s 793ms 3s 1s 208ms
# Lock time 0 25ms 173us 1ms 338us 761us 196us 260us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 11 198.67k 951 15.60k 2.65k 6.63k 2.71k 1.39k
# String:
# Databases lotusm_cs2... (64/85%), plainjan_c... (11/14%)
# Hosts localhost
# Users lotusm_cs3... (64/85%), plainjan_c... (11/14%)
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s ####################
# 10s+
# Tables
# SHOW TABLE STATUS FROM `lotusm_cs2cart` LIKE 'cscart_sessions'\G
# SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_sessions`\G
REPLACE INTO cscart_sessions (`session_id`, `area`, `expiry`, `data`) VALUES ('60dc85332ba1abddcc2fdd2c9efbe246', 'C', '1384812463', 'settings|a:2:{s:14:\"cart_languageC\";a:2:{s:5:\"value\";s:2:\"EN\";s:6:\"expiry\";i:1384929686;}s:19:\"secondary_currencyC\";a:2:{s:5:\"value\";s:3:\"USD\";s:6:\"expiry\";i:1384929686;}}_validator_data|a:1:{s:2:\"ua\";s:65:\"Mozilla/5.0 (Windows NT 6.0; rv:25.0) Gecko/20100101 Firefox/25.0\";}auth|a:13:{s:4:\"area\";s:1:\"C\";s:7:\"user_id\";i:0;s:9:\"user_type\";s:1:\"C\";s:10:\"tax_exempt\";s:1:\"N\";s:10:\"last_login\";i:0;s:13:\"usergroup_ids\";a:2:{i:0;i:0;i:1;i:1;}s:9:\"order_ids\";a:0:{}s:11:\"act_as_user\";b:0;s:10:\"this_login\";i:1384805263;s:25:\"password_change_timestamp\";i:0;s:10:\"company_id\";i:0;s:7:\"is_root\";s:1:\"N\";s:7:\"referer\";s:0:\"\";}cart|a:3:{s:8:\"products\";a:0:{}s:11:\"recalculate\";b:0;s:9:\"user_data\";a:0:{}}device|s:0:\"\";twg_user_agent|s:7:\"unknown\";twg_ua|s:65:\"Mozilla/5.0 (Windows NT 6.0; rv:25.0) Gecko/20100101 Firefox/25.0\";cache_mobile_frontend|s:1:\"N\";')\G
# Query 7: 0.00 QPS, 0.00x concurrency, ID 0x99AA0165670CE848 at byte 2050799
# This item is included in the report because it matches --limit.
# Scores: V/M = 1.60
# Time range: 2013-11-18 10:00:28 to 14:45:28
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 1 32
# Exec time 3 59s 102ms 6s 2s 4s 2s 2s
# Lock time 0 0 0 0 0 0 0 0
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 0 960 30 30 30 30 0 30
# String:
# Databases cphulkd
# Hosts localhost
# Users cphulkd
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ######################################
# 1s ################################################################
# 10s+
administrator command: Prepare\G
# Query 8: 0.00 QPS, 0.00x concurrency, ID 0xF64E6026FD36734A at byte 1672109
# This item is included in the report because it matches --limit.
# Scores: V/M = 2.41
# Time range: 2013-11-17 15:11:11 to 2013-11-18 18:45:33
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 2 67
# Exec time 3 51s 101ms 10s 767ms 2s 1s 266ms
# Lock time 0 10ms 93us 245us 152us 185us 30us 144us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 344 0 20 5.13 13.83 4.47 3.89
# Query size 0 3.93k 60 60 60 60 0 60
# String:
# Databases lotusm_cs2... (61/91%), plainjan_c... (4/5%)... 1 more
# Hosts localhost
# Users lotusm_cs3... (61/91%), plainjan_c... (4/5%)... 1 more
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s ###############
# 10s+
# Tables
# SHOW TABLE STATUS FROM `lotusm_cs2cart` LIKE 'cscart_stored_sessions'\G
# SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_stored_sessions`\G
DELETE FROM cscart_stored_sessions WHERE expiry < 1383600344\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
select * from cscart_stored_sessions WHERE expiry < 1383600344\G
# Query 9: 0.00 QPS, 0.00x concurrency, ID 0xAFC0DD278228530A at byte 2012208
# This item is included in the report because it matches --limit.
# Scores: V/M = 1.45
# Time range: 2013-11-17 14:55:05 to 2013-11-18 19:04:13
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 3 80
# Exec time 2 42s 101ms 6s 527ms 2s 875ms 253ms
# Lock time 5 163ms 120us 148ms 2ms 236us 16ms 176us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 1.13k 1 304 14.47 56.92 39.86 4.96
# Query size 0 7.66k 98 98 98 98 0 98
# String:
# Databases lotusm_cs2... (72/90%), plainjan_c... (8/10%)
# Hosts localhost
# Users lotusm_cs3... (72/90%), plainjan_c... (8/10%)
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s ######
# 10s+
# Tables
# SHOW TABLE STATUS FROM `plainjan_cscart1db` LIKE 'cscart_stored_sessions'\G
# SHOW CREATE TABLE `plainjan_cscart1db`.`cscart_stored_sessions`\G
# SHOW TABLE STATUS FROM `plainjan_cscart1db` LIKE 'cscart_sessions'\G
# SHOW CREATE TABLE `plainjan_cscart1db`.`cscart_sessions`\G
REPLACE INTO cscart_stored_sessions SELECT * FROM cscart_sessions WHERE 1 AND expiry < 1384814421\G
# Query 10: 0.00 QPS, 0.00x concurrency, ID 0xAAB1A4BB8262D565 at byte 820768
# This item is included in the report because it matches --limit.
# Scores: V/M = 8.89
# Time range: 2013-11-18 07:46:03 to 14:49:13
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 14
# Exec time 1 27s 331ms 16s 2s 5s 4s 323ms
# Lock time 0 6ms 383us 689us 453us 626us 83us 403us
# Rows sent 0 168 12 12 12 12 0 12
# Rows examine 2 137.99k 9.85k 9.87k 9.86k 9.80k 0 9.80k
# Query size 0 14.40k 1.03k 1.03k 1.03k 1012.63 0 1012.63
# String:
# Databases lotusm_cs2cart
# Hosts localhost
# Users lotusm_cs3admin
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s ###########
# 10s+ #####
# Tables
# SHOW TABLE STATUS FROM `lotusm_cs2cart` LIKE 'cscart_orders'\G
# SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_orders`\G
# SHOW TABLE STATUS FROM `lotusm_cs2cart` LIKE 'cscart_order_docs'\G
# SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_order_docs`\G
# SHOW TABLE STATUS FROM `lotusm_cs2cart` LIKE 'cscart_companies'\G
# SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_companies`\G
# SHOW TABLE STATUS FROM `lotusm_cs2cart` LIKE 'cscart_order_data'\G
# SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_order_data`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT distinct cscart_orders.order_id, cscart_orders.user_id, cscart_orders.is_parent_order, cscart_orders.parent_order_id, cscart_orders.company_id, cscart_orders.timestamp, cscart_orders.firstname, cscart_orders.lastname, cscart_orders.email, cscart_orders.status, cscart_orders.total, invoice_docs.doc_id as invoice_id, memo_docs.doc_id as credit_memo_id, cscart_companies.company as company_name, cscart_order_data.data as points FROM cscart_orders LEFT JOIN cscart_order_docs as invoice_docs ON invoice_docs.order_id = cscart_orders.order_id AND invoice_docs.type = 'I' LEFT JOIN cscart_order_docs as memo_docs ON memo_docs.order_id = cscart_orders.order_id AND memo_docs.type = 'C' LEFT JOIN cscart_companies ON cscart_companies.company_id = cscart_orders.company_id LEFT JOIN cscart_order_data ON cscart_order_data.order_id = cscart_orders.order_id AND cscart_order_data.type = 'W' WHERE 1 AND cscart_orders.is_parent_order != 'Y' AND cscart_orders.status != 'N' ORDER BY cscart_orders.timestamp desc, cscart_orders.order_id desc LIMIT 0, 12\G
# Query 11: 0.00 QPS, 0.00x concurrency, ID 0xA818789EF5D1CA62 at byte 2002150
# This item is included in the report because it matches --limit.
# Scores: V/M = 2.34
# Time range: 2013-11-17 15:27:54 to 2013-11-18 19:02:06
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 1 29
# Exec time 1 25s 224ms 7s 869ms 2s 1s 279ms
# Lock time 0 5ms 152us 279us 186us 247us 34us 167us
# Rows sent 0 29 1 1 1 1 0 1
# Rows examine 12 667.51k 22.99k 23.04k 23.02k 22.45k 0 22.45k
# Query size 0 2.98k 103 108 105.21 102.22 0.93 102.22
# String:
# Databases eco888_wpdb
# Hosts localhost
# Users eco888_wpusr
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s ################
# 10s+
# Tables
# SHOW TABLE STATUS FROM `eco888_wpdb` LIKE 'nivxq1o0g_bwps_log'\G
# SHOW CREATE TABLE `eco888_wpdb`.`nivxq1o0g_bwps_log`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT COUNT(*) FROM `nivxq1o0g_bwps_log` WHERE type=2 AND host='24.197.165.213' AND timestamp > 1384785196\G
# Query 12: 0.00 QPS, 0.00x concurrency, ID 0xCB9082DBB121AAF6 at byte 1212499
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.11
# Time range: 2013-11-17 13:48:56 to 2013-11-18 12:08:13
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 1 30
# Exec time 1 19s 557ms 2s 646ms 816ms 262ms 552ms
# Lock time 0 10ms 274us 520us 324us 445us 56us 301us
# Rows sent 3 1.17k 40 40 40 40 0 40
# Rows examine 7 416.81k 13.86k 13.93k 13.89k 13.78k 0 13.78k
# Query size 1 22.57k 770 771 770.50 755.64 0 755.64
# String:
# Databases lotusm_cs2cart
# Hosts localhost
# Users lotusm_cs3admin
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s ##
# 10s+
# Tables
# SHOW TABLE STATUS FROM `lotusm_cs2cart` LIKE 'cscart_product_feature_variants'\G
# SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_product_feature_variants`\G
# SHOW TABLE STATUS FROM `lotusm_cs2cart` LIKE 'cscart_product_feature_variant_descriptions'\G
# SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_product_feature_variant_descriptions`\G
# SHOW TABLE STATUS FROM `lotusm_cs2cart` LIKE 'cscart_seo_names'\G
# SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_seo_names`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT cscart_product_feature_variant_descriptions.*, cscart_product_feature_variants.*, cscart_seo_names.name as seo_name FROM cscart_product_feature_variants LEFT JOIN cscart_product_feature_variant_descriptions ON cscart_product_feature_variant_descriptions.variant_id = cscart_product_feature_variants.variant_id AND cscart_product_feature_variant_descriptions.lang_code = 'EN' LEFT JOIN cscart_seo_names ON cscart_seo_names.object_id = cscart_product_feature_variants.variant_id AND cscart_seo_names.type = 'e' AND cscart_seo_names.dispatch = '' AND cscart_seo_names.lang_code = 'EN' WHERE 1 AND cscart_product_feature_variants.feature_id IN ('7') ORDER BY cscart_product_feature_variants.position, cscart_product_feature_variant_descriptions.variant LIMIT 0, 40\G
# Query 13: 0.00 QPS, 0.00x concurrency, ID 0x73B82FC190CB664A at byte 784846
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.82
# Time range: 2013-11-17 16:03:39 to 2013-11-18 15:26:08
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 24
# Exec time 1 18s 109ms 3s 731ms 2s 775ms 339ms
# Lock time 1 37ms 620us 16ms 2ms 1ms 3ms 972us
# Rows sent 0 181 1 23 7.54 11.95 4.46 6.98
# Rows examine 0 2.24k 18 279 95.58 212.52 72.31 69.19
# Query size 2 40.27k 1.68k 1.68k 1.68k 1.61k 0 1.61k
# String:
# Databases lotusm_cs2... (20/83%), plainjan_c... (4/16%)
# Hosts localhost
# Users lotusm_cs3... (20/83%), plainjan_c... (4/16%)
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s ##########################
# 10s+
# Tables
# SHOW TABLE STATUS FROM `lotusm_cs2cart` LIKE 'cscart_products'\G
# SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_products`\G
# SHOW TABLE STATUS FROM `lotusm_cs2cart` LIKE 'cscart_product_descriptions'\G
# SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_product_descriptions`\G
# SHOW TABLE STATUS FROM `lotusm_cs2cart` LIKE 'cscart_product_prices'\G
# SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_product_prices`\G
# SHOW TABLE STATUS FROM `lotusm_cs2cart` LIKE 'cscart_products_categories'\G
# SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_products_categories`\G
# SHOW TABLE STATUS FROM `lotusm_cs2cart` LIKE 'cscart_categories'\G
# SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_categories`\G
# SHOW TABLE STATUS FROM `lotusm_cs2cart` LIKE 'cscart_seo_names'\G
# SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_seo_names`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT SQL_CALC_FOUND_ROWS products.*, descr1.product as product, MIN(IF(prices.percentage_discount = 0, prices.price, prices.price - (prices.price * prices.percentage_discount)/100)) as price, descr1.short_description, IF(descr1.short_description = '', descr1.full_description, '') as full_description, GROUP_CONCAT(IF(products_categories.link_type = 'M', CONCAT(products_categories.category_id, 'M'), products_categories.category_id)) as category_ids, products_categories.position, cscart_seo_names.name as seo_name FROM cscart_products as products LEFT JOIN cscart_product_descriptions as descr1 ON descr1.product_id = products.product_id AND descr1.lang_code = 'EN' LEFT JOIN cscart_product_prices as prices ON prices.product_id = products.product_id AND prices.lower_limit = 1 INNER JOIN cscart_products_categories as products_categories ON products_categories.product_id = products.product_id INNER JOIN cscart_categories ON cscart_categories.category_id = products_categories.category_id AND (cscart_categories.usergroup_ids = '' OR FIND_IN_SET(0, cscart_categories.usergroup_ids) OR FIND_IN_SET(1, cscart_categories.usergroup_ids)) AND cscart_categories.status IN ('A', 'H') LEFT JOIN cscart_seo_names ON cscart_seo_names.object_id = products.product_id AND cscart_seo_names.type = 'p' AND cscart_seo_names.dispatch = '' AND cscart_seo_names.lang_code = 'EN' WHERE 1 AND cscart_categories.category_id IN (220) AND products.company_id = 0 AND (products.usergroup_ids = '' OR FIND_IN_SET(0, products.usergroup_ids) OR FIND_IN_SET(1, products.usergroup_ids)) AND products.status IN ('A') AND prices.usergroup_id IN (0, 0, 1) GROUP BY products.product_id ORDER BY products_categories.position asc LIMIT 0, 24\G
# Query 14: 0.00 QPS, 0.00x concurrency, ID 0xA640B9AD5FA8EE15 at byte 839778
# This item is included in the report because it matches --limit.
# Scores: V/M = 2.71
# Time range: 2013-11-17 16:45:51 to 2013-11-18 20:04:29
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 1 32
# Exec time 1 15s 103ms 7s 471ms 857ms 1s 198ms
# Lock time 14 414ms 166us 204ms 13ms 12ms 47ms 224us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 2 35.02k 973 1.55k 1.09k 1.33k 153.48 1.04k
# String:
# Databases detoxrx_CS... (22/68%), son888_CSU... (10/31%)
# Hosts localhost
# Users detoxrx_cs... (22/68%), son888_csu... (10/31%)
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s ##
# 10s+
# Tables
# SHOW TABLE STATUS FROM `son888_CSU1` LIKE 'cscart_sessions'\G
# SHOW CREATE TABLE `son888_CSU1`.`cscart_sessions`\G
REPLACE INTO cscart_sessions (`session_id`, `area`, `expiry`, `data`, `company_id`) VALUES ('b74b30d2e0f4b60e640c5ceabf9e667f', 'C', '1384805969', '_validator_data|a:1:{s:2:\"ua\";s:203:\"Mozilla/5.0 (iPhone; CPU iPhone OS 6_0 like Mac OS X) AppleWebKit/536.26 (KHTML, like Gecko) Version/6.0 Mobile/10A5376e Safari/8536.25 (compatible; Googlebot-Mobile/2.1; +http://www.google.com/bot.html)\";}settings|a:3:{s:14:\"cart_languageC\";a:2:{s:5:\"value\";s:2:\"EN\";s:6:\"expiry\";i:1385403569;}s:19:\"secondary_currencyC\";a:2:{s:5:\"value\";s:3:\"USD\";s:6:\"expiry\";i:1385403569;}s:10:\"company_id\";a:2:{s:5:\"value\";i:1;s:6:\"expiry\";i:1385403569;}}auth|a:13:{s:4:\"area\";s:1:\"C\";s:7:\"user_id\";i:0;s:9:\"user_type\";s:1:\"C\";s:10:\"tax_exempt\";s:1:\"N\";s:10:\"last_login\";i:0;s:13:\"usergroup_ids\";a:2:{i:0;i:0;i:1;i:1;}s:9:\"order_ids\";a:0:{}s:11:\"act_as_user\";b:0;s:10:\"this_login\";i:1384798769;s:25:\"password_change_timestamp\";i:0;s:10:\"company_id\";i:0;s:7:\"is_root\";s:1:\"N\";s:7:\"referer\";s:0:\"\";}cart|a:3:{s:8:\"products\";a:0:{}s:11:\"recalculate\";b:0;s:9:\"user_data\";a:0:{}}device|s:6:\"iphone\";twg_user_agent|s:5:\"phone\";twg_ua|s:203:\"Mozilla/5.0 (iPhone; CPU iPhone OS 6_0 like Mac OS X) AppleWebKit/536.26 (KHTML, like Gecko) Version/6.0 Mobile/10A5376e Safari/8536.25 (compatible; Googlebot-Mobile/2.1; +http://www.google.com/bot.html)\";cache_mobile_frontend|s:1:\"Y\";security_hash|s:32:\"955a5cc31cbf47323861dbf963ff3603\";', '1')\G
# Query 15: 0.00 QPS, 0.00x concurrency, ID 0xE08A720756D8F9A8 at byte 2023029
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.83
# Time range: 2013-11-17 13:48:08 to 2013-11-18 19:15:47
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 26
# Exec time 0 14s 107ms 3s 523ms 1s 661ms 253ms
# Lock time 0 27ms 756us 2ms 1ms 1ms 281us 1ms
# Rows sent 0 26 1 1 1 1 0 1
# Rows examine 0 601 6 44 23.12 42.48 10.91 19.46
# Query size 3 61.37k 2.36k 2.36k 2.36k 2.27k 0 2.27k
# String:
# Databases lotusm_cs2... (18/69%), plainjan_c... (8/30%)
# Hosts localhost
# Users lotusm_cs3... (18/69%), plainjan_c... (8/30%)
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s ###############
# 10s+
# Tables
# SHOW TABLE STATUS FROM `lotusm_cs2cart` LIKE 'cscart_products'\G
# SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_products`\G
# SHOW TABLE STATUS FROM `lotusm_cs2cart` LIKE 'cscart_product_prices'\G
# SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_product_prices`\G
# SHOW TABLE STATUS FROM `lotusm_cs2cart` LIKE 'cscart_product_descriptions'\G
# SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_product_descriptions`\G
# SHOW TABLE STATUS FROM `lotusm_cs2cart` LIKE 'cscart_products_categories'\G
# SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_products_categories`\G
# SHOW TABLE STATUS FROM `lotusm_cs2cart` LIKE 'cscart_categories'\G
# SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_categories`\G
# SHOW TABLE STATUS FROM `lotusm_cs2cart` LIKE 'cscart_product_popularity'\G
# SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_product_popularity`\G
# SHOW TABLE STATUS FROM `lotusm_cs2cart` LIKE 'cscart_seo_names'\G
# SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_seo_names`\G
# SHOW TABLE STATUS FROM `lotusm_cs2cart` LIKE 'cscart_product_point_prices'\G
# SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_product_point_prices`\G
# SHOW TABLE STATUS FROM `lotusm_cs2cart` LIKE 'cscart_product_sales'\G
# SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_product_sales`\G
# SHOW TABLE STATUS FROM `lotusm_cs2cart` LIKE 'cscart_discussion'\G
# SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_discussion`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT cscart_products.*, cscart_product_descriptions.*, MIN(IF(cscart_product_prices.percentage_discount = 0, cscart_product_prices.price, cscart_product_prices.price - (cscart_product_prices.price * cscart_product_prices.percentage_discount)/100)) as price, GROUP_CONCAT(IF(cscart_products_categories.link_type = 'M', CONCAT(cscart_products_categories.category_id, 'M'), cscart_products_categories.category_id)) as category_ids, popularity.total as popularity, cscart_seo_names.name as seo_name, MIN(point_prices.point_price) as point_price, cscart_product_sales.amount as sales_amount, cscart_discussion.type as discussion_type FROM cscart_products LEFT JOIN cscart_product_prices ON cscart_product_prices.product_id = cscart_products.product_id AND cscart_product_prices.lower_limit = 1 AND cscart_product_prices.usergroup_id IN (0, 0, 1) LEFT JOIN cscart_product_descriptions ON cscart_product_descriptions.product_id = cscart_products.product_id AND cscart_product_descriptions.lang_code = 'EN' INNER JOIN cscart_products_categories ON cscart_products_categories.product_id = cscart_products.product_id INNER JOIN cscart_categories ON cscart_categories.category_id = cscart_products_categories.category_id AND (cscart_categories.usergroup_ids = '' OR FIND_IN_SET(0, cscart_categories.usergroup_ids) OR FIND_IN_SET(1, cscart_categories.usergroup_ids)) AND (cscart_products.usergroup_ids = '' OR FIND_IN_SET(0, cscart_products.usergroup_ids) OR FIND_IN_SET(1, cscart_products.usergroup_ids)) AND cscart_categories.status IN ('A', 'H') AND cscart_products.status IN ('A', 'H') LEFT JOIN cscart_product_popularity as popularity ON popularity.product_id = cscart_products.product_id LEFT JOIN cscart_seo_names ON cscart_seo_names.object_id = 170 AND cscart_seo_names.type = 'p' AND cscart_seo_names.dispatch = '' AND cscart_seo_names.lang_code = 'EN' LEFT JOIN cscart_product_point_prices as point_prices ON point_prices.product_id = cscart_products.product_id AND point_prices.lower_limit = 1 AND point_prices.usergroup_id IN (0, 0, 1) LEFT JOIN cscart_product_sales ON cscart_product_sales.product_id = cscart_products.product_id AND cscart_product_sales.category_id = 82 LEFT JOIN cscart_discussion ON cscart_discussion.object_id = cscart_products.product_id AND cscart_discussion.object_type = 'P' WHERE cscart_products.product_id = 170 AND cscart_products.company_id = 0 GROUP BY cscart_products.product_id\G
# Query 16: 0.00 QPS, 0.00x concurrency, ID 0x54158123C19799B3 at byte 856537
# This item is included in the report because it matches --limit.
# Scores: V/M = 1.12
# Time range: 2013-11-17 16:03:39 to 2013-11-18 16:32:50
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 24
# Exec time 0 13s 102ms 3s 562ms 2s 793ms 213ms
# Lock time 0 16ms 493us 933us 664us 799us 138us 708us
# Rows sent 1 541 18 31 22.54 30.19 4.50 20.43
# Rows examine 0 5.83k 164 389 248.58 381.65 76.04 223.14
# Query size 2 34.37k 1.42k 1.45k 1.43k 1.39k 0 1.39k
# String:
# Databases lotusm_cs2... (14/58%), plainjan_c... (10/41%)
# Hosts localhost
# Users lotusm_cs3... (14/58%), plainjan_c... (10/41%)
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s ############
# 10s+
# Tables
# SHOW TABLE STATUS FROM `plainjan_cscart1db` LIKE 'cscart_bm_snapping'\G
# SHOW CREATE TABLE `plainjan_cscart1db`.`cscart_bm_snapping`\G
# SHOW TABLE STATUS FROM `plainjan_cscart1db` LIKE 'cscart_bm_blocks'\G
# SHOW CREATE TABLE `plainjan_cscart1db`.`cscart_bm_blocks`\G
# SHOW TABLE STATUS FROM `plainjan_cscart1db` LIKE 'cscart_bm_block_statuses'\G
# SHOW CREATE TABLE `plainjan_cscart1db`.`cscart_bm_block_statuses`\G
# SHOW TABLE STATUS FROM `plainjan_cscart1db` LIKE 'cscart_bm_blocks_descriptions'\G
# SHOW CREATE TABLE `plainjan_cscart1db`.`cscart_bm_blocks_descriptions`\G
# SHOW TABLE STATUS FROM `plainjan_cscart1db` LIKE 'cscart_bm_blocks_content'\G
# SHOW CREATE TABLE `plainjan_cscart1db`.`cscart_bm_blocks_content`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT cscart_bm_snapping.grid_id as grid_id,cscart_bm_snapping.block_id as block_id,IFNULL(dynamic_object_content.content, default_content.content) as content,cscart_bm_block_statuses.object_ids as object_ids,cscart_bm_snapping.*,cscart_bm_blocks.*,cscart_bm_blocks_descriptions.* FROM cscart_bm_snapping LEFT JOIN cscart_bm_blocks ON cscart_bm_blocks.block_id = cscart_bm_snapping.block_id LEFT JOIN cscart_bm_block_statuses ON cscart_bm_snapping.snapping_id = cscart_bm_block_statuses.snapping_id AND cscart_bm_block_statuses.object_type LIKE 'pages' LEFT JOIN cscart_bm_blocks_descriptions ON cscart_bm_blocks.block_id = cscart_bm_blocks_descriptions.block_id LEFT JOIN cscart_bm_blocks_content AS default_content ON cscart_bm_blocks.block_id = default_content.block_id AND cscart_bm_blocks_descriptions.lang_code = default_content.lang_code AND default_content.snapping_id = 0 AND default_content.object_id = 0 AND default_content.object_type like '' LEFT JOIN cscart_bm_blocks_content AS dynamic_object_content ON cscart_bm_blocks.block_id = dynamic_object_content.block_id AND cscart_bm_blocks_descriptions.lang_code = dynamic_object_content.lang_code AND dynamic_object_content.object_id = 31 AND dynamic_object_content.object_type like 'pages' WHERE cscart_bm_snapping.grid_id IN (11, 36, 37, 109, 110, 42, 43, 44, 45, 52, 53, 62, 65) AND cscart_bm_blocks_descriptions.lang_code='EN' ORDER BY cscart_bm_snapping.order, cscart_bm_snapping.block_id\G
# Query 17: 0.00 QPS, 0.00x concurrency, ID 0x0437334A74016DC2 at byte 816055
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.28
# Time range: 2013-11-17 14:34:49 to 2013-11-18 20:16:03
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 1 33
# Exec time 0 13s 101ms 2s 406ms 1s 336ms 293ms
# Lock time 0 7ms 127us 333us 211us 247us 38us 204us
# Rows sent 0 4 0 1 0.12 0.99 0.32 0
# Rows examine 0 8 0 2 0.24 1.96 0.64 0
# Query size 0 4.74k 147 147 147 147 0 147
# String:
# Databases lotusm_cs2... (30/90%), plainjan_c... (3/9%)
# Hosts localhost
# Users lotusm_cs3... (30/90%), plainjan_c... (3/9%)
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s ######
# 10s+
# Tables
# SHOW TABLE STATUS FROM `plainjan_cscart1db` LIKE 'cscart_stat_sessions'\G
# SHOW CREATE TABLE `plainjan_cscart1db`.`cscart_stat_sessions`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT sess_id FROM cscart_stat_sessions WHERE session = 'dfb858e601926752b716ce9321d48879' AND expiry > 1384798534 ORDER BY timestamp DESC LIMIT 1\G
# Query 18: 0.00 QPS, 0.00x concurrency, ID 0x9731D62624867446 at byte 1337322
# This item is included in the report because it matches --limit.
# Scores: V/M = 1.59
# Time range: 2013-11-17 14:28:52 to 2013-11-18 15:32:02
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 25
# Exec time 0 13s 101ms 4s 534ms 2s 921ms 122ms
# Lock time 0 4ms 118us 268us 161us 214us 34us 144us
# Rows sent 26 8.67k 355 355 355 355 0 355
# Rows examine 0 9.50k 389 389 389 389 0 389
# Query size 0 1.86k 76 76 76 76 0 76
# String:
# Databases cookingw_cwe
# Hosts localhost
# Users cookingw_cwe
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s ########
# 10s+
# Tables
# SHOW TABLE STATUS FROM `cookingw_cwe` LIKE 'pqv7e1a_options'\G
# SHOW CREATE TABLE `cookingw_cwe`.`pqv7e1a_options`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT option_name, option_value FROM pqv7e1a_options WHERE autoload = 'yes'\G
# Query 19: 0.00 QPS, 0.00x concurrency, ID 0x05F042979B03EE6E at byte 1361141
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.53
# Time range: 2013-11-18 10:13:29 to 19:13:51
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 13
# Exec time 0 13s 157ms 3s 988ms 2s 721ms 1s
# Lock time 0 4ms 220us 385us 284us 366us 59us 280us
# Rows sent 0 41 1 10 3.15 6.98 2.87 0.99
# Rows examine 0 264 4 68 20.31 65.89 23.12 3.89
# Query size 0 5.23k 411 412 411.62 400.73 0 400.73
# String:
# Databases eco888_wpdb
# Hosts localhost
# Users eco888_wpusr
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s ######################################################
# 10s+
# Tables
# SHOW TABLE STATUS FROM `eco888_wpdb` LIKE 'nivxq1o0g_posts'\G
# SHOW CREATE TABLE `eco888_wpdb`.`nivxq1o0g_posts`\G
# SHOW TABLE STATUS FROM `eco888_wpdb` LIKE 'nivxq1o0g_term_relationships'\G
# SHOW CREATE TABLE `eco888_wpdb`.`nivxq1o0g_term_relationships`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT SQL_CALC_FOUND_ROWS nivxq1o0g_posts.ID FROM nivxq1o0g_posts INNER JOIN nivxq1o0g_term_relationships ON (nivxq1o0g_posts.ID = nivxq1o0g_term_relationships.object_id) WHERE 1=1 AND ( nivxq1o0g_term_relationships.term_taxonomy_id IN (150) ) AND nivxq1o0g_posts.post_type = 'post' AND (nivxq1o0g_posts.post_status = 'publish') GROUP BY nivxq1o0g_posts.ID ORDER BY nivxq1o0g_posts.post_date DESC LIMIT 0, 10\G
# Query 20: 0.00 QPS, 0.00x concurrency, ID 0x6FEF70E8A031CB42 at byte 838157
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.38
# Time range: 2013-11-17 15:01:07 to 2013-11-18 15:26:54
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 1 30
# Exec time 0 12s 118ms 2s 415ms 1s 398ms 293ms
# Lock time 0 6ms 156us 294us 211us 247us 33us 214us
# Rows sent 0 30 1 1 1 1 0 1
# Rows examine 0 30 1 1 1 1 0 1
# Query size 0 3.70k 78 164 126.43 158.58 26.63 107.34
# String:
# Databases son888_CSU... (20/66%), detoxrx_CS... (10/33%)
# Hosts localhost
# Users son888_csu... (20/66%), detoxrx_cs... (10/33%)
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s #######
# 10s+
# Tables
# SHOW TABLE STATUS FROM `son888_CSU1` LIKE 'cscart_cache'\G
# SHOW CREATE TABLE `son888_CSU1`.`cscart_cache`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT data, expiry FROM cscart_cache WHERE name = 'settings.cache_customer' AND company_id = 1\G
# Query 22: 0.00 QPS, 0.00x concurrency, ID 0x2C916F3117CCC32F at byte 1958350
# This item is included in the report because it matches --outliers.
# Scores: V/M = 1.28
# Time range: 2013-11-17 13:55:25 to 2013-11-18 17:23:13
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 16
# Exec time 0 9s 104ms 3s 575ms 3s 859ms 198ms
# Lock time 0 3ms 145us 270us 187us 247us 39us 167us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 0 4.92k 206 412 315.19 400.73 64.01 329.68
# String:
# Databases lotusm_cs2... (13/81%), plainjan_c... (3/18%)
# Hosts localhost
# Users lotusm_cs3... (13/81%), plainjan_c... (3/18%)
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s #########
# 10s+
# Tables
# SHOW TABLE STATUS FROM `lotusm_cs2cart` LIKE 'cscart_stat_requests'\G
# SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_stat_requests`\G
INSERT INTO cscart_stat_requests (`sess_id`, `timestamp`, `url`, `title`, `https`, `loadtime`, `request_type`) VALUES ('106688', '1384813815', '/index.php?dispatch=auth.login_form&return_url=index.php%3Fdispatch%3Dcategories.view%26category_id%3D80%26sort_by%3Dprice%26sort_order%3Ddesc', 'My account - ', 'Y', '0.73178601265', '3003')\G
# Query 23: 0.00 QPS, 0.00x concurrency, ID 0x662017756F8121E1 at byte 2054004
# This item is included in the report because it matches --outliers.
# Scores: V/M = 0.38
# Time range: 2013-11-17 13:35:43 to 2013-11-18 14:45:56
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 11
# Exec time 0 8s 118ms 2s 754ms 2s 533ms 596ms
# Lock time 0 11ms 293us 7ms 1ms 515us 2ms 395us
# Rows sent 0 11 1 1 1 1 0 1
# Rows examine 0 26 2 4 2.36 3.89 0.74 1.96
# Query size 0 7.45k 693 695 693.82 685.39 0 685.39
# String:
# Databases lotusm_cs2... (7/63%), plainjan_c... (4/36%)
# Hosts localhost
# Users lotusm_cs3... (7/63%), plainjan_c... (4/36%)
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s ########################
# 10s+
# Tables
# SHOW TABLE STATUS FROM `lotusm_cs2cart` LIKE 'cscart_images_links'\G
# SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_images_links`\G
# SHOW TABLE STATUS FROM `lotusm_cs2cart` LIKE 'cscart_images'\G
# SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_images`\G
# SHOW TABLE STATUS FROM `lotusm_cs2cart` LIKE 'cscart_common_descriptions'\G
# SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_common_descriptions`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT cscart_images_links.*, cscart_images.image_path, cscart_common_descriptions.description AS alt, cscart_images.image_x, cscart_images.image_y, cscart_images.image_id as images_image_id FROM cscart_images_links LEFT JOIN cscart_images ON cscart_images_links.image_id = cscart_images.image_id LEFT JOIN cscart_common_descriptions ON cscart_common_descriptions.object_id = cscart_images.image_id AND cscart_common_descriptions.object_holder = 'images' AND cscart_common_descriptions.lang_code = 'EN' WHERE cscart_images_links.object_type = 'product' AND cscart_images_links.type = 'M' AND cscart_images_links.object_id = 336 ORDER BY cscart_images_links.position, cscart_images_links.pair_id\G
# Query 25: 0.00 QPS, 0.00x concurrency, ID 0xCA357C524800ED6B at byte 817052
# This item is included in the report because it matches --outliers.
# Scores: V/M = 0.58
# Time range: 2013-11-17 18:44:39 to 2013-11-18 13:53:36
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 17
# Exec time 0 8s 101ms 2s 471ms 1s 523ms 266ms
# Lock time 0 3ms 136us 232us 174us 204us 26us 172us
# Rows sent 0 9 0 1 0.53 0.99 0.50 0.99
# Rows examine 14 782.35k 5.05k 48.59k 46.02k 46.68k 9.82k 46.68k
# Query size 0 935 55 55 55 55 0 55
# String:
# Databases lotusm_cs2... (16/94%), plainjan_c... (1/5%)
# Hosts localhost
# Users lotusm_cs3... (16/94%), plainjan_c... (1/5%)
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s ########
# 10s+
# Tables
# SHOW TABLE STATUS FROM `plainjan_cscart1db` LIKE 'cscart_stat_ips'\G
# SHOW CREATE TABLE `plainjan_cscart1db`.`cscart_stat_ips`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT ip_id FROM cscart_stat_ips WHERE ip = 1279679393\G
# Query 30: 0.00 QPS, 0.00x concurrency, ID 0x0352A56577868FFE at byte 1696559
# This item is included in the report because it matches --outliers.
# Scores: V/M = 2.36
# Time range: 2013-11-17 15:27:54 to 2013-11-18 16:31:08
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 10
# Exec time 0 7s 100ms 4s 683ms 1s 1s 134ms
# Lock time 0 2ms 116us 357us 189us 224us 64us 152us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 0 2.33k 168 305 238.60 284.79 56.01 258.32
# String:
# Databases eco888_wpdb
# Hosts localhost
# Users eco888_wpusr
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s ################
# 10s+
# Tables
# SHOW TABLE STATUS FROM `eco888_wpdb` LIKE 'nivxq1o0g_bwps_log'\G
# SHOW CREATE TABLE `eco888_wpdb`.`nivxq1o0g_bwps_log`\G
INSERT INTO `nivxq1o0g_bwps_log` (`type`,`timestamp`,`host`,`user`,`username`,`url`,`referrer`,`data`) VALUES ('2','1384781737','146.0.74.202','0','','/not_found','','')\Gthere is very few queries (2,66k in 1,25 day) so there is not much to say and it would be inaccurate
You had before few millions, do you have some bursts of traffic ?
It would be good if you could post results after such burst of traffic
You had before few millions, do you have some bursts of traffic ?
It would be good if you could post results after such burst of traffic
Yes, we do get bursts of traffic when we do our email marketing. Here are some new results. If I understand this right, slow queries is not a problem any more?
Code:
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 568M (Tables: 3514)
[--] Data in InnoDB tables: 240K (Tables: 15)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 49
-------- Performance Metrics -------------------------------------------------
[--] Up for: 8d 16h 52m 53s (9M q [12.072 qps], 139K conn, TX: 11B, RX: 3B)
[--] Reads / Writes: 60% / 40%
[--] Total buffers: 321.0M global + 1.9M per thread (100 max threads)
[OK] Maximum possible memory usage: 508.5M (25% of installed RAM)
[OK] Slow queries: 0% (11K/9M)
[OK] Highest usage of available connections: 40% (40/100)
[OK] Key buffer size / total MyISAM indexes: 200.0M/108.4M
[OK] Key buffer hit rate: 99.9% (35M cached / 48K reads)
[OK] Query cache efficiency: 86.1% (6M cached / 7M selects)
[!!] Query cache prunes per day: 25608
[OK] Sorts requiring temporary tables: 0% (421 temp sorts / 126K sorts)
[!!] Temporary tables created on disk: 32% (198K on disk / 608K total)
[OK] Thread cache hit rate: 99% (100 created / 139K connections)
[!!] Table cache hit rate: 0% (3K open / 8M opened)
[OK] Open file limit used: 34% (7K/21K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
[OK] InnoDB data size / buffer pool: 240.0K/50.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
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 (> 30M)
tmp_table_size (> 25M)
max_heap_table_size (> 25M)
table_cache (> 4000)[!!] Temporary tables created on disk: 32% (198K on disk / 608K total)
You got a lot of queries using temporary tables on disk, those should be optimized
[!!] Table cache hit rate: 0% (3K open / 8M opened)
and tons of temporary tables created in memory
all this can be optimized
is your code custom written ? or its some CMS ?
It would be good if you installed MariaDB or Percona instead of default Mysql, this would give you extended info in slowlog
You got a lot of queries using temporary tables on disk, those should be optimized
[!!] Table cache hit rate: 0% (3K open / 8M opened)
and tons of temporary tables created in memory
all this can be optimized
is your code custom written ? or its some CMS ?
It would be good if you installed MariaDB or Percona instead of default Mysql, this would give you extended info in slowlog