Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
  • 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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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 !!!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.