I have an application that currently uses a single database with thousands of tables (MyISAM) containing hit counter data. Is there a performance benefit or loss in using multiple databases vs a single database? The current database is about 106GB on disk.
single vs. multiple databases on a machine? Not realy. But you should think about using “multiple” Tables or Partitioning )
The data is currently being kept in a table for each day (which is why there are 1000s of tables going back to every day since 2002) but if there is a more efficient storage mechanism recommended I am all ears. IT currently takes about 10 hours to do a restore of this backup (backup using mysqldump to file) and if we can lower our backup/restore times it would be all the better.
I recommend to use LVM snapshots for backups. MySQL data directory should be on LVM partition for it.