Simpletests run significantly slower on PostgreSQL due to schema recreation

Subscribe.

Can you please post the time for MySQL vs. time for Postgres? Just to give a baseline against any improvements.

Also, if anyone has contacts for people in the PostgreSQL community, please point them to this issue so those interested can jump in with suggestions, patches, ...etc.

How do you have postgres configured? Its default configuration on most Linux distros is, as I understand, horrific. SSL is frequently enabled for no reason, the memory settings are all wrong, etc. I don't know the details for how to speed it up, but I'm sure Google does. That is probably worth looking into (and documenting!) before we try to micro-optimize the Drupal Postgres driver.

That said, I suspect most of the extra time in the Drupal Postgres driver is due to the need to hit the schema for all insert/update queries. There's no way around that because of Postgres' requirement for special handling of BLOB fields, at least not without patching PDO itself to do internally what we're doing in userspace. :-)

That said, I suspect most of the extra time in the Drupal Postgres driver is due to the need to hit the schema for all insert/update queries.

My experience of before-DB:TNG times, when I first battled to make simpletest work on PostgreSQL is that Drupal was already slower on PostgreSQL than on MySQL (both in their default Debian configuration). So I'm not sure it comes from the schema overhead.

Anyway, about that, I'm pretty sure we could issue a simply query against the information_schema to grab all the columns of type blob. That would avoid wasting memory with the full schema when we in fact only need to know which columns are blobs.

Hm. If the extra query is faster than hitting the schema, I'm fine with it. We can optimize for Postgres specifically in the driver however makes sense. That sounds like it could also at least partially solve #301038: Add a cross-compatible database schema introspection API. Damien, can you open a new issue to give that a try?

Write queries in PostgreSQL are always slower, when compared to mysql, due to the explicit syncing it does. (in order to protect the data.) (afaik)

#11 was already taken ;)

Well of course it would only be fair to compare PostgreSQL vs. Innodb.

you might want to use EXPLAIN to find the reason for the slow queries.

http://www.postgresql.org/docs/8.3/static/sql-explain.html

When comparing MySQL on MyISAM with PostgreSQL (with fsync = 0, and with a pgpool frontend with query caching) I see consistently that Drupal on PostgreSQL is two times slower than on MySQL (both with caching and without caching).

I'm inclined to remove this issue from the surge list. To keep Postgres in core it needs to work without bugs, not work at optimal speed. Vis, I don't think this issue is a blocker for supporting Postgres, especially when it looks like the main issue is not in PHP to begin with but in the server configuration.

Yeah, any sort of comparison against MyISAM is useless, given the fact that it's hardly the same thing. MyISAM doesn't do transactions, referential integrity, only does table-level locking, is not ACID compliant, etc.
So it does a lot less work and is thus able to do many more queries.
Also, MySQL has a built-in query cache, that, if enabled, will skew the results of a benchmark even more.

So, any sort of testing with less than ~100 concurrent database connections is more or less bound to come out in MyISAMs favour. So this is, in my humble opinion, not a bug.

/me agrees, not a surge item.

subscribing. i was amazed at how slow pgsql is running the tests. it'd be great to speed it up a bit just to keep people willing to test it.

A good way to make the tests run a lot faster on on PostgreSQL is to run them inside a transaction, like Django has just started to do. Then we can just rull back whatever we did, instead of having to reset the entire database for each test :)
http://simonwillison.net/2009/Jan/16/fast/

PostgreSQL and MySQL behaviour are quite different on different workloads.
Furthermore PostgreSQL spend some more time taking care of data integrity and fast roll backs.

Anyway a couple of tricks:
- you've to increase shared memory on most default Linux setup and then adjust shared memory in postgresql config too. PostgreSQL and Linux use very conservative setup.
Just hints
/etc/sysctl.conf
kernel.shmmax=134217728
kernel.shmall=2097152
postgresql.conf
shared_buffers = 120MB
work_mem = 32MB
Actual settings depends on memory on the box and quality of workload, but I'd say these are the first things to consider.

- EXPLAIN and EXPLAIN ANALYZE may help to tune queries
- VACUUM ANALYZE may help. I've seen dramatic improvements running queries on a just restored DB and on a just restored DB once I ran VACUUM ANALYZE
- log_min_duration_statement = may help to see where the problems are

The main advantage of PostgreSQL over MySQL is the ability to log long-running queries, have full access to statistics on index/disc use and optimize queries. This is a long issue to discuss. For example, UPDATEs can be written in batches, which boosts performance. Choose:
fsync = off

At first, you need to set shared memory to a good level and tune Postgresql.conf to make sure that indexes are loaded in memory. Then all your queries should run in less than 30 ms. Make sure you are using pgAdmin3 graphical interface. It has good representation of statistics on index/seqential scans.

I am presently migrating a 500.000 user messages board from PhpBB to Drupal. I log every query > 30 ms and there is a bunch of them.

In postgresql.conf, set:

log_min_duration_statement = 30

Example:

2009-03-13 16:05:24 CET LOG: durée : 305.646 ms, instruction : SELECT iid FROM aggregator_item WHERE fid = 55 AND link = 'http://www.ouest-france.fr/ofdernmin_-Le-groupe-immobilier-Celeos-repris-avec-une-centaine-de-salaries-en-moins_-855374--BKN_actu.Htm'

EXPLAIN SELECT iid FROM aggregator_item WHERE fid = 55 
AND link = 'http://www.ouest-france.fr/ofdernmin_-Le-groupe-immobilier-Celeos-repris-avec-une-centaine-de-salaries-en-moins_-855374--BKN_actu.Htm'

"  Recheck Cond: (fid = 55)"
"  Filter: ((link)::text = 'http://www.ouest-france.fr/ofdernmin_-Le-groupe-immobilier-Celeos-repris-avec-une-centaine-de-salaries-en-moins_-855374--BKN_actu.Htm'::text)"
"  ->  Bitmap Index Scan on aggregator_item_fid_idx  (cost=0.00..8.69 rows=58 width=0)"
"        Index Cond: (fid = 55)"

In this example you can see that the database is querying data using index on fid and then does a sequential scan on link. Looking at the database structure, you find that the table aggregator_item misses an index on link.

So I added the index manually, I hope you can add it to Drupal code:

CREATE INDEX aggregator_item_link
  ON aggregator_item
  USING btree
  (link);

Drupal is full of these issues. My logs are completely feed-up (Mbytes), which means that a real SQL query design work is needed to improve Drupal SQL queries. This is not a MySQL or a PostgreSQL issue. But using PostgreSQL during design helps and will benefit MySQL.

Also I would like to point out that I manage one site using MySQL with 1000 user and another using PostgreSQL with 500.000 nodes. The problem with MySQL is that it breaks under heavy load and sometimes your page is full of SQL queries with no result.

I would say: design Drupal and run large professional sites using PostgreSQL, and use MySQL for small/medium sites. I am not against MySQL, I can only discourage people from using MySQL in a serious development work like Drupal.

EXPLAIN SELECT iid FROM aggregator_item WHERE fid = 55
AND link = 'http://www.ouest-france.fr/ofdernmin_-Le-groupe-immobilier-Celeos-repris-avec-une-centaine-de-salaries-en-moins_-855374--BKN_actu.Htm'

Query plan shows full usage of index, no sequential scan i what PostgreSQL and ANY database including MySQL likes :

"Index Scan using aggregator_item_link on aggregator_item  (cost=0.00..8.28 rows=1 width=4)"
"  Index Cond: ((link)::text = 'http://www.ouest-france.fr/ofdernmin_-Le-groupe-immobilier-Celeos-repris-avec-une-centaine-de-salaries-en-moins_-855374--BKN_actu.Htm'::text)"
"  Filter: (fid = 55)"

After setting an index, the query runs in 60 ms:

Total query runtime: 61 ms.
1 rows retrieved.

Could someone add the index on aggregator_item?

I opened a Drupal query surge index page:
http://drupal.org/node/401040

Edit: I think I found a possibel flood of Drupal forum.

I wanted to provide clarification about some of the points raised above.

Any test that includes a heavy dose of table modifications (adding or dropping for example) is extraordinarly unfair to PostgreSQL, due to its transactional DDL capabilities. Basically, any change like that you make can be rolled back, which adds considerable overhead to the whole thing. See http://wiki.postgresql.org/wiki/Transactional_DDL_in_PostgreSQL:_A_Compe... for more details. This isn't really an important performance concern in production, where such changes are rare compared to routine queries, but it can really skew performance comparisons for typical regression tests that do lots of table changes.

If there's any common Drupal benchmark that focuses more on the query side of things, that might be a more fair comparison to run. Also, even as a fan I'd never suggest the PostgreSQL is the best choice for trivially sized data sets or user counts. Pop a million rows in and throw over 100 clients at the server if you want to start making a more real-world comparison here. There may very well still be a performance gap, but that would make it more likely you'd be optimizing the important bits here.

Tuning a PostgreSQL server for good performance hasn't really been well documented until around a year ago when I created http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server to collect the best practices here. That covers all the common optimizations now.

In older versions of PostgreSQL, the usual way to turn off the need to commit after every transactions was to set "fsync = off", as suggested above. Starting in PostgreSQL 8.3, the synchronous_commit feature provides similar speed improvements in a way that might even be safe enough to use in some production environments.

Postgres really expects that it has good data about the tables it's working with available, in the form of the statistics it collects via the ANALYZE feature. If you insert a large number of records, any recent version will try to take care of that automatically via the autovacuum daemon; older ones (before 8.2) aren't so good at that. Note that this is independent of the need to VACUUM old tables to get rid of dead rows. The only coupling between the two is that both are routine maintenance features and are therefore often combined into one operation: "VACUUM ANALYZE" is simply VACUUM followed by ANALYZE, and the autovacuum daemon handles both tasks.

Well, there are two different questions here (I voluntarily put aside the general SQL design considerations of jmpoure, which apply the same to all the database engines we support):

• How to improve the performance of testing on PostgreSQL? As noted above, testing is very DDL intensive, which strongly penalizes PostgreSQL. That's one part of the problem. Another part is that our testing framework supports running tests in parallel, but that seems to fail hard on PostgreSQL. Any ideas why would be very appreciated.
• How to improve general Drupal performance on PostgreSQL? At this time, we have no aggregated performance index for Drupal. But you can *feel* the difference between two local development environments running on MySQL and PostgreSQL. This "feeling" needs to be quantified and analyzed. Part of it is probably psychological, but there is definitely room for improvement in several key areas. We need to analyze the performance gap first, then discuss possible solutions.

This is not directed at any particular person in this thread, but rather a common trend that I have seen every time PostgreSQL is discussed.

We get people extolling the virtues of PostgreSQL vs. MySQL, that it is free and not owned by any entity, that it has a superior architecture, that it is more robust, ...etc.

All of the above is true in absolute terms, e..g when you are coding an application from scratch and decided from the start to use PostgreSQL, and optimize your application for it. It is a moot point though when we are talking about an existing application that is widely used such as Drupal, they are moot points.

So, to sum up: Enough preaching: we are on the same side, and we are tired of hearing this again and again. Most importantly, let us see practical solutions (i.e. concrete patches that make Drupal better with PostgreSQL, and testing/porting of existing modules to work with it).

If you care about PostgreSQL, no one will do the work for you. Jump in and join the rest of us ...

subscribing

Clarified title, the original bug report is about performance when running simpletests.

I think there might be a possible solution in truncating the tables between tests instead of having them recreated for each test, and then wrapping each test in a transaction.

Doing that would involve a bit of refactoring of the test system, though, and I wonder if it's worth the trouble.

chx recently noted to me that SimpleTest now has 2 base classes we can inherit from for unit test, one of which does not do a full reinstall. He recommended moving the bulk of the database test classes over to that since they really don't need a full Drupal install; just a working database. I wanted to get a few outstanding unit-test-adding patches committed before we do that sort of refactoring but that should greatly reduce the amount of schema traffic necessary for the DB tests, which currently are some of the slowest. That should help all DBs in test runs, but Postgres more than the others.

Crell — this is sort of tangental to this thread but the one problem with the db-less base classes is that there's no db at all. So you can very easily test functions that don't have any db-calls but sadly there are very few such candidates. Lots of code has a call to watchdog() in the error handling and other functions that require database support.

chx implied that the DB layer itself still works; that is, we can create and populate just the tables that the DB tests use and not the 50 others that Drupal includes by default. If PostgreSQL is slow on the table creation operations, that's a lot of table creations we can skip.

It's tangential, but tangential does mean it touches. :-)

Setting fsync=off in the postgresql.conf *massively* speeds up performance, but since this is a cluster-wide setting, think twice before you enable this on your production server. Some people from #postgresql on irc.freenode.net consider "fsync=off" equivalent to "inappropriate for any scenario where you care even a little bit about your data".

Another consideration might be to use TEMPORARY tables for simpletest, but then AFAIK all statements need to be within the same transaction.

I am closing this as in the latest release (7.90) the PostgreSQL tests run as fast as MySQL tests.

See the PostgreSQL test: https://drupal.org/comment/14538642 in
#3259739: [meta] Priorities for 2022-06-01 release of Drupal 7

If someone think that the "fixed" status will suit better, feel free to change it.