1

I have an application that is constantly inserting to a stats table. The average number of open connections is usually about 20 to 22.

There is also a processing script in PHP run by a cron that runs every 30 minutes. Around 2am UTC it takes about 10 minutes to process which is the longest amount of time during a day.

Around this time, I'm getting about 8000 PDOException: SQLSTATE[08004] [1040] Too many connections errors.

The max_connections is set to 500.

I've observed that by doing SHOW PROCESSLIST; I don't usually see any queries in the Info column. While the cron is running, I do see the query for the cron in the Info column and then the SQL inserts quickly pile up below it.

I don't really know a great deal about configuring MySQL but it seems like the inserts to the stats table are getting queued and surpassing the max connection limit while the Cron script is running.

What can be causing this and which configuration settings should I look at changing?

Asa Carter
  • 239
  • 1
  • 3
  • 14
  • Are any other cron jobs running around that time? – Jenny D Apr 23 '19 at 09:48
  • No, I've disabled a cron that does a MySQL backup to a remote server, while trying to identify the issue. – Asa Carter Apr 23 '19 at 09:50
  • Is the table in question InnoDB or MyISAM? Is the cron script performing any write operations (INSERT, DELETE, UPDATE) in this table? In what status are the INSERT queries when this happens? If you are using MyISAM and the queries show "waiting for table level lock", you must keep in mind, that MyISAM [locks on table level](https://dev.mysql.com/doc/refman/8.0/en/internal-locking.html) and you maybe should consider switching to a storage engine, which does row-level locking. – s1lv3r Apr 23 '19 at 09:57
  • They are InnoDB tables. Lots of inserts and deletes are being done in the cron script. Each statement is closed after execution and the connection is closed on completion. – Asa Carter Apr 23 '19 at 10:02
  • So the interesting info still is: What is the state of the piling up INSERT commands? – s1lv3r Apr 23 '19 at 10:05
  • The average time it takes to run the cron script is about 11 seconds and I'm usually sleeping during the busy periods so it is difficult to capture. I will try to take a screenshot the next time I can see some data. – Asa Carter Apr 23 '19 at 10:10
  • I should also add that the database is in a docker container. Nginx/PHP is in another container and the cron script is initiated from the host. I'm not sure if that makes any difference? – Asa Carter Apr 23 '19 at 10:12
  • I see. :) I don't think your Docker setup is at fault. It's hard to point to a concrete fault. I would say the sequence of events is quite obvious to begin with: The database isn't handling the INSERT queries fast enough while the cron is running, which make the connections pile up and reach the max_conn limit. You may be able to solve this problem in numerous different ways. Throw more hardware at it, optimize your InnoDB config, reprogram your application to buffer the queries client side, etc. ... – s1lv3r Apr 23 '19 at 10:29
  • I think the hardware should be ok. It's a dedicated server Intel Xeon E5 2630v4 (10 x 2.20 GHz), 256GB RAM. Though it is shared with some other less intensive apps in other containers. I think it's more likely to be the innoDB config. It's more or less the standard config except for turning off strict mode and increasing the connection limit. – Asa Carter Apr 23 '19 at 10:36
  • There is definitely a lot to tune, especially with such a hardware, but it's hard to give specific advice for such a unspecific problem. [`innodb_flush_log_at_trx_commit`](https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit) makes a big difference on INSERT heavy workloads, but of course has some other implications ... – s1lv3r Apr 23 '19 at 10:48
  • Here's a link to my recent mysqld.log. https://pastebin.com/sHjx5Zg7 Starting up it says `Number of pools: 1`. Should that be more than 1? – Asa Carter Apr 23 '19 at 11:01
  • Under MySQL 5.7 it's normally initially 8 of them, but if your buffer pool size is less then 1G, it is set to one pool ... yours is only 128M. If you have memory available you should likely increase both variables. TBH whole books have been written about this topic, maybe you should just google for `InnoDB tuning`. There are lots of guides out there and the 600 char comment box of stackexchange is to short to discuss this. :-) – s1lv3r Apr 23 '19 at 11:22
  • Thanks, I've found a few guides so I'll start with making changes to the buffer pool and monitor it. What is the difference between the number of pools and buffer pool instances though? – Asa Carter Apr 23 '19 at 12:22
  • Tell us more about the 10-minute task. We should work on making it faster or breaking it up, such as in smaller transactions. – Rick James Apr 29 '19 at 00:33
  • Add another cron job -- have it run at the same time as the cron in question. Code it to sleep 9 seconds, then grab `SHOW FULL PROCESSLIST;`. – Rick James Apr 29 '19 at 00:36
  • Aha, page cleaner problem, too. Please provide `SHOW VARIABLES;`, `SHOW GLOBAL STATUS;` and how much RAM is available to MySQL's docker. – Rick James Apr 29 '19 at 00:39
  • 128M! Ouch! Set it to 70% of available RAM ("available" after discounting for the various other apps.) Then set the instances to buffer_pool_size/1G, but min of 1 and max of 16. And I have some of the 600-byte limit left over. – Rick James Apr 29 '19 at 00:41
  • I did improve the cron script and it's taking less than a minute at it's busiest time now. The connection issues continued so I just get doubling the max connection limit and the buffer pool size until it was resolved. I ended up with a buffer pool size of 6G and a 2000 max connection limit. There is a lot more capacity for RAM on the host but there are other containers running as well so I just kept it to the minimum that I could without getting errors. – Asa Carter Apr 29 '19 at 08:30

1 Answers1

0

Can you check below parameters? try to increase the max_user_connections value and if application hold connection for long reduce the wait_timeout.

max_user_connections=[value]
wait_timeout = [value]
asktyagi
  • 2,401
  • 1
  • 5
  • 19