3
  • MySQL 5.5.28

Running pt-query-digest on a slow query log, I get something like this:

# 1.2s user time, 10ms system time, 22.30M rss, 114.48M vsz # Current date: Wed Oct 24 23:44:05 2012 # Hostname: x # Files: /var/log/mysql/mysql-slow.log # Overall: 4.07k total, 220 unique, 0.00 QPS, 0.09x concurrency __________ # Time range: 2012-10-04 04:45:56 to 2012-10-24 23:35:52 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= # Exec time 158487s 10s 1522s 39s 130s 112s 13s # Lock time 2489s 0 736s 611ms 73us 16s 35us # Rows sent 1.16G 0 86.77M 299.28k 25.99k 3.36M 0.99 # Rows examine 21.47G 0 1.12G 5.40M 23.50M 21.71M 0.99 # Query size 326.48k 16 20.85k 82.08 258.32 385.79 31.70 # Profile # Rank Query ID Response time Calls R/Call Apdx V/M Item # ==== ================== ================ ===== ========= ==== ===== ==== # 1 0xD2E9F9911E27D964 12280.9558 7.7% 874 14.0514 0.00 7.84 CALL cpcplus_pre_genjs # 2 0xA1631F45049C9276 10005.1251 6.3% 25 400.2050 0.00 47... CALL selfserving_banner_addnew # 3 0xDC7BE1A7B0ACF971 8683.7438 5.5% 15 578.9163 0.00 30... CALL selfserving_campaign_update_inline ...

The cpcplus_pre_genjs stored procedure: http://fpaste.org/YMXd/

Try profiling this stored:

mysql> select @@profiling; +-------------+ | @@profiling | +-------------+ | 1 | +-------------+ 1 row in set (0.00 sec) mysql> call reportingdb.cpcplus_pre_genjs(); +---+ | 1 | +---+ | 1 | +---+ 1 row in set (15.24 sec) Query OK, 0 rows affected (15.24 sec) mysql> show profiles; +----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 8177 | 0.00015300 | INSERT INTO norep_banner_zone_no_gen_today_tmp1(id,userid,zoneid,location,bannerid) SELECT id,userid,zoneid,location,bannerid FROM norep_banner_zone_no_gen_today_tmp | | 8178 | 0.00075400 | INSERT INTO norep_zonebannertmp_bk(zoneid,block,location,bannerid) SELECT A.zoneid,103,A.location,A.bannerid FROM norep_banner_zone_no_gen_today_tmp A WHERE (SELECT COUNT(*) FROM norep_banner_zone_no_gen_today_tmp1 B WHERE B.userid = A.userid AND B.id > A.id) <1 | | 8179 | 0.00006600 | INSERT INTO norep_zonebannertmp(zoneid,block,location,bannerid) SELECT zoneid,block,location,bannerid FROM norep_zonebannertmp_bk | | 8180 | 0.00013100 | INSERT INTO norep_banner_channel_tmp(bannerid,channelid,location) SELECT A.bannerid,B.`ssv_channelid`,_location FROM norep_zonebannertmp_bk A INNER JOIN `ox_zones` B ON B.`zoneid` = A.zoneid WHERE A.zoneid = _zoneid AND A.location = _location | | 8181 | 0.00185200 | UPDATE norep_bannertmp1 A,( SELECT A.bannerid,SUM(D.money) money FROM norep_zonebannertmp_bk A INNER JOIN norep_banner_zone_in D ON D.bannerid = A.bannerid AND D.zoneid = A.zoneid AND D.location = A.location WHERE A.zoneid = _zoneid AND A | | 8182 | 0.00000900 | COMMIT | | 8183 | 0.03358200 | INSERT INTO norep_user_zone_tmp(userid,zoneid,location) SELECT DISTINCT C.`uid` userid,A.zoneid,A.location FROM norep_zonebannertmp A INNER JOIN `ox_banners` B ON B.`bannerid` = A.bannerid INNER JOIN `ox_campaigns` C ON C.`campaignid` = B.`campaignid` WHERE C.`revenue_type` = 10 | | 8184 | 0.01083300 | UPDATE `selfserving_user_zone_day_tmp` A, norep_user_zone_tmp B SET A.`num` = A.`num` + 1 WHERE A.`userid` = B.userid AND A.`zoneid` = B.zoneid AND A.`location` = B.location | | 8185 | 0.00175600 | INSERT INTO `selfserving_user_zone_day_tmp`( `userid`, `zoneid`, `location`, `num`) SELECT A.userid,A.zoneid,A.location,1 FROM norep_user_zone_tmp A LEFT JOIN `selfserving_user_zone_day_tmp` B ON B.`userid` = A.userid AND B.`zoneid` = A.zoneid AND B.`location` = A.location WHERE B.`num` IS NU | | 8186 | 0.11734900 | DELETE FROM `norep_cpcplus_genjs_temp` | | 8187 | 0.02444800 | INSERT INTO `norep_cpcplus_genjs_temp`( `bannerid`, `zoneid`, `location`,block) SELECT DISTINCT `bannerid`, `zoneid`, `location`, block FROM norep_zonebannertmp | | 8188 | 0.01252500 | UPDATE `norep_sys_config` A SET A.`cpcplus_flag_js`=1 | | 8189 | 0.01244800 | UPDATE `sys_services_monitoring` A SET A.`time_calc` = CURRENT_TIMESTAMP WHERE A.`id` = 31 | | 8190 | 0.00008500 | SELECT 1 | | 8191 | 0.00002500 | SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ | +----------+------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 15 rows in set (0.00 sec)

Can anyone help me optimize this?

Improve this question

1 Answer 1

Reset to default
1

Having taken a quick look at the Stored Procedure, I have three suggestions

SUGGESTION #1

The table norep_banner_zone_in (lines 198-210) is MyISAM. Perhaps it should a MEMORY table.

DROP TABLE IF EXISTS norep_banner_zone_in; CREATE TEMPORARY TABLE `norep_banner_zone_in` ( `channelid` INT , `zoneid` INT , `location` TINYINT(4) , `bannerid` INT , `CTR` DECIMAL(6,3) , `money` INT, KEY `channelid` (`channelid`) , KEY `zoneid` (`zoneid`) , KEY `bannerid` (`bannerid`) , KEY `location` (`location`) )ENGINE=MEMORY;

SUGGESTION #2

By default, table indexes for the MEMORY Storage Engine using HASH indexes. Try changing the CREATE TABLE statements on all the MEMORY tables to use BTREEs. This may help with any ranged-based queries (such as lines 306,318,425) and INNER JOINs.

For example, 

DROP TABLE IF EXISTS norep_banner_zone_in; CREATE TEMPORARY TABLE `norep_banner_zone_in` ( `channelid` INT , `zoneid` INT , `location` TINYINT(4) , `bannerid` INT , `CTR` DECIMAL(6,3) , `money` INT, KEY `channelid` (`channelid`) USING BTREE, KEY `zoneid` (`zoneid`) USING BTREE, KEY `bannerid` (`bannerid`) USING BTREE, KEY `location` (`location`) USING BTREE )ENGINE=MEMORY;

SUGGESTION #3

Instead of Dropping and Recreating the Table, why not Create if not exists and Truncate?

CREATE TEMPORARY TABLE IF NOT EXISTS `norep_banner_zone_in` ( `channelid` INT , `zoneid` INT , `location` TINYINT(4) , `bannerid` INT , `CTR` DECIMAL(6,3) , `money` INT, KEY `channelid` (`channelid`) USING BTREE, KEY `zoneid` (`zoneid`) USING BTREE, KEY `bannerid` (`bannerid`) USING BTREE, KEY `location` (`location`) USING BTREE )ENGINE=MEMORY; TRUNCATE TABLE norep_banner_zone_in;

Give it a Try !!!

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.