Re: Shudden Slow down of MySQL Queries

[jayaram pagoti]

Good Morning James,

CREATE TABLE `availability` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`hotel_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`room_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`datum` datetime DEFAULT '0000-00-00 00:00:00',
`count` smallint(5) unsigned DEFAULT '0',
`price` int(14) unsigned DEFAULT NULL,
`validity_id` int(10) unsigned NOT NULL DEFAULT '0',
`manuell` tinyint(3) unsigned NOT NULL DEFAULT '0',
`hofesoda_garantietyp_id` tinyint(3) unsigned NOT NULL DEFAULT '2',
PRIMARY KEY (`id`),
UNIQUE KEY `room_id_datum` (`room_id`,`datum`),
KEY `iroom_id` (`room_id`),
KEY `idxCusObj` (`hotel_id`),
KEY `idxSaison` (`validity_id`)
) ENGINE=InnoDB

** as you said, we need to drop the indexs (iroom_id,idxSaison).

We are deleting old data based on datum column, " begin transaction: select id from availability where datum < 'yesterday', loop begins: delete from availability where id='?'; loop end: commit;

I have seen somewhere, that if the table's data has changed 1/16, then It Needs to be optimized (ALTER, ANALYZE).

And this problem not occurring daily while deleting operation also. It has been occurring after some time ( 60+ days) and suddenly all connections related to innodb waiting at 'freeing items, update' status.


One more is, We don't have proper load test applications in our environment. Shall we move directly to 5.5.4 if the application supports version upgrade. We can do testing using sysbench likely. Not able to generate load same as our production environment.

What are the monitoring tools (or) any do you prefer, for being proactive.

Thanks you very much for your great support !