I am a member of a PhpBB soccer forum, and during games, a lot of members like to gather at the site giving a running commentary for folks who cannot watch. Of course there are also a lot of users reading the posts. Usually the site will crash after some event (like a goal) … I don’t know how many folks are using the forum when it crashes but I want to say anywhere from 100 - 200 users.
Is there is anything that can be done or is it time to move to another RDBMS.
when installing MySQL there is 3 options:
Decision Support (DSS)/OLAP: Choose this option if your server does not require a large number of concurrent connections. The maximum number of connections is set at 100, with an average of 20 concurrent connections assumed.
Online Transaction Processing (OLTP): Choose this option if your server requires a large number of concurrent connections. The maximum number of connections is set at 500.
Manual Setting: Choose this option to manually set the maximum number of concurrent connections to the server. Choose the number of concurrent connections from the drop-down box provided, or type the maximum number of connections into the drop-down box if the number you desire is not listed.
(this list contains up to 1400)
i hope it answers your question.
Thanks for the lightning fast response.
The MySQL database is already installed, anyway I can tweak the configuration files to increase the concurrent users to 1400 (I’m assuming thats the max right?)
Can MySQL really handle 1400 users concurrently reading and writing to the database?!
well, in practice! i don’t know! this part of your question should wait for a GURU!
GURUS!!! WE NEED YOUR HELP HERE!!
Thanks Chagh … I hear them coming now
is your server software actually crashing? is so, you need to look for problems or upgrade to newer stable versions. even in high loads, your server should not be crashing.
now, before you start pointing your finger at mysql, you need to figure out if mysql is actually the problem. it’s just as likely to be your webserver, in which case you need to limit your connections to your webserver, not increase them.
but here are some things you need to look at on the SQL side:
first thing is to look at your mysql configuration. mysql comes with a handful of configuration files for different server loads. try implementing the medium or large configuration file, making sure to copy your server-specific settings like data path.
you should also turn on the query cache (see http://dev.mysql.com/doc/mysql/en/query-cache.html). it may not help depending on how the application written, but if it doesn’t help, it doesn’t add much overhead.
also look at turning on the logs, particularly the slow-query-log as well as apaches logs…find out where the bottleneck is.
upping the max_connections will add some performance, but tuning the sql and properly closing the connections(mysql_close) as soon as you are finished with them, (tuning the application) is likely to garner more success.
also make sure you have the proper indeces on the db tables…any where clause should be indexes. check to make sure that the indexes that exist are valid, an index on the wron column can easily bring a db down to its knees. check to make sure that when you do a search on a table you only bring back the minimum of rows that you need, don’t suck in the entire table if you don’t have to, thats another big performance hit…