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

[boud]

hi

www.axxs has been given connection timed out since around 10:00 UTC+0 Wed,
i.e. early evening in Oceania, which makes sense, since that's the time
when most people are likely to access it. That makes about 14 hours' down
time (so far).

If anyone manages to get into the system in the next few hours or so,
i suggest you set mysql.persistent_connections = Off in /etc/php.ini
and restart mysqld and the webserver without waiting for any more
comments from people doing individual sites.  This might break some
functionality in some sites, but when the server is inaccessible for
over 50% of the time, that's a pretty severe loss of functionality
anyway.

solidarity
boud




On Wed, 23 May 2007, boud wrote:

> 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
> _______________________________________________
> axxs-sysadmin mailing list
> axxs-sysadmin at lists.indymedia.org
> http://lists.indymedia.org/mailman/listinfo/axxs-sysadmin
>