Members Members   Search Search   Help Help   Register Register   Login Login   Home Home


Home » Performance » MySQL » single database vs multiple databases
single database vs multiple databases [message #3176] Fri, 13 June 2008 09:15 Go to next message
dsswanson is currently offline dsswanson
Messages: 3
Registered: June 2008
Junior Member
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.
Report message to a moderator
Re: single database vs multiple databases [message #3177 is a reply to message #3176 ] Fri, 13 June 2008 09:20 Go to previous messageGo to next message
erkules is currently offline erkules
Messages: 58
Registered: December 2007
Member
single vs. multiple databases on a machine? Not realy. But you should think about using "multiple" Tables or Partitioning Smile
Report message to a moderator
Re: single database vs multiple databases [message #3178 is a reply to message #3177 ] Fri, 13 June 2008 09:23 Go to previous messageGo to next message
dsswanson is currently offline dsswanson
Messages: 3
Registered: June 2008
Junior Member
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.
Report message to a moderator
Re: single database vs multiple databases [message #3179 is a reply to message #3178 ] Fri, 13 June 2008 10:05 Go to previous messageGo to next message
erkules is currently offline erkules
Messages: 58
Registered: December 2007
Member
If you dont want your application go for partition.
I would recommend to split it up into different tables. Speeds up dump and restore.

You could also use merging tables and compress older data.
Report message to a moderator
Re: single database vs multiple databases [message #3180 is a reply to message #3179 ] Fri, 13 June 2008 10:07 Go to previous messageGo to next message
dsswanson is currently offline dsswanson
Messages: 3
Registered: June 2008
Junior Member
How does partition work? I don't think i have compressed tables either... Where is good documentation on how this is done and what the ramifications are?
Report message to a moderator
Re: single database vs multiple databases [message #3182 is a reply to message #3180 ] Fri, 13 June 2008 16:30 Go to previous messageGo to next message
erkules is currently offline erkules
Messages: 58
Registered: December 2007
Member
Partitioning comes with 5.1 (http://dev.mysql.com/doc/refman/5.1/en/partitioning.html)
Report message to a moderator
Re: single database vs multiple databases [message #3230 is a reply to message #3176 ] Mon, 30 June 2008 05:33 Go to previous message
debug is currently offline debug
Messages: 128
Registered: March 2008
Senior Member
I recommend to use LVM snapshots for backups. MySQL data directory should be on LVM partition for it.

Mikhail Solovyev, MySQL Performance Expert
MySQL Performance Blog
MySQL Consulting
Report message to a moderator
Previous Topic:'Query execution was interrupted' (1317)
Next Topic:Dynamic Create table using cursor ??
Goto Forum:
  

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [ ]


Current Time: Fri Jan 9 00:20:48 EST 2009

Total time taken to generate the page: 0.01207 seconds
.:: Contact :: Home :: MySQL Support by Percona.com ::.

Powered by: FUDforum 2.7.5.
Copyright ©2001-2006 FUD Forum Bulletin Board Software

MySQL Performance | Forum authority Badge