MySQL Forums
Forum List  »  Performance

Re: Utilize maximum system resources for better performance
Posted by: Ronald Brown
Date: August 08, 2015 01:27AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Utilize maximum system resources for better performance
1341
August 08, 2015 01:27AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.