Re: Experiences with mysql_pconnect?
- From: Alex Weber <alexweber15@xxxxxxxxx>
- Date: Sat, 18 Oct 2008 20:04:52 -0700 (PDT)
On Oct 17, 8:32 pm, Jerry Stuckle <jstuck...@xxxxxxxxxxxxx> wrote:
Alex Weber wrote:
On Oct 17, 2:24 pm, Jerry Stuckle <jstuck...@xxxxxxxxxxxxx> wrote:
Alex Weber wrote:
Jerry Stuckle wrote:> John,
John Murtari wrote:
Folks,What is "busy"? 10 connections/sec? 100 connections/sec?
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.
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.
> 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-stateme...
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.
jstuck...@xxxxxxxxxxxxx
==================
Fair enough Jerry, I stand corrected.
oh and no, i don't have any experience with mysql_pconnect :P
Alex
.
- References:
- Experiences with mysql_pconnect?
- From: John Murtari
- Re: Experiences with mysql_pconnect?
- From: Jerry Stuckle
- Re: Experiences with mysql_pconnect?
- From: Alex Weber
- Re: Experiences with mysql_pconnect?
- From: Jerry Stuckle
- Re: Experiences with mysql_pconnect?
- From: Alex Weber
- Re: Experiences with mysql_pconnect?
- From: Jerry Stuckle
- Experiences with mysql_pconnect?
- Prev by Date: Re: Large Scale PHP Application Design Questions
- Next by Date: Re: Why is break so lame, and no goto
- Previous by thread: Re: Experiences with mysql_pconnect?
- Next by thread: Re: Experiences with mysql_pconnect?
- Index(es):
Relevant Pages
|
