• You are not logged in.
  • 1

Database Cleanup

  • Started by Chris98
  • 9 Replies
  • Chris98
  • Project Manager
  • Reputation: 49
  • From: England, United Kingdom
  • Registered: 15-04-2015
  • Posts: 481

This task will clean up sleeping MySQL processes.

NOTE: Ensure that your database user can kill processes before using this task. If you cannot, it will result in database errors when attempting to remove sleeping processes.

Kind regards,
Christopher Marshall
chris [at] get-aura [dot] org

Project Manager

Offline
  • 0
  • cyberman
  • Member
  • Reputation: 17
  • From: Federal Republic of Germany
  • Registered: 07-07-2015
  • Posts: 167

Sorry for my not available knowledges but why should I kill sleeping MySQL processes? Never heard before. 

And why they are sleeping? Think they should running (or not)...

Universe is corrupt. Reboot: Y/N?

Offline
  • 0
  • Chris98
  • Project Manager
  • Reputation: 49
  • From: England, United Kingdom
  • Registered: 15-04-2015
  • Posts: 481

In short, you should kill them because they take up space in your process list. And this is the more complex explanation:

The value 'max_user_connections' set by your webhost sets the amount of simultaneous connections to your database at the same time. For some hosts I've seen this as low as 3.Each user that visits your forum uses one connection, and these stay in the process list until the connection has ended and then they are terminated (there is something called persistent connections which keep your connection for longer, and someone clever might point this out, but they get more complex so I won't go there).

If the process list is full, then no more open connections will be allowed (they also vanish when apache is restarted). Sleeping connections generally are created when a PHP script connections to MySQL, does some stuff, and then doesn't get disconnected again after doing something that takes some time.

Essentially, 'sleeping' processes happen when you have a lot of connections to the database and they don't get disconnected. The basic idea to prevent this is to stop PHP processes from running for a long time, or to disconnect them from the database when they aren't used anymore.

There are other causes, such as the server load, which causes queries to take longer as the database server load increases, and this causes the process list to fill up.

Sleeping processes do nothing and block up the process list which prevents new connections (hence the "too many connections" error). These are a common problem on every webhost, and CMS, and is often dependant on how the hosting environment itself is configured rather then the CMS (such as the max_user_connections.

Hope this helps a bit. smile

Kind regards,
Christopher Marshall
chris [at] get-aura [dot] org

Project Manager

Offline
  • 0
  • cyberman
  • Member
  • Reputation: 17
  • From: Federal Republic of Germany
  • Registered: 07-07-2015
  • Posts: 167

Thx for your explanation.

In my (limited) knowledge it goes this way - open connection, get/set data, close connection. If I use mysql compression it's very fast. No sleeping.

Think clean up sleeping processes is only important on persistant connections - see comment on linked page


[size=2][font=Helvetica, Arial, sans-serif]A note por PHP developers. You can find this error if your scripts open persistent connections, wich aren't closed even if the script terminates. Use mysql_connect() instead of mysql_pconnect() unless you have a good reason.[/font][/size]

Are persistant connections the Panther default?

I'm running FluxBB currently on my own server but have trouble with persistant connections so the value is


$p_connect = false;

By the way - you are talking about Apache. Have you tested Panther on Nginx too?

Universe is corrupt. Reboot: Y/N?

Offline
  • 0
  • Chris98
  • Project Manager
  • Reputation: 49
  • From: England, United Kingdom
  • Registered: 15-04-2015
  • Posts: 481

That comment points to using the MySQL library of functions, which have since been deprecated in favour of PDO or MySQLi. While a lot of the practices have changed, the point of the comment does remain.

Are persistant connections the Panther default?

No - this setting is used by the default on the server, it is not set. But, we should really allow for this either way. Creating a ticket ...

By the way - you are talking about Apache. Have you tested Panther on Nginx too?

I haven't. I'm not sure whether another member of the development team has, that might be something to add to our to-do list before 1.0.9 is released.

EDIT: I've added persistent connection support.

  • Last edited by Chris98 (14-09-2015 20:01:52)

Kind regards,
Christopher Marshall
chris [at] get-aura [dot] org

Project Manager

Offline
  • 0
  • cyberman
  • Member
  • Reputation: 17
  • From: Federal Republic of Germany
  • Registered: 07-07-2015
  • Posts: 167

Thx again.

Don't know if it helps ...  FluxBB is running with Nginx.

Another question - is there a way to activate/deactivate tasks like this inside admin panel?

Universe is corrupt. Reboot: Y/N?

Offline
  • 0
  • Chris98
  • Project Manager
  • Reputation: 49
  • From: England, United Kingdom
  • Registered: 15-04-2015
  • Posts: 481

Don't know if it helps ...  FluxBB is running with Nginx.

Then it's probably ok. But we'll double-check it.

is there a way to activate/deactivate tasks like this inside admin panel

Yes - you can edit/delete any tasks from inside the admin panel. By default, Panther 1.0.9+ will come with one task, to check for forum updates periodically on the first of every month. You will also receive an additional task (in the tasks directory only) to optimise your database.

You can also delete any task files from the server, as well as upload new ones. I've attached a screenshot of the task manager to this post, this might help to answer any additional questions you have.

34

  • Last edited by Chris98 (04-11-2015 10:06:45)
  • Reason for edit:

Kind regards,
Christopher Marshall
chris [at] get-aura [dot] org

Project Manager

Offline
  • 0
  • cyberman
  • Member
  • Reputation: 17
  • From: Federal Republic of Germany
  • Registered: 07-07-2015
  • Posts: 167

Ahh, I see ... great.

Universe is corrupt. Reboot: Y/N?

Offline
  • 0
  • vank1ta
  • Development Lead/Co-Manager
  • Reputation: 4
  • Registered: 15-04-2015
  • Posts: 12

Hi cyberman,
Panther works under Nginx. smile
Please have a look at this topic.

Offline
  • 2
  • cyberman
  • Member
  • Reputation: 17
  • From: Federal Republic of Germany
  • Registered: 07-07-2015
  • Posts: 167

Thx for your reply - really good news.

Have seen this topic but was not sure if your development has changed something on the possibility to run Panther with Nginx.

Universe is corrupt. Reboot: Y/N?

Offline
  • 0

Users in this topic: 0 guests, 0 registered users

  • 1