Hello,
Can someone help explain a little how pages are served and how to determine where a bottleneck on a site with concurrent users? I'm testing a site where 25 logged-in users change pages about every 15 seconds. My understanding is:
- An Apache process handles the requests. Apache needs to be configured so that (1) processes die eventually rather than ratchet up memory, by using MaxRequestsPerChild, (2) and MaxClients is configured properly to not exceed the available memory on the machine. If more requests for connections come in than are available, then Apache figures out how to queue them up.
- Drupal connects to the MySQL databases to read and write data. MySQL needs to be configured to allow the right amount of connections, the right memory and InnoDB settings, etc. Also, MySQL lookups can be rewritten to run faster.
I've done a chunk of configuration to try to make this work smoothly but am still running into problems. I've configured MaxClients to 50 on a 2GB machine, and the total memory usage hovers around less than 2GB so that seems to work okay. I've configured MySQL to use 50 connections as well. Using Devel, I see that the MySQL queries look they take less than a second, often less than .1 seconds, total to run as well; they do include a couple write queries (from autologout and statistics) but still these don't bring the total query time much higher.
But the web site keeps loading slowly when there's 25 logged-in users browsing around different pages at a brisk pace (switching pages every 15 seconds). I thought that if Apache is making sure to have enough clients but not enough to thrash the memory, and if the MySQL queries were short, then the pages should load fast. Am I misunderstanding something about how Apache or MySQL is working that might be causing bottlenecks?
One other specific question is, if 25 users are viewing a page and connecting to MySQL, how does MySQL handle all those requests -- simultaneously, in queue, etc.?
Thank you.
Comments
If 25 dynamic users can slow
If 25 dynamic users can slow the server, then either you have minimal cache assets [management] in place, and / or a sub-optimal Apache / Server setup.
A optimized Apache server should be able to handle several hundred connections at one time. I think your server is memory thrashing.... (low resources).
The MaxClients directive sets the limit on the number of simultaneous requests that will be served.
Any connection attempts over the MaxClients limit will normally be queued, up to a number based on the ListenBacklog directive.
Once a child process is freed at the end of a different request, the connection will then be serviced.
Apache is good for small files (Text, possibly CSS/JS etc.) but not so great at larger files like images, video, flash etc. This is because each one of those requires a new request (Unless you're using Keep-alive but that doesn't improve it too much). So if you've got a page with 49 external resources (so 50 requests in total) that takes 1 second to load, and your max clients is set at 100, you can only process two page views a second before requests start being queued.
MySQL handles requests through a queue. MySQL is a single-process, multithreaded application. One master thread has the highest priority among all the MySQL threads to control the server. In addition to the main threads, a dedicated set of user threads run at normal priority in the thread pools to handle simultaneous client requests. For each client request, one single thread is created to process the client request and sends back the result to each client -once the result is ready. And there is one single user thread that waits for input from the console and a group of utility threads running at lower priority to handle some background tasks.
MySQL may not scale well with the number of the user threads handling client requests. Performance scales up efficiently with each additional user thread until it reaches a peak performance point. After that, any increasing the number of user connections will decrease MySQL performance because of thread concurrency contention. For applications where the number of user connections is tunable, you need to determine -- for different workloads -- the optimum number of user connections for peak performance. The
innodb_thread_concurrencyparameter can be configured to set the maximum number of threads concurrently kept inside InnoDB. You need to increase this value when you see many queries in the queue inSHOW ENGINE INNODB STATUS\G.eg: [in Linux]
mysql -u root -pmysql>
mysql> SHOW ENGINE INNODB STATUS\G
--
Linux: Web Developer
Peter Bowey Computer Solutions
Australia: GMT+9:30
(¯`·..·[ Peter ]·..·´¯)
Are you running an opcode
Are you running an opcode cache (I recommend APC)? If not then each Apache thread will be using far more memory than is necessary.
As for MySQL, in my experience the challenge is not concurrent threads (InnoDB can scale effectively to at least 64 concurrent threads), rather temporary tables that need to be written to disk (usually due to filtering on one table and sorting on another).
But this statement here I think indicates the problem:
If you have any queries taking more than 5ms then that's probably a problem. Any queries taking as much as a second are causing huge problems. You probably need to optimize those queries. Possibly create some new indexes. And possibly cache the results.
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
Optimization
There a are too many issues to tackle here for proper optimization, opcode cache, maxclients, innodb buffers etc. etc. They all help and there are good writeups out there about them.
But do a quick thing, First disable keepAlives in apache so resources to mysql are de-allocated and use memlock in your my.conf. That should give you a quick boost. You will need to increase maxclients of course...
Keep in mind that if apache and mysql are on the same machine then they are competing for resources. So optimization turns more into fine balancing ...
Good luck
But do a quick thing, First
This doesn't make any sense to me. Once PHP is finished it closes the connection to the database. So the connection is not held open for subsequent requests over the same HTTP connection. KeepAlive is an important performance enhancement. If you are having issues with stray DB connections that are not closing you'd be better off to decrease wait_timeout in my.cnf.
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
Not easy to optimize by smell
Unless of course he is using persistent mysql connections , or there is a lock lingering somewhere in the sql statements. A quick die off of his php instance will pave the way to more optimizations.
It is always difficult to tune a machine by listening to someone describing its performance. There could be 100 different things affecting it as you well know. Fell free to help the guy out.
You should use a monitoring
You should use a monitoring tool like munin so you can see memory consumption on a graph and compare that to apache requests, MySQL queries, slow queries, and CPU usage; I'm wondering if you might be paging to disk; I had that happening when I went beyond about 50 logged in users on a site with 2 GB of RAM.
Calculate how much RAM is being used by your average http process, and make sure your max clients setting allows for overhead of MySQL, Linux, etc. the second you start paging, everything gets horribly slow.