0

Total newb in terms of databases/server management but currently running a large database:

  • LAMP Stack Ubuntu
  • WordPress platform (can't change)
  • Over 100,000 database entries
  • Site adds new entries via API 2x a day
  • Site deletes expired entries 2x a day
  • Users search entries for keyword matches
  • Under 100 users a day

The server frequently gives mysql-slow errors, becomes unresponsive or uses >200% server resources. (Mostly CPU and seems to stem from MySQLd process).

I'm running on a Linode machine LAMP stack:

4 GB RAM
4 CPU Cores
96 GB SSD Storage
19.72 GB swap

No idea where to start learning about these types of issues. Would be great if someone can push me in the right direction to learn about common issues with large databases, or even to understand how MySQLd uses CPU memory and how I can better optimize that - as I figure with such low traffic it must be a config issue or a bad script.

I don't expect anyone to troubleshoot this, I just don't even know where to start.

AdamSC
  • 103
  • 3
  • http://serverfault.com/questions/646852/excessive-number-of-sleeping-processes-in-centos-howto-diagnose/646876#646876 – user9517 Jun 10 '16 at 16:14

2 Answers2

1

I'd first take a look at your app code and the queries you are generating. Using "SHOW PROCESSLIST" will show you the mysql processes and state. If you see queries with full table scans, file sorts, or copy-to-tmp, you'll want to use "EXPLAIN [sql statements] .. " and see where you can optimize.

I would venture a guess only, as a result of the limited info you provide, that you have I/O bottleneck due to un-indexed queries, or queries so large that you are exceeding the amount of allocated memory. You should not be using swap for mysql, at the very least you should have as much memory as your db needs to stay in memory as much as possible.

Michael Steinfeld
  • 396
  • 2
  • 5
0

For mysql tuning You can start with http://mysqltuner.com/ (You can post output here).

For wordpress I suggest using some caching plugin like W3 Total Cache with memcached backend and enabling all the caches You can. Object cache should help with mysql load.

With mysql removing lot's of data running mysqlcheck -Ao might also help. Also if mysql queries suffer from locking issues moving some tables to InnoDB might help but it doesn't seem to be this way in Your case.

Lazy404
  • 365
  • 1
  • 6