MySQL queries getting piled up

[Nobitakung]

I got the below error on my site:
------------------------------------------------------
An exception occurred: User mysite_guy already has more than 'max_user_connections' active connections in /home/mysite/public_html/library/Zend/Db/Adapter/Mysqli.php on line 333

1. Zend_Db_Adapter_Mysqli->_connect() in Zend/Db/Adapter/Abstract.php at line 315
2. Zend_Db_Adapter_Abstract->getConnection() in XenForo/Application.php at line 730
3. XenForo_Application->loadDb()
4. call_user_func_array() in XenForo/Application.php at line 973
5. XenForo_Application->lazyLoad() in XenForo/Application.php at line 1004
6. XenForo_Application::get() in XenForo/Application.php at line 1609
7. XenForo_Application::getDb() in XenForo/Model.php at line 161
8. XenForo_Model->_getDb() in XenForo/Model/DataRegistry.php at line 138
9. XenForo_Model_DataRegistry->_getMultiFromDb() in XenForo/Model/DataRegistry.php at line 97
10. XenForo_Model_DataRegistry->getMulti() in XenForo/Dependencies/Abstract.php at line 147
11. XenForo_Dependencies_Abstract->preLoadData() in XenForo/FrontController.php at line 127
12. XenForo_FrontController->run() in /home/mysite/public_html/index.php at line 13

---------------------------------------------------------------
(I replace the real account/directory name with mysite)

When I asking my host to check it out, I got their response as below:

------------------------------------------------------------------
The MySQL queries with respect to the database "mysite_guy" was getting piled up which was the reason for the connections to be getting completely used

----
| 66726 | mysite_guy | localhost | mysite_xen | Execute | 35816 | Creating delayed handler | INSERT DELAYED INTO xf_attachment_view
| 70099 | mysite_guy | localhost | mysite_xen | Execute | 35754 | Creating delayed handler | INSERT DELAYED INTO xf_attachment_view
| 77440 | mysite_guy | localhost | mysite_xen | Execute | 35665 | Creating delayed handler | INSERT DELAYED INTO xf_nflj_showcase_item_view
| 77894 | mysite_guy | localhost | mysite_xen | Execute | 35658 | Creating delayed handler | INSERT DELAYED INTO xf_attachment_view
| 79755 | mysite_guy | localhost | mysite_xen | Execute | 35628 | Creating delayed handler | INSERT DELAYED INTO xf_attachment_view
----

I've killed the queries now and the site is loading now, please contact the developer of the site and investigate why this was happening so as to avoid further issues.
------------------------------------------------------------------

It's look like there is some problem in the attachment uploading of the showcase addon.

I currently use showcase 2.5.4, just not sure if upgrading will solved the problem or not as I can't find any thread mention about 'max_user_connections'.

If this is already addressed somewhere, please point me to that page. Thanks.

 

[Bob]

'max_user_connections'

That is an issue with your SERVER aka HOST. My guess is that you are on a shared server or limited resources server.

When I asking my host to check it out, I got their response as below:

------------------------------------------------------------------
The MySQL queries with respect to the database "mysite_guy" was getting piled up which was the reason for the connections to be getting completely used

Yes, those queries were not being run because of issues with your HOST.

I've killed the queries now and the site is loading now, please contact the developer of the site and investigate why this was happening so as to avoid further issues.

Those queries are processing VIEW STATISTICS. They are using MySQL Delayed Insert queries.

70099 | mysite_guy | localhost | mysite_xen | Execute | 35754 | Creating delayed handler | INSERT DELAYED INTO xf_attachment_view

77894 | mysite_guy | localhost | mysite_xen | Execute | 35658 | Creating delayed handler | INSERT DELAYED INTO xf_attachment_view

79755 | mysite_guy | localhost | mysite_xen | Execute | 35628 | Creating delayed handler | INSERT DELAYED INTO xf_attachment_view

66726 | mysite_guy | localhost | mysite_xen | Execute | 35816 | Creating delayed handler | INSERT DELAYED INTO xf_attachment_view

These 4 SQL Queries are processing ATTACHMENT Views (the number of times a specific attachment has been viewed)

77440 | mysite_guy | localhost | mysite_xen | Execute | 35665 | Creating delayed handler | INSERT DELAYED INTO xf_nflj_showcase_item_view

This one is processing a Showcase Item views (the number if times specific showcase items have been viewed).

XENFORO runs a TON OF THESE.

It's look like there is some problem in the attachment uploading of the showcase addon.

This is not even remotely TRUE. You are making a baseless accusation because you saw one of the queries with the word showcase it in. The queries are not even the problem, your server configuration is!

This is an issue with your HOST due to the server configuration (max user connections). You need a less restrictive server.

You might want to consider disabling DELAYED INSERTS. This is a core XenForo Option setting.

I currently use showcase 2.5.4, just not sure if upgrading will solved the problem or not as I can't find any thread mention about 'max_user_connections'.

XenForo and Showcase are NOT the issue. Max User Connections has absolutely NOTHING to do with Showcase or XenForo. Its that your server configuration is not setup to handle the amount of processing that your site requires.

 

[Nobitakung]

Thank you for your suggestion. I have disabled the 'insert delayed' as you suggested.

So, did you mean those sql queries in queue has nothing to do with uploading attachment? it's just viewing?

 

[Bob]

So, did you mean those sql queries in queue has nothing to do with uploading attachment? it's just viewing?

That is what I said. Just look at the QUERY.

 INSERT DELAYED INTO xf_attachment_view

INSERT INTO TABLE NAME. INSERT means that a new record is being added to the table. The table in this case is xf_attachment_view. That table stores the VIEW COUNTS for when each time a user views an attachment in the xf attachment system.

The ISSUE you are having is because of all the VIEW TABLES having records created via delayed queries. Those delayed queries are attempting to run and your HOST is preventing that because of how they configured your server.

Until THEY fix your server, its going to continue happening.

 

[Nobitakung]

Thanks again for explanation. I know the sql but I just don't clearly understand what the xf_attachment_view is used for. I checked the table in my database and found that there are only 8 records there, while I have thousands or records in xf_attachment. And there is a view_count field in the xf_attachment already. So, I wonder what activities actually lead to the INSERT query to the xf_attachment_view. It must be very rarely-happen activity according to the nature of my site.

As I have already disable the INSERT DELAYED in XF setting, I think this is unlikely to happen again, but I'd like to make some test first.

Note: my xf_attachment_view table has only 1 field; attachment_id, no view_count. I'm using XF 1.5.6

 

[Bob]

but I just don't clearly understand what the xf_attachment_view is used for.

As for how it works, its pretty basic level stuff.

The xf_attachment_view is a MEMORY table that temporarily stores attachment ID's each time that an attachment is viewed. Then once every 30 minutes a CRON runs which updates the View Counts for Attachments. It does this by querying the xf_attachment_view table and getting the counts for each attachment_id. The process then updates the view_count field in the xf_attachments table for each attachment_id that has new view counts. The xf_attachment_view table is then truncated (emptied) and the process starts over again.

If you have further questions about the core xenforo attachments system and how it works, you need to ask those questions at xenforo.com.

 

[Nobitakung]

That means the INSERT into xf_attachment_view occurs almost every time people visit my site. And after disable the INSERT DELAYED, there is no any issue yet. So, it's very possible that the problem has been solved.

Thank you very much for your help.

 

[Bob]

That means the INSERT into xf_attachment_view occurs almost every time people visit my site.

Yes, and the same goes with THREADS. Each time a thread is viewed, the thread_id is written to the xf_thread_view table and once every 30 minutes a CRON does the same thing as it does for attachments.

If you have XenForo Media Gallery, it too does this for both albums and media.

Showcase does it as well for Showcase Item views.

btw, turning off delayed inserts won't fix anything with your HOST. Ignoring the problem (YOUR HOST) is the wrong thing to do. You will continue to have problems until you find a better host with a better server configuration that matchs the needs of your site.

 

[Nobitakung]

btw, turning off delayed inserts won't fix anything with your HOST. Ignoring the problem (YOUR HOST) is the wrong thing to do. You will continue to have problems until you find a better host with a better server configuration that matchs the needs of your site.

From what had happened, it seems that the server has problem in processing the INSERT DELAYED, so, disabling that query type should fix the issue, doesn't it? Or you meant that that 'max_user_connections' limit may still be a problem if my traffic increase in the future?

 

[Bob]

I will make it Large Bold Red text so that maybe this time you will READ it.

turning off delayed inserts won't fix anything with your HOST. Ignoring the problem (YOUR HOST) is the wrong thing to do. You will continue to have problems until you find a better host with a better server configuration that matchs the needs of your site.