Several times a day on our Civi site (CiviCRM 5.11.0 with Drupal 7.64) we are experiencing the following error:
PDOException: SQLSTATE[HY000] [1203] User xxx already has more than 'max_user_connections' active connections in lock_may_be_available() (line 167 of /includes/lock.inc).
The error causes the site to crash and lasts for about a minute or two.
We are on a shared hosting platform, and I contacted support about this. They gave us the following information about the issue:
You have a limit of 40 connections per user which is being exceeded. The problem is that your database is not optimized, and 95% of the queries to it take an average of eight seconds to be executed. Each query goes through more than 12 billion rows. This is causing connections to overlap, hence the error.
They suggested I should 'optimise the database'. I am not completely sure what I should do to optimise the database. Can anyone give me any advice on what I can do to help prevent this error?
The support people also sent me an excerpt from the MySQL slow query log:
# Current date: Mon Apr 1 17:20:26 2019
# Overall: 18.13k total, 902 unique, 0.13 QPS, 0.32x concurrency _________
# Time range: 2019-03-31T00:27:16 to 2019-04-01T17:20:06
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 45692s 100ms 52s 3s 8s 3s 1s
# Lock time 16s 0 4s 857us 366us 28ms 108us
# Rows sent 28.16M 0 3.17M 1.59k 964.41 28.34k 964.41
# Rows examine 12.25G 0 3.18M 708.38k 2.15M 753.22k 362.29k
# Rows affecte 4.70M 0 1.23M 271.74 0.99 12.94k 0
# Bytes sent 15.78G 11 2.63G 912.46k 830.38k 23.41M 790.84k
# Query size 323.12M 6 4.15M 18.25k 1.86k 140.56k 51.63
# Query 1: 0.08 QPS, 0.31x concurrency, ID 0x1859D41021C2B8A4 at byte 62429572
# This item is included in the report because it matches --limit.
# Scores: V/M = 1.44
# Time range: 2019-03-31T01:01:42 to 2019-04-01T17:20:06
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 59 10837
# Exec time 94 43287s 100ms 16s 4s 8s 2s 4s
# Lock time 20 3s 50us 51ms 294us 176us 2ms 113us
# Rows sent 36 10.33M 0 1000 999.72 964.41 15.04 964.41
# Rows examine 96 11.86G 19.53k 2.85M 1.12M 2.26M 694.00k 1.03M
# Rows affecte 0 0 0 0 0 0 0 0
# Bytes sent 54 8.59G 2.15k 846.53k 831.20k 830.38k 23.42k 790.84k
# Query size 0 555.76k 51 53 52.51 51.63 0.45 51.63
# String:
# Databases wfto_civicrm
# Hosts localhost
# Last errno 0
# Users wfto
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ########
# 1s ################################################################
# 10s+ #
# Tables
# SHOW TABLE STATUS FROM `wfto_civicrm` LIKE 'log_civicrm_group'
# SHOW CREATE TABLE `wfto_civicrm`.`log_civicrm_group`
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM `log_civicrm_group` LIMIT 2047000, 1000
# Query 2: 0.01 QPS, 0.01x concurrency, ID 0x9BE31CBEA5AB2481 at byte 154842157
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.09
# Time range: 2019-03-31T00:59:00 to 2019-04-01T16:23:24
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 9 1726
# Exec time 1 772s 100ms 1s 447ms 777ms 202ms 433ms
# Lock time 3 533ms 60us 50ms 308us 204us 2ms 119us
# Rows sent 5 1.64M 0 1000 996.96 964.41 51.88 964.41
# Rows examine 2 361.70M 31.25k 381.79k 214.59k 362.29k 95.30k 211.82k
# Rows affecte 0 0 0 0 0 0 0 0
# Bytes sent 2 387.79M 3.55k 276.83k 230.07k 233.54k 17.06k 222.42k
# Query size 0 99.19k 58 59 58.85 56.92 0 56.92
# String:
# Databases wfto_civicrm
# Hosts localhost
# Last errno 0
# Users wfto
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s #
# 10s+
# Tables
# SHOW TABLE STATUS FROM `wfto_civicrm` LIKE 'log_civicrm_custom_field'
# SHOW CREATE TABLE `wfto_civicrm`.`log_civicrm_custom_field`
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM `log_civicrm_custom_field` LIMIT 367000, 1000
