Opened 18 months ago

Last modified 2 months ago

#12385 assigned Bug/Something is broken

Frequent max_user_connections limit reached

Reported by: https://id.mayfirst.org/joseph Owned by: https://id.mayfirst.org/joseph
Priority: High Component: Tech
Keywords: juanita.mayfirst.org mysql max_user_connections Cc: linda@…, https://id.mayfirst.org/jackaponte, https://id.mayfirst.org/kristin, https://id.mayfirst.org/jamie, https://id.mayfirst.org/ninareyes
Sensitive: no

Description

Hey y'all,

warresisters.org on juanita.mayfirst.org has been increasingly resulting in the attached error message because MySQL can't handle the number of requests. Can we just simply increase that? Or are there other solutions to this problem that would be better?

Joseph

Attachments (1)

hhgfglgpehfemldc.png (24.0 KB) - added by https://id.mayfirst.org/joseph 18 months ago.
Drupal max_user_connections error

Download all attachments as: .zip

Change History (40)

Changed 18 months ago by https://id.mayfirst.org/joseph

Drupal max_user_connections error

comment:1 Changed 18 months ago by https://id.mayfirst.org/jaimev

  • Cc https://id.mayfirst.org/jamie added

jamie what do you suggest?

comment:2 Changed 18 months ago by https://id.mayfirst.org/jamie

The limit is used to keep a single user from taking down an entire mysql server instance by maxing out the server-wide limit. That's not really an issue on a dedicated server so I just bumped you from 25 to 50 for both your drupal and civi user in the control panel (I think the server limit is 200 so we could increase it even more).

However... I suspect this won't fix the problem since usually when you hae 25 concurrent connections it means the connections are piling up for some reason and will pile-up no matter how many concurrent connections are allowed.

If you happen to be available when you get that error, you could run (as root):

mysqladmin processlist

Which should list the queries being run at the moment which will probably give us a hint as to the problem. I'm guessing that it's a deadlock issue. I've frequently seen this problem when someone dedupes a huge group of contacts which requires the creation of a giant temporary dedupe table, which is being selected from the civicrm_contact table. If it's a massive group, then it can takes minutes or even hours to complete and during this time, the records included in the dedupe select statement have a write lock on them - so you can modify those contacts.

In any event... getting a list of queries being run should help pin-point the problem.

comment:3 Changed 17 months ago by https://id.mayfirst.org/jaimev

  • Owner set to https://id.mayfirst.org/joseph
  • Status changed from new to assigned

Sending back to you for now joseph. Let us know if the suggestions above don't help.

comment:4 Changed 17 months ago by https://id.mayfirst.org/joseph

Thanks, y'all. I'll keep an eye out and try to keep more information if this happens again. I'll close this in two weeks if there have been no further incidents.

comment:5 Changed 17 months ago by https://id.mayfirst.org/jackaponte

Hi Jamie and Jaime,

Unfortunately http://www.warresisters.org/ is down again with the same error:

PDOException: SQLSTATE[42000] [1226] User 'wrl_web_drup' has exceeded the 'max_user_connections' resource (current value: 50) in lock_may_be_available() (line 167 of /usr/local/share/drupal-7.53/includes/lock.inc).

I'm not 100% sure what needs to be done to resolve this in the short-term? I don't have root access on juanita so can't take the troubleshooting or analysis steps described above.

comment:6 Changed 17 months ago by https://id.mayfirst.org/jackaponte

Ah, looks like perhaps max_user_connections needs to be increased even further on the server (100? More?) and then mysqld needs to be restarted? My guess from reviewing past notes about this problem!

comment:7 Changed 17 months ago by https://id.mayfirst.org/jaimev

  • Owner changed from https://id.mayfirst.org/joseph to https://id.mayfirst.org/jaimev

I ran processlist as jamie mentioned above and can see the following.

0 juanita:~# mysqladmin processlist
+--------+--------------+-----------+--------------+---------+-------+---------------------------------+------------------------------------------------------------------------------------------------------+
| Id     | User         | Host      | db           | Command | Time  | State                           | Info                                                                                                 |
+--------+--------------+-----------+--------------+---------+-------+---------------------------------+------------------------------------------------------------------------------------------------------+
| 675787 | wrl_web_drup | localhost | wrl_web_drup | Sleep   | 14581 |                                 |                                                                                                      |
| 675788 | wrl_web_drup | localhost | wrl_web_civi | Query   | 14581 | Copying to tmp table            | SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicrm_contact contact_a  LE |
| 678226 | wrl_web_drup | localhost | wrl_web_drup | Sleep   | 4163  |                                 |                                                                                                      |
| 678235 | wrl_web_drup | localhost | wrl_web_civi | Sleep   | 4444  |                                 |                                                                                                      |
| 678371 | wrl_web_drup | localhost | wrl_web_civi | Query   | 4162  | Waiting for table metadata lock | DROP TRIGGER IF EXISTS civicrm_contact_before_insert                                                 |
| 678372 | wrl_web_drup | localhost | wrl_web_drup | Sleep   | 4155  |                                 |                                                                                                      |
| 678374 | wrl_web_drup | localhost | wrl_web_drup | Sleep   | 4156  |                                 |                                                                                                      |
| 678376 | wrl_web_drup | localhost | wrl_web_civi | Query   | 4155  | Waiting for table metadata lock | DROP TRIGGER IF EXISTS civicrm_contact_before_insert                                                 |
| 678377 | wrl_web_drup | localhost | wrl_web_civi | Query   | 4155  | Waiting for table metadata lock | DROP TRIGGER IF EXISTS civicrm_contact_before_insert                                                 |
| 678446 | wrl_web_drup | localhost | wrl_web_drup | Sleep   | 4007  |                                 |                                                                                                      |
| 678447 | wrl_web_drup | localhost | wrl_web_civi | Query   | 4007  | Waiting for table metadata lock | SELECT  is_deleted  
 FROM civicrm_contact 
 
 WHERE (  civicrm_contact.id = 144007 )                |
| 678456 | wrl_web_drup | localhost | wrl_web_civi | Query   | 3959  | Waiting for table metadata lock | SELECT  id  
 FROM civicrm_contact 
 
 WHERE (  civicrm_contact.api_key  = 'XXXXXXXXXXXXXXXXXX'  |
| 678460 | wrl_web_drup | localhost | wrl_web_drup | Sleep   | 3953  |                                 |                                                                                                      |
| 678461 | wrl_web_drup | localhost | wrl_web_civi | Query   | 3953  | Waiting for table metadata lock | SELECT * 
 FROM civicrm_contact 
 
 WHERE (  civicrm_contact.id = 163222 )                           |
| 678479 | wrl_web_drup | localhost | wrl_web_civi | Query   | 3869  | Waiting for table metadata lock | SELECT  id  
 FROM civicrm_contact 
 
 WHERE (  civicrm_contact.api_key  = 'XXXXXXXXXXXXXXXXXX'  |
| 678480 | wrl_web_drup | localhost | wrl_web_drup | Sleep   | 3862  |                                 |                                                                                                      |
| 678481 | wrl_web_drup | localhost | wrl_web_civi | Query   | 3862  | Waiting for table metadata lock | SELECT  contact_type  
 FROM civicrm_contact 
 
 WHERE (  civicrm_contact.id = 163222 )              |
| 678490 | wrl_web_drup | localhost | wrl_web_civi | Query   | 3840  | Waiting for table metadata lock | SELECT  id  
 FROM civicrm_contact 
 
 WHERE (  civicrm_contact.api_key  = 'XXXXXXXXXXXXXXXXXX'  |
| 678504 | wrl_web_drup | localhost | wrl_web_civi | Query   | 3779  | Waiting for table metadata lock | SELECT  id  
 FROM civicrm_contact 
 
 WHERE (  civicrm_contact.api_key  = 'XXXXXXXXXXXXXXXXXX'  |
| 678535 | wrl_web_drup | localhost | wrl_web_civi | Query   | 3689  | Waiting for table metadata lock | SELECT  id  
 FROM civicrm_contact 
 
 WHERE (  civicrm_contact.api_key  = 'XXXXXXXXXXXXXXXXXX'  |
| 678557 | wrl_web_drup | localhost | wrl_web_civi | Query   | 3660  | Waiting for table metadata lock | SELECT  id  
 FROM civicrm_contact 
 
 WHERE (  civicrm_contact.api_key  = 'XXXXXXXXXXXXXXXXXX'  |
| 678558 | wrl_web_drup | localhost | wrl_web_drup | Sleep   | 3640  |                                 |                                                                                                      |
| 678559 | wrl_web_drup | localhost | wrl_web_civi | Query   | 3640  | Waiting for table metadata lock | SELECT    civicrm_contact.*,
          civicrm_email.email          as email
FROM      civicrm_conta |
| 678574 | wrl_web_drup | localhost | wrl_web_civi | Query   | 3598  | Waiting for table metadata lock | SELECT  id  
 FROM civicrm_contact 
 
 WHERE (  civicrm_contact.api_key  = 'XXXXXXXXXXXXXXXXXX'  |
| 678576 | wrl_web_drup | localhost | wrl_web_civi | Query   | 3571  | Waiting for table metadata lock | SELECT  id  
 FROM civicrm_contact 
 
 WHERE (  civicrm_contact.api_key  = 'XXXXXXXXXXXXXXXXXX'  |
| 678600 | wrl_web_drup | localhost | wrl_web_civi | Query   | 3509  | Waiting for table metadata lock | SELECT  id  
 FROM civicrm_contact 
 
 WHERE (  civicrm_contact.api_key  = 'XXXXXXXXXXXXXXXXXX'  |
| 678612 | wrl_web_drup | localhost | wrl_web_civi | Query   | 3480  | Waiting for table metadata lock | SELECT  id  
 FROM civicrm_contact 
 
 WHERE (  civicrm_contact.api_key  = 'XXXXXXXXXXXXXXXXXX'  |
| 678628 | wrl_web_drup | localhost | wrl_web_civi | Query   | 3391  | Waiting for table metadata lock | SELECT  id  
 FROM civicrm_contact 
 
 WHERE (  civicrm_contact.api_key  = 'XXXXXXXXXXXXXXXXXX'  |
| 678644 | wrl_web_drup | localhost | wrl_web_drup | Sleep   | 3275  |                                 |                                                                                                      |
| 678645 | wrl_web_drup | localhost | wrl_web_civi | Query   | 3274  | Waiting for table metadata lock | SELECT    civicrm_contact.*,
          civicrm_email.email          as email
FROM      civicrm_conta |
| 678653 | wrl_web_drup | localhost | wrl_web_civi | Query   | 3211  | Waiting for table metadata lock | SELECT  id  
 FROM civicrm_contact 
 
 WHERE (  civicrm_contact.api_key  = 'XXXXXXXXXXXXXXXXXX'  |
| 678684 | wrl_web_drup | localhost | wrl_web_drup | Sleep   | 2972  |                                 |                                                                                                      |
| 678697 | wrl_web_drup | localhost | wrl_web_civi | Query   | 2972  | Waiting for table metadata lock | DROP TRIGGER IF EXISTS civicrm_contact_before_insert                                                 |
| 678700 | wrl_web_drup | localhost | wrl_web_drup | Sleep   | 2948  |                                 |                                                                                                      |
| 678701 | wrl_web_drup | localhost | wrl_web_civi | Query   | 2948  | Waiting for table metadata lock | DROP TRIGGER IF EXISTS civicrm_contact_before_insert                                                 |
| 678783 | wrl_web_drup | localhost | wrl_web_civi | Query   | 2398  | Waiting for table metadata lock | SELECT  id  
 FROM civicrm_contact 
 
 WHERE (  civicrm_contact.api_key  = 'XXXXXXXXXXXXXXXXXX'  |
| 678785 | wrl_web_drup | localhost | wrl_web_drup | Sleep   | 2374  |                                 |                                                                                                      |
| 678786 | wrl_web_drup | localhost | wrl_web_civi | Query   | 2374  | Waiting for table metadata lock | SELECT    civicrm_contact.*,
          civicrm_email.email          as email
FROM      civicrm_conta |
| 678792 | wrl_web_drup | localhost | wrl_web_civi | Query   | 2309  | Waiting for table metadata lock | SELECT  id  
 FROM civicrm_contact 
 
 WHERE (  civicrm_contact.api_key  = 'XXXXXXXXXXXXXXXXXX'  |
| 678793 | wrl_web_drup | localhost | wrl_web_civi | Query   | 2279  | Waiting for table metadata lock | SELECT  id  
 FROM civicrm_contact 
 
 WHERE (  civicrm_contact.api_key  = 'XXXXXXXXXXXXXXXXXX'  |
| 678840 | wrl_web_drup | localhost | wrl_web_civi | Query   | 2011  | Waiting for table metadata lock | SELECT  id  
 FROM civicrm_contact 
 
 WHERE (  civicrm_contact.api_key  = 'XXXXXXXXXXXXXXXXXX'  |
| 678891 | wrl_web_drup | localhost | wrl_web_drup | Sleep   | 1685  |                                 |                                                                                                      |
| 678892 | wrl_web_drup | localhost | wrl_web_civi | Query   | 1685  | Waiting for table metadata lock | SELECT    civicrm_contact.*,
          civicrm_email.email          as email
FROM      civicrm_conta |
| 678904 | wrl_web_drup | localhost | wrl_web_drup | Sleep   | 1615  |                                 |                                                                                                      |
| 678905 | wrl_web_drup | localhost | wrl_web_civi | Query   | 1615  | Waiting for table metadata lock | SELECT    civicrm_contact.*,
          civicrm_email.email          as email
FROM      civicrm_conta |
| 678920 | wrl_web_drup | localhost | wrl_web_drup | Sleep   | 1582  |                                 |                                                                                                      |
| 678921 | wrl_web_drup | localhost | wrl_web_civi | Query   | 1582  | Waiting for table metadata lock | SELECT * 
 FROM civicrm_contact 
 
 WHERE (  civicrm_contact.id = 163222 )                           |
| 678922 | wrl_web_drup | localhost | wrl_web_drup | Sleep   | 1573  |                                 |                                                                                                      |
| 678923 | wrl_web_drup | localhost | wrl_web_civi | Query   | 1573  | Waiting for table metadata lock | SELECT  is_deleted  
 FROM civicrm_contact 
 
 WHERE (  civicrm_contact.id = 144007 )                |
| 678998 | wrl_web_drup | localhost | wrl_web_civi | Query   | 1198  | Waiting for table metadata lock | SELECT  id  
 FROM civicrm_contact 
 
 WHERE (  civicrm_contact.api_key  = 'XXXXXXXXXXXXXXXXXX'  |
| 679747 | root         | localhost |              | Query   | 0     |                                 | show processlist                                                                                     |
+--------+--------------+-----------+--------------+---------+-------+---------------------------------+------------------------------------------------------------------------------------------------------+

I am not sure if increasing the number of php processes should make a difference, 50 is a lot.

I've gone ahead and restarted apache2 and mysql.

Last edited 8 months ago by https://id.mayfirst.org/jaimev (previous) (diff)

comment:8 Changed 17 months ago by https://id.mayfirst.org/jackaponte

Many thanks for getting the site back up, Jaime!

comment:9 Changed 17 months ago by https://id.mayfirst.org/joseph

Hey y'all,

Yes, Jaime, thanks for the help here. I'll note that the API key shown in the processlist output is sensitive information. I've gone ahead and changed it, but can you please scrub it regardless?

This output makes me think that cron isn't completing and therefore the reason we're running out of connections is these dangling queries just sit there waiting for a table lock that never comes. Seemingly the server is running out of some resource, but I'm just speculating. I'm not watching closely enough to know offhand. We don't have this problem on any other VPS's where we run Civi. Is there any reason that WRL would be having such an issue here? I can't help but think #12301 is another symptom. How is 10GB of space not be enough to export 3K contacts? Could this be recursively dependent smart groups?

Joseph

comment:10 Changed 17 months ago by https://id.mayfirst.org/jamie

  • Sensitive set

I'll work on a more comprehensive response shortly but for now marking as sensitive.

comment:11 Changed 17 months ago by https://id.mayfirst.org/jamie

The query that caused the problem starts with "SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name FROM civicrm_contact contact_a LE". That appears to be a user doing a search and specifying the sort order, which requires writing out to disk, which is going to slow things down.

That step established a lock, which all the other queries are waiting on to be released.

It's not clear from the logs why that query never completed. I see a number of Deadlocks in your ConfigAndLog file, but the most recent is from the 26th (see https://network.progressivetech.org/node/1125 for more info on dead locks).

Under Search Preferences, you might consider select "no" for "Include Alphabetical Pager" and "Include Order By Clause" to avoid long and slow queries like this.

We also could consider running a cron job to kill long running queries (another technique we use at PTP to avoid this problem). It looks something like this:

  # Kill all mysql processes running longer than 2 hours
  seconds=7200
  notify="you@example.org"
  mysqladmin processlist | egrep '[0-9]' | while read pid user host db command time therest; do
    [ "$command" = "Sleep" ] && continue
    if [ -n "$time" ] && [ "$time" -gt "$seconds" ] && [ -n "$pid" ]; then
      if [[ "$pid" =~ ^[0-9]+$ ]]; then
        mysqladmin kill "$pid"
        echo | mail -s "Kill mysql process" "$notify"
      fi
    fi
  done

And, although it's a big change, you might consider upgrading to 4.7 to take advantage of the improvements for big data sets and mysql locking errors. PTP made that change, and enabled deterministic group cache clearing which made a big difference.

comment:12 Changed 17 months ago by https://id.mayfirst.org/joseph

  • Owner changed from https://id.mayfirst.org/jaimev to https://id.mayfirst.org/joseph

comment:13 Changed 15 months ago by https://id.mayfirst.org/joseph

Hey y'all,

Just updating that we've done the 4.7 upgrade, and while some of the issues we were seeing seemed to be fixed, things still aren't great. Admittedly vague, but we're in the process of testing various scenarios and waiting on a bit more data. We'll let post any updates when we get them.

Joseph

comment:14 Changed 14 months ago by https://id.mayfirst.org/jamie

  • Cc https://id.mayfirst.org/jackaponte https://id.mayfirst.org/kristin added

comment:15 Changed 14 months ago by https://id.mayfirst.org/jamie

(See #12724).

Thanks Joseph for the update.

I also wanted to point to #12734 - our new server which will provision SSD storage for MySQL. Once we have the up and running we can move juanita to that server so it can take advantage of the SSD cards. I don't think that will solve all problems (even with SSD cards, CiviCRM can get stuck on poorly optimized queries) but it should help a lot.

comment:16 Changed 8 months ago by https://id.mayfirst.org/jaimev

  • Cc https://id.mayfirst.org/ninareyes added

comment:17 Changed 8 months ago by https://id.mayfirst.org/jamie

Has the transition of juanita to linda been completed? It appears that juanita is running on linda and has the database partition on the SSD card.

But... I still see juanita referenced in cleaver.pp and also I still see juanita's lvs, etc on cleaver.

comment:18 Changed 8 months ago by https://id.mayfirst.org/jaimev

I can confirm juanita is running on linda. I will cleanup leftover references on cleaver now.

comment:19 Changed 8 months ago by https://id.mayfirst.org/jaimev

  • Sensitive unset

comment:20 Changed 8 months ago by https://id.mayfirst.org/jamie

  • Resolution set to fixed
  • Status changed from assigned to closed

Thanks Jaime! I am assuming we can close this ticket now since there has been no report of the problem recurring since we moved the guest.

comment:21 Changed 8 months ago by https://id.mayfirst.org/jaimev

  • Resolution fixed deleted
  • Status changed from closed to assigned

Actually there is a new report in #13230. Sorry, you weren't copied there but I've been trying to move the discussion here where there are more details.

comment:22 Changed 8 months ago by https://id.mayfirst.org/ninareyes

Hi everyone,

We're still running into an issue with WRL's scheduled cron jobs being unable to complete and are getting the max_user_connections error again. This has interfered with scheduled backups over the past week. Attempting to run cron via drush just now returns the following:

0 wrl-web@juanita:~/web.warresisters.org/web/sites$ drush cron -v
Executing: mysql --defaults-extra-file=/tmp/drush_Ot7UzN --database=wrl_web_drup --host=localhost --silent  < /tmp/drush_Bf9X26
Executing: mysql --defaults-extra-file=/tmp/drush_UGGb2a --database=wrl_web_drup --host=localhost --silent  < /tmp/drush_dnS2iu
WD backup_migrate: CiviCRM Database backed up successfully to wrl-civi-thrice-daily-2017-10-24T16-19-29 (94.34 MB) in destination CiviCRM Thrice Daily in 4 min 2 sec.       [notice]
WD cron: PDOException: SQLSTATE[42000] [1226] User 'wrl_web_drup' has exceeded the 'max_user_connections' resource (current value: 50) in                                 [error]
backup_migrate_source_db->_get_db_connection() (line 220 of
/home/members/warresisters/sites/web.warresisters.org/web/sites/all/modules/backup_migrate/includes/sources.db.inc).
WD linkchecker: Link checks completed.                                                                                                                                         [info]
<p>Initialization Error</p><p><pre>Array
(
    [callback] =&gt; Array
        (
            [0] =&gt; CRM_Core_Error
            [1] =&gt; simpleHandler
        )

    [code] =&gt; -24
    [message] =&gt; DB Error: connect failed
    [mode] =&gt; 16
    [debug_info] =&gt;  [nativecode=User 'wrl_web_drup' has exceeded the 'max_user_connections' resource (current value: 50)]
    [type] =&gt; DB_Error
    [user_info] =&gt;  [nativecode=User 'wrl_web_drup' has exceeded the 'max_user_connections' resource (current value: 50)]
    [to_string] =&gt; [db_error: message=&quot;DB Error: connect failed&quot; code=-24 mode=callback callback=CRM_Core_Error::simpleHandler prefix=&quot;&quot; info=&quot; [nativecode=User 'wrl_web_drup' has exceeded the 'max_user_connections' resource (current value: 50)]&quot;]
)
</pre></p><p></p><p>Initialization Error</p><p><pre>Array
(
    [callback] =&gt; Array
        (
            [0] =&gt; CRM_Core_Error
            [1] =&gt; simpleHandler
        )

    [code] =&gt; -24
    [message] =&gt; DB Error: connect failed
    [mode] =&gt; 16
    [debug_info] =&gt;  [nativecode=User 'wrl_web_drup' has exceeded the 'max_user_connections' resource (current value: 50)]
    [type] =&gt; DB_Error
    [user_info] =&gt;  [nativecode=User 'wrl_web_drup' has exceeded the 'max_user_connections' resource (current value: 50)]
    [to_string] =&gt; [db_error: message=&quot;DB Error: connect failed&quot; code=-24 mode=callback callback=CRM_Core_Error::simpleHandler prefix=&quot;&quot; info=&quot; [nativecode=User 'wrl_web_drup' has exceeded the 'max_user_connections' resource (current value: 50)]&quot;]
)
</pre></p><p></p>Drush command terminated abnormally due to an unrecoverable error.                                                                                                        [error]

Let me know what other information I can provide.

comment:23 Changed 8 months ago by https://id.mayfirst.org/jamie

I tried the cron job too and I saw 50 sleeping connections immediately come up. I think drupal and civicrm should reuse existing database connections rather than create new ones... unless that is overridden somewhere.

comment:24 Changed 8 months ago by https://id.mayfirst.org/jamie

Hm - CiviCRM does have new_link=true set in civicrm.settings.php. Have you tried it with that set to false?

Are your CiviCRM cron jobs triggered via the drush cron command, or are these just drupal cron jobs being run?

comment:25 Changed 8 months ago by https://id.mayfirst.org/ninareyes

I switched that value to false and tried to run cron via drush, but I ran into the same error. I've switched it back to true but can change it again if needed.

Here's the crontab we have setup, in case it's helpful:

4 */8 * * * /usr/bin/drush --root=/home/members/warresisters/sites/web.warresisters.org/web --uri=warresisters.org --quiet cc drush && /usr/bin/drush --root=/home/members/warresisters/sites/web.warresisters.org/web --uri=warresisters.org --quiet cron
*/15 * * * * /usr/bin/drush -u 1 -r /home/members/warresisters/sites/web.warresisters.org/web @sites civicrm-api job.execute auth=0 -y > /dev/null

comment:26 Changed 8 months ago by https://id.mayfirst.org/jamie

  • Resolution set to fixed
  • Status changed from assigned to feedback

I'm baffled. I was able to repeat the process and witness over 50 sleeping mysql processes running.

However, today, I see only 24 sleeping processes and the cron job completes without errors.

I also noticed that the sleeping processes are all connecting to the civi database, not the drupal database.

Since this is a dedicated guest, there is no reason you can't set your max user connections as high as you want (I think you will always reach your limit before any other resource contention kicks in), so I just boosted you from 50 to 100 (originally it was 25).

I think there is some strange drupal/civicrm business going on, but not sure what - the only way to get to the bottom, I think, would be to use a dev server and re-rerun the cron job while disabling modules and extensions one at a time.

In any event, if the problem persists even with 100 allowed mysql connections, let us know.

comment:27 Changed 7 months ago by automatic

  • Status changed from feedback to closed

No news is good news (we hope)! Given the lack of feedback, we think this ticket can be closed.

comment:28 Changed 2 months ago by https://id.mayfirst.org/ninareyes

  • Resolution fixed deleted
  • Status changed from closed to assigned

Hi everyone,

Reopening this per the last update in #13230. This is happening again for warresisters.org. It was fine for quite some time, but scheduled cron jobs started triggering this error after I updated backup_migrate to 7.x-3.5, which broke the module, and forced me to roll back to 7.x-3.3. I reinstalled the module completely when I rolled back so that any and all database changes were wiped out, so I'm not sure why this is happening again, though at the very least, the site is able to bring itself back up after a few moments. Would it be possible to bump up max_user_connections again? Is there any other troubleshooting that we should be doing right now?

comment:29 Changed 2 months ago by https://id.mayfirst.org/jamie

Frustrating - it appears that the mysql users are already set to 100 user connections, so I'm not sure increasing that number will help.

If there is a way you could capture the output of mysqladmin processlist as root that would help identify which queries are piling up.

comment:30 Changed 2 months ago by https://id.mayfirst.org/jaimev

I think only jack has root access to this server but let us know if you need us to add another ssh key.

comment:31 Changed 2 months ago by https://id.mayfirst.org/ninareyes

Hi all,

My key is in authorized_keys for the wrl-web user, ending in Hobbes-Too.local. Would it be possible to grant me root access as well? Thanks in advance.

comment:32 Changed 2 months ago by https://id.mayfirst.org/jaimev

I've just added that key to root access for juanita via puppet. Can you try logging in as root?

comment:33 Changed 2 months ago by https://id.mayfirst.org/ninareyes

Was able to log in as root, thanks! I ran mysqladmin processlist but this is all that I got:

0 juanita:~# mysqladmin processlist
+--------+------+-----------+----+---------+------+-------+------------------+
| Id     | User | Host      | db | Command | Time | State | Info             |
+--------+------+-----------+----+---------+------+-------+------------------+
| 227704 | root | localhost |    | Query   | 0    |       | show processlist |
+--------+------+-----------+----+---------+------+-------+------------------+

comment:34 Changed 2 months ago by https://id.mayfirst.org/jamie

Did you run the query while you were getting the Drupal error?

Most of the time, no queries are running.

However, if drupal is ever reporting that you have exceeded the limit of connections - you should see a lot more records returned and the info column will report what the query is (see comment 7 above).

comment:35 Changed 2 months ago by https://id.mayfirst.org/ninareyes

I'll try to catch the next cron job and run the query then, as this seems to coincide when those are run.

comment:36 Changed 2 months ago by https://id.mayfirst.org/ninareyes

I ran cron manually just now, then ran the command, and got the following:

0 juanita:/home/members/warresisters/sites/web.warresisters.org/web/sites# mysqladmin processlist
+------+--------------+-----------+--------------+---------+------+-------+------------------+
| Id   | User         | Host      | db           | Command | Time | State | Info             |
+------+--------------+-----------+--------------+---------+------+-------+------------------+
| 3148 | wrl_web_drup | localhost | wrl_web_drup | Sleep   | 20   |       |                  |
| 3149 | wrl_web_drup | localhost | wrl_web_civi | Sleep   | 31   |       |                  |
| 3150 | wrl_web_drup | localhost | wrl_web_civi | Sleep   | 31   |       |                  |
| 3151 | wrl_web_drup | localhost | wrl_web_civi | Sleep   | 30   |       |                  |
| 3152 | wrl_web_drup | localhost | wrl_web_civi | Sleep   | 30   |       |                  |
| 3153 | wrl_web_drup | localhost | wrl_web_civi | Sleep   | 29   |       |                  |
| 3154 | wrl_web_drup | localhost | wrl_web_civi | Sleep   | 29   |       |                  |
| 3155 | wrl_web_drup | localhost | wrl_web_civi | Sleep   | 28   |       |                  |
| 3157 | wrl_web_drup | localhost | wrl_web_civi | Sleep   | 28   |       |                  |
| 3158 | wrl_web_drup | localhost | wrl_web_civi | Sleep   | 27   |       |                  |
| 3159 | wrl_web_drup | localhost | wrl_web_civi | Sleep   | 27   |       |                  |
| 3162 | wrl_web_drup | localhost | wrl_web_civi | Sleep   | 26   |       |                  |
| 3163 | wrl_web_drup | localhost | wrl_web_civi | Sleep   | 26   |       |                  |
| 3164 | wrl_web_drup | localhost | wrl_web_civi | Sleep   | 25   |       |                  |
| 3165 | wrl_web_drup | localhost | wrl_web_civi | Sleep   | 25   |       |                  |
| 3166 | wrl_web_drup | localhost | wrl_web_civi | Sleep   | 24   |       |                  |
| 3167 | wrl_web_drup | localhost | wrl_web_civi | Sleep   | 24   |       |                  |
| 3168 | wrl_web_drup | localhost | wrl_web_civi | Sleep   | 23   |       |                  |
| 3169 | wrl_web_drup | localhost | wrl_web_civi | Sleep   | 23   |       |                  |
| 3170 | wrl_web_drup | localhost | wrl_web_civi | Sleep   | 23   |       |                  |
| 3171 | wrl_web_drup | localhost | wrl_web_civi | Sleep   | 22   |       |                  |
| 3172 | wrl_web_drup | localhost | wrl_web_civi | Sleep   | 22   |       |                  |
| 3173 | wrl_web_drup | localhost | wrl_web_civi | Sleep   | 21   |       |                  |
| 3174 | wrl_web_drup | localhost | wrl_web_civi | Sleep   | 21   |       |                  |
| 3175 | wrl_web_drup | localhost | wrl_web_civi | Sleep   | 21   |       |                  |
| 3185 | root         | localhost |              | Query   | 0    |       | show processlist |
+------+--------------+-----------+--------------+---------+------+-------+------------------+

Doesn't look like this cron job triggered any errors this time around, so I'll try again this afternoon.

comment:37 Changed 2 months ago by https://id.mayfirst.org/jamie

It's very suspicious to have so many sleeping processes - I can see how you could easily run out of available mysql connections. I just read through the the upstream backup and migrate issue which suggests that even with a working version of the module, a lot of sleeping processes is normal (I just bumped your users from 100 to 200 allowed connetions). I'm guessing that's why you might intermittently get the error even with the rolled back version so hopefully this ensures it doesn't come back.

I noticed that there is a commenting asking for someone to test a version of backup and migrate with a certain commit rolled back - is that something you might have time to try out?

comment:38 Changed 2 months ago by https://id.mayfirst.org/kristin

I noticed that there is a commenting asking for someone to test a version of backup and migrate with a certain commit rolled back - is that something you might have time to try out?'

That person is me. ;) And I'll be doing the testing when I have the time, maybe next week.

comment:39 Changed 2 months ago by https://id.mayfirst.org/jamie

Ha! awesome :)

Please login to add comments to this ticket.

Note: See TracTickets for help on using tickets.