Thread: Time to put theory to the test?
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.
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
On 26/04/11 15:04, Rob Wultsch wrote:
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.
+1On 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.
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.
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...
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
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.
---- 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
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
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/
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
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