Message-ID: <4BAA010E.3000101@2ndquadrant.com>
Date: Wed, 24 Mar 2010 08:09:50 -0400
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Yeb Havinga <yebhavinga@gmail.com>
CC: Tom Lane <tgl@sss.pgh.pa.us>, "Ross J. Reedstrom" <reedstrm@rice.edu>,
	pgsql-performance@postgresql.org
Subject: Re: mysql to postgresql, performance questions
References: <4BA23936.2030507@gmail.com> <4BA596D2.4050406@squeakycode.net>
	<20100323185232.GD6662@rice.edu> <5342.1269372121@sss.pgh.pa.us>
	<4BA934B9.9070401@2ndquadrant.com> <4BA9D39B.6020103@gmail.com>
In-Reply-To: <4BA9D39B.6020103@gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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@2ndQuadrant.com   www.2ndQuadrant.us