PostgreSQL performance Optimization: PDO::ATTR_EMULATE_PREPARES [#827554]

The performance advantage to using prepared statements is the ability to recycle them. However, given Drupal's nature, Drupal doesn't really utilize this feature, rather it hardly ever recycles prepared statements. Instead, more time is spent preparing the statement by postgres than is needed which creates a performance decrease.

This is most noticeable with the test bot currently in construction. We think with PDO::ATTR_EMULATE_PREPARES set to FALSE, we'll be able to get the build time from 30s to 20s.

Considering MySQL already uses this same feature, I see no issues with commiting this to Drupal 7.

Comment	File	Size	Author
#8	827554-pgsql-more-comments.patch	1.13 KB	Josh Waihi
#8
#2	827554-better-comments.patch	1.07 KB	Josh Waihi
#2
	pgsql-ATTR_EMULATE_PREPARES.patch	820 bytes	Josh Waihi

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Comment #1

moshe weitzman CreditAttribution: moshe weitzman commented 15 June 2010 at 03:23

That comment is a bit lacking IMO. The variation of queries is different for each site. Consider Drupal 6 and its identical hundreds of url alias queries. Surely some contrib modules are doing the same. I think its fine to disable this for core but lets help folks understand why they might want to enable emulation.

Comment #2

Josh Waihi CreditAttribution: Josh Waihi commented 15 June 2010 at 05:32

File	Size
827554-better-comments.patch	1.07 KB

Better comments.

Comment #3

markir CreditAttribution: markir commented 15 June 2010 at 05:49

Note that the description is unfortunately incorrect. It should say:

set PDO::ATTR_EMULATE_PREPARES set to TRUE for pgsql.

i.e right now pgsql does *not* use emulated prepare. This patch enables it!

The comment in the patch should perhaps read something like:

// Prepared statements are most effective for performance when queries
// are recycled (used several times). However, if they are not re-used,
// prepared statements become in-effecient. Limited testing of basic
// Drupal installs shows prepared queries are seldom re-used.
// However, mileage may vary depending on which additional
// modules are installed - if in doubt reset to FALSE and
// measure performance.

Comment #4

Damien Tournoud CreditAttribution: Damien Tournoud commented 15 June 2010 at 06:16

Status:

Needs review

» Reviewed & tested by the community

We came to the same conclusion for all the other drivers. Scary, but true: prepared statements are rarely a good idea, except if you have a persistent client (but nobody has that nowadays).

Comment #5

Berdir

German

Switzerland

CreditAttribution: Berdir commented 15 June 2010 at 07:31

+++ includes/database/pgsql/database.inc
@@ -40,6 +40,12 @@ class DatabaseConnection_pgsql extends DatabaseConnection {
+      // are recycled (used several times). However, if they are no re-used,

Shouldn't that be no*t* re-used?

+++ includes/database/pgsql/database.inc
@@ -40,6 +40,12 @@ class DatabaseConnection_pgsql extends DatabaseConnection {
+      // prepared statements become in-effecient. Since most of Drupal's

Shouldn't that be "ineffecient" aka without the "-"?

68 critical left. Go review some!

Comment #6

Crell CreditAttribution: Crell commented 15 June 2010 at 15:06

We're currently not caching the prepared statement object anyway in the default implementation, so even if reusing the prepared statement did get us something it wouldn't make sense to do the full round trip to the server.

The MySQL driver doesn't go into this level of detail in its comments. I'm not sure that it needs to.

I'm +1 on this patch with the grammar tweaks mentioned in #5.

Comment #7

Dries CreditAttribution: Dries commented 15 June 2010 at 15:45

Status:

Reviewed & tested by the community

» Needs work

Needs a reroll to fix the grammar issues.

Comment #8

Josh Waihi CreditAttribution: Josh Waihi commented 15 June 2010 at 20:54

Status:

Needs work

» Needs review

File	Size
827554-pgsql-more-comments.patch	1.13 KB

Attached are better comments. Some added from Markkir (He's a PostgreSQL Database contributor!)

Comment #9

Crell CreditAttribution: Crell commented 15 June 2010 at 22:13

I don't know that we need the "if in doubt" line at the end, as it seems rather confrontational, but I'm fine either way. This is good to go on my end.