Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

mysql too many connections/looses connection #982

Closed
samimussbach opened this issue Sep 14, 2009 · 5 comments
Closed

mysql too many connections/looses connection #982

samimussbach opened this issue Sep 14, 2009 · 5 comments
Labels
Bug For errors / faults / flaws / inconsistencies etc. Critical Indicates the severity of an issue is very critical and the issue has a very high priority. duplicate For issues that already existed in our issue tracker and were reported previously.
Milestone
0.4.4

Comments

@samimussbach
Copy link

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
Copy link
Contributor

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
Copy link
Author

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
Copy link
Member

mattab commented Sep 14, 2009

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
Copy link
Author

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
Copy link
Contributor

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

@samimussbach samimussbach added this to the Piwik 0.4.4 milestone Jul 8, 2014
This issue was closed.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
Bug For errors / faults / flaws / inconsistencies etc. Critical Indicates the severity of an issue is very critical and the issue has a very high priority. duplicate For issues that already existed in our issue tracker and were reported previously.
Projects
None yet
Milestone
0.4.4
Development

No branches or pull requests
3 participants
@mattab @robocoder @samimussbach