How will MySQL queries affect my server?

If I have a contstant 2000+ users browsing my website and each page on the site requires say 20+ MySQL queries, is this going to make the site appear to lag down? I know the bandwidth of the server will handle it, but what kind of server would I need to handle this? Also, will the size of my database affect this at all? If so, what kind of lag time are we talking about here?

My next question is would it benefit me to have seperate databases for different things, or would it be best to have 1 database hosted on the same server as the website and just send all the queries across the same server?

 

If you are willing to do something about it, caching and query optimisation is where I would go first, something about 20+ queries on a page seems very unnecessary to me. Multiple databases won't do much and will be a problem with table interactions and joins. Instead, focus on separating archive-able data and result set minimisation.

 

Hi mjda

20 queries per page view is not uncommon if you are using a content management system. My advice is:

1) As krt says, make sure each query is tuned. Run 'explain select ...' for each query and see if you can add/change indexes to improve performance. Table scan queries are really going to hurt with 2000+ users.

2) Turn on the MySQL query cache. Give it 10M memory to begin with and watch the 'low memory prune count', add more memory if this starts to grow dramatically.

3) Consider making the connections between the webserver and MySQL persistent. Experiences vary but I have found it better to keep persistent connections between apache and MySQL.

4) You can have many databases in a single MySQL server and unless the configuration of each database has to be very different I would host them all out of a single MySQL server.

5) There are performance advantages hosting the webserver and MySQL in the same host (for example you can use unix sockets for database connections and save all your TCP/IP bandwidth for serving clients) *BUT* watch the memory usage! If MySQL runs out of real memory then it just runs real slow if apache runs out of real memory then it panics and usually requires a restart - very ugly. Hosting the database on a separate server stops the webserver and MySQL fighting over resources and makes it easier to add more webservers if you need them.

HTH

Petey

 

20 queries might not be uncommon but that is only to serve a fresh page, I cannot think of enough components of a CMS or any mainstream script that would require that many queries for every page access as you can cache much of the data.

 

Hi krt,

I was taking the worst case and thinking of products like drupal that build pages dynamically for logged in users (far more content is cachable for anonymous users).

Drupal pages are built using content blocks and each block can have a number of SQL queries feeding it and the content may vary depending on the user, role or time of day.

 

Thanks for your replies guys. I feel like I also used a "worse case" scenario in my description. I may actually have 2000 constant users browsing, but at the moment I'm only looking at around 4 MySQL queries per page. One thing that stinks, though, is that one of those queries will try to pull a list of all of those 2000 users' usernames and user ID numbers. It will be printing something similar to the active list of usernames on DP's main page. I have a dedicated server for this, and I can upgrade the memory in the server if needed. You guys both talk about caching the MySQL queries. I hate to sound stupid, but where would I activate that option at?

Thanks again for your replies.

 

Hi mjda

Look on your system for the my.cnf file. If you are using *nix then it usually lives in /etc.

Open the file in a text editor and change/add the following parameters:

#The memory allocated to store results from old queries.
query_cache_size=10M

#Don't cache results that are bigger than this.
query_cache_limit=400k

#Query cache type to use.
query_cache_type=1

save my.cnf and restart MySQL.

If you want to test if the query cache is working simply start a SQL command session and run the command:
show status like 'Qcache%';

Petey

 

Either that or I often prefer to have a cron task that runs every x minutes that gets the new list of active members (or whatever data is needed for the component in question). Via either method, every time the page is accessed, this statically cached data is output instead of querying and getting fresh data from the database. The important thing here is the balance between wait times and load reduction.

 

Check out caching solutions, such as shmop and memcached. For more less heavy scenarios, even caching to a file should work fine.

Keeping MySQL and Apache on the same server is good as it provides good data transfer speeds between the two. However, with user base and query per second numbers increasing, you might want to have a separate, or even several boxes for MySQL, balanced using either replication or clustering, depending on your case.