I have 50 databases on a reasonably spec'd Debian MySQL Server v5.1.63 The size of these database vary from about 3MB -> 7GB each.
Each database has a history database and most( but not all) tables in the main database to write record changes back to the history database. (so there are about 25 main/25 history's). Each database has about 300 tables each. And there is an average of about 350 triggers all up.
Giving a total trigger count of about ~350 x 25 = ~8750 triggers.
Most of the triggers just do a before insert, or before update, they take a copy of the record and write it to this history table (so we could restore it or view the changes in a log).
I think we have pinpointed the bottleneck to the fact that all the databases are fighting over access to the information schema, because this is a MyISAM table, and it locks at a table level not a InnoDB table.
I know this is a Lot of triggers, but the database are actually quite small (averaging about 150mb each) and there is only about 80 users of which only about ~10->~20 are concurrently sending requests at any one time who use the application (PHP/MySQL). We plan to use our PHP framework to manage the historical records and remove a large number of the triggers if this will provide performance increases.
We are also considering upgrading to mysql 5.6 as I have heard that the information schema locks at (row level)?
The CPU and ram is hardly utilized at all on this server, and even running an hdparm test when it was going slow still produces a pretty good result.
Any help at all is greatly appreciated.