mysql_pconnect breaks search [#156668]

On stubru.be we're using a dedicated database server and as documented in database.mysql.inc, we changed

$connection = @mysql_connect($url['host'], $url['user'], $url['pass'], TRUE, 2);
on line 90 into:
$connection = @mysql_pconnect($url['host'], $url['user'], $url['pass'], TRUE, 2);

to use persistent database connections.

The result is that sometimes -not every time, but it happens frequently- we get this error when using the search:

Table 'temp_search_results' already exists query: CREATE TEMPORARY TABLE temp_search_results SELECT i.type, i.sid, 5 * (72.019322125587 * i.relevance) + 5 * POW(2, (GREATEST(n.created, n.changed, c.last_comment_timestamp) - 1183531382) * 6.43e-8) + 5 * (2.0 - 2.0 / (1.0 + c.comment_count * 0.0011737089201878)) + 5 * (2.0 - 2.0 / (1.0 + nc.totalcount * 1.7279423834892E-06)) AS score FROM temp_search_sids i INNER JOIN search_dataset d ON i.sid = d.sid AND i.type = d.type INNER JOIN node n ON n.nid = i.sid LEFT JOIN node_comment_statistics c ON c.nid = i.sid LEFT JOIN node_counter nc ON nc.nid = i.sid WHERE (d.data LIKE '% foto %') ORDER BY score DESC in /data/web/stubru/includes/database.mysql.inc op regel 167.

When that happens, you get the search results of the previous search.
I can reproduce this on my personal website. Both sites are running Drupal 5.1.

Comments

Comment #1

kbahey commented 4 July 2007 at 14:57

Starting with Drupal 6, persistent connections will not be supported, because we use Auto Increment.

So, you should stay away from persistent connections.

I am leaving this as active in case someone else wants to comment on it.

Comment #2

irakli commented 30 January 2008 at 23:28

mysql_pconnect is how you do MySQL conneciton pooling in PHP. Not supporting it, basically means dropping support for DB connection pool. That in turn makes it next to impossible to deploy Drupal on a n-tier hardware topology (PHP and MySQL on separate servers) since each db connection will cost a lot of time, memory and cpu (not an issue when connecting locally through a socket).

Sounds really radical, unless I am missing something? What would you use to achieve connection pool, instead? Alternatives for scalability?

Comment #3

drupdrips commented 23 August 2008 at 02:14

I second Irakli's thoughts. So Khalid, are you saying that there is something better available in drupal6 to support connection pooling. For a heavy hit site constant open/close of connections to the DB does not seem like a good way to go ..

Comment #4

drupdrips commented 23 August 2008 at 03:28

Well after a bit of search and homework I see some comments in the past and this has been squashed in the highest echelons : http://drupal.org/node/55516#comment-389850 although in a completely separate context.

I guess problems with auto incrementing etc. and with search got a shotgun approach to pconnect's benefit as a whole. If the web server and db is on the same physical server I guess the benefit is hardly any if at all, but once you start to scale up and throw multiple web servers at a db host running on another server (even if on the same network) the benefit of persistent connections may help IF that IS your bottleneck for a heavy hit site. My feeling is by the time you are handling such a heavy concurrent usage you are probably bottlenecking on some other place ..The only other problems I saw about connections maxing out on sql server is really a configurational issue. The sql server max connection limit should be the sum of max # of processes or its children from the n web servers. Of course the memory allocations need to be high too .. but when you are thinking of scaling up to n-tier architecture you are already throwing money at hardware for good reasons I suppose.

we should be able to see open doors to options of releasing bottleneck. The folks making the decisions for Drupal's core are some of the smartest most brilliant people around. So I am pretty sure my concern is not really a valid one .. but I just hope that this will not become an architectural flaw to preclude connection pooling in Drupal's core.

Comment #5

ramuchidambaram commented 29 August 2008 at 10:20

In Drupal 6 I have tried changing the mysql_connect() to mysql_pconnect(), and i did some testing on the site. I have not found any error on my site. Can anybody explain where i will get error.
We are planning to change the default connection to pconnect to implement connection pooling.

Comment #6

yonailo

He/him

commented 10 September 2008 at 12:09

Could you explain with a bit more of detail what the problem is when you have persisten connections with autoincrement ? I don't know what you mean with "auto increment".... if you are meaning mysql AUTO INCREMENT column flag I find very weird you can not use persisten connections because of this feature.