MySQL Optimization – How to Optimize Stored Procedures?

MySQLoptimizationstored-procedures

  • 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?

Best Answer

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

Related Question