USE_PCONNECT / mysql_pconnect is unused?

[theg33kb0y]

Hello all, I might be missing something here so please let me know if this is being done some other way:

I've looked at includes/class/db/mysql/query_factory.php on 1.3.8 and 1.3.9, and both use mysql_connect().

I also grepped the tree for mysql_pconnect and it doesn't exist.

Does this mean the USE_PCONNECT option is unused in Zen, and MySQL connections are neither persistent, nor can they be made persistent?


-Eric

[DrByte]

Correct. It was removed due to significant problems caused by large numbers of hosting companies' server configurations, and because users thought it was a good idea to enable the option, thus breaking their stores. You can read large numbers of forum threads discussing the pro's and con's and the reasons for making the decision years back.

In general, it's a BAD choice for the majority of users, especially those on shared hosting, and especially those who have no idea what they're doing when they use it.

[theg33kb0y]

Its an easy enough change, why are persistent connections not used?

-Eric

[gilby]

Its an easy enough change, why are persistent connections not used?

-Eric

What do you think would be the advantage of using them?

[kobra]

[QUOTEtheg33kb0y]Its an easy enough change, why are persistent connections not used?[/QUOTE]
As most are on a shared shared hosting service, one wants these to die once the data is processed to free up resources for others

[theg33kb0y]

Quote:

Originally Posted by theg33kb0y
Its an easy enough change, why are persistent connections not used?

Originally Posted by gilby
What do you think would be the advantage of using them?

Opening a new connection to the database is an expensive operation for many reasons.
There's a great academic paper on the details here: http://www.eecg.toronto.edu/~livio/p...xsc-osdi10.pdf

If you can keep keep persistent connections because you are on a private (non-shared) hosting server, you will have lower response times and better CPU cache utilization. Indeed, shared-hosting environments may benefit as well due the the reduced syscall invocation overhead.

In unix, there are (at least) six round-trips to kernel space: 3 from the PHP side and 3 (possibly 4) from the MySQL side for every new MySQL connection:

PHP Syscalls
socket()
connect()
write()

MySQL Syscalls
epoll()
accept()
epoll()
read()

Each context switch from user-space to kernel and back requires high latency CPU state-transitios when switching the CPU to privileged mode, and when changing the virtual memory manger's page mappings to physical memory. Since PHP and MySQL are probably running on different CPU cores or sockets, these effect two CPU's caches'.

One side effect of changing VMM page mappings is TLB flushes and/or TLB shoot-downs. The TLB is the CPU-local cache that maps memory page lookups to physiscal memory, in order to avoid a soft-fault to the kernel to insert the mapping.

Switching between user mode and kernel space on the same CPU can flush most of the instruction cache, most of the L1 cache, and part (if not all) of the L2 cache since the kernel has very different (and large) data structures. Modern CPU L3 caches on servers are large, but remember, they are ~10 to 100x slower than the L1 cache.

There is additional overhead, perhaps less obvious, for configurations that house their MySQL server on a different physical system or virtual machine. In this case, every new TCP connection requires the 3-way, 3-packet handshake: SYN ; SYN-ACK ; ACK

Each transmission or receipt interrupts the kernel since the network card raises a hardware interrupt line on the CPU, forcing it to service the incoming data. If the kernel core servicing the network card driver happens to be in userspace doing something important (like parsing PHP for some other transaction), it will be forced into kernel space to service the request, inuring the cache and state-transition penalties described above.

To summarize:

Using mysql_connect for each transaction:
1. At least 6 kernel transitions for local MySQL instances
2. At least 9 kernel transitions for remote MySQL instances

Using mysql_pconnect for persistent connections.
1. First connection, same as above.
2. Subsequent kernel invocations for local system: 3 kernel transactions (write,epoll,read)
3. Subsequent kernel invocations for remote calls: 5 kernel transactions: (write,interrupt,interrupt,epoll,read)

There may be other overhead I am missing here, but this is a reasonable overview for the types of things you might think about with respect to persistent connections.

Originally Posted by theg33kb0y
Its an easy enough change, why are persistent connections not used?

Originally posted by kobra
As most are on a shared shared hosting service, one wants these to die once the data is processed to free up resources for others

Indeed, this is why the Zen authors have the USE_PCONNECT option in their configuration. My question is this: why is USE_PCONNECT completely ignored in the default Zen MySQL database class? The variable exists---use it!. If it is not used, then it is misleading to those who understand the consequences of such a choice, and choose to enable it. If many host on shared hosting, then default it off with the warning "only enable this if you know what you are doing", and perhaps a link to this post.

Different hosting services will have different policies, and providers can manage this by setting the [url=http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_wait_timeout]wait_timeout[/u] relatively low. Given the cache and state-transition effects listed above, an ISP might not want hour-long persistent connections, but 5-10 second persistent connections would actually increase their performance for heavily loaded hosted sites, as they will spend less time in connection buildup/teardown.

We've made the mysql_pconnect change on our system and have seen noticeable increases in response time. I'm sharing this with the community so others might benefit from this patch.

-Eric

Here is the patch to properly respect the USE_PCONNECT configuration variable. Even with this patch, you can disable persistent connections by clearing USE_PCONNECT in includes/configuration.php


Patch against includes/classes/db/mysql/query_factory.php for Zen 1.3.8

--- a/query_factory.php	2010-11-21 15:03:20.411809318 -0800
+++ b/query_factory.php	2010-11-21 15:03:23.963365243 -0800
@@ -28,7 +28,7 @@
     $this->database = $zf_database;
     if (!function_exists('mysql_connect')) die ('Call to undefined function: mysql_connect().  Please install the MySQL Connector for PHP');
     if ($zf_pconnect != 'false') {
-      $this->link = @mysql_connect($zf_host, $zf_user, $zf_password, true);
+      $this->link = @mysql_pconnect($zf_host, $zf_user, $zf_password);
     } else {
     // pconnect disabled ... leaving it as "connect" here instead of "pconnect"
       $this->link = @mysql_connect($zf_host, $zf_user, $zf_password, true);