Too many database connections
Development and Programming
My site no longer loads and I can no longer access the control panel. After some investigation, it seems there are requests that are taking too long to load or are stuck. When I try to log into phpMyAdmin to checkout the database, it tells me there are too many connections. Below is a log of the database queries. Thanks for any help with this issue. EE v5.3.2.
#
2:36
+———–+——————+——————–+——————————-+———+——–+——————————+——————————————————————————————————+———–+—————+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+———–+——————+——————–+——————————-+———+——–+——————————+——————————————————————————————————+———–+—————+
| 226373475 | myDB | xx.xx.xx.16:40736 | database_name_here | Execute | 220575 | Sending data | SELECT t.entry_id, t.channel_id, t.forum_topic_id, t.author_id, t.ip_address, t.title, t.url_title, | 0 | 0 |
| 226373637 | myDB | xx.xx.xx.16:42320 | database_name_here | Execute | 220537 | Sending data | SELECT exp_channel_titles.entry_id as ChannelEntry__entry_id, ChannelEntry_field_id_44.field_id_44 a | 2 | 0 |
| 226374442 | myDB | xx.xx.xx.16:50722 | database_name_here | Execute | 220359 | Sending data | SELECT exp_channel_titles.entry_id as ChannelEntry__entry_id, ChannelEntry_field_id_44.field_id_44 a | 2 | 0 |
| 226374499 | myDB | xx.xx.xx.16:52106 | database_name_here | Query | 220345 | Waiting for table level lock | UPDATE exp_members SET last_activity = 1600719187 WHERE member_id = 1 | 0 | 0 |
| 226374574 | myDB | xx.xx.xx.16:53182 | database_name_here | Query | 220322 | Waiting for table level lock | UPDATE exp_members SET last_activity = 1600719210 WHERE member_id = 1 | 0 | 0 |
| 226374608 | myDB | xx.xx.xx.16:53472 | database_name_here | Query | 220318 | Waiting for table level lock | UPDATE exp_members SET last_activity = 1600719215 WHERE member_id = 1 | 0 | 0 |
| 226374667 | myDB | xx.xx.xx.16:53876 | database_name_here | Query | 220306 | Waiting for table level lock | UPDATE exp_members SET last_activity = 1600719227 WHERE member_id = 1 | 0 | 0 |
| 226374694 | myDB | xx.xx.xx.16:54124 | database_name_here | Query | 220300 | Waiting for table level lock | UPDATE exp_members SET last_activity = 1600719233 WHERE member_id = 1 | 0 | 0 |
| 226374719 | root | localhost | database_name_here | Query | 220292 | Waiting for table level lock | explain SELECT t.entry_id, t.channel_id, t.forum_topic_id, t.author_id, t.ip_address, t.title, t.ur | 0 | 0 |
| 226374724 | myDB | xx.xx.xx.16:54538 | database_name_here | Query | 220290 | Waiting for table level lock | UPDATE exp_members SET last_activity = 1600719243 WHERE member_id = 1 | 0 | 0 |
| 226409713 | mtbackup | 72.47.246.12:52577 | database_name_here | Query | 213852 | Waiting for table level lock | SELECT /*!40001 SQL_NO_CACHE */ * FROM exp_members | 0 | 0 |
| 227263391 | myDB | xx.xx.xx.22:53364 | database_name_here | Execute | 62966 | Waiting for table level lock | SELECT *
FROM (exp_members m, exp_member_groups g)
WHERE g.site_id = 1
AND m.group_id = g. | 0 | 0 |
| 227263413 | myDB | xx.xx.xx.22:53578 | database_name_here | Execute | 62963 | Waiting for table level lock | SELECT *
FROM (exp_members m, exp_member_groups g)
WHERE g.site_id = 1
AND m.group_id = g. | 0 | 0 |
| 227263949 | myDB | xx.xx.xx.21:60404 | database_name_here | Execute | 62838 | Waiting for table level lock | SELECT *
FROM (exp_members m, exp_member_groups g)
WHERE g.site_id = 1
AND m.group_id = g. | 0 | 0 |
| 227265302 | myDB | xx.xx.xx.21:44974 | database_name_here | Execute | 62590 | Waiting for table level lock | SELECT *
FROM (exp_members m, exp_member_groups g)
WHERE g.site_id = 1
AND m.group_id = g. | 0 | 0 |
| 227265854 | myDB | xx.xx.xx.21:51338 | database_name_here | Execute | 62490 | Waiting for table level lock | SELECT *
FROM (exp_members m, exp_member_groups g)
WHERE g.site_id = 1
AND m.group_id = g. | 0 | 0 |
| 227266040 | myDB | xx.xx.xx.21:53146 | database_name_here | Execute | 62459 | Waiting for table level lock | SELECT *
FROM (exp_members m, exp_member_groups g)
WHERE g.site_id = 1
AND m.group_id = g. | 0 | 0 |
| 227266310 | myDB | xx.xx.xx.21:55624 | database_name_here | Execute | 62403 | Waiting for table level lock | SELECT *
FROM (exp_members m, exp_member_groups g)
WHERE g.site_id = 1
AND m.group_id = g. | 0 | 0 |
| 227269701 | myDB | xx.xx.xx.21:56208 | database_name_here | Execute | 61775 | Waiting for table level lock | SELECT *
FROM (exp_members m, exp_member_groups g)
WHERE g.site_id = 1
AND m.group_id = g. | 0 | 0 |
| 227270694 | myDB | xx.xx.xx.21:36758 | database_name_here | Execute | 61592 | Waiting for table level lock | SELECT *
FROM (exp_members m, exp_member_groups g)
WHERE g.site_id = 1
AND m.group_id = g. | 0 | 0 |
| 227271250 | myDB | xx.xx.xx.21:43566 | database_name_here | Execute | 61440 | Waiting for table level lock | SELECT *
FROM (exp_members m, exp_member_groups g)
WHERE g.site_id = 1
AND m.group_id = g
Can you restart mysql, then get in, repair and optimize the tables. Just on principle. Also- do you know what max_connections is set to?
Has it been running fine on this version and just suddenly had the problem? Anything change about the time the problem happened? Can you tell if you’ve had a spike in traffic and/or members?
Also- can you see the slow query log? I wonder if there’s something in particularly having an issue.
One way to address it is via tuning mysql, but if it was fine, we’d like to spot what is suddenly making it not fine.
Hi Robin,
I can’t restart mysql as it’s a shared host and I don’t have access to that. And I do not know what max_connections is set to.
The site had been running fine until after I installed Cartthrob and started getting my hands dirty with it. I reached out the Cartthrob’s developer and they thought it sounded like a hosting environment issue. I’ve built dozens of EE sites on this server environment without issue, though.
I ended up creating a new database and new database user and things seem to be working again for now.
So I really don’t have any concrete ideas as to what caused the issue.