4 ms·

MyISAM, InnoDB, and PostgreSQL

32 points by mattyb 17y ago

jokull 17y ago
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.
- dialtone 17y ago
  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_steps_to_postgresql_performance/ http://www.commandprompt.com/blogs/joshua_drake/2010/03/5_st...
- rbranson 17y ago
  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.
- jokull 17y ago
  Thanks to rbranson and dialtone. Very useful comments.
- moe 17y ago
  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 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.
  - sabat 17y ago
    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.
    - moe 17y ago
      Excuse me? http://www.postgresql.org/docs/8.4/static/index.html 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.html 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.html http://www.postgresql.org/docs/8.4/static/auth-pg-hba-conf.h...
- rufugee 17y ago
  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.
justinsb 17y ago
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?
prodigal_erik 17y ago
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.

32 points by mattyb 17y ago

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.

dialtone 17y ago
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_steps_to_postgresql_performance/ http://www.commandprompt.com/blogs/joshua_drake/2010/03/5_st...
rbranson 17y ago
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.
jokull 17y ago
Thanks to rbranson and dialtone. Very useful comments.
moe 17y ago
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 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.
- sabat 17y ago
  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.
  - moe 17y ago
    Excuse me? http://www.postgresql.org/docs/8.4/static/index.html 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.html 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.html http://www.postgresql.org/docs/8.4/static/auth-pg-hba-conf.h...
rufugee 17y ago
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.

justinsb 17y ago

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?

prodigal_erik 17y ago

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.