MyISAM, InnoDB, and PostgreSQL | Hacker News Reader

MyISAM, InnoDB, and PostgreSQL

sequel.heroku.com

32 pointsmattyb12 comments

The biggest differences normally arise with multiple queries running concurrently. For example, InnoDB & PostgreSQL use MVCC whereas MyISAM uses locking, and PostgreSQL normally wins most benchmarks with high concurrency on multi-core machines (8+).

I'm not certain I understand what you ran in the 'heavy' and 'light' threading tests. Can you provide some more details / explanation?

Everyone measures how expensive transactions are compared to no isolation, but I've never seen anyone switch off isolation and measure how often their code stumbles across a half-baked update from another thread, nor how badly their code screws up their data when it happens.

My problem with PostgreSQL is that you're half expected to tweak it to your needs. Ideally I would have to record and replay real life activity against the database for each adjustment. It's too much work to do properly. An expert could probably make a good first guess though. Which is why I've wondered if there is a tool where you drag a couple of sliders that describe your server specs and application nature and output config.

If you want to tune your database to squeeze every single Mhz and MB on your machine then indeed you need to take the time to learn the parameters that PostgreSQL offers you, and it does offer you those parameters, it's an extra compared to MySQL. But you don't have to, you can pretty much limit yourself to a few configuration parameters that are very useful and would get you 80% of the way, and of course add some monitoring, especially for slow queries.

A good starting point for setting up a database is here: http://www.commandprompt.com/blogs/joshua_drake/2010/03/5_st...

Not really. There are just a handful of parameters that should be set based mostly around the amount of RAM you would like to dedicate to the database. The last 10% of performance can be squeezed out by setting another half dozen or so parameters related to disk I/O and CPU capabilities. If you are really, REALLY worried about it, for about $500-$1000, I'm sure you could get any one of the qualified independent PostgreSQL consultants to take a look at your specs, tune your configuration, and help you benchmark the setup.

Thanks to rbranson and dialtone. Very useful comments.

My problem with PostgreSQL is that you're half expected to tweak it to your needs.

That's a myth. Postgres runs out of the box just as well as MySQL, with no more configuration effort.

A good starting point could be: http://www.revsys.com/writings/postgresql-performance.html

As you can see there is only a very small number of settings that you really should set consciously, just as you have to choose consciously between InnoDB/MyISAM and various baseline settings on MySQL.

Beyond these eight parameters (most of them relate to the amount of RAM you want it to use) postgres ships with reasonable defaults. All the other options may become interesting to tune it later for special workloads (something that you can hardly do with MySQL) but are usually safe to ignore.

The security settings that allow you to even connect are 1) hard to find, 2) somewhat difficult to track down documentation for, and 3) not what everyone would call "reasonable". I like Postgres, but it needs to evolve a more admin-centric attitude in my estimation.

Excuse me?

http://www.postgresql.org/docs/8.4/static/index.html

If it takes you more than 10 seconds to find "Client authentication" under "Server administration" then do you really think you are qualified to setup a database server?

Sorry if this comes across rude, but criticizing security in postgres in a comparison against MySQL is beyond absurd.

The grant-system in MySQL is a bloody mess. It doesn't even have Roles. And it is prone to "funny" side-effects, such as the dreaded anonymous user issue which afaik is still unfixed in latest stable.

In a nutshell: If you don't remove the two anonymous user grants that mysql puts in there by default then a grant to 'foo'@'%' will allow 'foo' to connect from any host except localhost. Makes sense? Not? Thought so.

Here's why: The connection attempt from localhost is rejected because the default entry ''@'localhost' matches before your 'foo'@'%' entry. So that's what we call admin-centric these days, shipping with mousetraps like that in the default config?

http://dev.mysql.com/doc/refman/5.0/en/connection-access.htm...

Then there's postgres, look how hard it is:

First you specify what users are allowed to connect from where and to which databases (pg_hba.conf). Then you create the actual users (CREATE USER). Finally you grant privileges to them (GRANT). Optionally you can group them under roles.

The common setup that allows local users to connect without a password as any user but demands a password from remote connections takes all of two lines in pg_hba.conf:

  # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
  host    all         all         0.0.0.0/0             md5
  local   all         all                               trust

Then you can make your users:

  CREATE USER foo WITH PASSWORD 'secret';

And give them access to stuff:

  GRANT ... TO foo;

Man that was hard. I'm all exhausted now. But hey, it's right there in the docs. With examples and all. http://www.postgresql.org/docs/8.4/static/auth-pg-hba-conf.h...

Right, so if you want to connect over the network? What happens? Oh, yeah -- it doesn't work. And then you have google and google with no real idea of what keywords to use other than 'postgres connection problem' until you find the arcane configuration that lets you connect.

Real simple. Yeah.

Have you ever looked at the postgres documentation at all?

We run an large ERP system against PostgreSQL with its default settings. It handles the load admirably, even with over 4000 users hitting it concurrently. I've never felt the need to tweak it.

Reply on news.ycombinator.com