[MP2] Strange slow MySQL queries

[Erlend Simonsen <fu...@planetarion.com>]

I run a site with a few million MySQL requests a day, but I've run
into a strange problem which I'm trying to slove.

The server is running Apache 2.0.47, mod_perl2-1.99r09, DBI 1.38 and
DBD-mysql 2.1026 on a FreeBSD 4.8 machine. 

About once or twice a day, I get a bunch of slow queries that doesn't
look like they should be slow. And most of the day they aren't. 

Examples:

# Query_time: 51  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
update innodb_tbl set col1=col1-1000, col2=col2-1000, col3=col3-1000 
where primary_key=50;

# Query_time: 46  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
update innodb_tbl set col4=1 where primary_key=50;

# Query_time: 11  Lock_time: 0  Rows_sent: 1  Rows_examined: 0
select * from innodb_tbl where primary_key=1 for update;

.....

The list goes on and on... All of my slow queries are really fast at
all other times. Fast as in instant. This doesn't look like a load
issue, and I'm having problems figuring out what is causing this. 

Any ideas where to start digging and what I should try doing? I really
need this solved.

Regards,
Erlend Simonsen

Re: [MP2] Strange slow MySQL queries

[Patrick Mulvany <pa...@firedrake.org>]

Hi,

This looks more like a MySQL problem than a specific MP2 issue. However here are a couple of pointers.

All the queries below are updates on the same table this would indicate to me you may be having a locking issue. What you need to do is locate the locking transaction it may be as simple as :-

	select * from innodb_tbl for update;  

This locks the whole table and would block all of the quiries highlighted.

Hope it helps

Paddy

P.S. Please feel free to contact me off list if you wish to go into more depth.


Are you using Apache::DBI or transactions? 

On Mon, Sep 08, 2003 at 01:13:31PM +0200, Erlend Simonsen wrote:
> I run a site with a few million MySQL requests a day, but I've run
> into a strange problem which I'm trying to slove.
> 
> The server is running Apache 2.0.47, mod_perl2-1.99r09, DBI 1.38 and
> DBD-mysql 2.1026 on a FreeBSD 4.8 machine. 
> 
> About once or twice a day, I get a bunch of slow queries that doesn't
> look like they should be slow. And most of the day they aren't. 
> 
> Examples:
> 
> # Query_time: 51  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
> update innodb_tbl set col1=col1-1000, col2=col2-1000, col3=col3-1000 
> where primary_key=50;
> 
> # Query_time: 46  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
> update innodb_tbl set col4=1 where primary_key=50;
> 
> # Query_time: 11  Lock_time: 0  Rows_sent: 1  Rows_examined: 0
> select * from innodb_tbl where primary_key=1 for update;
> 
> .....
> 
> The list goes on and on... All of my slow queries are really fast at
> all other times. Fast as in instant. This doesn't look like a load
> issue, and I'm having problems figuring out what is causing this. 
> 
> Any ideas where to start digging and what I should try doing? I really
> need this solved.
> 
> Regards,
> Erlend Simonsen