Re: Experiences with mysql_pconnect?

Alex Weber wrote:
On Oct 17, 2:24 pm, Jerry Stuckle <jstuck...@xxxxxxxxxxxxx> wrote:
Alex Weber wrote:
Jerry Stuckle wrote:
John Murtari wrote:
Folks,
We have a fairly busy web site present using PHP 5 to
talk to a MySQL 5.0.45 DB on an Apache 1.3 series server. It's
spread out over several physical hosts (one DB server), we use
DB tracking of sessions and there is some transaction usage also.
Right now we keep it simple using mysql_connect. I'd
looked at mysql_pconnect a few years but got scared away by the
warnings about problems. Somebody here wanted to revisit that
issue and I still see warning regarding transaction problems and
dangling connections to the server at the php.net site.
Is anybody using this on a busy site and finding it
worthwhile? I'd appreciate any tips to handle potential problems.
It sounds like it should be a performance boost if configured
properly.
Thanks.
What is "busy"? 10 connections/sec? 100 connections/sec?
Yes, there can be problems with using mysql_pconnect(). But if you're
running hundreds of connections per second, you might find it
advantageous to use it. You might start with just the SELECT
statements, though, to limit transaction problems. And leave the
statements which change the database using non-persistent connections.
Just remember - you have to have all the possible persistent connections
you might ever need allocated all the time. For instance, if your
average is 100 connections/second but you peak at 1,000 during busy
times, you need at least 1,000 persistent connections available at all
times. This can be a drain on the system resources, also.
> John,
>
> My bad for not directly answering your question but I suggest that if
> you're looking to optimize your DB stuff and learn new commands, etc I
> would look into the PDO extension (usually included by default):
>
>http://www.php.net/manual/en/class.pdo.php
>
> Besides abstracting specific database implementations (ie: same code
> for different DBs) it has good tools for preparing statements,
> persistence, etc.
>
> Using prepared statements will probably speed up your app a lot.
>
> Oh and also, you should really be using "mysqli" instead of "mysql" by
> now! ;)
>
> Alex
>
>

(top posting fixed)

Wrong person to respond to - but actually, tests have shown that
mysqli_xxx and PDO are both slower than mysql_xxx. Of course, they have
other advantages, but performance is not one of them.

Prepared statements are also slower than non-prepared statements,
especially when the server is remote as it requires multiple calls to MySQL.

P.S. Please don't top post. Thanks.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstuck...@xxxxxxxxxxxxx
==================

Jerry,

A couple points:
- i disagree about PDO being slower than mysql (not sure about
mysqli), anyway i'd like to see some source to back that up (PDO is a
pure-C class which is inherently faster)


The mysql interface is also written in pure-C - and doesn't have overhead of having to figure out which interface to use. Try benchmarks for both of them - you will find PDO is slower.


- i completely disagree with "prepared statements are slower than non-
prepared statements". its all about context. if you have similar
queries that you execute on a regular basis, preparing it and then
just executing it passing different variables each time is MUCH faster
than manually executing the statement each time. The ONLY scenario
where a prepared a statement might be slower than a non-prepared
statement is if its an uncommon query (ie: one that you don't execute
regularly or that varies a lot in its syntax)


No, it's not "all about context". The prepared statement and each bind call results in a call to the database. This can cause significant delays if the database is remote.

Also, it is very seldom in web apps that you prepare a statement once and call it multiple times with different bind parameters. The vast majority of the calls are one-time only. If they aren't, you're doing something seriously wrong in your SQL statements - and increasing overhead unnecessarily.

Remember - in a web app, each page is its own transaction - which is different from non-web apps.

most "generic" queries would greatly benefit from being prepared, for
example:

- login: "SELECT id, name FROM users WHERE email = ? AND password = ?"
- logging: "INSERT INTO log (event, time) VALUES (?, NOW())"
- etc.


And how often do you log in? More than once per page?

The same thing with logging - how often do you actually log something (other than for debugging purposes)? Not that often.

since those two are queries you would presumably execute regularly the
benefits are that they only need to be parsed once, so after preparing
it every execution is faster. (it also protects against sql-injection
by avoiding malformed queries)


But they are not executed multiple times by one page. Once the connection is closed (at the end of the script), the page must be prepared again. And don't even think of going to persistent connections - which have other problems.

there's a caveat though: "Using prepared SELECT statements on a MySQL
database prior to MySQL 5.1.17 can lead to SERIOUS performance
degradation." source: http://dev.mysql.com/doc/refman/5.1/en/query-cache.html

further reading: http://www.mysqlperformanceblog.com/2006/08/02/mysql-prepared-statements/

Anyway apologies for the tangent off your original post but there's
good info in here if you'd just read it and be less conservative.

Alex


No, I understand completely. However, most of this blog is not at all applicable to web pages, which are transactional. They are much more appropriate to applications which use the same connection for long periods of time.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@xxxxxxxxxxxxx
==================

.

Relevant Pages

  • Re: Reducing load for LAMP app?
    ... MySQL: as much as possible, he keeps query results in RAM, but ... His hoster says that Apache server is under significant load. ... Using apc is pretty much transparent, but memcached will require modifying your database abstraction layer using the memcached functions. ... With persistent connections, you must have the maximum number of connections *ever* required allocated *all of the time* - even if no one is using your server. ...
    (comp.lang.php)
  • Re: Experiences with mysql_pconnect?
    ... talk to a MySQL 5.0.45 DB on an Apache 1.3 series server. ... dangling connections to the server at the php.net site. ... especially when the server is remote as it requires multiple calls to MySQL. ... queries that you execute on a regular basis, preparing it and then ...
    (comp.lang.php)
  • Re: Experiences with mysql_pconnect?
    ... We have a fairly busy web site present using PHP 5 to ... talk to a MySQL 5.0.45 DB on an Apache 1.3 series server. ... dangling connections to the server at the php.net site. ... Prepared statements are also slower than non-prepared statements, especially when the server is remote as it requires multiple calls to MySQL. ...
    (comp.lang.php)
  • Re: Experiences with mysql_pconnect?
    ... talk to a MySQL 5.0.45 DB on an Apache 1.3 series server. ... Prepared statements are also slower than non-prepared statements, ... especially when the server is remote as it requires multiple calls to MySQL. ... statement is if its an uncommon query (ie: one that you don't execute ...
    (comp.lang.php)
  • Re: Reducing load for LAMP app?
    ... MySQL: as much as possible, he keeps query results in RAM, but ... His hoster says that Apache server is under significant load. ... Using memcached on the application layer can save a lot of network traffic to and from the database server, ... With persistent connections, you must have the maximum number of connections *ever* required allocated *all of the time* - even if no one is using your server. ...
    (comp.lang.php)