Could use some help with mysql
MySQLTuner makes suggestions on how to tweak mysql but I am not sure how to do that, or what to change the my.cnf to.
Here is the output from MySQLTuner
Here is what is in my.cnf
My Server Details:
>> MySQLTuner 1.2.0_1 - Major Hayden
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.32-cll
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 508M (Tables: 3514)
[--] Data in InnoDB tables: 224K (Tables: 14)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 6
-------- Performance Metrics -------------------------------------------------
[--] Up for: 12s (14 q [1.167 qps], 7 conn, TX: 10K, RX: 850)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 544.0M global + 2.8M per thread (500 max threads)
[!!] Maximum possible memory usage: 1.9G (96% of installed RAM)
[OK] Slow queries: 0% (0/14)
[OK] Highest usage of available connections: 0% (1/500)
[!!] Key buffer size / total MyISAM indexes: 48.0M/105.3M
[!!] Query cache efficiency: 0.0% (0 cached / 6 selects)
[OK] Query cache prunes per day: 0
[OK] Temporary tables created on disk: 0% (0 on disk / 2 total)
[OK] Thread cache hit rate: 85% (1 created / 7 connections)
[OK] Table cache hit rate: 78% (26 open / 33 opened)
[OK] Open file limit used: 0% (19/66K)
[OK] Table locks acquired immediately: 100% (36 immediate / 36 locks)
[OK] InnoDB data size / buffer pool: 224.0K/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
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
key_buffer_size (> 105.3M)
query_cache_limit (> 1M, or use smaller result sets)
Here is what is in my.cnf
[mysqld]
tmp_table_size=256M
read_buffer_size=128K
query_cache_type=1
query_cache_limit=1M
connect_timeout=10
query_cache_size=96M
join_buffer_size=128K
key_buffer=48M
local-infile=0
max_heap_table_size=256M
log-slow-queries="/var/lib/mysql/slow.log"
long_query_time=5
max_connections=500
max_connect_errors=10
thread_cache_size=4
open_files_limit=21412
table_cache=32K
max_allowed_packet=1M
default-storage-engine=MyISAM
innodb_file_per_table=1
My Server Details:
Processor Information
Total processors: 4
Processor #1
Vendor
GenuineIntel
Name
Intel(R) Atom(TM) CPU D525 @ 1.80GHz
Speed
1800.063 MHz
Cache
512 KB
Processor #2
Vendor
GenuineIntel
Name
Intel(R) Atom(TM) CPU D525 @ 1.80GHz
Speed
1800.063 MHz
Cache
512 KB
Processor #3
Vendor
GenuineIntel
Name
Intel(R) Atom(TM) CPU D525 @ 1.80GHz
Speed
1800.063 MHz
Cache
512 KB
Processor #4
Vendor
GenuineIntel
Name
Intel(R) Atom(TM) CPU D525 @ 1.80GHz
Speed
1800.063 MHz
Cache
512 KB
Memory Information
Memory for crash kernel (0x0 to 0x0) notwithin permissible range
Memory: 2038716k/2080768k available (2617k kernel code, 40900k reserved, 1671k data, 224k init)
System Information
Linux server._______.com 2.6.18-308.8.2.el5 #1 SMP Tue Jun 12 09:58:12 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux
Physical Disks
SCSI device sda: 625142448 512-byte hdwr sectors (320073 MB)
sda: Write Protect is off
sda: Mode Sense: 00 3a 00 00
SCSI device sda: drive cache: write back
SCSI device sda: 625142448 512-byte hdwr sectors (320073 MB)
sda: Write Protect is off
sda: Mode Sense: 00 3a 00 00
SCSI device sda: drive cache: write back
sd 0:0:0:0: Attached scsi disk sda
SCSI device sdb: 625142448 512-byte hdwr sectors (320073 MB)
sdb: Write Protect is off
sdb: Mode Sense: 00 3a 00 00
SCSI device sdb: drive cache: write back
SCSI device sdb: 625142448 512-byte hdwr sectors (320073 MB)
sdb: Write Protect is off
sdb: Mode Sense: 00 3a 00 00
SCSI device sdb: drive cache: write back
sd 1:0:0:0: Attached scsi disk sdb
sd 0:0:0:0: Attached scsi generic sg0 type 0
sd 1:0:0:0: Attached scsi generic sg1 type 0
Current Memory Usage
total used free shared buffers cached
Mem: 2041628 1987532 54096 0 22628 763736
-/+ buffers/cache: 1201168 840460
Swap: 4192944 510612 3682332
Total: 6234572 2498144 3736428
Current Disk Usage
Filesystem Size Used Avail Use% Mounted on
/dev/md1 287G 36G 237G 13% /
/dev/md0 99M 25M 70M 26% /boot
tmpfs 997M 0 997M 0% /dev/shm
/usr/tmpDSK 2.7G 1.7G 861M 67% /tmp
-
You need to leave Mysql running for at least 24 hours ( 48 hours is better) before running that Mysqltuner test to get an accurate picture of what's going on. Secondly, as the report suggests you need to add more physical RAM to your server. 2GB isn't really enough for a shared hosting environment. 0 -
No need to add more, you just need to optimize the settings a bit: [mysqld] skip-name-resolve local-infile=0 default-storage-engine=MyISAM max_connections = 100 max_user_connections = 50 max_connect_errors = 10 connect_timeout=10 tmp_table_size=25M max_heap_table_size=25M key_buffer_size=200M join_buffer_size=256K read_buffer_size=128K sort_buffer_size=256K read_rnd_buffer_size=1M query_cache_type=1 query_cache_limit=1M query_cache_size=30M slow_query_log=1 slow_query_log_file=mysql-slow.log long_query_time=0.1 thread_cache_size=16 table_open_cache=4000 open_files_limit=21412 max_allowed_packet=8M innodb_file_per_table=1 innodb_buffer_pool_size = 50M and in the end of /etc/sysctl.conf add vm.swappiness = 10 save, then in ssh run: sysctl -p 0 -
I will try that and report back my findings. Thanks 0 -
Here is the update tuning report -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.5.32-cll [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 511M (Tables: 3514) [--] Data in InnoDB tables: 224K (Tables: 14) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [!!] Total fragmented tables: 26 -------- Performance Metrics ------------------------------------------------- [--] Up for: 22h 42m 21s (1M q [12.999 qps], 15K conn, TX: 1B, RX: 450M) [--] Reads / Writes: 64% / 36% [--] 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% (823/1M) [OK] Highest usage of available connections: 55% (55/100) [OK] Key buffer size / total MyISAM indexes: 200.0M/106.3M [OK] Key buffer hit rate: 99.8% (3M cached / 6K reads) [OK] Query cache efficiency: 84.9% (769K cached / 906K selects) [!!] Query cache prunes per day: 26430 [OK] Sorts requiring temporary tables: 0% (8 temp sorts / 16K sorts) [!!] Temporary tables created on disk: 44% (19K on disk / 43K total) [OK] Thread cache hit rate: 99% (88 created / 15K connections) [OK] Table cache hit rate: 99% (3K open / 3K opened) [OK] Open file limit used: 34% (7K/21K) [OK] Table locks acquired immediately: 99% (261K immediate / 262K locks) [OK] InnoDB data size / buffer pool: 224.0K/50.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries without LIMIT clauses Variables to adjust: query_cache_size (> 30M) tmp_table_size (> 25M) max_heap_table_size (> 25M)0 -
Hello :) Do you notice an improvement in MySQL since the changes? Thank you. 0 -
Great, [OK] Maximum possible memory usage: 508.5M (25% of installed RAM) you can also post again all your "My Server Details" exactly "free -m" Another thing to do is optimizing your queries "[!!] Temporary tables created on disk: 44% (19K on disk / 43K total)" The ones that use temporary tables on disk If you are using some CMS, those bad queries are most likely in plugins 0 -
@cPanelMichael - the server seems to be running better. Although I suspect I will have some other tuning to do such as apache and/or php if that is possible. @thinkbot - How do I optimize my queries, we use wordpress and cs-cart on the server mainly. Here are the results of "free -m" root@server [/etc]# free -m total used free shared buffers cached Mem: 1993 1867 126 0 26 985 -/+ buffers/cache: 855 1138 Swap: 4094 602 3491 Do I need to worry about: tmp_table_size (> 25M) max_heap_table_size (> 25M) 0 -
Here is the results # 11.6s user time, 120ms system time, 32.62M rss, 175.04M vsz # Current date: Thu Nov 14 19:39:15 2013 # Files: /var/lib/mysql/mysql-slow.log # Overall: 2.78k total, 269 unique, 0.01 QPS, 0.00x concurrency __________ # Time range: 2013-11-10 18:49:02 to 2013-11-14 19:38:21 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 1031s 100ms 102s 370ms 857ms 2s 180ms # Lock time 16s 0 3s 6ms 799us 81ms 194us # Rows sent 54.84k 0 765 20.18 38.53 89.79 0 # Rows examine 6.98M 0 164.42k 2.57k 21.38k 7.64k 2.90 # Query size 2.37M 25 15.04k 893.34 3.52k 1.25k 202.40 # Profile # Rank Query ID Response time Calls R/Call V/M Item # ==== ================== ============== ===== ======= ===== ============= # 1 0xE6F05D29E7F679E6 155.2529 15.1% 11 14.1139 55.83 SELECT information_schema.TABLES # 2 0x2CE3D349814704A5 143.7938 13.9% 307 0.4684 0.40 INSERT maillog # 3 0xAFC0DD278228530A 59.5739 5.8% 246 0.2422 0.16 REPLACE SELECT cscart_stored_sessions cscart_sessions # 4 0x1543E6DFC49E1EF0 49.1282 4.8% 66 0.7444 0.00 SELECT cscart_product_feature_variants cscart_product_feature_variant_descriptions cscart_product_features_values cscart_product_features cscart_seo_names # 5 0xD65D7926B1674FD0 41.2148 4.0% 11 3.7468 26.07 SELECT information_schema.TABLES # 6 0x7194135125B38CDE 35.6636 3.5% 24 1.4860 0.46 SELECT information_schema.tables # 7 0xF64E6026FD36734A 34.8187 3.4% 127 0.2742 0.46 DELETE cscart_stored_sessions # 8 0xA818789EF5D1CA62 29.3907 2.9% 116 0.2534 0.05 SELECT nivxq?o?g_bwps_log # 9 0xAAB1A4BB8262D565 24.7821 2.4% 51 0.4859 0.14 SELECT cscart_orders cscart_order_docs cscart_companies cscart_order_data # 10 0xE08A720756D8F9A8 21.7203 2.1% 70 0.3103 0.17 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 # 11 0x0437334A74016DC2 20.1894 2.0% 98 0.2060 0.07 SELECT cscart_stat_sessions # 12 0x73B82FC190CB664A 18.5491 1.8% 65 0.2854 0.24 SELECT cscart_products cscart_product_descriptions cscart_product_prices cscart_products_categories cscart_categories cscart_seo_names # 13 0x5E9A5EC84A83CE6F 14.6545 1.4% 58 0.2527 0.11 REPLACE cscart_sessions # 14 0x4C1676B6F9A1F648 14.2338 1.4% 48 0.2965 0.24 SELECT cscart_stored_sessions # 15 0xB174B54D7229E515 13.2919 1.3% 36 0.3692 0.15 REPLACE SELECT cscart_stored_sessions cscart_sessions # 16 0x74451F964DFEF973 12.7197 1.2% 70 0.1817 0.12 SELECT cscart_products cscart_product_descriptions cscart_product_prices cscart_products_categories cscart_categories cscart_also_bought_products cscart_seo_names # 17 0x47B7279693127726 11.0434 1.1% 26 0.4247 0.13 UPDATE x?hd?z?kb_options # 18 0xBE0D45B9A02D10F8 9.9459 1.0% 4 2.4865 0.12 SHOW COLUMNS # 19 0x54158123C19799B3 9.8871 1.0% 35 0.2825 0.18 SELECT cscart_bm_snapping cscart_bm_blocks cscart_bm_block_statuses cscart_bm_blocks_descriptions cscart_bm_blocks_content # 20 0xF5CD90AE42F5F8CD 8.9065 0.9% 3 2.9688 4.61 SELECT cscart_product_subscriptions # MISC 0xMISC 302.0432 29.3% 1311 0.2304 0.0 <249 ITEMS> # Query 1: 0.00 QPS, 0.00x concurrency, ID 0xE6F05D29E7F679E6 at byte 1623231 # This item is included in the report because it matches --limit. # Scores: V/M = 55.83 # Time range: 2013-11-10 18:49:02 to 2013-11-12 21:32:44 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 11 # Exec time 15 155s 549ms 102s 14s 32s 28s 936ms # Lock time 0 3ms 206us 288us 233us 260us 25us 224us # Rows sent 0 33 3 3 3 3 0 3 # Rows examine 0 38.17k 3.47k 3.47k 3.47k 3.47k 0 3.47k # Query size 0 2.05k 191 191 191 191 0 191 # String: # Databases lotusm_cs2... (7/63%), bling888_w... (2/18%)... 1 more # 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 ENGINE,SUM(DATA_LENGTH),COUNT(ENGINE) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND ENGINE IS NOT NULL GROUP BY ENGINE ORDER BY ENGINE ASC\G # Query 2: 0.00 QPS, 0.00x concurrency, ID 0x2CE3D349814704A5 at byte 368414 # This item is included in the report because it matches --limit. # Scores: V/M = 0.40 # Time range: 2013-11-10 19:10:34 to 2013-11-14 19:19:09 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 11 307 # Exec time 13 144s 101ms 2s 468ms 1s 431ms 266ms # Lock time 0 124ms 265us 681us 403us 515us 79us 366us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 0 0 0 0 0 0 0 # Query size 44 1.06M 1.31k 6.38k 3.55k 5.20k 1.08k 3.35k # 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-11 09:44:17','1VfvWt-0005gF-25','10800','franchise@newsfounders.biz','newsfounders.biz','katrina@lotusmoon.biz','lotusmoon.biz',' Franchise guidance and information from industry leaders','173.213.70.20','','1','1','1',NULL,'0','0','8.675','spam, SpamAssassin (not cached, score=8.675, required 5, DKIM_SIGNED 0.10, DKIM_VALID -0.10, DKIM_VALID_AU -0.10, FROM_12LTRDOM 0.10, HTML_MESSAGE 0.00, INVALID_MSGID 1.17, MIME_HTML_ONLY 1.10, RCVD_IN_BRBL_LASTEXT 1.64, RDNS_NONE 1.27, SPF_PASS -0.00, T_REMOTE_IMAGE 0.01, URIBL_BLACK 1.77, URIBL_DBL_SPAM 1.70)','0','0','0','',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'server.smbessentials.com','2013-11-11','09:44:17','Received: from [173.213.70.20] (port=43101 helo=zha5.newsfounders.biz)\n by server.smbessentials.com with esmtp (Exim 4.80.1)\n (envelope-from )\n id 1VfvWt-0005gF-25\n for katrina@lotusmoon.biz; Mon, 11 Nov 2013 09:44:09 -0800\nDKIM-Signature: v=1; a=rsa-sha1; c=relaxed/relaxed; s=default; d=newsfounders.biz;\n h=To:Subject:Date:From:Message-ID:MIME-Version:Content-Type; i=Franchise@newsfounders.biz;\n bh=dL2kATMJiMSBC/RaH9FATSDsQIs=;\n b=KLBGvXLhuUEuO+BWNPrQ0kdtFb05yx6jEJl6CfJNKejKUMbOldCxHMvI8FgQ2pcg3eY2dbGy4hJd\n 0hk0O33b5XYNoxdnUgmTaOG0QOvkhs8AAq75WBynHAkDZR8N3KEwREPL8JfkQubzAFN7RDW9S7pj\n KpRIZEhqw7uzD2FAvEo=\nDomainKey-Signature: a=rsa-sha1; c=nofws; q=dns; s=default; d=newsfounders.biz;\n b=pbsUaNkx84k9qizTcQugC54IruD+Fcimw9iWrh+6jx5+gA07tGIL7xpprLDSQJf3Rz4NRdKctMyx\n W7PetEX05V9Ier4tBiMV8kO2Ka6QaEh8WV0DpYmlubpynMYZzWjlKYMguCZhhxd9orl6Rp8V9itO\n rOc7Nhiksc2ccqBy2hM=;\nTo: \nSubject: Franchise guidance and information from industry leaders\nDate: Mon, 11 Nov 2013 10:17:37 -0700\nFrom: \"Franchise\" \nMessage-ID: a2F0cmluYUBsb3R1c21vb24uYml6497@newsfounders.biz\nMIME-Version: 1.0\nContent-Type: text/html\nX-Spam-Subject: ***SPAM*** Franchise guidance and information from industry leaders\nX-Spam-Status: Yes, score=8.7\nX-Spam-Score: 87\nX-Spam-Bar: ++++++++\nX-Spam-Report: Spam detection software, running on the system \"server.smbessentials.com\", has\n identified this incoming email as possible spam. The original message\n has been attached to this so you can view it (if it isn\'t spam) or label\n similar future email. If you have any questions, see\n root\\@localhost for details.\n \n Content preview: Franchise New franchise opportunities available Look into\n a frachise investment and you could own your own business. [...] \n \n Content analysis details: (8.7 points, 5.0 required)\n \n pts rule name description\n ---- ---------------------- --------------------------------------------------\n 1.6 RCVD_IN_BRBL_LASTEXT RBL: RCVD_IN_BRBL_LASTEXT\n [173.213.70.20 listed in bb.barracudacentral.org]\n 1.8 URIBL_BLACK Contains an URL listed in the URIBL blacklist\n [URIs: newsfounders.biz]\n -0.0 SPF_PASS SPF: sender matches SPF record\n 1.7 URIBL_DBL_SPAM Contains an URL listed in the DBL blocklist\n [URIs: newsfounders.biz]\n 0.0 HTML_MESSAGE BODY: HTML included in message\n 1.1 MIME_HTML_ONLY BODY: Message only has text/html MIME parts\n -0.1 DKIM_VALID_AU Message has a valid DKIM or DK signature from author\'s\n domain\n -0.1 DKIM_VALID Message has at least one valid DKIM or DK signature\n 0.1 DKIM_SIGNED Message has a DKIM or DK signature, not necessarily valid\n 1.3 RDNS_NONE Delivered to internal network by a host with no rDNS\n 1.2 INVALID_MSGID Message-Id is not valid, according to RFC 2822\n 0.1 FROM_12LTRDOM From a 12-letter domain\n 0.0 T_REMOTE_IMAGE Message contains an external image\nX-Spam-Flag: YES','0')\G # Query 3: 0.00 QPS, 0.00x concurrency, ID 0xAFC0DD278228530A at byte 886141 # This item is included in the report because it matches --limit. # Scores: V/M = 0.16 # Time range: 2013-11-10 19:06:25 to 2013-11-14 19:20:59 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 8 246 # Exec time 5 60s 100ms 2s 242ms 640ms 199ms 163ms # Lock time 8 1s 112us 443ms 6ms 214us 42ms 152us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 2.27k 0 233 9.45 22.53 22.21 4.96 # Query size 0 23.54k 98 98 98 98 0 98 # String: # Databases lotusm_cs2... (213/86%), plainjan_c... (33/13%) # Hosts localhost # Users lotusm_cs3... (213/86%), plainjan_c... (33/13%) # 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 # SHOW TABLE STATUS FROM `lotusm_cs2cart` LIKE 'cscart_sessions'\G # SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_sessions`\G REPLACE INTO cscart_stored_sessions SELECT * FROM cscart_sessions WHERE 1 AND expiry < 1384223474\G # Query 4: 0.00 QPS, 0.00x concurrency, ID 0x1543E6DFC49E1EF0 at byte 3016404 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: 2013-11-13 14:29:13 to 2013-11-14 19:26:44 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 2 66 # Exec time 4 49s 718ms 831ms 744ms 777ms 28ms 705ms # Lock time 0 28ms 389us 481us 423us 445us 18us 403us # Rows sent 4 2.58k 40 40 40 40 0 40 # Rows examine 17 1.19M 18.46k 18.56k 18.51k 18.47k 52 18.47k # Query size 3 81.69k 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 = 342 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 ('7') 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 5: 0.00 QPS, 0.00x concurrency, ID 0xD65D7926B1674FD0 at byte 1623569 # This item is included in the report because it matches --limit. # Scores: V/M = 26.07 # Time range: 2013-11-10 18:49:03 to 2013-11-12 21:32:44 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 11 # Exec time 3 41s 513ms 35s 4s 816ms 10s 501ms # Lock time 0 3ms 196us 325us 231us 236us 31us 214us # Rows sent 0 11 1 1 1 1 0 1 # Rows examine 0 38.10k 3.46k 3.46k 3.46k 3.46k 0 3.46k # Query size 0 1.65k 154 154 154 154 0 154 # String: # Databases lotusm_cs2... (7/63%), bling888_w... (2/18%)... 1 more # 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(TABLE_NAME) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND Data_free > 0 AND NOT ENGINE='MEMORY'\G # Query 6: 0.00 QPS, 0.00x concurrency, ID 0x7194135125B38CDE at byte 804139 # This item is included in the report because it matches --limit. # Scores: V/M = 0.46 # Time range: 2013-11-10 20:30:03 to 2013-11-14 16:30:03 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 24 # Exec time 3 36s 1s 5s 1s 2s 826ms 1s # Lock time 0 5ms 178us 281us 212us 224us 22us 204us # Rows sent 1 792 33 33 33 33 0 33 # Rows examine 1 86.30k 3.60k 3.60k 3.60k 3.60k 0 3.60k # Query size 0 2.95k 126 126 126 126 0 126 # String: # Databases mysql # 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 TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA\G # Query 7: 0.00 QPS, 0.00x concurrency, ID 0xF64E6026FD36734A at byte 2491855 # This item is included in the report because it matches --limit. # Scores: V/M = 0.46 # Time range: 2013-11-10 19:06:25 to 2013-11-14 19:38:21 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 4 127 # Exec time 3 35s 101ms 4s 274ms 580ms 355ms 171ms # Lock time 1 207ms 83us 191ms 2ms 176us 17ms 113us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 1.31k 0 615 10.54 21.45 52.59 1.96 # Query size 0 7.44k 60 60 60 60 0 60 # String: # Databases lotusm_cs2... (119/93%), plainjan_c... (6/4%)... 2 more # Hosts localhost # Users lotusm_cs3... (119/93%), plainjan_c... (6/4%)... 2 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 < 1383214238\G # Converted for EXPLAIN # EXPLAIN /*!50100 PARTITIONS*/ select * from cscart_stored_sessions WHERE expiry < 1383214238\G # Query 8: 0.00 QPS, 0.00x concurrency, ID 0xA818789EF5D1CA62 at byte 2691747 # This item is included in the report because it matches --limit. # Scores: V/M = 0.05 # Time range: 2013-11-10 19:10:03 to 2013-11-14 19:10:24 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 4 116 # Exec time 2 29s 164ms 844ms 253ms 433ms 107ms 219ms # Lock time 0 20ms 142us 378us 171us 204us 25us 159us # Rows sent 0 116 1 1 1 1 0 1 # Rows examine 36 2.56M 22.43k 22.72k 22.59k 22.45k 146.50 22.45k # Query size 0 11.93k 103 108 105.29 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='146.0.73.155' AND timestamp > 1384424306\G # Query 9: 0.00 QPS, 0.00x concurrency, ID 0xAAB1A4BB8262D565 at byte 1966970 # This item is included in the report because it matches --limit. # Scores: V/M = 0.14 # Time range: 2013-11-11 09:04:22 to 2013-11-14 15:02:15 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 1 51 # Exec time 2 25s 330ms 1s 486ms 1s 261ms 339ms # Lock time 0 21ms 374us 439us 410us 424us 16us 403us # Rows sent 1 612 12 12 12 12 0 12 # Rows examine 6 499.93k 9.79k 9.83k 9.80k 9.80k 20 9.80k # Query size 2 52.46k 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 10: 0.00 QPS, 0.00x concurrency, ID 0xE08A720756D8F9A8 at byte 832775 # This item is included in the report because it matches --limit. # Scores: V/M = 0.17 # Time range: 2013-11-10 19:49:55 to 2013-11-14 19:01:40 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 2 70 # Exec time 2 22s 102ms 1s 310ms 672ms 229ms 219ms # Lock time 6 1s 749us 627ms 15ms 14ms 77ms 799us # Rows sent 0 70 1 1 1 1 0 1 # Rows examine 0 1.79k 6 68 26.23 44.60 14.01 19.46 # Query size 6 165.24k 2.36k 2.36k 2.36k 2.27k 0 2.27k # String: # Databases lotusm_cs2... (47/67%), plainjan_c... (23/32%) # Hosts localhost # Users lotusm_cs3... (47/67%), plainjan_c... (23/32%) # 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 = 260 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 = 67 LEFT JOIN cscart_discussion ON cscart_discussion.object_id = cscart_products.product_id AND cscart_discussion.object_type = 'P' WHERE cscart_products.product_id = 260 AND cscart_products.company_id = 0 GROUP BY cscart_products.product_id\G # Query 11: 0.00 QPS, 0.00x concurrency, ID 0x0437334A74016DC2 at byte 2186408 # This item is included in the report because it matches --limit. # Scores: V/M = 0.07 # Time range: 2013-11-10 18:52:29 to 2013-11-14 18:44:50 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 3 98 # Exec time 1 20s 100ms 725ms 206ms 477ms 120ms 163ms # Lock time 0 19ms 111us 340us 190us 236us 35us 176us # Rows sent 0 6 0 1 0.06 0.99 0.24 0 # Rows examine 0 12 0 2 0.12 1.96 0.47 0 # Query size 0 14.07k 147 147 147 147 0 147 # String: # Databases lotusm_cs2... (81/82%), plainjan_c... (17/17%) # Hosts localhost # Users lotusm_cs3... (81/82%), plainjan_c... (17/17%) # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s # 10s+ # Tables # SHOW TABLE STATUS FROM `lotusm_cs2cart` LIKE 'cscart_stat_sessions'\G # SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_stat_sessions`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT sess_id FROM cscart_stat_sessions WHERE session = 'b258e1d9f077977abfab428973a8bd28' AND expiry > 1384373907 ORDER BY timestamp DESC LIMIT 1\G # Query 12: 0.00 QPS, 0.00x concurrency, ID 0x73B82FC190CB664A at byte 447026 # This item is included in the report because it matches --limit. # Scores: V/M = 0.24 # Time range: 2013-11-10 23:42:08 to 2013-11-14 15:40:12 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 2 65 # Exec time 1 19s 101ms 1s 285ms 640ms 260ms 148ms # Lock time 4 690ms 0 256ms 11ms 2ms 45ms 626us # Rows sent 0 545 0 31 8.38 23.65 6.22 6.98 # Rows examine 0 5.28k 0 288 83.23 212.52 67.34 62.76 # Query size 4 109.07k 1.68k 1.68k 1.68k 1.61k 0 1.61k # String: # Databases lotusm_cs2... (49/75%), plainjan_c... (16/24%) # Hosts localhost # Users lotusm_cs3... (49/75%), plainjan_c... (16/24%) # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s #### # 10s+ # Tables # SHOW TABLE STATUS FROM `plainjan_cscart1db` LIKE 'cscart_products'\G # SHOW CREATE TABLE `plainjan_cscart1db`.`cscart_products`\G # SHOW TABLE STATUS FROM `plainjan_cscart1db` LIKE 'cscart_product_descriptions'\G # SHOW CREATE TABLE `plainjan_cscart1db`.`cscart_product_descriptions`\G # SHOW TABLE STATUS FROM `plainjan_cscart1db` LIKE 'cscart_product_prices'\G # SHOW CREATE TABLE `plainjan_cscart1db`.`cscart_product_prices`\G # SHOW TABLE STATUS FROM `plainjan_cscart1db` LIKE 'cscart_products_categories'\G # SHOW CREATE TABLE `plainjan_cscart1db`.`cscart_products_categories`\G # SHOW TABLE STATUS FROM `plainjan_cscart1db` LIKE 'cscart_categories'\G # SHOW CREATE TABLE `plainjan_cscart1db`.`cscart_categories`\G # SHOW TABLE STATUS FROM `plainjan_cscart1db` LIKE 'cscart_seo_names'\G # SHOW CREATE TABLE `plainjan_cscart1db`.`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 (36) 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, 96\G # Query 13: 0.00 QPS, 0.00x concurrency, ID 0x5E9A5EC84A83CE6F at byte 2765203 # This item is included in the report because it matches --limit. # Scores: V/M = 0.11 # Time range: 2013-11-10 19:23:55 to 2013-11-14 13:19:13 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 2 58 # Exec time 1 15s 101ms 958ms 253ms 501ms 163ms 189ms # Lock time 2 442ms 170us 267ms 8ms 3ms 38ms 236us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 0 0 0 0 0 0 0 # Query size 4 108.75k 854 12.31k 1.88k 4.07k 1.67k 1.26k # String: # Databases lotusm_cs2... (47/81%), plainjan_c... (11/18%) # Hosts localhost # Users lotusm_cs3... (47/81%), plainjan_c... (11/18%) # 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 ('67be3351f2a29d09dda28025738ee580', 'C', '1384471151', '_validator_data|a:1:{s:2:\"ua\";s:188:\"User-Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; WOW64; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET CLR 1.1.4322; .NET4.0C; .NET4.0E)\";}settings|a:2:{s:14:\"cart_languageC\";a:2:{s:5:\"value\";s:2:\"EN\";s:6:\"expiry\";i:1385068632;}s:19:\"secondary_currencyC\";a:2:{s:5:\"value\";s:3:\"USD\";s:6:\"expiry\";i:1385068632;}}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:1384463832;s:25:\"password_change_timestamp\";i:0;s:10:\"company_id\";i:0;s:7:\"is_root\";s:1:\"N\";s:7:\"referer\";s:88:\"http://www.prweb.com/releases/anti-aging-lactic-acid/toner-lotus-moon/prweb11332884.htm/\";}cart|a:4:{s:8:\"products\";a:0:{}s:11:\"recalculate\";b:0;s:9:\"user_data\";a:0:{}s:11:\"points_info\";a:1:{s:6:\"reward\";i:0;}}device|s:0:\"\";twg_user_agent|s:7:\"unknown\";twg_ua|s:188:\"User-Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; WOW64; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; .NET CLR 1.1.4322; .NET4.0C; .NET4.0E)\";cache_mobile_frontend|s:1:\"N\";')\G # Query 14: 0.00 QPS, 0.00x concurrency, ID 0x4C1676B6F9A1F648 at byte 708687 # This item is included in the report because it matches --limit. # Scores: V/M = 0.24 # Time range: 2013-11-10 18:49:18 to 2013-11-14 12:38:01 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 1 48 # Exec time 1 14s 102ms 2s 297ms 526ms 265ms 219ms # Lock time 1 248ms 111us 121ms 5ms 236us 23ms 152us # Rows sent 0 14 0 1 0.29 0.99 0.45 0 # Rows examine 0 14 0 1 0.29 0.99 0.45 0 # Query size 0 5.39k 115 115 115 115 0 115 # String: # Databases lotusm_cs2... (43/89%), plainjan_c... (5/10%) # Hosts localhost # Users lotusm_cs3... (43/89%), plainjan_c... (5/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 # EXPLAIN /*!50100 PARTITIONS*/ SELECT data FROM cscart_stored_sessions WHERE 1 AND session_id = '59c2b42f95b24d2b0eada4b55706d785' AND area = 'C'\G # Query 15: 0.00 QPS, 0.00x concurrency, ID 0xB174B54D7229E515 at byte 2684383 # This item is included in the report because it matches --limit. # Scores: V/M = 0.15 # Time range: 2013-11-10 19:34:57 to 2013-11-14 19:16:09 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 1 36 # Exec time 1 13s 104ms 1s 369ms 900ms 233ms 279ms # Lock time 0 6ms 140us 222us 169us 204us 18us 159us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 5 359.22k 27 10.80k 9.98k 10.29k 2.35k 10.29k # Query size 0 4.11k 117 117 117 117 0 117 # String: # Databases detoxrx_CS... (34/94%), son888_CSU... (2/5%) # Hosts localhost # Users detoxrx_cs... (34/94%), son888_csu... (2/5%) # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s # # 10s+ # Tables # SHOW TABLE STATUS FROM `detoxrx_CSE1` LIKE 'cscart_stored_sessions'\G # SHOW CREATE TABLE `detoxrx_CSE1`.`cscart_stored_sessions`\G # SHOW TABLE STATUS FROM `detoxrx_CSE1` LIKE 'cscart_sessions'\G # SHOW CREATE TABLE `detoxrx_CSE1`.`cscart_sessions`\G REPLACE INTO cscart_stored_sessions SELECT * FROM cscart_sessions WHERE 1 AND expiry < 1384452846 AND company_id = 0\G # Query 16: 0.00 QPS, 0.00x concurrency, ID 0x74451F964DFEF973 at byte 790618 # This item is included in the report because it matches --limit. # Scores: V/M = 0.12 # Time range: 2013-11-10 19:02:30 to 2013-11-14 18:39:02 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 2 70 # Exec time 1 13s 102ms 1s 182ms 356ms 150ms 141ms # Lock time 0 69ms 647us 19ms 984us 1ms 2ms 657us # Rows sent 0 280 4 4 4 4 0 4 # Rows examine 1 74.60k 325 1.57k 1.07k 1.46k 333.47 1.09k # Query size 5 121.88k 1.74k 1.74k 1.74k 1.74k 0 1.74k # 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_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_also_bought_products'\G # SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_also_bought_products`\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, GROUP_CONCAT(IF(products_categories.link_type = 'M', CONCAT(products_categories.category_id, 'M'), products_categories.category_id)) as category_ids, products_categories.position, SUM(cscart_also_bought_products.amount) amnt, 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_also_bought_products ON cscart_also_bought_products.related_id = products.product_id 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 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) AND cscart_also_bought_products.product_id = 246 GROUP BY cscart_also_bought_products.related_id ORDER BY products_categories.position asc LIMIT 0, 4\G # Query 17: 0.00 QPS, 0.00x concurrency, ID 0x47B7279693127726 at byte 1812100 # This item is included in the report because it matches --limit. # Scores: V/M = 0.13 # Time range: 2013-11-13 01:32:12 to 05:15:29 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 26 # Exec time 1 11s 123ms 1s 425ms 609ms 234ms 412ms # Lock time 0 5ms 148us 338us 209us 301us 53us 185us # Rows sent 0 0 0 0 0 0 0 0 # Rows examine 0 26 1 1 1 1 0 1 # Query size 1 28.09k 162 1.65k 1.08k 1.61k 390.19 1.14k # String: # Databases bling888_wpblog # Hosts localhost # Users bling888_wpadmin # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s ## # 10s+ # Tables # SHOW TABLE STATUS FROM `bling888_wpblog` LIKE 'x9hd5z90kb_options'\G # SHOW CREATE TABLE `bling888_wpblog`.`x9hd5z90kb_options`\G UPDATE `x9hd5z90kb_options` SET `option_value` = 'O:8:\"stdClass\":3:{s:12:\"last_checked\";i:1384339252;s:8:\"response\";a:2:{s:45:\"bulletproof-security/bulletproof-security.php\";O:8:\"stdClass\":6:{s:2:\"id\";s:5:\"14569\";s:4:\"slug\";s:20:\"bulletproof-security\";s:11:\"new_version\";s:5:\".49.6\";s:14:\"upgrade_notice\";s:300:\"Bonus Code Dismiss Notice Added: Author ID / User ID / Username BOT Probe Protection Code: \nProtects against hacker Bot Probes looking for WordPress author enumeration (a numbered list of Author ID's / User ID's) to exploit. \nGenerates a standard WordPress 404 Error instead of displaying A\";s:3:\"url\";s:51:\"https://wordpress.org/plugins/bulletproof-security/\";s:7:\"package\";s:70:\"https://downloads.wordpress.org/plugin/bulletproof-security.0.49.6.zip\";}s:67:\"social-networks-auto-poster-facebook-twitter-g/NextScripts_SNAP.php\";O:8:\"stdClass\":5:{s:2:\"id\";s:5:\"29712\";s:4:\"slug\";s:46:\"social-networks-auto-poster-facebook-twitter-g\";s:11:\"new_version\";s:5:\"3.0.7\";s:3:\"url\";s:77:\"https://wordpress.org/plugins/social-networks-auto-poster-facebook-twitter-g/\";s:7:\"package\";s:89:\"https://downloads.wordpress.org/plugin/social-networks-auto-poster-facebook-twitter-g.zip\";}}s:12:\"translations\";a:0:{}}' WHERE `option_name` = '_site_transient_update_plugins'\G # Converted for EXPLAIN # EXPLAIN /*!50100 PARTITIONS*/ select `option_value` = 'O:8:\"stdClass\":3:{s:12:\"last_checked\";i:1384339252;s:8:\"response\";a:2:{s:45:\"bulletproof-security/bulletproof-security.php\";O:8:\"stdClass\":6:{s:2:\"id\";s:5:\"14569\";s:4:\"slug\";s:20:\"bulletproof-security\";s:11:\"new_version\";s:5:\".49.6\";s:14:\"upgrade_notice\";s:300:\"Bonus Code Dismiss Notice Added: Author ID / User ID / Username BOT Probe Protection Code: \nProtects against hacker Bot Probes looking for WordPress author enumeration (a numbered list of Author ID's / User ID's) to exploit. \nGenerates a standard WordPress 404 Error instead of displaying A\";s:3:\"url\";s:51:\"https://wordpress.org/plugins/bulletproof-security/\";s:7:\"package\";s:70:\"https://downloads.wordpress.org/plugin/bulletproof-security.0.49.6.zip\";}s:67:\"social-networks-auto-poster-facebook-twitter-g/NextScripts_SNAP.php\";O:8:\"stdClass\":5:{s:2:\"id\";s:5:\"29712\";s:4:\"slug\";s:46:\"social-networks-auto-poster-facebook-twitter-g\";s:11:\"new_version\";s:5:\"3.0.7\";s:3:\"url\";s:77:\"https://wordpress.org/plugins/social-networks-auto-poster-facebook-twitter-g/\";s:7:\"package\";s:89:\"https://downloads.wordpress.org/plugin/social-networks-auto-poster-facebook-twitter-g.zip\";}}s:12:\"translations\";a:0:{}}' from `x9hd5z90kb_options` where `option_name` = '_site_transient_update_plugins'\G # Query 18: 0 QPS, 0x concurrency, ID 0xBE0D45B9A02D10F8 at byte 748164 __ # This item is included in the report because it matches --limit. # Scores: V/M = 0.12 # Time range: all events occurred at 2013-11-11 16:19:53 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 4 # Exec time 0 10s 2s 3s 2s 3s 542ms 3s # Lock time 47 8s 1s 3s 2s 3s 512ms 2s # Rows sent 0 16 4 4 4 4 0 4 # Rows examine 0 16 4 4 4 4 0 4 # Query size 0 132 33 33 33 33 0 33 # 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_sessions'\G # SHOW CREATE TABLE `lotusm_cs2cart`.`cscart_sessions`\G SHOW COLUMNS FROM cscart_sessions\G # Query 19: 0.00 QPS, 0.00x concurrency, ID 0x54158123C19799B3 at byte 556322 # This item is included in the report because it matches --limit. # Scores: V/M = 0.18 # Time range: 2013-11-10 19:49:48 to 2013-11-14 19:01:41 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 1 35 # Exec time 0 10s 103ms 994ms 282ms 740ms 223ms 180ms # Lock time 4 744ms 0 618ms 21ms 34ms 102ms 515us # Rows sent 1 851 0 31 24.31 30.19 6.97 23.65 # Rows examine 0 9.69k 0 388 283.46 381.65 104.97 258.32 # Query size 2 50.25k 1.40k 1.45k 1.44k 1.39k 0 1.39k # String: # Databases lotusm_cs2... (26/74%), plainjan_c... (9/25%) # Hosts localhost # Users lotusm_cs3... (26/74%), plainjan_c... (9/25%) # 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 'products' 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 = 24 AND dynamic_object_content.object_type like 'products' WHERE cscart_bm_snapping.grid_id IN (5, 34, 97, 98, 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 20: 0.00 QPS, 0.00x concurrency, ID 0xF5CD90AE42F5F8CD at byte 716008 # This item is included in the report because it matches --limit. # Scores: V/M = 4.61 # Time range: 2013-11-11 16:19:01 to 2013-11-12 02:14:47 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 0 3 # Exec time 0 9s 176ms 8s 3s 8s 4s 308ms # Lock time 0 584us 151us 232us 194us 224us 32us 194us # Rows sent 0 5 0 5 1.67 4.96 2.34 0 # Rows examine 0 5 0 5 1.67 4.96 2.34 0 # Query size 0 284 94 95 94.67 92.72 0 92.72 # String: # Databases lotusm_cs2... (2/66%), plainjan_c... (1/33%) # Hosts localhost # Users lotusm_cs3... (2/66%), plainjan_c... (1/33%) # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms ################################################################ # 1s ################################ # 10s+ # Tables # SHOW TABLE STATUS FROM `plainjan_cscart1db` LIKE 'cscart_product_subscriptions'\G # SHOW CREATE TABLE `plainjan_cscart1db`.`cscart_product_subscriptions`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT subscription_id FROM cscart_product_subscriptions WHERE product_id = 20 AND user_id = 0\G0 -
You should put that in brackets to make it easier to read # Overall: 2.78k total, 269 unique, 0.01 QPS, 0.00x concurrency __________ # Time range: 2013-11-10 18:49:02 to 2013-11-14 19:38:21 anyways, you got barely any queries, 4 days, less than 3k queries :( not much to measure anything thats weird, since in mysqltuner you got [--] Up for: 22h 42m 21s (1M q [12.999 qps], 15K conn, TX: 1B, RX: 450M) 1 milion queries in 23h Did you generate pt-query-digest on correct slow log file ?0 -
Sorry about the issue. I went to /var/lib/mysql/mysql-slow.log and downloaded the 7.5MB file. It is here: /https://dl.dropboxusercontent.com/u/3860069/mysql-slow.log0 -
Ok, 1. Table lotusm_cs2, son888_CSU SELECT count FROM cscart_twigmo_ua_stat WHERE `section` = 'windows_version' AND `value` = '' AND `month` = '2013-11-1'\G Please check if you have indexes on the columns after WHERE, especially 'month' UPDATE cscart_twigmo_ua_stat SET count=count+1 WHERE `section` = 'ios_version' AND `value` = '' AND `month` = '2013-11-1'\G # Converted for EXPLAIN # EXPLAIN /*!50100 PARTITIONS*/ select count=count+1 from cscart_twigmo_ua_stat where `section` = 'ios_version' AND `value` = '' AND `month` = '2013-11-1'\G 2. Table eco888_wpdb nivxq1o0g_options, index on column 'option_name' Those are the 2 from the pt-query-digest generated from /https://dl.dropboxusercontent.com/u/3860069/mysql-slow.log But there are tons of other queries that takes xx seconds, even xxx seconds, Can you post 'top' command result, to see if your server is not swapping ? Do you have munin plugin installed, and can you post cpu/load/memory graphs ? More Table detorx_CS, son888_CSU SELECT data, expiry FROM cscart_cache WHERE name = 'scheme_block_template.cache_customer' AND company_id = 1 check if there is index on name or/and company_id lotusm_cs2, plainjan_c SELECT name FROM cscart_seo_names WHERE `object_id` = '256' AND `type` = 'p' AND `dispatch` = '' AND `lang_code` = 'EN' index on object_id lotusm_cs2, detoxrx_CS, 2 more DELETE FROM cscart_access_restriction_block WHERE expires < 1384546600 index on expires You got not much queries, but those that you have in slow log were probably executed during some high server load, couse they take way too much time It would be good if you installed munin plugin for cpanel to monitor server usage, especially I/O 0 -
top - 15:27:19 up 1:47, 1 user, load average: 1.02, 1.08, 1.13 Tasks: 121 total, 1 running, 120 sleeping, 0 stopped, 0 zombie Cpu(s): 0.1%us, 1.4%sy, 0.0%ni, 97.8%id, 0.0%wa, 0.2%hi, 0.5%si, 0.0%st Mem: 2041628k total, 1999648k used, 41980k free, 32308k buffers Swap: 4192944k total, 128k used, 4192816k free, 927976k cached
Under plugins in WHM munin is not listed.0 -
Whm/Cpanel -> Manage Plugins There is munin 0 -
OK I found that and have installed it on the server. We also had the server crash after high loads earlier tonight. Time: Fri Nov 15 11:43:47 2013 -0800 1 Min Load Avg: 22.83 5 Min Load Avg: 6.67 15 Min Load Avg: 2.52 Running/Total Processes: 2/281 0 -
You can post pt-query-digest result of analysis after the crash with high load After you generate that, you can remove mysql-slow.log and run mysql then flush logs; to start mysql-slow.log again, so we will have clear info before the next spike of load Please also remember, when you have higher load to gather the result of top and mysqladmin -v process commands during high load This will help 0 -
Is this right? mysql> FLUSH LOGS; Query OK, 0 rows affected (0.00 sec)0 -
Yeah, you can post the review of pt-query-digest 0 -
The mysql-slow.log is still like 8+mgs in size. Shouldn't it have been flushed to 0? https://dl.dropboxusercontent.com/u/3860069/slow.txt 0 -
Ohh, you should remove it first, then flush logs, to recreate the file :( Can you try again, remove old log, then flush logs 0 -
Oh, ok. I have done this now. 0 -
Here is the updated slow queries report # 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','jenny@spa-boudoir.com','spa-boudoir.com','hrynkochanell@yahoo.com','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 )\n id 1ViT6o-0002Q8-C4\n for hrynkochanell@yahoo.com; Mon, 18 Nov 2013 09:59:39 -0800\nFrom: \"Smith Cole\" \nSubject: 09-28-2013\nTo: hrynkochanell@yahoo.com\nContent-Type: multipart/alternative; boundary=\"3kUhX5gnUf9tmIHMisg4wkSWuKdQ6a=_Lf0\"\nMIME-Version: 1.0\nReply-To: smithcole406@gmail.com\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','','')\G0 -
@thinkbot - can you share any insight from these last reports? 0 -
there 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 0 -
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? -------- 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)0 -
[!!] 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 0 -
The server has wordpress and cs-cart.com on it. Nothing custom written. 0
Please sign in to leave a comment.
Comments
27 comments