MySQL Tuning – increasing max_connections to allow more simultaneous users

MySQL Settings

As your website gets more traffic, you’ll find that you run into problems where MySQL slows down. It has to wait for existing users to finish their tasks before allowing new users to start up with theirs.

This is where the value max_connections comes in.

By default, the max_connections setting is set to 151. This means 151 people can simultaneously ask for things from your MySQL database. This might sound like a lot, but you can quickly cross this limit as your website gains more users.

Here’s how to increase that value.

First, load up MySQL Workbench. That’s the easiest way to adjust values for your database. Log into it.

Once in, go to your Administration main tab. On the left, under the Instance section, use Options File.

You’re now editing the parameters of your option file. Choose the Networking tab on the right.

Scroll down to look for the max_connections area. It’s probably currently set to 151.

Update that to a new value. I recommend going slowly here. Update it to something new like 200 to start. See how your server responds. Then update it again if you have to. That way you can get a sense of what the right value is. Note that you can’t just go wild and choose 800,000. The max connections your server can handle will depend on its memory, disk space, the other tasks it’s doing, and so on.

When you put in a new value, click Apply. You’ll get a confirmation window showing you the old value and new value, to make sure you didn’t mistype something. Confirm your changes.

In order for your MySQL server to use these new values, you have to stop and start the server. On the left-hand menu under Instance is an option to Startup / Shutdown. Choose that. Stop the server. Then start it again.

You should now be running with your new setting.

1 Trackback / Pingback

  1. Tuning Your MySQL my.ini File for High Website Traffic – ASP Is Fun

Leave a Reply

Your email address will not be published.


This site uses Akismet to reduce spam. Learn how your comment data is processed.