mysql too many connections/looses connection

[samimussbach]

with recent 0.4.3 piwik hangs with following error in error_log:

[Mon Sep 14 09:14:43 2009] [error] PHP Fatal error:  Uncaught exception 'Exception' with message 
'Error query: SQLSTATE[HY000]: General error: 2013 Lost connection to MySQL server during query\n\t\t\t\t\t\t\t\tIn query: 
/* SHARDING_ID_SITE = 1 */ \tSELECT idaction \n\t\t\t\t\t\t\tFROM piwik_log_action  WHERE name = ? AND type = ?\n\t\t\t\t\t\t\t\t
Parameters: array (\n  0 => 'vvz/showVVZ?baum=a%3A1%3A%7Bi%3A17%3Ba%3A1%3A%7Bi%3A201%3Ba%3A3%3A%7Bi%3A1624%3Ba%3A5%3A%7Bi%3A6797%3BN%3Bi%3A6796%3BN%3Bi%3A6802%3BN%3Bi%3A6799%3BN%3Bi%3A6806%3BN%3B%7Di%3A1622%3BN%3Bi%3A1623%3Ba%3A2%3A%7Bi%3A6813%3BN%3Bi%3A6815%3BN%3B%7D%7D%7D%7D',\n  1 => 1,\n)' 
in XXX/piwik/core/Tracker/Db.php:213\n
Stack trace:\n
#0 XXX/piwik/core/Tracker/Db.php(159): Piwik_Tracker_Db->query('/* SHARDING_ID_...', Array)\n
#1 XXX/piwik/core/Tracker/Action.php(136): Piwik_Tracker_Db->fetch('/* SHARDING_ID_...', Array)\n
#2 XXX/piwik/core/Tracker/Visit.php(115): Piwik_Tracker_Action->getId 
in XXX/piwik/core/Tracker/Db.php on line 213

The number of open connections increases and hits the mysql max connections border.

I am aware of the bug #885 and similar but I did not find any solution. This is fatal, we could not use piwik because it kills our site. This is why I set the priority that high, please correct.

Is this a piwik or a server problem?

[robocoder]

One connection is reserved for the MySQL database administrator (root). Please check SHOW PROCESSLIST to see how many are connected to your piwik database.
What have you set max mysql connections to?
Do your web server logs indicate an increase in visitors or actions?

I can only speculate at this point that the number of visitors or actions has increased, and that this is contributing to the load on your mysql server, but there may be contributing factors:

• Please also check that Piwik has read/write permissions to the tmp/cache/tracker folder.
• Try disabling browser triggered archiving; use cron instead.

If you're getting hit by smart bots (eg bing or googlebot), we can change things a bit to defer the creation of the tracker database object.

[samimussbach]

The number of users increased indeed heavily today. The connections to the piwikdb steadily increased unil 192, where the max connections was hit (set to 200). Rest was used by root and the page itself.

• Piwik has r/w permissions to the tmp/cache/tracker folder
• We have a cronjob running and nobody has been at the piwik interface.

I do not think that we got hit by bots, this where real users.

Might it be (just guessing) that the tables are locked when inserting rows and SElECT-Processes are waiting for end of lock, but the next insert locks again and therefore the number of connections is rising?

You use MyIsam, which locks the whole table, not just the row it is inserting. Perhaps InnoDb is a better choice for this table?

[mattab]

how many pages per day are you seeing on your Piwik?

what is the hardware of your piwik server?

Recording data in real time the way Piwik is doing currently is a known bottleneck, it can only handle a certain traffic.

Can you paste the output of a SHOW FULL PROCESSLIST; ?

One thing you can try is the suggestion in the piwik-hackers post: http://lists.piwik.org/pipermail/piwik-hackers/2009-August/000797.html

To move to a more scalable Piwik, a solution would be to batch load data in the log tables. This has various other requirements like: server side cookie data store #409.

[samimussbach]

We had 350k pages per day on a osx server quad-core 3GHz with 16GB DDR2. CPU was busy but not under highest load.

Output of the proceslist gave said 192 processes with similar queries like quoted above, waiting for better times. I can't give you the original output, as the server is now not so busy anymore and the problem therefore doesn't occour at the moment. In some days we have again such a peak. I applied your index-suggestion, we'll see. Perhaps I can convince my boss to give piwik a try in this peak, but it doesn't sound like it...

perhaps we just hit the bottleneck. How's the progress towards 0.5?

[robocoder]

Closing. #708 specifically targets the index issue, while #386 is the general performance issue.