http://www.mysql.com/doc/en/Internal_locking.html
gives it a higher priority, but that didn't fix the problem.
This is a desperate, devastating problem that could simply destroy our business if we don't get it fixed soon -- any help is appreciated.
-Bennett
At 10:27 PM 7/26/2003 -0700, Bennett Haselton wrote:
Jackson,
Thanks for your help. Two follow-ups:
a) Is there a command to show all the table locks, or locks on a particular table? I searched http://www.mysql.com/doc/en/index.html for locking and locks, but I couldn't find anything.
b) I'm reading
http://www.mysql.com/doc/en/Internal_locking.html
and it sounds like if the table doesn't have any write locks, you can have as many concurrent reads on it as you want, right? I know that there were definitely not any write operations on that table going on during some of the times when the SELECT query would randomly run for 10-20 seconds instead of 1 second, so I'm not sure if that's causing it. There could be other read operations going on at that time, though.
-Bennett
At 11:24 PM 7/26/2003 -0500, Jackson Miller wrote:This could be caused by table locking. If another Mysql process ha a lock on
the table then other processes start to queue up. Maybe this query is
getting stuck behind other queries or a single slow query.
Just a thought.
-Jackson
On Saturday 26 July 2003 11:00, Bennett Haselton wrote:
> I have a MySQL query running inside a CGI script on my site that, at random
> intervals, seems to take 10-20 seconds to complete instead of less than 1
> second. I spent so much time trying to track this down that I wrote a
> script which runs once a minute on the site, which (a) captures the output
> of "ps auwx" (listing all processes) so I can see if that has anything to
> do with the slowdown; (b) times how long it takes to run the query, and;
> (c) times how long it takes to run a similar query on a much smaller
> table. (Part (c) is so that I can separate out whether it's the size of
> the table in part (b) that's making the difference, or the time taken to do
> something that's common to both queries, like getting a database handle.)
>
> 90% of the time, the large-table query takes less than 1 second, but 10% of
> the time, it takes 15-20 seconds. (The small-table query always takes less
> than 1 second.) I looked at the output of "ps auwx" to see if there seemed
> to be a relationship between the %CPU time used by other processes, or the
> number of other running processes, and the speed of the query, but there
> didn't seem to be.
>
> So, my questions are:
> (a) What is the usual cause of this type of problem?
> (b) The query I'm running is:
> SELECT * FROM news_feed_item WHERE news_feed_owner_userid = $my_id;
> on a table whose description is:
> +------------------------+------------------+------+-----+---------+-------
>---------+
>
> | Field | Type | Null | Key | Default |
>
> Extra |
> +------------------------+------------------+------+-----+---------+-------
>---------+
>
> | ID | int(10) unsigned | | PRI | NULL |
>
> auto_increment |
>
> | news_feed_owner_userid | int(10) unsigned | YES | MUL |
>
> NULL | |
>
> | URL | varchar(255) | YES | |
>
> NULL | |
>
> | title | varchar(255) | YES | |
>
> NULL | |
>
> | description | text | YES | |
>
> NULL | |
>
> | date_and_time | datetime | YES | |
>
> NULL | |
>
> | news_site_name | varchar(255) | YES | |
>
> NULL | |
> +------------------------+------------------+------+-----+---------+-------
>---------+
>
> (if it wraps, widen message window to see it all).
>
> Since I've already defined an index on news_feed_owner_userid, is there
> anything else I can do to make this kind of query run faster on this table?
>
> -Bennett
>
> [EMAIL PROTECTED] http://www.peacefire.org
> (425) 497 9002
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
[EMAIL PROTECTED] http://www.peacefire.org (425) 497 9002
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
[EMAIL PROTECTED] http://www.peacefire.org (425) 497 9002
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
