- MySQL 5.5.28
Executando pt-query-digest em um log de consulta lento, obtenho algo assim:
# 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
...
O cpcplus_pre_genjsprocedimento armazenado: http://fpaste.org/YMXd/
Tente criar o perfil deste armazenado:
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)
Alguém pode me ajudar a otimizar isso?
