universe wrote:
Ok, this makes some sense! We are in fact using PHP and there are in fact queries known to be slow. Is it possible that the PHP end is timing out the connection and the backend is continuing to process the query?Hi David,I've seen a similar behavior on our machine. We had someone re-write the code for a particular PHP site that was producing tons of slow queries (while the CPU was 100% idle the script would still take 15 seconds to run its queries) and the problem was (sort of) fixed.
Have a constant look at "mysqladmin processlist", maybe there are tons of mysql processes sitting sleeping. You can adjust the max. wait time for (interactive) connections with: In your "my.cnf", under [mysqld]: set-variable = interactive_timeout=90 set-variable = wait_timeout=90 This will close sleeping connections after 90 seconds. Default is 8 days. However, this is not a real fix for your problems but might help temporary so that mysqld is not taking your server down. This only makes sense if you have sleeping processes (according to "mysqladmin processlist").
Ah! Thanks, this is very useful information.
This was actually the first thing I did. The query that shows up there consistently is a known problem, and I briefly griped about it on this list before, but no one seemed to have a solution. I'll reiterate, in case you might:Active the sleep log, it will show you which queries take up too much time (> 10 secs): In your "my.cnf", under [safe_mysqld]: log-slow-queries = /var/log/mysql-slow.log
SELECT DISTINCT
en_name as name, en_body as body,
news.id, en_headline as headline, mnemonic,
date_format(disp_stamp, '%Y') as year,
date_format(disp_stamp,'%m') as month,
date_format(disp_stamp,'%d') as day,disp_stamp
FROM
news, news_competitions, competitions
WHERE
(news.primary_section=competitions.id
OR
(news.id=news_competitions.newsid
AND
news_competitions.competitionid=competitions.id))
AND competitions.mnemonic='a_comp'
AND en_headline!=''
ORDER
by disp_stamp desc;
is an example of one of the slow queries.
mysql> describe news;
+-----------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+----------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| en_headline | text | YES | | NULL | |
| sp_headline | text | YES | | NULL | |
| en_body | text | YES | | NULL | |
| sp_body | text | YES | | NULL | |
| disp_stamp | datetime | YES | | NULL | |
| primary_section | int(11) | YES | MUL | NULL | |
| disp_order | int(11) | YES | MUL | NULL | |
+-----------------+----------+------+-----+---------+----------------+
mysql> describe news_competitions
-> ;
+---------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| newsid | int(11) | YES | | NULL | |
| competitionid | int(11) | YES | | NULL | |
| disp_order | int(11) | YES | | NULL | |
+---------------+---------+------+-----+---------+----------------+
mysql> describe competitions;
+---------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| mnemonic | text | YES | MUL | NULL | |
| en_name | text | YES | | NULL | |
| en_url | text | YES | | NULL | |
| competition | tinyint(1) | YES | | NULL | |
| has_section | tinyint(1) | YES | MUL | NULL | |
| disp_order | int(11) | YES | MUL | NULL | |
+---------------+-------------+------+-----+---------+----------------+
are the referenced tables.
An EXPLAIN on the above query yields:
+-------------------+------+-----------------+------+---------+-------+------+---------------------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------------------+------+-----------------+------+---------+-------+------+---------------------------------------------+
| competitions | ref | PRIMARY,mne | mne | 67 | const | 1 | where used; Using temporary; Using filesort |
| news_competitions | ALL | NULL | NULL | NULL | NULL | 1721 | |
| news | ALL | PRIMARY,section | NULL | NULL | NULL | 988 | where used |
+-------------------+------+-----------------+------+---------+-------+------+---------------------------------------------+
which seems very wrong, since the seuential scans are not warranted given the presence of the indexes.
When I remove the OR, it uses the indexes. It seems like a misbehaving query planner, but maybe there's something I'm not considering? Our workaround idea was to two queries, one with each part of the OR, and then do the union in the application. This *greaty* increases the speed, but is difficult to implement everywhere on our site. We'd like to not have to second-guess our database for performance reasons - can you see anything wrong with that query?
-dj
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
