pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedTime to put theory to the test?
11+ messages / 10 participants
[nested] [flat]
* Time to put theory to the test?
@ 2011-04-25 19:30 J Sisson <sisson.j@gmail.com>
2011-04-26 03:04 ` Re: Time to put theory to the test? Rob Wultsch <wultsch@gmail.com>
2011-04-26 17:57 ` Re: Time to put theory to the test? Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 11+ messages in thread
From: J Sisson @ 2011-04-25 19:30 UTC (permalink / raw)
To: pgsql-performance
Not sure if this is the right list...but:
Disclaimer: I realize this is comparing apples to oranges. I'm not
trying to start a database flame-war. I just want to say thanks to
the PostgreSQL developers who make my life easier.
I manage thousands of databases (PostgreSQL, SQL Server, and MySQL),
and this past weekend we had a massive power surge that knocked out
two APC cabinets. Quite a few machines rebooted (and management is
taking a new look at the request for newer power cabinets heh).
Talking theory is one thing, predicting results is another...and yet
the only thing that counts is "what happens when 'worst-case-scenario'
becomes reality?"
Long story short, every single PostgreSQL machine survived the failure
with *zero* data corruption. I had a few issues with SQL Server
machines, and virtually every MySQL machine has required data cleanup
and table scans and tweaks to get it back to "production" status.
I was really impressed...you guys do amazing work. Thank you.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Time to put theory to the test?
2011-04-25 19:30 Time to put theory to the test? J Sisson <sisson.j@gmail.com>
@ 2011-04-26 03:04 ` Rob Wultsch <wultsch@gmail.com>
2011-04-26 06:30 ` Re: Time to put theory to the test? Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2011-04-26 14:13 ` Re: Time to put theory to the test? J Sisson <sisson.j@gmail.com>
1 sibling, 2 replies; 11+ messages in thread
From: Rob Wultsch @ 2011-04-26 03:04 UTC (permalink / raw)
To: J Sisson <sisson.j@gmail.com>; +Cc: pgsql-performance
On Mon, Apr 25, 2011 at 12:30 PM, J Sisson <sisson.j@gmail.com> wrote:
> machines, and virtually every MySQL machine has required data cleanup
> and table scans and tweaks to get it back to "production" status.
Tip from someone that manages thousands of MySQL servers: Use InnoDB
when using MySQL. Using a crash unsafe product will yield undesirable
results when a server crashes. It is also faster for many use cases.
InnoDB is crash safe. It is just that simple.
--
Rob Wultsch
wultsch@gmail.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Time to put theory to the test?
2011-04-25 19:30 Time to put theory to the test? J Sisson <sisson.j@gmail.com>
2011-04-26 03:04 ` Re: Time to put theory to the test? Rob Wultsch <wultsch@gmail.com>
@ 2011-04-26 06:30 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
1 sibling, 0 replies; 11+ messages in thread
From: Mark Kirkwood @ 2011-04-26 06:30 UTC (permalink / raw)
To: sisson.j@gmail.com; wultsch@gmail.com; +Cc: pgsql-performance
On 26/04/11 15:04, Rob Wultsch wrote:
> On Mon, Apr 25, 2011 at 12:30 PM, J Sisson<sisson.j@gmail.com> wrote:
>> machines, and virtually every MySQL machine has required data cleanup
>> and table scans and tweaks to get it back to "production" status.
> Tip from someone that manages thousands of MySQL servers: Use InnoDB
> when using MySQL. Using a crash unsafe product will yield undesirable
> results when a server crashes. It is also faster for many use cases.
>
> InnoDB is crash safe. It is just that simple.
>
+1
Or even switch to the Mariadb fork and use the crash safe Aria engine
[1] instead of Myisam if you must be without transactions. That has the
additional benefit of getting out from under the "Big O" - which is
always nice!
Cheers
Mark
[1] I have not personally tested that Aria is crash safe, but can attest
that Innodb certainly is.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Time to put theory to the test?
2011-04-25 19:30 Time to put theory to the test? J Sisson <sisson.j@gmail.com>
2011-04-26 03:04 ` Re: Time to put theory to the test? Rob Wultsch <wultsch@gmail.com>
@ 2011-04-26 14:13 ` J Sisson <sisson.j@gmail.com>
2011-04-26 14:58 ` Re: Time to put theory to the test? Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-26 15:02 ` Re: Time to put theory to the test? Scott Marlowe <scott.marlowe@gmail.com>
2011-04-26 15:20 ` Re: Time to put theory to the test? gnuoytr@rcn.com
2011-04-26 15:51 ` Re: Time to put theory to the test? Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 4 replies; 11+ messages in thread
From: J Sisson @ 2011-04-26 14:13 UTC (permalink / raw)
To: Rob Wultsch <wultsch@gmail.com>; +Cc: pgsql-performance
On Mon, Apr 25, 2011 at 10:04 PM, Rob Wultsch <wultsch@gmail.com> wrote:
> Tip from someone that manages thousands of MySQL servers: Use InnoDB
> when using MySQL.
Granted, my knowledge of PostgreSQL (and even MSSQL) far surpasses my
knowledge of MySQL, but if InnoDB has such amazing benefits as being
crash safe, and even speed increases in some instances, why isn't
InnoDB default? I suppose the real issue is that I prefer software
that gives me safe defaults that I can adjust towards the "unsafe" end
as far as I'm comfortable with, rather than starting off in la-la land
and working back towards sanity.
I'll concede that the issues we had with MySQL were self-inflicted for
using MyISAM. Thanks for pointing this out. Time to go get my
knowledge of MySQL up to par with my knowledge of PostgreSQL...
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Time to put theory to the test?
2011-04-25 19:30 Time to put theory to the test? J Sisson <sisson.j@gmail.com>
2011-04-26 03:04 ` Re: Time to put theory to the test? Rob Wultsch <wultsch@gmail.com>
2011-04-26 14:13 ` Re: Time to put theory to the test? J Sisson <sisson.j@gmail.com>
@ 2011-04-26 14:58 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-26 17:04 ` Re: Time to put theory to the test? Kenneth Marshall <ktm@rice.edu>
3 siblings, 1 reply; 11+ messages in thread
From: Kevin Grittner @ 2011-04-26 14:58 UTC (permalink / raw)
To: J Sisson <sisson.j@gmail.com>; Rob Wultsch <wultsch@gmail.com>; +Cc: pgsql-performance
J Sisson <sisson.j@gmail.com> wrote:
> Rob Wultsch <wultsch@gmail.com> wrote:
>> Tip from someone that manages thousands of MySQL servers: Use
>> InnoDB when using MySQL.
>
> Granted, my knowledge of PostgreSQL (and even MSSQL) far surpasses
> my knowledge of MySQL, but if InnoDB has such amazing benefits as
> being crash safe, and even speed increases in some instances, why
> isn't InnoDB default?
Because it's not as fast as the unsafe ISAM implementation for most
benchmarks.
There is one minor gotcha in InnoDB (unless it's been fixed since
2008): the release of locks is not atomic with the persistence of
the data in the write-ahead log (which makes it S2PL but not SS2PL).
So it is possible for another connection to see data that won't be
there after crash recovery. This is justified as an optimization.
Personally, I would prefer not to see data from other transactions
until it has actually been successfully committed.
-Kevin
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Time to put theory to the test?
2011-04-25 19:30 Time to put theory to the test? J Sisson <sisson.j@gmail.com>
2011-04-26 03:04 ` Re: Time to put theory to the test? Rob Wultsch <wultsch@gmail.com>
2011-04-26 14:13 ` Re: Time to put theory to the test? J Sisson <sisson.j@gmail.com>
2011-04-26 14:58 ` Re: Time to put theory to the test? Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2011-04-26 17:04 ` Kenneth Marshall <ktm@rice.edu>
0 siblings, 0 replies; 11+ messages in thread
From: Kenneth Marshall @ 2011-04-26 17:04 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: J Sisson <sisson.j@gmail.com>; Rob Wultsch <wultsch@gmail.com>; pgsql-performance
On Tue, Apr 26, 2011 at 09:58:49AM -0500, Kevin Grittner wrote:
> J Sisson <sisson.j@gmail.com> wrote:
> > Rob Wultsch <wultsch@gmail.com> wrote:
> >> Tip from someone that manages thousands of MySQL servers: Use
> >> InnoDB when using MySQL.
> >
> > Granted, my knowledge of PostgreSQL (and even MSSQL) far surpasses
> > my knowledge of MySQL, but if InnoDB has such amazing benefits as
> > being crash safe, and even speed increases in some instances, why
> > isn't InnoDB default?
>
> Because it's not as fast as the unsafe ISAM implementation for most
> benchmarks.
>
> There is one minor gotcha in InnoDB (unless it's been fixed since
> 2008): the release of locks is not atomic with the persistence of
> the data in the write-ahead log (which makes it S2PL but not SS2PL).
> So it is possible for another connection to see data that won't be
> there after crash recovery. This is justified as an optimization.
> Personally, I would prefer not to see data from other transactions
> until it has actually been successfully committed.
>
> -Kevin
>
In addition, their fulltext indexing only works with MyISAM tables.
Ken
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Time to put theory to the test?
2011-04-25 19:30 Time to put theory to the test? J Sisson <sisson.j@gmail.com>
2011-04-26 03:04 ` Re: Time to put theory to the test? Rob Wultsch <wultsch@gmail.com>
2011-04-26 14:13 ` Re: Time to put theory to the test? J Sisson <sisson.j@gmail.com>
@ 2011-04-26 15:02 ` Scott Marlowe <scott.marlowe@gmail.com>
3 siblings, 0 replies; 11+ messages in thread
From: Scott Marlowe @ 2011-04-26 15:02 UTC (permalink / raw)
To: J Sisson <sisson.j@gmail.com>; +Cc: Rob Wultsch <wultsch@gmail.com>; pgsql-performance
On Tue, Apr 26, 2011 at 8:13 AM, J Sisson <sisson.j@gmail.com> wrote:
> On Mon, Apr 25, 2011 at 10:04 PM, Rob Wultsch <wultsch@gmail.com> wrote:
>> Tip from someone that manages thousands of MySQL servers: Use InnoDB
>> when using MySQL.
>
> Granted, my knowledge of PostgreSQL (and even MSSQL) far surpasses my
> knowledge of MySQL, but if InnoDB has such amazing benefits as being
> crash safe, and even speed increases in some instances, why isn't
> InnoDB default? I suppose the real issue is that I prefer software
> that gives me safe defaults that I can adjust towards the "unsafe" end
> as far as I'm comfortable with, rather than starting off in la-la land
> and working back towards sanity.
Because for many read heavy workloads myisam is still faster. Note
that even if you use innodb tables, your system catalogs are stored in
myisam. The Drizzle project aims to fix such things, but I'd assume
they're a little ways from full production ready status.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Time to put theory to the test?
2011-04-25 19:30 Time to put theory to the test? J Sisson <sisson.j@gmail.com>
2011-04-26 03:04 ` Re: Time to put theory to the test? Rob Wultsch <wultsch@gmail.com>
2011-04-26 14:13 ` Re: Time to put theory to the test? J Sisson <sisson.j@gmail.com>
@ 2011-04-26 15:20 ` gnuoytr@rcn.com
3 siblings, 0 replies; 11+ messages in thread
From: gnuoytr@rcn.com @ 2011-04-26 15:20 UTC (permalink / raw)
To: pgsql-performance
---- Original message ----
>Date: Tue, 26 Apr 2011 09:13:17 -0500
>From: pgsql-performance-owner@postgresql.org (on behalf of J Sisson <sisson.j@gmail.com>)
>Subject: Re: [PERFORM] Time to put theory to the test?
>To: Rob Wultsch <wultsch@gmail.com>
>Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
>
>On Mon, Apr 25, 2011 at 10:04 PM, Rob Wultsch <wultsch@gmail.com> wrote:
>> Tip from someone that manages thousands of MySQL servers: Use InnoDB
>> when using MySQL.
>
>Granted, my knowledge of PostgreSQL (and even MSSQL) far surpasses my
>knowledge of MySQL, but if InnoDB has such amazing benefits as being
>crash safe, and even speed increases in some instances, why isn't
>InnoDB default?
because it is. recently.
http://dev.mysql.com/doc/refman/5.5/en/innodb-default-se.html
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Time to put theory to the test?
2011-04-25 19:30 Time to put theory to the test? J Sisson <sisson.j@gmail.com>
2011-04-26 03:04 ` Re: Time to put theory to the test? Rob Wultsch <wultsch@gmail.com>
2011-04-26 14:13 ` Re: Time to put theory to the test? J Sisson <sisson.j@gmail.com>
@ 2011-04-26 15:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-04-26 15:54 ` Re: Time to put theory to the test? Magnus Hagander <magnus@hagander.net>
3 siblings, 1 reply; 11+ messages in thread
From: Tom Lane @ 2011-04-26 15:51 UTC (permalink / raw)
To: J Sisson <sisson.j@gmail.com>; +Cc: Rob Wultsch <wultsch@gmail.com>; pgsql-performance
J Sisson <sisson.j@gmail.com> writes:
> Granted, my knowledge of PostgreSQL (and even MSSQL) far surpasses my
> knowledge of MySQL, but if InnoDB has such amazing benefits as being
> crash safe, and even speed increases in some instances, why isn't
> InnoDB default?
It *is* default in the most recent versions (5.5 and up). They saw
the light eventually. I wonder whether being bought out by Oracle
had something to do with that attitude adjustment ...
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Time to put theory to the test?
2011-04-25 19:30 Time to put theory to the test? J Sisson <sisson.j@gmail.com>
2011-04-26 03:04 ` Re: Time to put theory to the test? Rob Wultsch <wultsch@gmail.com>
2011-04-26 14:13 ` Re: Time to put theory to the test? J Sisson <sisson.j@gmail.com>
2011-04-26 15:51 ` Re: Time to put theory to the test? Tom Lane <tgl@sss.pgh.pa.us>
@ 2011-04-26 15:54 ` Magnus Hagander <magnus@hagander.net>
0 siblings, 0 replies; 11+ messages in thread
From: Magnus Hagander @ 2011-04-26 15:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: J Sisson <sisson.j@gmail.com>; Rob Wultsch <wultsch@gmail.com>; pgsql-performance
On Tue, Apr 26, 2011 at 17:51, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> J Sisson <sisson.j@gmail.com> writes:
>> Granted, my knowledge of PostgreSQL (and even MSSQL) far surpasses my
>> knowledge of MySQL, but if InnoDB has such amazing benefits as being
>> crash safe, and even speed increases in some instances, why isn't
>> InnoDB default?
>
> It *is* default in the most recent versions (5.5 and up). They saw
> the light eventually. I wonder whether being bought out by Oracle
> had something to do with that attitude adjustment ...
Oracle has owned innodb for quite some time. MySQL didn't want to make
themselves dependant on an Oracle controlled technology. That argument
certainly went away when Oracle bought them - and I think that was the
main reason. Not the "oracle mindset" or anything like that...
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Time to put theory to the test?
2011-04-25 19:30 Time to put theory to the test? J Sisson <sisson.j@gmail.com>
@ 2011-04-26 17:57 ` Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 11+ messages in thread
From: Josh Berkus @ 2011-04-26 17:57 UTC (permalink / raw)
To: pgsql-performance
J,
> Long story short, every single PostgreSQL machine survived the failure
> with *zero* data corruption. I had a few issues with SQL Server
> machines, and virtually every MySQL machine has required data cleanup
> and table scans and tweaks to get it back to "production" status.
Can I quote you on this? I'll need name/company.
And, thank you for posting that regardless ...
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2011-04-26 17:57 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-04-25 19:30 Time to put theory to the test? J Sisson <sisson.j@gmail.com>
2011-04-26 03:04 ` Rob Wultsch <wultsch@gmail.com>
2011-04-26 06:30 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2011-04-26 14:13 ` J Sisson <sisson.j@gmail.com>
2011-04-26 14:58 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-04-26 17:04 ` Kenneth Marshall <ktm@rice.edu>
2011-04-26 15:02 ` Scott Marlowe <scott.marlowe@gmail.com>
2011-04-26 15:20 ` gnuoytr@rcn.com
2011-04-26 15:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-04-26 15:54 ` Magnus Hagander <magnus@hagander.net>
2011-04-26 17:57 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox