Re: Innodb bogs down on complicated query(ies)

[Marko Mäkelä]

Lee,

Have you tried replacing the stored procedures with client-side code? As far as I have understood, the stored procedure implementation in MySQL tries to lock all tables for the duration of the execution of the procedure, in order to ensure that statement-based replication is deterministic. With row-based replication, the locking could be relaxed. Again, to my understanding, InnoDB downgrades the locks requested by MySQL in most cases.

There might also be some configuration option for controlling the locking. If you are not using statement-based replication, you could at least have a look at innodb_locks_unsafe_for_binlog.

Please let us know if this helps. If not, we will have a closer look, since performance and locking problems are important for us.

Best regards,

Marko Mäkelä
Senior Software Engineer
Innobase Oy