Re: Utilize maximum system resources for better performance
Rick James Wrote:
-------------------------------------------------------
> > now my queries takes 1.xx seconds
>
> Turn the QC back on while we fix those queries.
> Let's see one of them. Please show us these 3
> things
>
> SELECT...
> EXPLAIN SELECT ...
> SHOW CREATE TABLE
>
> We may be able to speed up the query so that it
> will be fast even if the QC is off. This will
> _indirectly_ help the UPDATEs.
sorry for delay.
mysql> SHOW CREATE TABLE content;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| content | CREATE TABLE `content` (
`hash` char(40) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`title` char(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`og_name` char(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`keywords` char(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`files_count` smallint(5) unsigned NOT NULL DEFAULT '0',
`more_files` smallint(5) unsigned NOT NULL DEFAULT '0',
`files` char(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '0',
`category` smallint(3) unsigned NOT NULL DEFAULT '600',
`size` bigint(19) unsigned NOT NULL DEFAULT '0',
`leechers` int(11) NOT NULL DEFAULT '0',
`completed` int(11) NOT NULL DEFAULT '0',
`seeders` int(11) NOT NULL DEFAULT '0',
`creation_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`upload_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`last_updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`vote_up` int(11) unsigned NOT NULL DEFAULT '0',
`vote_down` int(11) unsigned NOT NULL DEFAULT '0',
`comments_count` int(11) NOT NULL DEFAULT '0',
`imdb` int(8) unsigned NOT NULL DEFAULT '0',
`video_sample` tinyint(1) NOT NULL DEFAULT '0',
`video_quality` tinyint(2) NOT NULL DEFAULT '0',
`audio_lang` varchar(127) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`subtitle_lang` varchar(127) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`verified` tinyint(1) unsigned NOT NULL DEFAULT '0',
`uploader` int(11) unsigned NOT NULL DEFAULT '0',
`anonymous` tinyint(1) NOT NULL DEFAULT '0',
`enabled` tinyint(1) unsigned NOT NULL DEFAULT '0',
`tfile_size` int(11) unsigned NOT NULL DEFAULT '0',
`scrape_source` tinyint(1) unsigned NOT NULL DEFAULT '0',
`record_num` int(11) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`record_num`),
UNIQUE KEY `hash` (`hash`),
KEY `upload_date` (`upload_date`),
KEY `seeders` (`seeders`),
KEY `leechers` (`leechers`),
KEY `vote_up` (`vote_up`),
KEY `comments_count` (`comments_count`),
KEY `tfile_size` (`tfile_size`),
KEY `e_c_d_v` (`enabled`,`category`,`upload_date`,`verified`),
KEY `e_d_v` (`enabled`,`upload_date`,`verified`),
KEY `e_c_v` (`enabled`,`category`,`verified`),
KEY `e_v` (`enabled`,`verified`),
KEY `e_u` (`enabled`,`uploader`)
) ENGINE=InnoDB AUTO_INCREMENT=6733249 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=FIXED |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
edit: the once per day 6.5 mil rows update used to take 15 minutes in myisam and now it takes above 7-8+ hours (i killed the process) , i didn't know why , so i switched hash field to utf8_unicode_ci again from ascii_general_ci, but still same , so i guess ,it has to do with innodb .
if i set query_cache_type = 1 quereis are faster (first time it takes 1.xx but later same queries takes 0.00 )
if i comment out line query_cache_type = 1 , then *few* queries are slower. (first time it takes 1.xx and later too takes 1.xx)
there are 2 queries get fired on page load,
the 1st query which gets results counts. (required for pagination)
SELECT count(*) as total_results FROM content WHERE content.enabled = 1 ;
this is 2nd query which gets result counts
SELECT content.title, content.og_name, content.record_num, content.category, content.upload_date, content.hash, content.comments_count, content.verified, content.uploader, content.size, content.seeders, content.leechers FROM content WHERE enabled = 1 ORDER BY upload_date DESC LIMIT 0,25
first query is very slow while second query is slightly slow/not that slow.
so we will discuss about 1st query .
mysql> SELECT count(*) as total_results FROM content WHERE content.enabled = 1 ;
+---------------+
| total_results |
+---------------+
| 6686811 |
+---------------+
1 row in set (1.18 sec)
mysql> EXPLAIN SELECT count(*) as total_results FROM content WHERE content.enabled = 1 ;
+----+-------------+---------+------+-----------------------------+------+---------+-------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+-----------------------------+------+---------+-------+---------+-------------+
| 1 | SIMPLE | content | ref | e_c_d_v,e_d_v,e_c_v,e_v,e_u | e_v | 1 | const | 2899103 | Using index |
+----+-------------+---------+------+-----------------------------+------+---------+-------+---------+-------------+
1 row in set (0.00 sec)
no matter how many times i fire query , it takes 1+ seconds to retrun the count.
above query is simple query ,
this one is with more parameters or where conditions .
mysql> SELECT count(*) as total_results FROM content WHERE content.enabled = 1 AND category BETWEEN 400 AND 420 AND upload_date >= '2014-08-08' AND verified = 1;
+---------------+
| total_results |
+---------------+
| 8807 |
+---------------+
1 row in set (0.09 sec)
mysql> explain SELECT count(*) as total_results FROM content WHERE content.enabled = 1 AND category BETWEEN 400 AND 420 AND upload_date >= '2014-08-08' AND verified = 1;
+----+-------------+---------+-------+-----------------------------------------+---------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+-----------------------------------------+---------+---------+------+--------+--------------------------+
| 1 | SIMPLE | content | range | upload_date,e_c_d_v,e_d_v,e_c_v,e_v,e_u | e_c_d_v | 9 | NULL | 292518 | Using where; Using index |
+----+-------------+---------+-------+-----------------------------------------+---------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)
now queries with more parameters is quite fast as compared to simple query .,
any help is appreaciated.
additionally
[root@]# pt-query-digest /var/log/mysql-slow-queries1.log
*******************************************************************
Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
together with SSL_ca_file|SSL_ca_path for verification.
If you really don't want to verify the certificate and keep the
connection open to Man-In-The-Middle attacks please set
SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
at /usr/bin/pt-query-digest line 11832.
# 320ms user time, 20ms system time, 26.69M rss, 225.96M vsz
# Current date: Sat Aug 8 03:17:19 2015
# Hostname: localdomain
# Files: /var/log/mysql-slow-queries1.log
# Overall: 211 total, 16 unique, 0.00 QPS, 0.01x concurrency _____________
# Time range: 2015-07-30 10:38:32 to 2015-08-08 03:15:39
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 5840s 1s 569s 28s 151s 69s 1s
# Lock time 590ms 0 227ms 3ms 16ms 17ms 103us
# Rows sent 291.67M 0 47.24M 1.38M 6.29M 3.93M 0.99
# Rows examine 1.28G 0 47.24M 6.19M 6.29M 3.11M 6.29M
# Query size 23.36k 38 369 113.37 299.03 78.66 69.19
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ================== =============== ===== ======== ===== ===========
# 1 0xEFEA7E37189A1219 4465.5020 76.5% 36 124.0417 51.13 SELECT content
# 2 0x4FBE53CC3F7FAB0F 576.5739 9.9% 2 288.2870 54... SELECT content
# 3 0x67A347A2812914DF 417.8572 7.2% 3 139.2857 45.37 SELECT content_files
# 4 0xE9FA0FF18D274F23 186.1905 3.2% 147 1.2666 0.38 SELECT content
# MISC 0xMISC 193.4053 3.3% 23 8.4089 0.0 <12 ITEMS>
# Query 1: 0.00 QPS, 0.01x concurrency, ID 0xEFEA7E37189A1219 at byte 35812
# This item is included in the report because it matches --limit.
# Scores: V/M = 51.13
# Time range: 2015-07-31 08:56:51 to 2015-08-05 10:54:25
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 17 36
# Exec time 76 4466s 1s 515s 124s 151s 80s 144s
# Lock time 83 490ms 113us 227ms 14ms 40ms 38ms 144us
# Rows sent 71 209.16M 102.67k 6.36M 5.81M 6.29M 1.56M 6.29M
# Rows examine 16 209.16M 102.67k 6.36M 5.81M 6.29M 1.56M 6.29M
# Query size 32 7.56k 215 215 215 215 0 215
# String:
# Databases basesite
# Hosts localhost
# Users basesite
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms
# 1s #
# 10s+ ################################################################
# Tables
# SHOW TABLE STATUS FROM `basesite` LIKE 'content'\G
# SHOW CREATE TABLE `basesite`.`content`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT record_num, title, og_name, category, UNIX_TIMESTAMP(upload_date) as upload_date_timestamp, hash, vote_up, comments_count, verified, uploader, size, seeders, leechers , keywords FROM content WHERE enabled = 1\G
# Query 2: 0.03 QPS, 8.48x concurrency, ID 0x4FBE53CC3F7FAB0F at byte 22575
# This item is included in the report because it matches --limit.
# Scores: V/M = 546.48
# Time range: 2015-08-02 01:44:43 to 01:45:51
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 2
# Exec time 9 577s 8s 569s 288s 569s 397s 288s
# Lock time 0 254us 101us 153us 127us 153us 36us 127us
# Rows sent 2 7.79M 1.44M 6.35M 3.90M 6.35M 3.47M 3.90M
# Rows examine 0 7.79M 1.44M 6.35M 3.90M 6.35M 3.47M 3.90M
# Query size 0 76 38 38 38 38 0 38
# String:
# Databases basesite
# Hosts localhost
# Users root
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms
# 1s ################################################################
# 10s+ ################################################################
# Tables
# SHOW TABLE STATUS FROM `basesite` LIKE 'content'\G
# SHOW CREATE TABLE `basesite`.`content`\G
# EXPLAIN /*!50100 PARTITIONS*/
select * from content where enabled =1\G
# Query 3: 0.01 QPS, 1.32x concurrency, ID 0x67A347A2812914DF at byte 31445
# This item is included in the report because it matches --limit.
# Scores: V/M = 45.37
# Time range: 2015-08-02 03:26:22 to 03:31:39
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 1 3
# Exec time 7 418s 63s 254s 139s 246s 79s 97s
# Lock time 0 0 0 0 0 0 0 0
# Rows sent 19 55.66M 2.03M 47.24M 18.55M 46.53M 20.07M 6.29M
# Rows examine 4 55.66M 2.03M 47.24M 18.55M 46.53M 20.07M 6.29M
# Query size 0 159 48 57 53 56.92 4.12 51.63
# String:
# Databases basesite
# Hosts localhost
# Users root
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms
# 1s
# 10s+ ################################################################
# Tables
# SHOW TABLE STATUS FROM `basesite` LIKE 'content_files'\G
# SHOW CREATE TABLE `basesite`.`content_files`\G
SELECT /*!40001 SQL_NO_CACHE */ * FROM `content_files`\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
SELECT /*!40001 SQL_NO_CACHE */ * FROM `content_files`\G
# Query 4: 0.00 QPS, 0.00x concurrency, ID 0xE9FA0FF18D274F23 at byte 22575
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.38
# Time range: 2015-07-30 10:44:53 to 2015-08-08 03:15:39
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 69 147
# Exec time 3 186s 1s 9s 1s 1s 694ms 1s
# Lock time 13 79ms 70us 23ms 535us 167us 3ms 93us
# Rows sent 0 147 1 1 1 1 0 1
# Rows examine 71 934.91M 6.34M 6.38M 6.36M 6.29M 0 6.29M
# Query size 43 10.19k 71 71 71 71 0 71
# String:
# Databases basesite
# Hosts localhost
# Users basesite (133/90%), root (14/9%)
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms
# 1s ################################################################
# 10s+
# Tables
# SHOW TABLE STATUS FROM `basesite` LIKE 'content'\G
# SHOW CREATE TABLE `basesite`.`content`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT count(*) as total_results FROM content WHERE content.enabled = 1\G
Thanks for your time.