[axxs-sysadmin] the overload/mysql problem - maybe php persistent connections is the problem?

[boud]

hi axxs-sysadmin,

Please check your php code for calls to  pconnect - this is for "persistent
connections" to databases, especially the mysql database. While this may
sound like a good thing in principle, it seems that the issue is not that
simple and that unless you access databases on another server, you probably
don't need them. i'm just paraphrasing what i read, i'm not an expert on
the issue.  Some people who know more than me wrote these pages:

http://www.thescripts.com/forum/thread532966.html
http://de3.php.net/manual/en/features.persistent-connections.php
http://drupal.org/node/42626


However, it does seem like persistent connections were causing major
blockages on www.axxs.org - for the past many, many weeks. We should
wait a few days to see if the problem really has been fixed - at the
moment we're just limiting the number of persistent connections to 30.

But if nobody needs them, we might want to turn them off totally.

If you replace   pconnect   by   connect  in your php code, that should
(probably) be enough.

Anyway, please check if you use them or if you need to use them and
speak up if you need persistent connections or if you already have a problem
with the limiting of pconnections to 30.


Hoping that this hypothesis about the excesive load problems is the 
right one...

The 15-min running average load has been stable at about 1-1.5 over
the past 5 hours according to my analysis (/tmp/uptime_cron.log):

https://samizdat.axxs.org/150

while during the past few weeks at least the 5 minute average load
frequently got up through 40, 50, even up to 90 which was generally
when the system got 99.9% stuck.

Anyway, let's see...

solidarity
boud



> ---------- Forwarded message ----------
> Date: Wed, 23 May 2007 08:26:31 +1000
> From: aketus

> Cheers for looking into this boud - it's been a problem for a long time.
> 
> I'll check my sites' files for pconnects and I think I'll also migrate them to 
> borg tonight.
> 
> For those running Drupal sites, read this:
> http://drupal.org/node/42626
> 
> It appears that the relevant code would be in /includes/database.mysql.inc and 
> I think that pconnect is not the default. Worthwhile checking anyway
> 
> aketus
> 
> 
> 
> Quoting boud <boud at riseup.net>:
> 
> > hi all
> > 
> > php/mysql persistent links may be a problem.


> > Does anyone really need persistent links? It seems that it's only
> > useful if you're using databases on other servers, and even then, you
> > need to be careful with them.
> > 
> > 
> > After irc discussion, i've put:
> > mysql.max_persistent = 30 # or some other proposed number
> > as at least a temporary measure.
> > 
> > 
> > http://www.thescripts.com/forum/thread532966.html
> > 
> >> Drop the persistent connections. That should stop the "too many
> >> connections" errors. Reduced memory in the server may reduce swapping
> >> in the MySQL server and speed things up. Or, you may have to throw
> >> more hardware at the problem (say, put the database on a separate
> >> server from the web server).
> >> 
> > 
> > 
> > http://de3.php.net/manual/en/features.persistent-connections.php
> > 
> >> Persistent connections are good if the overhead to create a link to
> >> your SQL server is high. Whether or not this overhead is really high
> >> depends on many factors. Like, what kind of database it is, whether or
> >> not it sits on the same computer on which your web server sits, how
> >> loaded the machine the SQL server sits on is and so forth.
> > 
> >> Persistent connections were designed to have one-to-one mapping to
> >> regular connections. That means that you should always be able to
> >> replace persistent connections with non-persistent connections, and it
> >> won't change the way your script behaves. It may (and probably will)
> >> change the efficiency of the script, but not its behavior!
> >

CHECK FOR PERSISTENT LINKS

ADELAIDE
i replaced  pconnect by connect in:
/home/httpd/vhosts/adelaide.indymedia.org/httpdocs/imc_classes/imc_Connection.inc

i suggest people do  grep pconnect <myfiles.php>   in your php code files
to see if you have pconnect entries.


TRANSMISSION.CC

includes/database.pgsql.inc: *
Note that you can change the pg_connect() call to pg_pconnect() if you

includes/database.mysql.inc: *
Note that you can change the mysql_connect() call to mysql_pconnect()
if you

modules/audio/getid3/getid3/extension.cache.mysql.php: 
if  (!function_exists('mysql_pconnect')) {

modules/audio/getid3/getid3/extension.cache.mysql.php: 
$this->connection = mysql_pconnect($host,  $username, $password);

modules/audio/getid3/getid3/extension.cache.mysql.php: 
die('mysql_pconnect() failed - check permissions  and spelling.');


cheers
boud