Re: Speed of MySQL DBs

MySQL creates a distinct directory for each database you create. For each table you create, it creates three files, Two remain rather small and I believe handle the table structure, the third grows with the data. I once helped build what started out as a database of 1,590 tables. We broke it down to 159 databases of ten tables each and got far better performance. Unlike postgres, you can actually backup and restore the filesystem files, though using the dump function gives you a more portable snapshot of the data.

-- Hugh

Comment on Re: Speed of MySQL DBs

Replies are listed 'Best First'.
Re^2: Speed of MySQL DBs by john_oshea (Priest) on Feb 06, 2006 at 10:04 UTC
Huh? You can too back up PostgreSQL's files on disk, as long as the database itself isn't running (I'd presume MySQL would have the same limitation). If you want to back/restore up while the database is running, pg_dump/pg_restore are your friends.	[reply]
Re^3: Speed of MySQL DBs by hesco (Deacon) on Feb 06, 2006 at 17:45 UTC
That may be. But I had been given to understand that restoring such data to a new installation of pg would create issues related to the oid's. Consequently I have stuck to the dump and restore methods, which recreate the oid's when the data is re-inserted into the database tables. But I have been able to restore an installation and preserve the existing passwords without requiring a reset, by copying the /var/lib/mysql/mysql directory and dropping it into a new instance (of the same version). /var/lib/mysql being the datapath for the server and the mysql directory inside it storing the user, password and privilege data for the installation. -- Hugh	[reply]
Re^4: Speed of MySQL DBs by john_oshea (Priest) on Feb 06, 2006 at 18:58 UTC
I think the oid issue only bites if you have something dependent on oids remaining unchanged. As a (very) small data-point, I've moved databases between installations successfully and had no problems. I am, however, fortunate enough to have come across PostgreSQL around the time that the developers started talking about deprecating oids at some point (v7.2 onwards, I think), so I've just avoided them from the outset. And having just re-read the last paragraph, I think the phrase "Just my 2 cents worth" would probably be overstating it ;-)	[reply]