Joomla MySQL 性能 [英] Joomla MySQL Performance
问题描述
我一直在开发一个版本为 2.5.11 的 Joomla 站点.该站点的访问量会非常高.
I have been developing a Joomla site with version 2.5.11.Site will be under very high traffic.
我的问题是关于 MySQL 查询性能.数据库包含大约60000行内容表，下面看到的查询(核心com_content文章模型查询)执行时间约为6秒.很慢.
My problem is about MySQL query performance. Database includes about 60000 rows in content table, and the query seen below (core com_content articles model query) execution time is about 6 seconds.Very slow.
SELECT a.id,
a.title,
a.alias,
a.title_alias,
a.introtext,
a.checked_out,
a.checked_out_time,
a.catid,
a.created,
a.created_by,
a.created_by_alias,
CASE
WHEN a.modified = 0 THEN a.created
ELSE a.modified
END as modified,
a.modified_by,
uam.name as modified_by_name,
CASE
WHEN a.publish_up = 0 THEN a.created
ELSE a.publish_up
END as publish_up,
a.publish_down,
a.images,
a.urls,
a.attribs,
a.metadata,
a.metakey,
a.metadesc,
a.access,
a.hits,
a.xreference,
a.featured,
LENGTH(a.fulltext) AS readmore,
CASE
WHEN badcats.id is not null THEN 0
ELSE a.state
END AS state,
c.title AS category_title,
c.path AS category_route,
c.access AS category_access,
c.alias AS category_alias,
CASE
WHEN a.created_by_alias > ' ' THEN a.created_by_alias
ELSE ua.name
END AS author,
ua.email AS author_email,
contact.id as contactid,
parent.title as parent_title,
parent.id as parent_id,
parent.path as parent_route,
parent.alias as parent_alias,
ROUND(v.rating_sum / v.rating_count, 0) AS rating,
v.rating_count as rating_count,
c.published,
CASE
WHEN badcats.id is null THEN c.published
ELSE 0
END AS parents_published
FROM #__content AS a
LEFT JOIN #__content_frontpage AS fp ON fp.content_id = a.id
LEFT JOIN #__categories AS c ON c.id = a.catid
LEFT JOIN #__users AS ua ON ua.id = a.created_by
LEFT JOIN #__users AS uam ON uam.id = a.modified_by
LEFT JOIN
(SELECT contact.user_id, MAX(contact.id) AS id, contact.language FROM
#__contact_details AS contact WHERE contact.published = 1 GROUP BY
contact.user_id, contact.language) AS contact ON contact.user_id =
a.created_by
LEFT JOIN #__categories as parent ON parent.id = c.parent_id
LEFT JOIN #__content_rating AS v ON a.id = v.content_id
LEFT OUTER JOIN
(SELECT cat.id as id FROM #__categories AS cat JOIN #__categories AS
parent ON cat.lft BETWEEN parent.lft AND parent.rgt WHERE parent.extension
= 'com_content' AND parent.published != 1 GROUP BY cat.id) AS badcats ON
badcats.id = c.id
WHERE CASE
WHEN badcats.id is null THEN a.state
ELSE 0
END = 1 AND
a.featured = 0 AND
a.id NOT IN (8921, 33722, 33728, 33729, 34187, 35047, 36784, 36236, 33724,
19522) AND
a.catid IN (8, 39, 40, 38, 72, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19,
20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 36, 37, 41) AND
(a.publish_up = '0000-00-00 00:00:00' OR
a.publish_up <= '2013-06-12 06:44:44') AND
(a.publish_down = '0000-00-00 00:00:00' OR
a.publish_down >= '2013-06-12 06:44:44')
GROUP BY a.id,
a.title,
a.alias,
a.title_alias,
a.introtext,
a.checked_out,
a.checked_out_time,
a.catid,
a.created,
a.created_by,
a.created_by_alias,
a.created,
a.modified,
a.modified_by,
uam.name,
a.publish_up,
a.attribs,
a.metadata,
a.metakey,
a.metadesc,
a.access,
a.hits,
a.xreference,
a.featured,
a.fulltext,
a.state,
a.publish_down,
badcats.id,
c.title,
c.path,
c.access,
c.alias,
uam.id,
ua.name,
ua.email,
contact.id,
parent.title,
parent.id,
parent.path,
parent.alias,
v.rating_sum,
v.rating_count,
c.published,
c.lft,
a.ordering,
parent.lft,
fp.ordering,
c.id,
a.images,
a.urls
ORDER BY publish_up DESC
LIMIT 4, 4
例如当我改变时
(a.publish_up = '0000-00-00 00:00:00' OR
a.publish_up <= '2013-06-12 06:44:44') AND
(a.publish_down = '0000-00-00 00:00:00' OR
a.publish_down >= '2013-06-12 06:44:44')
与
(a.publish_up >= DATE_SUB(NOW(), INTERVAL 2 MONTH))
查询执行时间约为 0.5 秒
Query execution time is about ~0.5second
查询说明:
不破解核心代码怎么办，我从来不想破解核心代码...
What can I do without hacking the core code,I never want to hack the core code...
MySQL 服务器配置:
MySQL server Config:
skip-external-locking
skip-name-resolve
key_buffer_size = 128M
max_connections=1024
max_allowed_packet = 16M
net_buffer_length = 8K
table_open_cache = 512
table_cache = 2048
join_buffer_size = 128M
read_buffer_size = 128M
sort_buffer_size = 128M
read_rnd_buffer_size=2M
myisam_sort_buffer_size=128M
thread_concurrency=16
query_cache_size = 128M
query_cache_type=1
thread_cache_size = 1300
query_cache_limit=128M
max_heap_table_size = 1024M
tmp_table_size = 1024M
推荐答案
我发现的最好的方法是改进非常慢的查询以删除对存档的引用(这违背了存档状态的全部目的，即从列表结果中删除这些项目).只需获取已发布(或已发布 + 未发布)的项目，因此 state=1 或 state IN(0,1) 您可以使用 http://extensions.joomla.org/extensions/style-a-design/templating/15611
The best way I've found to improve that very slow query it to remove the references to archive (which defeat the entire purpose of the archive status which is to remove those items from list results). Just take items that are published (or published + unpublished) so eiher state=1 or state IN(0,1) You could look at possibly using http://extensions.joomla.org/extensions/style-a-design/templating/15611
切换到 InnoDB 可能也会有所帮助，但拥有最新版本的 MySQL 是获得改进的最大方法.
Switching to InnoDB will possibly help also but having the most recent possible version of MySQL is the biggest way to get improvements.
关于你现在可以做什么"，我很想看到你的拉取请求，为该查询添加日期限制.我们最近向 JDatabaseQuery 添加了数据数学，所以我做起来相对简单，而且会是一个非常好的改进.还有一些其他未决问题会对性能产生重大影响，如果您对其进行测试和评论，它们将进入下一个版本的代码库.例如:https://github.com/joomla/joomla-cms/pull/1274
About "what can you do now" I would love to see a pull request from you to add date limitations to that query. We have recently added data math to JDatabaseQuery so i is relatively simple to do and would be a really nice improvement. There are also some other pending issues that have big performance impacts and if you test and comment on them they will move forward into the code base for the next release. For example: https://github.com/joomla/joomla-cms/pull/1274
这篇关于Joomla MySQL 性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
