Re: postgre vs MySQL

At 11:37 AM 3/13/2008, Scott Marlowe wrote:

>I remember seeing something about some problems that using the
>tablespace per table option on some mysql site... goes to look...
>paraphrased from the Mysql Performance Blod... Using the
>innodb_file_per_table=1 setting really tends to work against you, as
>you tend to get lots of bloated tables over time. If all your innodb
>tables are in the same file, then when one frees space, another can
>use it. with files per table, you can't recover space this way.

With separate files per table, the space returns to the filesystem if
you run "optimize table" (something like "vacuum full").

In contrast when you have a single 70GB file with all the tables, it
NEVER shrinks unless you drop the entire database and reload it. That
could take more time than you get from the Boss/Customer standing
behind you (and asking every 5 minutes - "Is it back up yet?").

Also if you do that huge file thing:
1) From my experience, deleting stuff from innodb tables doesn't free
space up to be used by other tables, you still need to run optimize table.
2) I suspect even if you do "optimize table", often fragmentation or
something happens so not all space can be reclaimed - so that huge
file will tend to grow faster than your data does.
3) Depending on how much free _usable_ space there actually is left
in that huge file, optimize table could cause the single huge file to
get bigger because it makes a copy of the entire table.
4) The times when most admins want to do "optimize table" are often
the very times where 2)+3) could cause major unhappiness ;).

If you use innodb_file_per_table=1, you have a better idea of how
much space each table takes up, so you can figure out which tables
you should start with first and schedule shorter periods of time to
run "optimize table" on each table.

That said, many times it's just postponing the inevitable - you
regularly get some pain (optimize locks the table), the pain
gradually increases as your tables get bigger :). Hopefully by the
time the pain is a lot, people would have come up with better alternatives.

Currently postgresql's "vacuum full" also locks the affected tables.
Does 8.3 vacuum full effectively make a copy of the entire table? How
much extra space will the various vacuums use while vacuuming?

Regards,

Link.