As my tables grew to more than a few hundreds, MySQL performance degraded

Question

As long as my tables were less than 100, MySQL performed reasonably well but when they started to grow to more than a few hundreds, it (MYSQL) has become extremely slow. Now my tables are >1k and are very small and most contain less than 20-30 records.

Is there a specific MySQL setting which I should consider, in order for MySQL to perform better with large numbers of tables?

Answer 1

What's slow? Are you doing mass inserts or complex joins and selects or simple queries? Database optimization is complex; the answer might be as simple as 'add an index' or as complex as tuning your caching or communication buffer sizes.

Answer 2

Try also these scripts for MySQL optimization: http://blog.mysqltuner.com/ and http://www.day32.com/MySQL/

Answer 3

Try table_cache = 1024. It will eat up some extra memory, but it should definitely improve performance.