- 论坛徽章:
- 0
|
公司有一台外网服务器配置如下
CPU： Intel(R) Core(TM)2 CPU 6420 @ 2.13GHz
MEM:MemTotal: 3373240 kB
mysql: 5.0.51-log
单表最大数量不到十万，apache并发连接数一到150，mysql就会出现大量的锁表现象，系统负载一下就达到四五十。下面只是一段慢查询日志，可以看出sql语句表较冗长，请高手指点应该如何去优化
mysql slow query log
Report for slow logs: /usr/local/mysql/var/mysql-slow.log
1.49k queries total, 113 unique
Sorted by 't_sum'
Grand Totals: Time 60.33k s, Lock 31.63k s, Rows sent 693.18k, Rows Examined 213.08M
______________________________________________________________________ 001 ___
Count : 138 (9.26%)
Time : 10804 s total, 78.289855 s avg, 3 s to 198 s max (17.91%)
95% of Time : 9624 s total, 73.465649 s avg, 3 s to 162 s max
Lock Time (s) : 5428 s total, 39.333333 s avg, 0 to 123 s max (17.16%)
95% of Lock : 4576 s total, 34.931298 s avg, 0 to 120 s max
Rows sent : 9 avg, 9 to 9 max (0.18%)
Rows examined : 192 avg, 86 to 770 max (0.01%)
Database : B2B
Users :
root@localhost : 100.00% (13 of query, 100.00% (1491) of all users
Query abstract:
SELECT p.products_model AS relating_sku,p.products_image,pd.products_name,p.products_id,p.products_ordered ,p.products_price,p.products_date_added,p.products_tax_class_id FROM products AS p,products_to_categories AS ptc LEFT JOIN products_description AS pd ON ptc.products_id=pd.products_id WHERE ptc.products_id=p.products_id AND ptc.categories_id=N AND ptc.products_id!=N AND ((p.products_status=N AND p.limitquantity=N) OR (p.products_status=N AND p.limitquantity=N AND p.products_quantity>N)) ORDER BY p.products_date_added DESC LIMIT N;
Query sample:
select p.products_model as relating_sku,p.products_image,pd.products_name,p.products_id,p.products_ordered
,p.products_price,p.products_date_added,p.products_tax_class_id
from products as p,products_to_categories as ptc left join products_description as pd
on ptc.products_id=pd.products_id
where ptc.products_id=p.products_id
and ptc.categories_id=1820 and ptc.products_id!=23805 and ((p.products_status=1
and p.limitQuantity=0) or (p.products_status=1 and p.limitQuantity=1 and p.products_quantity>0))
order by p.products_date_added desc limit 9;
______________________________________________________________________ 002 ___
Count : 141 (9.46%)
Time : 9852 s total, 69.87234 s avg, 4 s to 170 s max (16.33%)
95% of Time : 8888 s total, 66.827068 s avg, 4 s to 91 s max
Lock Time (s) : 8643 s total, 61.297872 s avg, 0 to 128 s max (27.32%)
95% of Lock : 7874 s total, 59.203008 s avg, 0 to 82 s max
Rows sent : 1 avg, 1 to 1 max (0.02%)
Rows examined : 0 avg, 0 to 0 max (0.00%)
Database :
Users :
root@localhost : 100.00% (141) of query, 100.00% (1491) of all users
Query abstract:
SELECT p.products_id, pd.products_name,pd.products_seo_keywords, pd.products_description, p.products_model, p.products_quantity, p.products_image,p.products_imagetype,p.recommendation, pd.products_url, p.products_price, p.products_tax_class_id, p.products_date_added, p.products_date_available, p.manufacturers_id,p.products_cost, p.products_pricecurve,p.can_logo,p.limitquantity,pd.products_short_description FROM products p LEFT JOIN products_description pd ON p.products_id=pd.products_id WHERE p.products_status = 'S' AND p.products_id = 'S' AND pd.language_id = 'S';
Query sample:
select p.products_id, pd.products_name,pd.products_seo_keywords, pd.products_description, p.products_model,
p.products_quantity, p.products_image,p.products_imageType,p.recommendation, pd.products_url, p.products_price,
p.products_tax_class_id, p.products_date_added, p.products_date_available, p.manufacturers_id,p.products_cost,
p.products_pricecurve,p.can_logo,p.limitQuantity,pd.products_short_description
from products p left join products_description pd
on p.products_id=pd.products_id
where p.products_status = '1' and p.products_id = '23805'
and pd.language_id = '1';
______________________________________________________________________ 003 ___
Count : 71 (4.76%)
Time : 5717 s total, 80.521127 s avg, 5 s to 172 s max (9.48%)
95% of Time : 5034 s total, 75.134328 s avg, 5 s to 170 s max
Lock Time (s) : 4120 s total, 58.028169 s avg, 0 to 130 s max (13.02%)
95% of Lock : 3605 s total, 53.80597 s avg, 0 to 128 s max
Rows sent : 1 avg, 0 to 1 max (0.01%)
Rows examined : 0 avg, 0 to 0 max (0.00%)
Database :
Users :
root@localhost : 100.00% (71) of query, 100.00% (1491) of all users
Query abstract:
SELECT products_name FROM products_description WHERE products_id = 'S' AND language_id = 'S';
Query sample:
select products_name from products_description where products_id = '23805' and language_id = '1';
______________________________________________________________________ 004 ___
Count : 93 (6.24%)
Time : 5652 s total, 60.774194 s avg, 5 s to 119 s max (9.37%)
95% of Time : 5074 s total, 57.659091 s avg, 5 s to 113 s max
Lock Time (s) : 4763 s total, 51.215054 s avg, 0 to 104 s max (15.06%)
95% of Lock : 4245 s total, 48.238636 s avg, 0 to 102 s max
Rows sent : 1 avg, 1 to 1 max (0.01%)
Rows examined : 0 avg, 0 to 0 max (0.00%)
Database :
Users :
root@localhost : 100.00% (93) of query, 100.00% (1491) of all users
Query abstract:
SELECT COUNT(*) AS total FROM products p LEFT JOIN products_description pd ON p.products_id=pd.products_id WHERE p.products_status = 'S' AND p.products_id = 'S' AND pd.language_id = 'S';
Query sample:
select count(*) as total
from products p left join products_description pd on p.products_id=pd.products_id
where p.products_status = '1' and p.products_id = '23805' and
pd.language_id = '1';
______________________________________________________________________ 005 ___
Count : 58 (3.89%)
Time : 4746 s total, 81.827586 s avg, 3 s to 115 s max (7.87%)
95% of Time : 4404 s total, 80.072727 s avg, 3 s to 112 s max
Lock Time (s) : 4300 s total, 74.137931 s avg, 0 to 103 s max (13.59%)
95% of Lock : 3991 s total, 72.563636 s avg, 0 to 103 s max
Rows sent : 0 avg, 0 to 0 max (0.00%)
Rows examined : 0 avg, 0 to 0 max (0.00%)
Database :
Users :
root@localhost : 100.00% (5 of query, 100.00% (1491) of all users
Query abstract:
UPDATE products_description SET products_viewed = N WHERE products_id = 'S' AND language_id = 'S';
Query sample:
update products_description set products_viewed = 371 where products_id = '23805' and language_id = '1';
______________________________________________________________________ 006 ___
Count : 254 (17.04%)
Time : 4359 s total, 17.161417 s avg, 3 s to 84 s max (7.23%)
95% of Time : 3348 s total, 13.892116 s avg, 3 s to 74 s max
Lock Time (s) : 789 s total, 3.106299 s avg, 0 to 64 s max (2.49%)
95% of Lock : 378 s total, 1.568465 s avg, 0 to 10 s max
Rows sent : 0 avg, 0 to 0 max (0.00%)
Rows examined : 4 avg, 4 to 4 max (0.00%)
Database : B2B
Users :
root@localhost : 100.00% (254) of query, 100.00% (1491) of all users
Query abstract:
SELECT cache_id FROM cache WHERE cache_expires <= 'S';
Query sample:
SELECT cache_id from cache where cache_expires <= '2008-10-22 10:18:34';
______________________________________________________________________ 007 ___
Count : 104 (6.98%)
Time : 4310 s total, 41.442308 s avg, 4 s to 57 s max (7.14%)
95% of Time : 3973 s total, 40.540816 s avg, 4 s to 56 s max
Lock Time (s) : 277 s total, 2.663462 s avg, 0 to 28 s max (0.88%)
95% of Lock : 173 s total, 1.765306 s avg, 0 to 6 s max
Rows sent : 21 avg, 21 to 21 max (0.32%)
Rows examined : 84 avg, 84 to 84 max (0.00%)
Database :
Users :
root@localhost : 100.00% (104) of query, 100.00% (1491) of all users
Query abstract:
SELECT c.categories_id, cd.categories_name, c.parent_id FROM categories c,categories_description cd WHERE c.parent_id = 'S' AND c.categories_id = cd.categories_id AND cd.language_id='S' ORDER BY cd.categories_name;
Query sample:
select c.categories_id, cd.categories_name, c.parent_id from categories c,categories_description cd where c.parent_id = '' and c.categories_id = cd.categories_id and cd.language_id='1' order by cd.categories_name;
______________________________________________________________________ 008 ___
Count : 52 (3.49%)
Time : 1273 s total, 24.480769 s avg, 3 s to 80 s max (2.11%)
95% of Time : 1053 s total, 21.489796 s avg, 3 s to 69 s max
Lock Time (s) : 87 s total, 1.673077 s avg, 0 to 44 s max (0.28%)
95% of Lock : 16 s total, 326.531 ms avg, 0 to 4 s max
Rows sent : 1 avg, 1 to 1 max (0.01%)
Rows examined : 0 avg, 0 to 0 max (0.00%)
Database :
Users :
root@localhost : 100.00% (52) of query, 100.00% (1491) of all users
Query abstract:
SELECT cache_expires FROM cache WHERE cache_id='S' AND cache_language_id='S' LIMIT N;
Query sample:
SELECT cache_expires FROM cache WHERE cache_id='a93b9170a03ff54d81e95917742ea01b' AND cache_language_id='1' LIMIT 1;
______________________________________________________________________ 009 ___
Count : 55 (3.69%)
Time : 1177 s total, 21.4 s avg, 3 s to 84 s max (1.95%)
95% of Time : 930 s total, 17.884615 s avg, 3 s to 77 s max
Lock Time (s) : 159 s total, 2.890909 s avg, 0 to 60 s max (0.50%)
95% of Lock : 23 s total, 442.308 ms avg, 0 to 13 s max
Rows sent : 1 avg, 1 to 1 max (0.01%)
Rows examined : 159 avg, 0 to 1.41k max (0.00%)
Database :
Users :
root@localhost : 100.00% (55) of query, 100.00% (1491) of all users
Query abstract:
SELECT COUNT(*) AS total FROM products p, products_to_categories p2c WHERE p.products_id = p2c.products_id AND p.products_status = 'S' AND p2c.categories_id = 'S';
Query sample:
select count(*) as total from products p, products_to_categories p2c where p.products_id = p2c.products_id and p.products_status = '1' and p2c.categories_id = '1810';
______________________________________________________________________ 010 ___
Count : 75 (5.03%)
Time : 1083 s total, 14.44 s avg, 3 s to 81 s max (1.80%)
95% of Time : 791 s total, 11.140845 s avg, 3 s to 60 s max
Lock Time (s) : 54 s total, 720 ms avg, 0 to 12 s max (0.17%)
95% of Lock : 21 s total, 295.775 ms avg, 0 to 4 s max
Rows sent : 1 avg, 0 to 1 max (0.01%)
Rows examined : 0 avg, 0 to 0 max (0.00%)
Database :
Users :
root@localhost : 100.00% (75) of query, 100.00% (1491) of all users
Query abstract:
SELECT cache_id, cache_language_id, cache_name, cache_data, cache_global, cache_gzip, cache_method, cache_date, cache_expires FROM cache WHERE cache_id='S' AND cache_language_id='S';
Query sample:
SELECT cache_id, cache_language_id, cache_name, cache_data, cache_global, cache_gzip, cache_method, cache_date, cache_expires FROM cache WHERE cache_id='ca34fbe5f9a075091ad59abf02c259a7' AND cache_language_id='1';
Report for slow logs: /usr/local/mysql/var/mysql-slow.log
1.49k queries total, 113 unique
Sorted by 't_sum'
Grand Totals: Time 60.33k s, Lock 31.63k s, Rows sent 693.18k, Rows Examined 213.08M
______________________________________________________________________ 001 ___
Count : 138 (9.26%)
Time : 10804 s total, 78.289855 s avg, 3 s to 198 s max (17.91%)
95% of Time : 9624 s total, 73.465649 s avg, 3 s to 162 s max
Lock Time (s) : 5428 s total, 39.333333 s avg, 0 to 123 s max (17.16%)
95% of Lock : 4576 s total, 34.931298 s avg, 0 to 120 s max
Rows sent : 9 avg, 9 to 9 max (0.18%)
Rows examined : 192 avg, 86 to 770 max (0.01%)
Database : B2B
Users :
root@localhost : 100.00% (13 of query, 100.00% (1491) of all users
Query abstract:
SELECT p.products_model AS relating_sku,p.products_image,pd.products_name,p.products_id,p.products_ordered ,p.products_price,p.products_date_added,p.products_tax_class_id FROM products AS p,products_to_categories AS ptc LEFT JOIN products_description AS pd ON ptc.products_id=pd.products_id WHERE ptc.products_id=p.products_id AND ptc.categories_id=N AND ptc.products_id!=N AND ((p.products_status=N AND p.limitquantity=N) OR (p.products_status=N AND p.limitquantity=N AND p.products_quantity>N)) ORDER BY p.products_date_added DESC LIMIT N;
Query sample:
select p.products_model as relating_sku,p.products_image,pd.products_name,p.products_id,p.products_ordered
,p.products_price,p.products_date_added,p.products_tax_class_id
from products as p,products_to_categories as ptc left join products_description as pd
on ptc.products_id=pd.products_id
where ptc.products_id=p.products_id
and ptc.categories_id=1820 and ptc.products_id!=23805 and ((p.products_status=1
and p.limitQuantity=0) or (p.products_status=1 and p.limitQuantity=1 and p.products_quantity>0))
order by p.products_date_added desc limit 9;
______________________________________________________________________ 002 ___
Count : 141 (9.46%)
Time : 9852 s total, 69.87234 s avg, 4 s to 170 s max (16.33%)
95% of Time : 8888 s total, 66.827068 s avg, 4 s to 91 s max
Lock Time (s) : 8643 s total, 61.297872 s avg, 0 to 128 s max (27.32%)
95% of Lock : 7874 s total, 59.203008 s avg, 0 to 82 s max
Rows sent : 1 avg, 1 to 1 max (0.02%)
Rows examined : 0 avg, 0 to 0 max (0.00%)
Database :
Users :
root@localhost : 100.00% (141) of query, 100.00% (1491) of all users
Query abstract:
SELECT p.products_id, pd.products_name,pd.products_seo_keywords, pd.products_description, p.products_model, p.products_quantity, p.products_image,p.products_imagetype,p.recommendation, pd.products_url, p.products_price, p.products_tax_class_id, p.products_date_added, p.products_date_available, p.manufacturers_id,p.products_cost, p.products_pricecurve,p.can_logo,p.limitquantity,pd.products_short_description FROM products p LEFT JOIN products_description pd ON p.products_id=pd.products_id WHERE p.products_status = 'S' AND p.products_id = 'S' AND pd.language_id = 'S';
Query sample:
select p.products_id, pd.products_name,pd.products_seo_keywords, pd.products_description, p.products_model,
p.products_quantity, p.products_image,p.products_imageType,p.recommendation, pd.products_url, p.products_price,
p.products_tax_class_id, p.products_date_added, p.products_date_available, p.manufacturers_id,p.products_cost,
p.products_pricecurve,p.can_logo,p.limitQuantity,pd.products_short_description
from products p left join products_description pd
on p.products_id=pd.products_id
where p.products_status = '1' and p.products_id = '23805'
and pd.language_id = '1';
______________________________________________________________________ 003 ___
Count : 71 (4.76%)
Time : 5717 s total, 80.521127 s avg, 5 s to 172 s max (9.48%)
95% of Ti... |
|
免费注册
发表于 2008-10-22 16:51
发表于 2008-10-23 07:45
