So, recently we've been trying to figure out why Drupal loads so slowly at times on our system. One of the things we looked at was the MySQL statuses, to make sure everything was fine. We noticed that there were quite a lot of deadlocks and that it was causing delayed queries occasionally.
The statements that were deadlocking were all on the various cache tables (notably cache_page cache_path cache_menu). Based on the devel query log, there are several slow updates going out to these tables on each page load (which I do not understand, since I would have thought the menu and path caches would stay cached until I cleared it, and the page cache wouldn't be used for authenticated users). INNODB status from the MySQL server shows numerous deadlock occurrences during load testing (50 authenticated users), sometimes delaying queries up to 20 seconds (based on the status variables). It looks like, to me, that some kind of cache rebuilding is occurring on each request and these are conflicting when we have concurrent accesses (there are no problems with one user).
We tried logging slow queries to see if we could identify what was causing the deadlock but the only thing that comes out of it is the statement USE {DATABASE}; SET TIMESTAMP = {time}; which was occasionally taking a whole 18 seconds to execute!
So does anyone know if this kind of deadlocking is normal in Drupal or if it's perhaps the cause of our slowness? And any ideas on how to further identify the slowness would be very helpful. Our site is really heavy on modules (A quick count shows over 300 available modules and about 280 database tables) so that might be it on it's own, but I am curious on this deadlocking issue.