XF 1.5 SQL Queries 'Writing to Net'

[PrinceReborn]

We've recently just wiped our hard drive, reinstalled CentOS 7, Apache2, all the Xenforo requirements and restored our website/forum files. Only to discover that any pages using SQL are incredibly slow, sometimes taking over 10 seconds to load. Whereas pages that don't use SQL are loading instantly.

I have checked the SQL processlist and noticed tons of queries that are 'writing to net':

| 42573507 | xenforo              | 10.4.14.218:59470                        | xenforo    | Sleep   |    1 |                  | NULL                                                                                                 |    0.000 |
| 42573508 | xenforo              | 10.4.14.218:59524                        | xenforo    | Sleep   |    1 |                  | NULL                                                                                                 |    0.000 |
| 42573509 | xenforo              | 10.4.14.218:59432                        | xenforo    | Execute |    2 | Writing to net   | SELECT title, template_compiled
                        FROM xf_template_compiled
                        WHERE title IN ('page_nav', 'bb_co |    0.000 |
| 42573511 | xenforo              | 10.4.14.218:59536                        | xenforo    | Sleep   |    2 |                  | NULL                                                                                                 |    0.000 |
| 42573512 | xenforo              | 10.4.14.218:59542                        | xenforo    | Sleep   |    0 |                  | NULL                                                                                                 |    0.000 |
| 42573513 | xenforo              | 10.4.14.218:59554                        | xenforo    | Sleep   |    0 |                  | NULL                                                                                                 |    0.000 |
| 42573515 | xenforo              | 10.4.14.218:59496                        | xenforo    | Sleep   |    2 |                  | NULL                                                                                                 |    0.000 |
| 42573516 | xenforo              | 10.4.14.218:59638                        | xenforo    | Execute |    3 | Writing to net   | SELECT title, template_compiled
                        FROM xf_template_compiled
                        WHERE title IN ('page_nav', 'bb_co |    0.000 |
| 42573517 | xenforo              | 10.4.14.218:59708                        | xenforo    | Sleep   |    2 |                  | NULL                                                                                                 |    0.000 |
| 42573518 | xenforo              | 10.4.14.218:59714                        | xenforo    | Execute |    9 | Writing to net   | SELECT data_key, data_value
                        FROM xf_data_registry
                        WHERE data_key IN ('options', 'languages', |    0.000 |
| 42573519 | xenforo              | 10.4.14.218:59890                        | xenforo    | Sleep   |    5 |                  | NULL                                                                                                 |    0.000 |
| 42573520 | xenforo              | 10.4.14.218:59894                        | xenforo    | Execute |    8 | Writing to net   | SELECT data_key, data_value
                        FROM xf_data_registry
                        WHERE data_key IN ('options', 'languages', |    0.000 |
| 42573521 | xenforo              | 10.4.14.218:59932                        | xenforo    | Execute |    7 | Writing to net   | SELECT data_key, data_value
                        FROM xf_data_registry
                        WHERE data_key IN ('options', 'languages', |    0.000 |
| 42573522 | xenforo              | 10.4.14.218:59940                        | xenforo    | Execute |    7 | Writing to net   | SELECT data_key, data_value
                        FROM xf_data_registry
                        WHERE data_key IN ('options', 'languages', |    0.000 |
| 42573523 | xenforo              | 10.4.14.218:59956                        | xenforo    | Execute |    7 | Writing to net   | SELECT data_key, data_value
                        FROM xf_data_registry
                        WHERE data_key IN ('options', 'languages', |    0.000 |
| 42573524 | xenforo              | 10.4.14.218:59976                        | xenforo    | Execute |    6 | Writing to net   | SELECT data_key, data_value
                        FROM xf_data_registry
                        WHERE data_key IN ('options', 'languages', |    0.000 |
| 42573525 | xenforo              | 10.4.14.218:60110                        | xenforo    | Execute |    5 | Writing to net   | SELECT data_key, data_value
                        FROM xf_data_registry
                        WHERE data_key IN ('options', 'languages', |    0.000 |
| 42573526 | xenforo              | 10.4.14.218:60112                        | xenforo    | Execute |    5 | Writing to net   | SELECT data_key, data_value
                        FROM xf_data_registry
                        WHERE data_key IN ('options', 'languages', |    0.000 |
| 42573527 | xenforo              | 10.4.14.218:60146                        | xenforo    | Execute |    4 | Writing to net   | SELECT data_key, data_value
                        FROM xf_data_registry
                        WHERE data_key IN ('options', 'languages', |    0.000 |
| 42573528 | xenforo              | 10.4.14.218:60156                        | xenforo    | Execute |    3 | Writing to net   | SELECT data_key, data_value
                        FROM xf_data_registry
                        WHERE data_key IN ('options', 'languages', |    0.000 |
| 42573529 | xenforo              | 10.4.14.218:60258                        | xenforo    | Execute |    2 | Writing to net   | SELECT data_key, data_value
                        FROM xf_data_registry
                        WHERE data_key IN ('options', 'languages', |    0.000 |
| 42573531 | xenforo              | 10.4.14.218:60344                        | xenforo    | Execute |    2 | Writing to net   | SELECT data_key, data_value
                        FROM xf_data_registry
                        WHERE data_key IN ('options', 'languages', |    0.000 |
| 42573532 | xenforo              | 10.4.14.218:60230                        | xenforo    | Execute |    2 | Writing to net   | SELECT data_key, data_value
                        FROM xf_data_registry

Any advice?

 

[Mike]

This generally indicates its stuck in the networking phase. Are there networking problems between your web server and database server? (They don't appear to be on the same server based on IP, though I suppose it could be Docker communication?)

 

[PrinceReborn]

I cannot see any networking issues between the web server and MySQL server. However, these types of queries seem to have disappeared now, maybe I ran the processlist at a time when nothing much was showing.

What is odd is that before we formatted the web server, we did not experience any issues, and the MySQL server & config has not been touched at all since re got everything back up and running.

This time I ran the processlist it now seems to be showing a lot of SQL queries on 'Waiting for table level lock'. Below is part of the processlist, when I ran this one there were approximately 100 queries like this.

| 42665102 | xenforo    | 10.4.14.218:33962                        | xenforo    | Execute |    3 | Waiting for table level lock | INSERT INTO xf_session_activity
                                        (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
| 42665104 | xenforo    | 10.4.14.218:33968                        | xenforo    | Execute |   10 | Waiting for table level lock | INSERT INTO xf_session_activity
                                        (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
| 42665109 | xenforo    | 10.4.14.218:33982                        | xenforo    | Execute |    8 | Waiting for table level lock | INSERT INTO xf_session_activity
                                        (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
| 42665112 | xenforo    | 10.4.14.218:33998                        | xenforo    | Execute |    9 | Waiting for table level lock | INSERT INTO xf_session_activity
                                        (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
| 42665114 | xenforo    | 10.4.14.218:34000                        | xenforo    | Execute |    2 | Waiting for table level lock | INSERT INTO xf_session_activity
                                        (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
| 42665117 | xenforo    | 10.4.14.218:34016                        | xenforo    | Execute |    7 | Waiting for table level lock | INSERT INTO xf_session_activity
                                        (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
| 42665121 | xenforo    | 10.4.14.218:34024                        | xenforo    | Execute |    7 | Waiting for table level lock | INSERT INTO xf_session_activity
                                        (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
| 42665124 | xenforo    | 10.4.14.218:34036                        | xenforo    | Execute |    4 | Waiting for table level lock | INSERT INTO xf_session_activity
                                        (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
| 42665131 | xenforo    | 10.4.14.218:34056                        | xenforo    | Execute |    3 | Waiting for table level lock | INSERT INTO xf_session_activity
                                        (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
| 42665134 | xenforo    | 10.4.14.218:34068                        | xenforo    | Execute |    8 | Waiting for table level lock | INSERT INTO xf_session_activity
                                        (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
| 42665136 | xenforo    | 10.4.14.218:34072                        | xenforo    | Execute |    8 | Waiting for table level lock | INSERT INTO xf_session_activity
                                        (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
| 42665139 | xenforo    | 10.4.14.218:34084                        | xenforo    | Execute |    2 | Waiting for table level lock | INSERT INTO xf_session_activity
                                        (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
| 42665147 | xenforo    | 10.4.14.218:34106                        | xenforo    | Execute |    5 | Waiting for table level lock | INSERT INTO xf_session_activity
                                        (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
| 42665150 | xenforo    | 10.4.14.218:34116                        | xenforo    | Execute |    3 | Waiting for table level lock | INSERT INTO xf_session_activity
                                        (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
| 42665151 | xenforo    | 10.4.14.218:34120                        | xenforo    | Execute |    3 | Waiting for table level lock | INSERT INTO xf_session_activity
                                        (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
| 42665153 | xenforo    | 10.4.14.218:34124                        | xenforo    | Execute |    5 | Waiting for table level lock | INSERT INTO xf_session_activity
                                        (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
| 42665158 | xenforo    | 10.4.14.218:34154                        | xenforo    | Execute |    3 | Waiting for table level lock | INSERT INTO xf_session_activity
                                        (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
| 42665160 | xenforo    | 10.4.14.218:34170                        | xenforo    | Execute |    4 | Waiting for table level lock | INSERT INTO xf_session_activity
                                        (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
| 42665161 | xenforo    | 10.4.14.218:34178                        | xenforo    | Execute |    5 | Waiting for table level lock | INSERT INTO xf_session_activity
                                        (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
| 42665164 | xenforo    | 10.4.14.218:34184                        | xenforo    | Execute |    3 | Waiting for table level lock | INSERT INTO xf_session_activity
                                        (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
| 42665165 | xenforo    | 10.4.14.218:34190                        | xenforo    | Execute |    7 | Waiting for table level lock | INSERT INTO xf_session_activity
                                        (user_id, unique_key, ip, controller_name, controller_action, v |    0.000 |
| 42665167 | xenforo    | 10.4.14.218:34196                        | xenforo    | Execute |    6 | Waiting for table level lock | SELECT session_activity.*
                                        ,
                                        user.*
                                FROM xf_session_activity AS session_activity
                 |    0.000 |
| 42665169 | xenforo    | 10.4.14.218:34206                        | xenforo    | Execute |    9 | Waiting for table level lock | SELECT session_activity.*
                                        ,
                                        user.*
                                FROM xf_session_activity AS session_activity
                 |    0.000 |

 

[PrinceReborn]

Here is a screenshot showing the query taking the longest, which is always 'writing to net' using xf_session_activity table query, followed by a waiting for table level lock INSERT INTO xf_session_activity table query, followed by multiple SELECT xf_session_activity table queries.

I can now fully confirm that the forum lag is related to these queries, and that nobody can load any forum pages until they eventually disappear from the process list.

 

[TPerry]

Try placing your sessions into cache (memcached/redis) instead of into mySQL.
Have you optimized your my.cnf for the server?

 

[PrinceReborn]

We are caching everything at the moment. However, just realised the issue, and it is network related. Asking my host to reconfigure the private network as ping times between the web server and sql server are fluctuating like crazy.