Re: mysql to postgresql, performance questions

Yeb Havinga wrote:
> Greg Smith wrote:
>> MySQL corruption is one of the top ten cause of a MythTV system
>> crashing.
> It would be the same with PG, unless the pg cluster configuration with
> mythtv would come with a properly configured WAL - I had corrupted
> tables (and a personal wiki entry (the other mysql database in my
> house) *only* when I sometimes took the risk of not shutting down the
> machine properly when e.g. the remote was missing).

You can shutdown a PostgreSQL database improperly and it will come back
up again just fine unless a number of things have happened at just the
wrong time:

1) You've written something to disk
2) The write is sitting in in a write cache, usually on the hard drive,
but the OS believes the data has been written
3) There is a hard crash before that data is actually written to disk

Now, this certainly still happens with PostgreSQL; was just discussing
that yesterday with a client who runs an app on desktop hardware in
countries with intermittant power, and database corruption is a problem
for them. However, that's a fairly heavy write volume situation, which
is not the case with most MythTV servers. The actual window where the
WAL will not do what it's supposed to here is pretty narrow; it's easy
to trigger if you pull the plug when writing constantly, but that's not
a typical MythTV database load.

Also, moving forward, we'll see the default filesystem on more Linux
systems shift to ext4, and it's starting to lose even this
vulnerability--newer kernels will flush the data out to disk in this
situation using the appropriate drive command.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.us