hi,
I've just ran into some problems analyzing the slow-query-log.
1. I have many entries like:
# Time: 080312 13:07:33
# [EMAIL PROTECTED]: cms[cms] @ localhost []
# Query_time: 17 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
COMMIT;
# [EMAIL PROTECTED]: cms[cms] @ localhost []
# Query_time: 17 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
COMMIT;
# [EMAIL PROTECTED]: cms[cms] @ localhost []
# Query_time: 21 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
COMMIT;
# [EMAIL PROTECTED]: cms[cms] @ localhost []
# Query_time: 17 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
COMMIT;
# [EMAIL PROTECTED]: cms[cms] @ localhost []
# Query_time: 12 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
COMMIT;
# [EMAIL PROTECTED]: 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
# [EMAIL PROTECTED]: 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
