hi,

I've just ran into some problems analyzing the slow-query-log.

1. I have many entries like:

# Time: 080312 13:07:33
# User@Host: cms[cms] @ localhost []
# Query_time: 17 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
COMMIT;
# User@Host: cms[cms] @ localhost []
# Query_time: 17 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
COMMIT;
# User@Host: cms[cms] @ localhost []
# Query_time: 21 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
COMMIT;
# User@Host: cms[cms] @ localhost []
# Query_time: 17 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
COMMIT;
# User@Host: cms[cms] @ localhost []
# Query_time: 12 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
COMMIT;
# User@Host: cms[cms] @ localhost []
# Query_time: 13 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
COMMIT;

Is there any possibility to get some verbose information about those
"COMMIT"s?

2. We encountered some deadlocks with innodb transactions. After tuning
some options:

innodb_file_per_table
innodb_buffer_pool_size=2G
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=2
transaction-isolation=READ-COMMITTED
innodb_log_buffer_size = 4M

they went away, but we're still seeing slow queries that are very simple
but on tables that are often written. for example:

# Time: 080312 13:15:17
# User@Host: cms[cms] @ localhost []
# Query_time: 18 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
SET timestamp=1205324117;
INSERT INTO login_try (website_id, login_tries) VALUES (96406, 1) ON
DUPLICATE KEY UPDATE login_tries = login_tries + 1;

here the table schema:

mysql> show create table login_try\G
*************************** 1. row ***************************
Table: login_try
Create Table: CREATE TABLE `login_try` (
`website_id` int(11) unsigned NOT NULL,
`login_tries` int(10) NOT NULL,
`modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
PRIMARY KEY (`website_id`),
KEY `modified` (`modified`),
CONSTRAINT `fk_logintry_1` FOREIGN KEY (`website_id`) REFERENCES
`website_config` (`basisID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

I assume that those slow queries occur because there's too much IO load
on the machines caused by other processes. Any hints?

Thanks,

-soenke

hi again,

for those that are interested: the problem was indeed the filesystem
with slow lookups of BIG directories (this had nothing to do with mysql
but caused much iowait and therefore the mysql process had been heavily
impacted).


Soenke Ruempler - NorthClick wrote:
> I assume that those slow queries occur because there's too much IO load
> on the machines caused by other processes. Any hints?
> [...]

-soenke

Hi,

On Mon, Mar 17, 2008 at 12:59 PM, Soenke Ruempler - NorthClick
<sr@northclick.de> wrote:
> hi again,
>
> for those that are interested: the problem was indeed the filesystem
> with slow lookups of BIG directories (this had nothing to do with mysql
> but caused much iowait and therefore the mysql process had been heavily
> impacted).

I'd be interested to know what filesystem you're using and how big the
directories are. When you say big, do you mean number of entries in
the directory, or space used?

Thanks
Baron

Hi Baron,

Baron Schwartz wrote:

> I'd be interested to know what filesystem you're using and how big the
> directories are. When you say big, do you mean number of entries in
> the directory, or space used?

There were about 70k files in /tmp (caused by a mistake). the web
application on this server had many lookups to tmp and those were slowed
down.

Filesystem is ext3 with dir_index turned-on, noatime on an hardware raid1.

-soenke

Wow! 70k files in /tmp. Hell of a mistake I hope it doesn't happen often.
Arthur

On 3/17/08, Soenke Ruempler - NorthClick <sr@northclick.de> wrote:
>
> Hi Baron,
>
>
> There were about 70k files in /tmp (caused by a mistake). the web
> application on this server had many lookups to tmp and those were slowed
> down.
>
> -soenke
>