7

I'm performing some load tests on my brand new server Apache/PHP/Mysql (Bitnami LAMP stack. Php 7.0.3/MySQL 5.7.10 ). I'm using AWS with 1 EC2 instance behind a loadBalancer. At the moment I'm using loader.io or jmeter for the tests. I'm testing a very simple API that perform this query:

select *,sleep(0.5) from debug limit 1

I added the sleep(0.5) because I wanted to see how the server behave with multiple concurrent connections and I found a bottleneck: if I run "SHOW PROCESSLIST" I can see only 5 process even if I have 10 concurrent user. The load test show that the connections are queued because the response time is growing during the test from 500 milliseconds to several seconds (depending on the duration of the test and the number of concurrent users).

I checked

select @@max_connections

and it's 151 (the default). max_user_connections is 0. What other parameter should I check to increase the number of concurrent connection on my DB?

If I run the test with 5 concurrent users, each one get a response in 500 milliseconds. If I add more concurrent users than the response time slow down.

If I run the load test on an API that does not access the DB there are no issues even with 400 concurrent users.

EDIT:

Monitoring with HTOP I see:

Tasks: 34, 245 thr; 2 running

Could be here the issue?

Thanks a lot

Stefano Giacone
  • 2,016
  • 3
  • 27
  • 50
  • Can you please describe your PHP/MySQL server in more details? What web server are you using? Apache module, CGI, FCGI? – CodeZombie Feb 19 '16 at 16:17
  • How are you running your concurrent tests? Have you tried making the sleep longer to guarantee that the query sticks around long enough? – Mr. Llama Feb 19 '16 at 16:18
  • This query will only sleep if there is at least one row of data in the debug table. Also check MAX_USER_CONNECTIONS for the user is also zero as it can be set per user. – Steve E. Feb 19 '16 at 16:38
  • I updated the description. I'm using Apache. What do you mean with "guarantee that the query sticks around long enough"? – Stefano Giacone Feb 19 '16 at 16:39
  • @Steve: MAX_USER_CONNECTIONS is 0 and there is 1 row of data. If I run the test with 5 concurrent users each one get a response in 500 milliseconds – Stefano Giacone Feb 19 '16 at 16:40
  • If you don't get good responses here, I'd suggest moving this question to: http://dba.stackexchange.com/ – dmgig Feb 19 '16 at 16:41
  • Just to add, I tested this with a 20s delay and got all 10 requests showing concurrently in the process list. – Steve E. Feb 19 '16 at 16:47
  • ok, so there's a problem with my [default] configuration... – Stefano Giacone Feb 19 '16 at 16:53
  • @StefanoGiacone - What I mean by "not sticking around long enough" is that all 10 queries have to be in the same 500ms window in order to appear in the process list, which is somewhat improbable. Additionally, on some RDBMSs, processes aren't listed unless they've existed for a certain length of time. – Mr. Llama Feb 19 '16 at 17:01
  • MySQL is good about showing processes in PROCESSLIST quite promptly. – Rick James Feb 20 '16 at 01:46
  • Does "loader.io" have a config that is set to 5? Or does it take 100ms to launch a query? – Rick James Feb 20 '16 at 01:48
  • What is MaxClients on Apache? – Rick James Feb 20 '16 at 01:49
  • MySQL _can_ (but _should not_) handle a thousand simultaneous active connections. – Rick James Feb 20 '16 at 01:49

2 Answers2

2

There are two areas for allowing concurrent connections:

1. Web servers

Check number of concurrent connections allowed by your web server.

See

  1. The Secret To 10 Million Concurrent Connections -The Kernel Is The Problem, Not The Solution
  2. How many socket connections can a web server handle?
  3. How do you increase the max number of concurrent connections in Apache?
  4. How to optimize apache web server for maximum concurrent connections or increase max clients in apache

Select your web server wisely to handle more concurrent connections.

2. Database and Open Files

Check your MySQL DB is capable of handling max concurrent connection coming from WebServer PHP.

Set max_connections to some higher amount.

set global max_connections := 800;

Also check open_files_limit for your OS. On linux, your process is limited to 1024 files, by default. This is very low, since every thread, connection, and, of course, file -- make for a file handle in linux. So set open_files_limit to some generous number (say 10000) to clear up your many connections with the operating system.

Community
  • 1
  • 1
Somnath Muluk
  • 55,015
  • 38
  • 216
  • 226
2

The solution was actually very easy:

The Bitnami LAMP stack I'm using is configured with PHP-FPM, so the configuration is not on the apache side but in php/etc/common.conf

pm=ondemand
pm.max_children=5
pm.start_servers=2
pm.min_spare_servers=1
pm.max_spare_servers=3

inreasing the pm.max_children=5 solved the issue.

Stefano Giacone
  • 2,016
  • 3
  • 27
  • 50
  • So issue is related to Web Servers as mentioned in my answer. Thanks for writing detailed root cause for your issue. – Somnath Muluk Feb 26 '16 at 13:07
  • BTW you can still accept my answer and award me bounty for pointing towards solution. You will not be getting bounty anyway. See [How does accepting an answer work?](http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) – Somnath Muluk Feb 26 '16 at 13:09