MyBB Community Forums

MyBB Community Forums > Community > General Discussion > Web Development and Administration > Help needed with MySQL max users being reached
Full Version: Help needed with MySQL max users being reached
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
Pages: 1 2

DrXotick

2014-08-20, 12:58 PM
I still can't solve this problem. It appears to be an issue with the resources being sucked all at once. 99.9% of the time it's perfect. Then it gets all jammed up. I am on highest end business class shared hosting. 8GB virtual, 2GB pRAM. It appears to not have anything to do with the # of users online. Hosting has said there are a few queries that are slow (related to php conditionals) but nothing that would cause this sort of 'clog'.

They have enabled logging every 15 min...

From hosting:

"
We have created a set of commands which perform monitoring of your account resource usage every 15 minutes and log the results in /home/chinerfj/monitoring/ directory. The following services are monitored:

Apache
MySQL
+
the results of the following commands are logged:

top
ps
"


But not sure what I'm looking for here in regards to what is jamming me up?

Here is a screenshot of use over the last 24 hours... At the bottom is when it jammed up with the SQL error (:42).
[attachment=32225]

spork985

2014-08-20, 01:20 PM
Do an iostat -x. I bet the problem is diskio considering the CPU load I see on your host.

DrXotick

2014-08-21, 08:38 AM
Hi Spork,

- Is the issue you suspect something that would follow me from server to server? I had a similar issue on the previous server and they moved me over to a server with more RAM (previous pRAM was 1GB) which has helped the issue, but not entirely eliminated the "MySQL max users error" (happens, but far less frequently) nor the strange spiking every several hours...Sometimes it will actually be okay for 24 hours or more... then these odd spikes that coincide with the MySQL issue.


- Is iostat -x something I can do via cPanel? ...or need to delve into putty or some such to accomplish...

Thanks much

Cameron:D

2014-08-21, 09:47 AM
iostat -x will need to be run via SSH.

Actually, if cpanel lets you run commands then that'll probably work.

Given the load average of 4+ and the almost idle CPU it does look like disk issues to me too (Slow disk causes MySQL queries to take a long time/long table locks, causing connections to back up).

DrXotick

2014-08-26, 12:24 PM
Yes. Here is a new screenshot. It says I hit the limit 91 times. But there is only 1 spike in 24 hours... :-|

But this is a different server and nearly the same issue was happening on the previous server. Hosting continues to suggest it's an optimization issue and the result of a few slow MySQL queries and/or too many users on at once. How they can possible glean that from this data...Is well beyond me.

Is the disk issue something that can follow from server to server?

[attachment=32266]

[attachment=32267]

I have accessed my hosting account via SSH/putty, but when typing "iostat -x", it gives me "-bash: command not found"... So I found this is part of sysstat. I can't figure how to install sysstat though. I have tried 'yum install sysstat', but it's a no go.

The help much appreciated.

DrXotick

2014-08-31, 07:30 AM
Anyone can help with this. I am fairly tech savvy, but this is  beyond me. Getting the


"MyBB has experienced an internal SQL error and cannot continue.

SQL Error:
1226 - User 'chinerfj_mybb305' has exceeded the 'max_user_connections' resource (current value: 30)
Query:
[READ] Unable to connect to MySQL server"

Hosting is useless, only saying "your site is too busy", which is obvious bull. It has nothing to do with # of users online and 99% of the time is fine.

Here is current resource usage:

[attachment=32293]

niere8

2014-08-31, 10:26 AM
Try this: http://community.mybb.com/thread-133890.html

DrXotick

2014-09-01, 11:24 AM
Thanks niere. It seems we have *possibly* narrowed it down.

Here is what apache and mysql and mysql slow looks like during the spikes and max mysql user connections error.

Can someone makes sense of this? It seems to have something to do with *likes* and searching through 160k rows for something.... 


Sun Aug 31 03:15:01 EDT 2014
   1-464  373412 0/128/939107  W 4.04  81     0    0.0    0.89  9461.46  173.245.54.213     mysite.com             GET /forum/Thread-
   3-464  372358 0/184/1106425 W 6.15  195    0    0.0    1.18  11346.24 103.22.201.118     mysite.com             GET /forum/Thread-
   5-464  380808 0/152/1486647 W 4.32  100    0    0.0    0.96  15477.54 173.245.54.185     mysite.com             GET /forum/Thread-
   7-464  378439 0/72/1439839  W 2.82  307    0    0.0    0.16  15007.23 173.245.54.213     mysite.com             GET /forum/Thread-
   8-464  378847 0/151/514822  W 4.07  119    0    0.0    1.17  5119.96  173.245.54.213     mysite.com             GET /forum/Thread-
   9-464  373672 0/170/377553  W 4.21  16     0    0.0    0.67  3928.66  173.245.54.213     mysite.com             GET /forum/Thread-
   11-464 379844 0/114/396800  W 3.76  167    0    0.0    0.52  4247.03  173.245.54.213     mysite.com             GET /forum/Thread-
   13-464 374171 0/43/1114246  W 1.05  57     0    0.0    0.33  11436.07 141.101.105.176    mysite.com             GET /forum/Thread-
   15-464 373908 0/145/1258631 W 5.15  198    0    0.0    0.67  12988.97 173.245.49.161     mysite.com             GET /forum/Thread-
   17-464 382486 0/24/1174721  W 0.32  69     0    0.0    0.08  12264.28 173.245.54.213     mysite.com             GET /forum/Thread-
   19-464 379535 0/77/1047536  W 1.60  236    0    0.0    0.34  10978.49 103.22.201.95      mysite.com             GET /forum/Thread-
   20-464 374063 0/33/968682   W 1.03  124    0    0.0    0.33  10094.18 188.114.111.86     mysite.com             GET /forum/Thread-
   21-464 382992 0/10/875130   W 0.42  17     0    0.0    0.00  9164.68  173.245.56.184     mysite.com             GET /forum/Thread-
   22-464 373239 0/211/771449  W 5.36  79     0    0.0    1.78  8326.65  173.245.54.213     mysite.com             GET /forum/Thread-
   25-456 294986 0/406/466206  G 6.83  70283  0    0.0    5.56  4978.84  173.245.48.197     mysite.com             GET /forum/images/metrostyle/alt_styles/alt_style.php HTTP/1.1
   26-464 382063 0/31/408418   W 0.26  100    0    0.0    0.30  4259.53  173.245.56.227     mysite.com             GET /forum/Thread-
   27-464 374408 0/59/334537   W 1.69  80     0    0.0    0.32  3480.60  173.245.54.213     mysite.com             GET /forum/Thread-
   28-464 370579 0/352/258039  W 10.25 0      0    0.0    1.67  2711.38  173.245.48.197     mysite.com             GET /forum/attachment.php?thumbnail=627 HTTP/1.1
   29-464 379294 0/126/207694  W 2.71  218    0    0.0    0.41  2050.42  108.162.254.111    mysite.com             GET /forum/Thread-
   31-464 383021 0/20/142795   W 0.89  16     0    0.0    0.06  1504.86  173.245.54.213     mysite.com             GET /forum/Thread-
   32-464 377061 0/1/112430    W 0.03  474    0    0.0    0.00  1185.56  173.245.54.177     mysite.com             GET /forum/Thread-
   33-464 382605 0/19/97603    W 0.15  60     0    0.0    0.03  994.14   173.245.54.213     mysite.com             GET /forum/Thread-
   34-464 382814 0/94/81747    W 1.75  9      0    0.0    0.33  853.62   173.245.54.213     mysite.com             GET /forum/Thread-
   36-464 374457 0/93/52154    W 2.70  234    0    0.0    0.51  599.95   173.245.54.213     mysite.com             GET /forum/Thread-
   38-464 383073 0/54/37074    W 1.74  0      0    0.0    0.16  419.68   173.245.48.197     mysite.com             GET /forum/attachment.php?thumbnail=625 HTTP/1.1
   40-464 375088 0/303/23239   W 8.42  120    0    0.0    6.18  250.18   173.245.50.196     mysite.com             GET /forum/Thread-
   41-464 383074 0/32/19131    _ 1.05  0      95   0.0    1.11  202.08   173.245.48.197     mysite.com             GET /forum/attachment.php?thumbnail=626 HTTP/1.1
   42-464 377104 0/0/18648     W 0.92  473    0    0.0    0.00  182.56   173.245.54.213     mysite.com             GET /forum/Thread-
   46-464 383153 0/15/16224    W 0.78  15     0    0.0    0.03  174.02   173.245.54.213     mysite.com             GET /forum/Thread-
   47-464 381215 0/280/12126   _ 4.98  0      94   0.0    3.91  122.69   173.245.48.197     mysite.com             GET /forum/attachment.php?thumbnail=624 HTTP/1.1
   50-464 377165 0/7/12686     W 0.19  469    0    0.0    0.00  143.51   173.245.54.213     mysite.com             GET /forum/Thread-
   51-464 378091 0/30/8136     W 1.08  369    0    0.0    0.32  97.46    173.245.54.213     mysite.com             GET /forum/Thread-
   53-464 377170 0/97/6252     W 2.65  392    0    0.0    0.52  60.20    173.245.56.169     mysite.com             GET /forum/Thread-
   54-464 377171 0/198/6307    W 5.08  250    0    0.0    1.14  71.82    173.245.54.224     mysite.com             GET /forum/Thread-
   55-464 375264 0/253/4343    W 6.45  109    0    0.0    0.92  42.31    173.245.54.213     mysite.com             GET /forum/Thread-
   Process: mysite.com.cgi  (/home/myBB/.system/fcgid/mysite.com.cgi)


PHP logs:

Sun Aug 31 03:15:02 EDT 2014
root      383793  0.0  0.0 103292   824 ?        S    03:15   0:00          \_ grep myBB
myBB  373157  3.6  0.0      0     0 ?        Z    03:04   0:22  |   \_ [php-cgi] <defunct>
myBB  373182  4.1  0.0 254436 30396 ?        S    03:04   0:26  |   \_ /usr/local/php/5.4/bin/php-cgi -d memory_limit=1024M
myBB  374093  1.5  0.0 254568 30220 ?        S    03:05   0:09  |   \_ /usr/local/php/5.4/bin/php-cgi -d memory_limit=1024M
myBB  374242  1.9  0.0 254960 31840 ?        S    03:05   0:10  |   \_ /usr/local/php/5.4/bin/php-cgi -d memory_limit=1024M
myBB  374259  0.6  0.0 254912 30560 ?        S    03:05   0:03  |   \_ /usr/local/php/5.4/bin/php-cgi -d memory_limit=1024M
myBB  374393  0.2  0.0 254448 30420 ?        S    03:05   0:01  |   \_ /usr/local/php/5.4/bin/php-cgi -d memory_limit=1024M
myBB  374475  0.2  0.0 254436 30084 ?        S    03:05   0:01  |   \_ /usr/local/php/5.4/bin/php-cgi -d memory_limit=1024M
myBB  374533  0.7  0.0 254308 30164 ?        S    03:05   0:04  |   \_ /usr/local/php/5.4/bin/php-cgi -d memory_limit=1024M
myBB  374549  0.4  0.0 254572 30312 ?        S    03:05   0:02  |   \_ /usr/local/php/5.4/bin/php-cgi -d memory_limit=1024M
myBB  374605  0.6  0.0 254656 30420 ?        S    03:05   0:03  |   \_ /usr/local/php/5.4/bin/php-cgi -d memory_limit=1024M
myBB  374648  0.1  0.0 254576 30356 ?        S    03:05   0:00  |   \_ /usr/local/php/5.4/bin/php-cgi -d memory_limit=1024M
myBB  374700  1.0  0.0 254312 30112 ?        S    03:05   0:05  |   \_ /usr/local/php/5.4/bin/php-cgi -d memory_limit=1024M
myBB  374715  0.3  0.0 254592 30268 ?        S    03:05   0:01  |   \_ /usr/local/php/5.4/bin/php-cgi -d memory_limit=1024M
myBB  374729  0.4  0.0 253884 29716 ?        S    03:05   0:02  |   \_ /usr/local/php/5.4/bin/php-cgi -d memory_limit=1024M
myBB  374762  1.2  0.0      0     0 ?        Z    03:05   0:06  |   \_ [php-cgi] <defunct>
myBB  374902  3.1  0.0 255844 32504 ?        S    03:05   0:17  |   \_ /usr/local/php/5.4/bin/php-cgi -d memory_limit=1024M
myBB  375505  1.3  0.0 254668 30484 ?        S    03:06   0:06  |   \_ /usr/local/php/5.4/bin/php-cgi -d memory_limit=1024M
myBB  377867  0.8  0.0 254404 30040 ?        S    03:08   0:03  |   \_ /usr/local/php/5.4/bin/php-cgi -d memory_limit=1024M
myBB  378047  0.2  0.0 254636 30292 ?        S    03:08   0:01  |   \_ /usr/local/php/5.4/bin/php-cgi -d memory_limit=1024M
myBB  378048  1.4  0.0      0     0 ?        Z    03:08   0:05  |   \_ [php-cgi] <defunct>
myBB  379846  1.8  0.0 254412 30072 ?        S    03:10   0:05  |   \_ /usr/local/php/5.4/bin/php-cgi -d memory_limit=1024M
myBB  379853  2.3  0.0 255332 31344 ?        S    03:10   0:06  |   \_ /usr/local/php/5.4/bin/php-cgi -d memory_limit=1024M
myBB  379983  1.6  0.0 254308 30020 ?        S    03:10   0:04  |   \_ /usr/local/php/5.4/bin/php-cgi -d memory_limit=1024M
myBB  380042  1.5  0.0 254584 30408 ?        S    03:10   0:03  |   \_ /usr/local/php/5.4/bin/php-cgi -d memory_limit=1024M
myBB  380867  5.0  0.0 254588 30388 ?        S    03:11   0:09  |   \_ /usr/local/php/5.4/bin/php-cgi -d memory_limit=1024M
myBB  380884  5.7  0.0 254844 30700 ?        S    03:11   0:11  |   \_ /usr/local/php/5.4/bin/php-cgi -d memory_limit=1024M
myBB  380956  4.1  0.0 254420 30200 ?        S    03:11   0:07  |   \_ /usr/local/php/5.4/bin/php-cgi -d memory_limit=1024M
myBB  381136  3.0  0.0 254956 31596 ?        S    03:12   0:05  |   \_ /usr/local/php/5.4/bin/php-cgi -d memory_limit=1024M
myBB  381227  7.6  0.0 254408 30116 ?        S    03:12   0:13  |   \_ /usr/local/php/5.4/bin/php-cgi -d memory_limit=1024M
myBB  382061  0.7  0.0 254824 30560 ?        S    03:13   0:00  |   \_ /usr/local/php/5.4/bin/php-cgi -d memory_limit=1024M
myBB  382194  7.5  0.0 246140 22300 ?        S    03:13   0:07  |   \_ /usr/local/php/5.4/bin/php-cgi -d memory_limit=1024M
myBB  382210  1.2  0.0 254328 30144 ?        S    03:13   0:01  |   \_ /usr/local/php/5.4/bin/php-cgi -d memory_limit=1024M
myBB  382226  7.4  0.0 254420 30112 ?        S    03:13   0:07  |   \_ /usr/local/php/5.4/bin/php-cgi -d memory_limit=1024M
myBB  383035 19.3  0.0 254320 30120 ?        S    03:14   0:06  |   \_ /usr/local/php/5.4/bin/php-cgi -d memory_limit=1024M
myBB  383556  8.3  0.0 235324 12248 ?        S    03:14   0:00  |   \_ /usr/local/php/5.4/bin/php-cgi -d memory_limit=1024M


MySQL logs (threads):

Sun Aug 31 03:15:01 EDT 2014
| 4896676 | myBB_database_666 | localhost | myBB_database_666  | Query          | 470  | Sending data       | SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid |
| 4896681 | myBB_database_666 | localhost | myBB_database_666  | Query          | 470  | Sending data       | SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid |
| 4896714 | myBB_database_666 | localhost | myBB_database_666  | Query          | 466  | Sending data       | SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid |
| 4897121 | myBB_database_666 | localhost | myBB_database_666  | Query          | 389  | Sending data       | SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid |
| 4897255 | myBB_database_666 | localhost | myBB_database_666  | Query          | 366  | Sending data       | SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid |
| 4897583 | myBB_database_666 | localhost | myBB_database_666  | Query          | 304  | Sending data       | SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid |
| 4897946 | myBB_database_666 | localhost | myBB_database_666  | Query          | 249  | Sending data       | SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid |
| 4898033 | myBB_database_666 | localhost | myBB_database_666  | Query          | 233  | Sending data       | SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid |
| 4898047 | myBB_database_666 | localhost | myBB_database_666  | Query          | 230  | Sending data       | SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid |
| 4898127 | myBB_database_666 | localhost | myBB_database_666  | Query          | 214  | Sending data       | SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid |
| 4898215 | myBB_database_666 | localhost | myBB_database_666  | Query          | 194  | Sending data       | SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid |
| 4898232 | myBB_database_666 | localhost | myBB_database_666  | Query          | 193  | Sending data       | SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid |
| 4898459 | myBB_database_666 | localhost | myBB_database_666  | Query          | 163  | Sending data       | SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid |
| 4898705 | myBB_database_666 | localhost | myBB_database_666  | Query          | 121  | Sending data       | SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid |
| 4898721 | myBB_database_666 | localhost | myBB_database_666  | Query          | 119  | Sending data       | SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid |
| 4898734 | myBB_database_666 | localhost | myBB_database_666  | Query          | 116  | Sending data       | SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid |
| 4898788 | myBB_database_666 | localhost | myBB_database_666  | Query          | 107  | Sending data       | SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid |
| 4898845 | myBB_database_666 | localhost | myBB_database_666  | Query          | 98   | Sending data       | SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid |
| 4898846 | myBB_database_666 | localhost | myBB_database_666  | Query          | 98   | Sending data       | SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid |
| 4898946 | myBB_database_666 | localhost | myBB_database_666  | Query          | 77   | Sending data       | SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid |
| 4898950 | myBB_database_666 | localhost | myBB_database_666  | Query          | 77   | Sending data       | SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid |
| 4898954 | myBB_database_666 | localhost | myBB_database_666  | Query          | 77   | Sending data       | SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid |
| 4899010 | myBB_database_666 | localhost | myBB_database_666  | Query          | 66   | Sending data       | SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid |
| 4899039 | myBB_database_666 | localhost | myBB_database_666  | Query          | 57   | Sending data       | SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid |
| 4899057 | myBB_database_666 | localhost | myBB_database_666  | Query          | 55   | Sending data       | SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid |
| 4899358 | myBB_database_666 | localhost | myBB_database_666  | Query          | 14   | Sending data       | SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid |
| 4899364 | myBB_database_666 | localhost | myBB_database_666  | Query          | 13   | Sending data       | SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid |
| 4899366 | myBB_database_666 | localhost | myBB_database_666  | Query          | 13   | Sending data       | SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid |
| 4899374 | myBB_database_666 | localhost | myBB_database_666  | Query          | 13   | Sending data       | SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid |
| 4899408 | myBB_database_666 | localhost | myBB_database_666  | Query          | 7    | Sending data       | SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid |
| 4899450 | myBB_database_666    | localhost | myBB_database_666 | Query          | 0    | System lock        | SELECT api_key FROM login ORDER BY id ASC LIMIT 1                                                    |



MySQL slow logs:

 # Time: 140831  3:14:31
# User@Host: myBB_database_666[myBB_database_666] @ localhost []
# Query_time: 441.793370  Lock_time: 0.015952 Rows_sent: 6  Rows_examined: 165279
SET timestamp=1409469271;
SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid) WHERE mp.uid = p.uid) AS count FROM mybb_posts p WHERE pid IN('10818','10834','10835','10836','10868','10870','11360','20266','27887') GROUP BY p.uid;
# Time: 140831  3:14:33
# User@Host: myBB_database_666[myBB_database_666] @ localhost []
# Query_time: 345.928987  Lock_time: 0.011524 Rows_sent: 7  Rows_examined: 128555
SET timestamp=1409469273;
SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid) WHERE mp.uid = p.uid) AS count FROM mybb_posts p WHERE pid IN('13513','13552','14727','16991','24420','29167','29168') GROUP BY p.uid;
# Time: 140831  3:14:34
# User@Host: myBB_database_666[myBB_database_666] @ localhost []
# Query_time: 344.098269  Lock_time: 0.022044 Rows_sent: 7  Rows_examined: 128555
SET timestamp=1409469274;
SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid) WHERE mp.uid = p.uid) AS count FROM mybb_posts p WHERE pid IN('23472','24629','25801','26320','26766','26862','30519') GROUP BY p.uid;
# Time: 140831  3:14:35
# User@Host: myBB_database_666[myBB_database_666] @ localhost []
# Query_time: 138.653963  Lock_time: 0.016899 Rows_sent: 3  Rows_examined: 55095
SET timestamp=1409469275;
SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid) WHERE mp.uid = p.uid) AS count FROM mybb_posts p WHERE pid IN('28840','30576','30586') GROUP BY p.uid;
# Time: 140831  3:15:06
# User@Host: myBB_database_666[myBB_database_666] @ localhost []
# Query_time: 475.380701  Lock_time: 0.019865 Rows_sent: 8  Rows_examined: 183646
SET timestamp=1409469306;
SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid) WHERE mp.uid = p.uid) AS count FROM mybb_posts p WHERE pid IN('6174','6534','6605','6610','7034','7043','8240','8974','9056','10282') GROUP BY p.uid;

# Time: 140831  3:15:09
# User@Host: myBB_database_666[myBB_database_666] @ localhost []
# Query_time: 473.756436  Lock_time: 0.020370 Rows_sent: 3  Rows_examined: 183636
SET timestamp=1409469309;
SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid) WHERE mp.uid = p.uid) AS count FROM mybb_posts p WHERE pid IN('30422','30459','30516','30619','30689','30697','30705','30706','30712','30719') GROUP BY p.uid;
# Time: 140831  3:15:21
# User@Host: myBB_database_666[myBB_database_666] @ localhost []
# Query_time: 77.559047  Lock_time: 0.009585 Rows_sent: 2  Rows_examined: 36730
SET timestamp=1409469321;
SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid) WHERE mp.uid = p.uid) AS count FROM mybb_posts p WHERE pid IN('14112','28259') GROUP BY p.uid;
# Time: 140831  3:15:22
# User@Host: myBB_database_666[myBB_database_666] @ localhost []
# Query_time: 325.221756  Lock_time: 0.021541 Rows_sent: 5  Rows_examined: 128551
SET timestamp=1409469322;
SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid) WHERE mp.uid = p.uid) AS count FROM mybb_posts p WHERE pid IN('19746','19747','19757','19803','24396','30635','30877') GROUP BY p.uid;
# Time: 140831  3:15:28
# User@Host: myBB_database_666[myBB_database_666] @ localhost []
# Query_time: 40.098000  Lock_time: 0.006625 Rows_sent: 1  Rows_examined: 18363
SET timestamp=1409469328;
SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid) WHERE mp.uid = p.uid) AS count FROM mybb_posts p WHERE pid IN('30714') GROUP BY p.uid;
# Time: 140831  3:16:07
# User@Host: myBB_database_666[myBB_database_666] @ localhost []
# Query_time: 173.025205  Lock_time: 0.013590 Rows_sent: 3  Rows_examined: 73458
SET timestamp=1409469367;
SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid) WHERE mp.uid = p.uid) AS count FROM mybb_posts p WHERE pid IN('14790','29158','29166','30789') GROUP BY p.uid;

Anyone can decipher this, what the possible issue is? Hosting seems very confident this is the cause.

Or perhaps these things in the logs getting jammed up is just a symptom of something deeper. I'm not sure.

Cameron:D

2014-09-01, 01:03 PM
What plugin are you using for likes? It seems to be doing something strange, I'm almost certain that a subquery isn't needed and that is where dozens of threads are getting stuck.

Can you run

EXPLAIN EXTENDED SELECT p.uid, (SELECT COUNT(*) FROM mybb_post_likes l LEFT JOIN mybb_posts mp ON (l.post_id = mp.pid) WHERE mp.uid = p.uid) AS count FROM mybb_posts p WHERE pid IN('10818','10834','10835','10836','10868','10870','11360','20266','27887') GROUP BY p.uid;

from PHPMyAdmin and paste the output?

DrXotick

2014-09-01, 02:35 PM
- I initially ran this under the query tab. When I did it, it didn't come back with anything at all. I hope that wouldn't mess something up?

- The results when running it under MySQL tab was:


[attachment=32301]


- I'm using "Like System 1.4.0" in conjunction with MyAlerts. I just realized that "Thank you/Like System 1.5.0" is still installed, though not activated and not set to "on" (I was using that prior to Like System). Not sure if this has anything to do with anything or not.

- I'm also totally perplexed as to why the issue is only happening for a couple times a day...if that. sometimes the spike only happens once every 48 hours...
Pages: 1 2
MyBB Community Forums > Community > General Discussion > Web Development and Administration > Help needed with MySQL max users being reached