I'm well aware of the advantages of Postgres over MySQL in terms of data integrity, but from reading around the subject I was led to believe that the performance advantage that MySQL had over Postgres no longer existed and they ran about the same speed.
I've just created a brand new moodle install (1.9.3) on a dual core test machine with 2GB RAM. I created one for postgres (with a fair amount of optimisations enabled to make it use the RAM), and one with a standard mysql install with config settings taken from the huge template.
I then request the main page http://127.0.0.1/index.php repeatedly using http_load, and measure the speed at which it serve's pages. First with postgres then with mysql (postgres is off while mysql is being used, and visa-versa).
With postgres I get 13-14 requests per second, with MySQL I get 20-21 requests per second - a 50% improvement.
Disk IOs are minimal in both cases, suggesting that everything is in RAM.
Is this what others have seen, or am I doing something wrong?
In reply to Anthony Wright
Re: MySQL much faster that Postgres - is this true?
by Elvedin Trnjanin -
Is the MySQL table engine InnoDB or MyISAM? The former is slower and you'd really need to use the former in a comparison with Postgres.
The biggest performance benefit of Postgres is in scaling; for very large and highly concurrent tables, Postgres is shown to have better write performance.
Add something like 50k users and simulate 1k of them actually using Moodle, then you should be able to prove my hypothesis.
In reply to Elvedin Trnjanin
Re: MySQL much faster that Postgres - is this true?
by Martín Langhoff -
Agreed 200% - testing with an empty DB is very wrong. You need representative data in it.
(Edit: removed notes about config, the OP has done some tuning...)
The other thing is to ensure you have good statistics on the indexes. Vacuum full on Pg... and I can't remember the command on MySQL.
(Edit: removed notes about config, the OP has done some tuning...)
The other thing is to ensure you have good statistics on the indexes. Vacuum full on Pg... and I can't remember the command on MySQL.
Ok, so I've done some more testing with a sizable database, and still get the same result.
I've taken the database from a secondary school that uses moodle heavily. The database is a MySQL MyISAM, so I converted it to Postgres so that I have identical data content to test against. I then fetched /login/index.php 1000 times. MySQL was 2x faster than Postgres.
The setup I have is a dual core virtual machine system, with one VM for the Database Server and one VM for the Web Server. The Database Server has the latest production versions of MySQL and Postgres installed, but I only run one at a time. The system is a clean system, and the databases have been loaded from dumps. MySQL is using the huge configuration settings, Postgres has been tuned to make it use the available RAM - I've looked at loads of performance guides, including the moodle postgres one.
The VM architecture allows me to see how much CPU time has been given to each VM, which gives some interesting results.
MySQL (MyISAM tables)
55 fetches/second
2.5 CPU seconds Database Server
31.9 CPU seconds Web Server
Postgres
27.7 fetches/second
27.9 CPU seconds Database Server
31.1 CPU second Web Server
I tried to convert the database to InnoDB, but I got an error:
ERROR 1214 (HY000) at line 1117: The used table type doesn't support FULLTEXT indexes
So it seems that I can only convert some tables to InnoDB, but I don't know which ones at this stage.
What I don't understand is why Postgres takes 10x the CPU power to retrieve the data from RAM as MySQL, which would suggest that in the setup I have, MySQL is actually 10x faster that Postgres, but this impact is being softened by my VM setup. This is surely going to be a read only operation so I can't see how any of the Postgres integrity checking should impact on this.
I've taken the database from a secondary school that uses moodle heavily. The database is a MySQL MyISAM, so I converted it to Postgres so that I have identical data content to test against. I then fetched /login/index.php 1000 times. MySQL was 2x faster than Postgres.
The setup I have is a dual core virtual machine system, with one VM for the Database Server and one VM for the Web Server. The Database Server has the latest production versions of MySQL and Postgres installed, but I only run one at a time. The system is a clean system, and the databases have been loaded from dumps. MySQL is using the huge configuration settings, Postgres has been tuned to make it use the available RAM - I've looked at loads of performance guides, including the moodle postgres one.
The VM architecture allows me to see how much CPU time has been given to each VM, which gives some interesting results.
MySQL (MyISAM tables)
55 fetches/second
2.5 CPU seconds Database Server
31.9 CPU seconds Web Server
Postgres
27.7 fetches/second
27.9 CPU seconds Database Server
31.1 CPU second Web Server
I tried to convert the database to InnoDB, but I got an error:
ERROR 1214 (HY000) at line 1117: The used table type doesn't support FULLTEXT indexes
So it seems that I can only convert some tables to InnoDB, but I don't know which ones at this stage.
What I don't understand is why Postgres takes 10x the CPU power to retrieve the data from RAM as MySQL, which would suggest that in the setup I have, MySQL is actually 10x faster that Postgres, but this impact is being softened by my VM setup. This is surely going to be a read only operation so I can't see how any of the Postgres integrity checking should impact on this.
I've done some more delving on moodle and InnoDB, and from what I understand from the moodle performance page and the forum post referenced there, MyISAM is the way to go with MySQL.
http://docs.moodle.org/en/Performance#MySQL_performance
So I keep getting stuck with my original problem made even more starkly by my last set of stats. MySQL seems to be 2x and possibly 10x faster than Postgres for me on the requests I'm using for benchmarking. I've seen all over the place people saying they run at about the same speed, or Postgres runs faster for them than MySQL. I've been through loads of Postgres performance web sites and applied most if not all of their recommendations.
What am I doing wrong?
http://docs.moodle.org/en/Performance#MySQL_performance
So I keep getting stuck with my original problem made even more starkly by my last set of stats. MySQL seems to be 2x and possibly 10x faster than Postgres for me on the requests I'm using for benchmarking. I've seen all over the place people saying they run at about the same speed, or Postgres runs faster for them than MySQL. I've been through loads of Postgres performance web sites and applied most if not all of their recommendations.
What am I doing wrong?
What am I doing wrong?
Hard to say! Many many variables at play. Non-persistent connections are more expensive with Pg, for example. Perhaps the VM scheduler is playing odd games between the 2 virtual boxes. Perhaps you are using MySQL's query cache which is great if your data isn't changing -- which is true during a benchmark run but false in real life usage. PostgreSQL version matters a lot - all the 8.x series have been closing the performance gap, and 8.3 is heads and shoulders above 8.2 on some of the areas where MySQL was better.
And perhaps MySQL is still faster for your workload, OS, hardware, etc. All the "X is faster" statements need to be qualified. Mine too
I have seen similar results to yours with small/medium datasets, but never with any larger datasets, specially when stuff does not fit in RAM, and/or is write-heavy. Talking about specific queries, MySQL outperforms Pg in trivial queries, Pg outperforms MySQL on complex queries (accesslib! ).
In any case, the consistency thing is a dealmaker for me -- whatever happens, Pg always comes back up, and my data is there
Yes... and that last point is not to be understated.
François from our Wellington dev centre just brought http://monty-says.blogspot.com/2008/11/oops-we-did-it-again-mysql-51-released.html to my attention: interesting post about MySQL processes and current state of 5.1 from (MySQL founder) Michael Widenius.
François from our Wellington dev centre just brought http://monty-says.blogspot.com/2008/11/oops-we-did-it-again-mysql-51-released.html to my attention: interesting post about MySQL processes and current state of 5.1 from (MySQL founder) Michael Widenius.
In reply to Myles Carrick
Re: MySQL much faster that Postgres - is this true?
by Fredrik Ndjurumana -
I had tested running moodle with MySQL and PostgreSQL and I got very big different in performance.
I got no problem with both database, when my moodle site hitted by no more than 15 users. The problem started when we got more than 20 users online at the same time. My moodle site that used MySQL database hang up, even the CPU usages under 40% and Memory Usage under 50%. But when i run with PostgreSQL, i got no problem even my moodle site hitted by more than 300 users.
In my experiences, running moodle with MySQL database, need higher Hardware specification than run it with PostgreSQL.
I got no problem with both database, when my moodle site hitted by no more than 15 users. The problem started when we got more than 20 users online at the same time. My moodle site that used MySQL database hang up, even the CPU usages under 40% and Memory Usage under 50%. But when i run with PostgreSQL, i got no problem even my moodle site hitted by more than 300 users.
In my experiences, running moodle with MySQL database, need higher Hardware specification than run it with PostgreSQL.
Catalyst did some benchmarking comparing MySQL ISAM to Postgres 8.3 some months ago for a client.
We used real data (1 GB database) and used JMeter to go through a reasonable sequence of page loads/actions.
In the end, with two fully tuned databases, we got a performance bonus of about 20% on MySQL.
Of course, the same tests with MySQL InnoDB would have probably looked completely different.
Francois
We used real data (1 GB database) and used JMeter to go through a reasonable sequence of page loads/actions.
In the end, with two fully tuned databases, we got a performance bonus of about 20% on MySQL.
Of course, the same tests with MySQL InnoDB would have probably looked completely different.
Francois
Maybe these are really stupid questions, but:
a) How do you know when your database is fully tuned?
I can get a bit of information out of postgres from vacuum analyse, but not much else, so the only way I have so far to change settings and see how my performance figures look like.
b) How do you measure performance to be able to accurately compare with MySQL?
The way I'm doing it, is repeatedly generate the same http request and look at the number of requests per second the systems will handle, and the CPU time taken to achieve that.
The problem I struggle with is that I have a have a reasonably large (700MB) production database. When I load it into MySQL and Postgres on my test system in such a way that the whole database seems to be in RAM, and then do a series of what should be read-only operations, Postgres seems to use 10x the CPU to do the same thing.
I can't come up with a good explanation of why this should be (everything's in RAM and it's read-only operations so no complex locking issues). I really want to use Postgres, and from comments like the above, it really seems the right way to go, but I need to understand why I have such performance differences in my tests, and preferably get postgres to use similar amounts of CPU to MySQL in the conditions I've described above.
a) How do you know when your database is fully tuned?
I can get a bit of information out of postgres from vacuum analyse, but not much else, so the only way I have so far to change settings and see how my performance figures look like.
b) How do you measure performance to be able to accurately compare with MySQL?
The way I'm doing it, is repeatedly generate the same http request and look at the number of requests per second the systems will handle, and the CPU time taken to achieve that.
The problem I struggle with is that I have a have a reasonably large (700MB) production database. When I load it into MySQL and Postgres on my test system in such a way that the whole database seems to be in RAM, and then do a series of what should be read-only operations, Postgres seems to use 10x the CPU to do the same thing.
I can't come up with a good explanation of why this should be (everything's in RAM and it's read-only operations so no complex locking issues). I really want to use Postgres, and from comments like the above, it really seems the right way to go, but I need to understand why I have such performance differences in my tests, and preferably get postgres to use similar amounts of CPU to MySQL in the conditions I've described above.
In reply to Anthony Wright
Re: MySQL much faster that Postgres - is this true?
by Elvedin Trnjanin -
To answer your part b -
The reason your MyISAM table is at the moment faster than InnoDB or Postgres is because you're only reading. Keep in mind that on a busy site there will be quite a lot of writing as well, especially to mdl_log.
Our mdl_log is something like 9 million records (pruned every 4 months by archiving courses) and it takes 20 seconds to find a list of courses that haven't been active in x months (nice query I found on this site) compared to InnoDB's 50-60 seconds.
Since MyISAM does table level locking, if there are 50 people viewing courses or taking quizes, etc, then the level of writing to those respective tables will slow down the queries per second considerably. We've switched to InnoDB because of this reason; even though it reads slower, under a load it scales better resulting in better performance.
I'd like to quantify this but I'm still working on building a JMeter test and I've have other higher priorities to finish first.
Add some writes to your test and you'll see the benefit. I've seen benchmarks that for very large databases, Postgres writes scale much better than writes to InnoDB.
The reason your MyISAM table is at the moment faster than InnoDB or Postgres is because you're only reading. Keep in mind that on a busy site there will be quite a lot of writing as well, especially to mdl_log.
Our mdl_log is something like 9 million records (pruned every 4 months by archiving courses) and it takes 20 seconds to find a list of courses that haven't been active in x months (nice query I found on this site) compared to InnoDB's 50-60 seconds.
Since MyISAM does table level locking, if there are 50 people viewing courses or taking quizes, etc, then the level of writing to those respective tables will slow down the queries per second considerably. We've switched to InnoDB because of this reason; even though it reads slower, under a load it scales better resulting in better performance.
I'd like to quantify this but I'm still working on building a JMeter test and I've have other higher priorities to finish first.
Add some writes to your test and you'll see the benefit. I've seen benchmarks that for very large databases, Postgres writes scale much better than writes to InnoDB.
In reply to Anthony Wright
Re: MySQL much faster that Postgres - is this true?
by Tariq Adel Al Ammadi -