Sherwin,
If there are only 7-10 users on the system and even if they all make multiple connections per user depending on the screen/report they are executing exceeding 1000 connections seems hi.
Is it possible that there is some “run away” php scripts that are using persistent connections?
When you got the error, did you check at the number of MySQL concurrent connections?
What state they were in? Lock Wait, running etc…
Unfortunately for you, these are two different systems. The screenshot came from a system that had entries in the calendar that was causing the long run time. Both systems had this same error message in the log. But the calendar was causing the CPU’s to max out because of some entry.
When I clear the post_calendar_event table the system would load normally.
Also, the system with the max out CPU’s has over 50 users.
When I would check the number of concurrent connections it would be around 140 and most of them would be sleeping. It would never get to the default 200 connections but the message too many database connections would show in the log anyway. I can attach my current MySQL and PHP config files if you would like to see them.
Again, the systems are not running fine and that Too Many connections message has not appeared in over a week since I changed my config files.
here are some server stats where database errors keep popping up
mysql> show status like '%onn%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Aborted_connects | 125 |
| Connections | 43053 |
| Max_used_connections | 152 |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_finished_connects | 0 |
| Threads_connected | 73 |
+--------------------------+-------+
7 rows in set (0.00 sec)
the below error can be called from various scripts like
interface/patient_file/summary/demographics.php
Too many connections in /var/www/html/openemr/vendor/adodb/adodb-php/drivers/adodb-mysqli.inc.php on line 123, referer: https://openemr/interface/main/main_screen.php?auth=login&site=default
other aborted connects do count failed logins i believe
After I saw your message @stephenwaite, I logged into the server to check the log file and found the error below. I ran the same command you show. Below are the results.
[26-Jul-2017 16:56:30 America/Bogota] SQL Error with statement:insert failed: INSERT INTO openemr_postcalendar_events ( pc_catid, pc_multiple, pc_aid, pc_pid, pc_title, pc_time, pc_hometext, pc_informant, pc_eventDate, pc_endDate, pc_duration, pc_recurrtype, pc_recurrspec, pc_startTime, pc_endTime, pc_alldayevent, pc_apptstatus, pc_prefcatid, pc_location, pc_eventstatus, pc_sharing, pc_facility,pc_billing_location,pc_room ) VALUES (?,?,?,?,?,NOW(),?,?,?,?,?,?,?,?,?,?,?,?,?,1,1,?,?,?)--Unknown column 'Array' in 'field list'==>C:\emr_wamp\www\openemr\library\encounter_events.inc.php at 339:sqlInsert />
[26-Jul-2017 17:08:51 America/Bogota] PHP Fatal error: Maximum execution time of 600 seconds exceeded in C:\emr_wamp\www\openemr\interface\main\calendar\modules\PostCalendar\pnuserapi.php on line 1428
mysql> show status like '%onn%';
+-----------------------------------------------+---------------------+
| Variable_name | Value |
+-----------------------------------------------+---------------------+
| Aborted_connects | 0 |
| Connection_errors_accept | 0 |
| Connection_errors_internal | 0 |
| Connection_errors_max_connections | 0 |
| Connection_errors_peer_address | 0 |
| Connection_errors_select | 0 |
| Connection_errors_tcpwrap | 0 |
| Connections | 2903 |
| Locked_connects | 0 |
| Max_used_connections | 173 |
| Max_used_connections_time | 2017-07-27 12:10:03 |
| Performance_schema_session_connect_attrs_lost | 0 |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_finished_connects | 0 |
| Threads_connected | 172 |
+-----------------------------------------------+---------------------+
16 rows in set (0.00 sec)
It is a good thing that the server is not hung up trying to run a job it can’t.
CPU usage rarely goes over 40%. Memory usage is at 48%. I can live with that since there is 16GB of RAM.
On another server, there are these warnings in the log like this:
2016-07-05T13:33:51.086073Z 24 [Warning] InnoDB: Cannot open table openemr/morphine_eq from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.Good day.
Please I’m also experiencing the same issues with “too many connections” when the number of devices connected exceeds 10. Once this happens openemr becomes very unstable, locking people out and preventing logins.
There are also persistent “check that mysql is running” and “can’t connect to sql database” error messages.
i really need help.
Could you please share your mysql config file along with the connection status?
That is from MySql:
show status like ‘%onn%’;
@visolveemr, do you happen to have a sample my.cnf for gnu/linux with innodb that you could share? thank you
Stephen…We just prepared a snippet from ViSolve’s past performance work for a system vendor.
These are the parameters ViSolved used to get a peak performance number using “Sysbench” benchmark using MySQL and InnoDB.
As always, care should be taken in using these parameters as there is no one size fits all and the performance number can vary significantly based on the application and the underlying hardware configuration.,
Here is the tuned cnf file parameters: http://www.visolve.com/hc/docs/mysql-optimal-config.pdf
thanks @visolve, looks like ibdata keeps growing but the actual openemr tables are not updating
looks like before you upgrade the tables we need this setting:
https://www.quora.com/How-can-I-avoid-having-a-gigantic-ibdata1-file-when-using-InnoDB
probably should post this for upgraders
https://dev.mysql.com/doc/refman/5.5/en/converting-tables-to-innodb.html
OUR SOLUTION:
This was one of those times when the error message is not the issue. The issue is the calendar. There is a bug in the recurring events function of the calendar. To test this on your system, backup the calendar and then empty the calendar table. Use the program. You will see the connections will stop growing. Reload the calendar and the connection overload will return.
We could not figure out what was being done to set off this chain reaction. The person that was setting the events that was causing the problem stopped. The office changed their policy on recurring events and did not share with us what they did.