Re: Оптимизация на база данни
От хоста са направили задълбочен анализ на проблема и получих това по и-мейл току що, не вярвах, че са такива кучета тия от HostGator
Greetings,
Thank you for your patience. Upon reviewing the MySQL activities for this account we have discovered a number of issues that prevent, or ultimately degrade, any effective MySQL index. It appears that you are operating an ad service via a Joomla component/plugin/module and this plugin is inefficient in design. This plugin appears to hold caching mechanisms in its code base but it not using the caching mechanisms its fullest potential. Additionally, the SQL queries associated with this plugin are the most offending, in regards to resource use. The queries typically consist of multiple joins and full table reads which inherently consume resources. I have documented two additional issues within the queries themselves below for your reference. If you or your programmer are responsible for the code base used by this program I would recommend the following: 1) Split the major statements (specifically SQL queries using JOIN's) out to single, simple, select statement! s 2) cache the result of each query for an appropriate time span to either disk or memcache 3) Review Issue #2 documented below. If you are not responsible for this component I would have to recommend that you either 1) investigate other mechanisms to produce the ad system you desire (Google Adsense?) 2) upgrade to a VPS/dedicated server and make the appropriate changes to the timeout of SQL queries appropriately. The latter option would be the easiest as it would allow for a simple migration that could be handled by our transfers/sales department and also provide insurance that our SQL monitoring service does not implement restrictions on this account in the future.
Issues regarding the queries themselves:
Issue #1
Comparison functions for the 'adcat.adid' and 'adcat.catid' cannot be improved within this query due to the comparison style. I would recommend splitting up this query completly and build the associatations from cache within PHP. This will reduce the MySQL load per query and increase performance is the data needed to build the associatations could be cached on disk or in a separate cache table.
Issue #2
From the first query: """ WHERE 1 AND (a.ad_headline LIKE '%404.%' OR a.ad_text LIKE '%404.%' OR a.ad_headline LIKE '%404.%' OR a.ad_text LIKE '%404.%' OR a.ad_headline LIKE '%404.%' OR a.ad_text LIKE '%404.%') """
There appears to be an issue with the query logic used in this query (repetitive 'LIKE' statements). Please have your developer investigate the use case of this query to determine a more suitable approach.
mysql> explain SELECT a.*, p.name as parent, p.id as parentid, c.name as cat, c.id as catid, u.username as user FROM jos_adsmanager_ads as a LEFT JOIN jos_adsmanager_adcat as adcat ON adcat.adid = a.id LEFT JOIN jos_users as u ON a.userid = u.id LEFT JOIN jos_adsmanager_categories as c ON adcat.catid = c.id LEFT JOIN jos_adsmanager_categories as p ON c.parent = p.id WHERE 1 AND (a.ad_headline LIKE '%404.%' OR a.ad_text LIKE '%404.%' OR a.ad_headline LIKE '%404.%' OR a.ad_text LIKE '%404.%' OR a.ad_headline LIKE '%404.%' OR a.ad_text LIKE '%404.%') AND a.published = 1 and c.published = 1 GROUP BY a.id ORDER BY `a`.`t_stars` DESC, a.date_created DESC ,a.id DESC LIMIT 0, 10;
+----+-------------+-------+--------+---------------+---------+---------+----------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+----------------------------+-------+----------------------------------------------+
| 1 | SIMPLE | adcat | index | PRIMARY | PRIMARY | 8 | NULL | 22043 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | obqbi_j15.adcat.adid | 1 | Using where |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | obqbi_j15.a.userid | 1 | |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | obqbi_j15.adcat.catid | 1 | Using where |
| 1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY | 4 | obqbi_j15.c.parent | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+----------------------------+-------+----------------------------------------------+
5 rows in set (0.00 sec)
mysql> explain SELECT a.id,a.views, a.ad_headline, a.category, a.date_created,p.id as parentid,p.name as parent,c.id as catid, c.name as cat FROM jos_adsmanager_ads as a LEFT JOIN jos_adsmanager_adcat as adcat ON adcat.adid = a.id LEFT JOIN jos_users as u ON a.userid = u.id LEFT JOIN jos_adsmanager_categories as c ON adcat.catid = c.id LEFT JOIN jos_adsmanager_categories as p ON c.parent = p.id WHERE c.published = 1 and a.published = 1 GROUP BY a.id ORDER BY a.date_created DESC ,a.id DESC LIMIT 0, 3;
Current database: obqbi_j15
+----+-------------+-------+--------+---------------+---------+---------+----------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+----------------------------+-------+----------------------------------------------+
| 1 | SIMPLE | adcat | index | PRIMARY | PRIMARY | 8 | NULL | 22043 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 4 | obqbi_j15.adcat.adid | 1 | Using where |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | obqbi_j15.a.userid | 1 | Using index |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | obqbi_j15.adcat.catid | 1 | Using where |
| 1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY | 4 | obqbi_j15.c.parent | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+----------------------------+-------+----------------------------------------------+
5 rows in set (0.00 sec)
mysql> explain SELECT DISTINCT a.id FROM jos_adsmanager_ads as a LEFT JOIN jos_adsmanager_adcat as adcat ON a.id = adcat.adid WHERE 1 AND (a.ad_headline LIKE '%404.%' OR a.ad_text LIKE '%404.%' OR a.ad_headline LIKE '%404.%' OR a.ad_text LIKE '%404.%' OR a.ad_headline LIKE '%404.%' OR a.ad_text LIKE '%404.%') AND a.published = 1;
Current database: obqbi_j15
+----+-------------+-------+------+---------------+---------+---------+---------------------+-------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+---------------------+-------+------------------------------+
| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 21997 | Using where; Using temporary |
| 1 | SIMPLE | adcat | ref | PRIMARY | PRIMARY | 4 | obqbi_j15.a.id | 1 | Using index; Distinct |
+----+-------------+-------+------+---------------+---------+---------+---------------------+-------+------------------------------+
2 rows in set (0.00 sec)
mysql> show indexes from jos_adsmanager_adcat;
+----------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| jos_adsmanager_adcat | 0 | PRIMARY | 1 | adid | A | 22043 | NULL | NULL | | BTREE | |
| jos_adsmanager_adcat | 0 | PRIMARY | 2 | catid | A | 22043 | NULL | NULL | | BTREE | |
+----------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)
mysql> show indexes from jos_adsmanager_ads ;
Current database: obqbi_j15
+--------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| jos_adsmanager_ads | 0 | PRIMARY | 1 | id | A | 21997 | NULL | NULL | | BTREE | |
+--------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)
