Invision Community 4: SEO, prepare for v5 and dormant account notifications Matt November 11, 2024Nov 11
Posted April 27, 20159 yr Several times this week I've had to restart MySQL on my server because the user for IPS 4 maxes-out on connections. I did not have this problem with IPS 3, and it has only been happening since 4.0.2.Is anyone else having this issue?EDIT: Also, is there a recommended value for this setting in MySQL?
April 27, 20159 yr How high you should set your connections is mostly memory related. There's no point raising it if you don't have the memory to support that number of connections. I've got a pretty busy board at 1.2 million posts and rarely needed >30 connections on IPB 3.This script will tell you how much your "max" memory usage is so you can use it to tweak your max_connections value. http://mysqltuner.comAs for IPB 4. I just upgraded on my live site and I had to disable "Activity Stream" it was causing some really long running queries. Which in turn locked many tables. So that means that new connections are made because other connections are waiting for table locks. It may be that your max connections is fine - just try and disable that first. Myself and others have logged bugs about this, but no replies yet.Your mySQL slow query log (/var/log/mysqld.slow.log on my server, check your my.cnf for the location) - will list long running queries which is how I found the Activity Stream query.
April 27, 20159 yr Author My max_user_connections is set to 300, server has 2GB RAM, but also runs the webserver. OS is Debian with no GUI. I can't run that script as I do not have root on MySQL.Significant slow queries seem to be related to Permissions.I think I'm going to be submitting a ticket. It shouldn't lock a table for 170+ seconds to run a permissions query.
April 28, 20159 yr You've still got Activity Stream turned on. I didn't, but I imagine if someone clicks Activity Stream -> Posts that your site will grind to a halt. You can disable it from ACP -> System -> Applications -> System ->Activity Stream. (Or you could just remove it from Guests in the same place).Assuming you've got the lowest possible defaults on your mysql server (no doubt they are higher) then this is how you calculate how much total memory your mysql can use:Total memory that could be used = key_buffer_size + tmp_table_size + ((per thread mem)*max_connections)Per connection memory = sort_buffer_size + read_buffer_size + read_rnd_buffer_size + join_buffer_sizeDefaults:sort_buffer_size=2Mread_buffer_size=128Kread_rnd_buffer_size=256Kjoin_buffer_size=128KAnd I'll assume super low values for:key_buffer_size = 128Mtmp_table_size = 32MSo let's say you ever did have 300 connections, your server would try and use:Per connection: 2048KB + 128 +256 + 128 = 2.5MTotal for 300 connections: 128M + 32M + (300 x 1536KB) = 910MYou can get your actual values from phpmyadmin if you have that installed and work out your exact memory usage. If you are running a webserver as well I'd probably not go above half your ram.
April 28, 20159 yr I'm just saying, those are the defaults. I have no information on his actual configuration.
Archived
This topic is now archived and is closed to further replies.