Plots performance, database maintenance options

1 view
Skip to first unread message

Sebastian Silva

unread,
Apr 17, 2017, 9:27:37 AM4/17/17
to w...@publiclab.org, plots-dev, Jeffrey Warren

Hi,

Lately we've been having issues of performance on the Publiclab.Org server.

The main issue I see is is that we seem to be reaching memory limits. I attribute this to an overgrown database file (21GB), when our data really is < 3GB.

Last week I performed a dump/reload expecting the database data files to shrink, but after the process they were the same size.

I learned that I should've manually removed these database files before Mysql will recreate them.

I can schedule the same maintenance process (scheduling downtime for 2-3 hours), this time making sure to remove the large files.

Or, in an attempt to avoid downtime... we could setup replication in a separate server (e.g. archive.publiclab.org), and then point the website to the secondary database while the primary is being cleaned. This should entail no downtime.

We should make a decision and schedule it asap.

I am of course open to other suggestions to improve performance.

Regards,

Sebastian

Publiclab.Org Server Gardener

Sebastian Silva

unread,
Apr 17, 2017, 1:32:26 PM4/17/17
to w...@publiclab.org, plots-dev, Jeffrey Warren

Hi,

Here's more info from the dashboard graphs and Plots server info.

We had a sporadic slow response from the server between 2:00 to 10:00 AM UTC.

I reviewed the backup process configuration and, usually Sundays we have two backup processes (weekly and daily).

Daily process is retained for a month. Weekly for two.

But these happen the night before (sat->sun).

Daily backup process started Apr 17, 2017 - 2:00 am EST and ended Apr 17, 2017 - 6:15 am EST. (6am->10:15am UTC).

While the backup process doesn't seem to have triggered the peak, it appears to have made it worse.

CPU:


RAM:


El 17/04/17 a las 08:27, Sebastian Silva escribió:
Reply all
Reply to author
Forward
0 new messages