Mysql databaseToo many connections


(Sherwin Gaddis) #1

There is a practice of about 12 - 17 users. They are regularly getting this error message in the PHP error log.
Then, the system freezes up and the database has to be restarted. Is anyone else experiencing this?

php_error.log (77.5 KB)

Demo farm and up for grabs demos are back!
(Stephen Waite) #2

yes, have you increased the innodb_buffer_pool_size in my.cnf @juggernautsei ?

(Gowtham) #3

try flushing the hosts

(Sherwin Gaddis) #4

what is the recommended size of the pool?

(Stephen Waite) #5

can be as much as 80% of total RAM

(Jerry P) #6

@juggernautsei I’m being nosy again but, are you on version 5 patch 3 or maybe newer? I fear we may have a script not releasing memory. PDF libraries could be the lark.

(Stephen Waite) #7

hi @sjpadgett, i’ve noticed it on version 5 patch 3, OpenEMR 5.0.0 mysqli_real_connect(): (HY000/1040): Too many connections · Issue #960 · openemr/openemr · GitHub

(Sherwin Gaddis) #8

@sjpadgett Yes, it is in that instance that I am getting that message in the error log. I have increased the pool size to 2GB like @stephenwaite suggested and I will monitor for the next few days to see if it goes away.

(Jerry P) #9

I would not so much suspect billing reports unless maybe you are using the HCFA-1500 with the graphic background form but perhaps custom report has a problem and not completing and yet not throwing any errors(hangs up). Perhaps, are you using that report much?
This may not be anything but my internal instincts fired off when I read this post:).

(Stephen Waite) #10

@sjpadgett, i think you’re right about custom_report

(Stephen Waite) #11

@sjpadgett but have no proof so will keep you posted like @juggernautsei advised

(visolve) #12

Sherwin -

That is a good start. But this may / may not solve the problem.

Here is the root cause for the error “Too Many Connections”

Basically, the error is caused when the number of connection exceeds the simultaneous connection that is define by max_connections parameter in the config file.

The objective is to reduce the number of concurrent connections inside the DB (increase the request/response time) and if you are I/O bounded increasing the buffer may help the issues.

Also, find out if any of your query is hogging the DB (sorting, serial read,…)

When increasing the buffer pool, be sensitive to OS and file system functions, If you take too much to the DB then you will starve the file system as the DB makes the I/O thru file system.

Love to see the system CPU/IO utilization report when you are getting this error.

ViSolve OpenEMR performance team.

(Sherwin Gaddis) #13

You requested this?

(Stephen Waite) #14

@juggernautsei, did you restart mysql after updating my.cnf or my.ini if this is xampp?

(Sherwin Gaddis) #15

Yes, this image is not a current view. The system is running at a normal level now.

@visolve I was thinking of getting a database monitoring tool to see which queries are running the longest. However, those tools are not cheap and I have not found any free ones.

also, I increased the number of threads to an insane number in the my.ini file. I set it to 1000 connections.

(Jerry P) #16

I just think that there is an underlying issue here besides connection pool size. Just to be safe you might check your access logs as could be a chance of a DOS attack or somewhere connections are being abandoned.

(Sherwin Gaddis) #17

I always check to see if there is a DOS attack first before looking internally. I stayed up for nearly 24hrs working on that system. I have lived through DOS attacks and they are 24/7 not 9-5.

The CPU usage subsided when the staff went home. I could open the calendar and run up the CPU by myself and then reset the system and the usage would go to zero. So, that was my proof positive it was not a DOS.

Another way I look for a C-99 attack is searching for odd files in the www folder that are not supposed to be there. Yes, I check the access log to see if any files from the outside are being written to my htdoc folder. I have been through that kind of attack also. None of those things exist.

Some of my systems are closed off from the outside. The log at the beginning of this thread comes from a server that has no request outside of the LAN. It is not ported out to the WWW. It is safe behind a meraki firewall.

(Jerry P) #18

@juggernautsei Okay sorry. Maybe it is is just pool size.

(visolve) #19

BTW, what are the versions of MySQL and InnoDB?

Also, can you provide the following two details?

Max_Connections (mysql) and innodb_thread_concurrency

(Sherwin Gaddis) #20

MySQL 5.7.11
InnoDB - Unknown
innodb_thread_concurrency (not used)
innodb_read_io_threads = 8
innodb_write_io_threads = 8
max_connections = 1000