Prestashop - Mysql Connection Getting Timed Out For 100 + Concurrent Users

[deepakchezhian]

Hi,

 

I am evaluating the performance of prestashop for one of my ECommerce project. I have used JMeter for doing the test. Prestashop is working fine till 100 users, but if i increase the user count above 100, some requests getting failed with the following error.

PHP Fatal error: Uncaught Link to database cannot be established: SQLSTATE[HY000] [2002] Resource temporarily unavailable

Prestashop is deployed in a Redhat Linux machine(Core i5, 8gb ram) using Xampp server(1.8.1) and connecting to a remote MySql(5.5.27) in a Redhat Linux machine(Core i5, 2gb ram)

I have configured the following parameters in Apache and mysql.

 

httpd-mpm.conf

<IfModule mpm_prefork_module>
StartServers			 5
MinSpareServers		  5
MaxSpareServers		 30
MaxRequestWorkers	  250
MaxConnectionsPerChild   0
</IfModule>

my.cnf

[mysqld]
user			= root
port			= 3306
socket		  = /opt/lampp/var/mysql/mysql.sock
skip-external-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
max_connections = 500
query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 4M
max_connect_errors = 30
connect_timeout = 300
net_read_timeout = 300
net_write_timeout = 300

Am i missing any configuration or is this an expected behavior?

 

Thanks in advance

Deepak

Edited February 24, 2013 by deepakchezhian (see edit history)

[Trip]

I don't know jmeter but what are you testing exacly? 100 concurrent users at the same time? I think this case is really rare to none existing. Besides that why do you use a dedictated MySQL Server? I assume that this is in most cases a performance decrease espacially as prestashop generates relativly big queries with large result sets.

I spare my comments on running mysql as root here but I would not rely to much on synthetic load tests. Imo it is better to monitor your live site and see how many resources you really need. In my case I have a maximum of 19 busy servers but an average of 2.

From my experience running to much spare servers is contraproductive and does not necessarily enhance performance. These values have to be adjusted by observing the live scenario and should only changed in small steps. I use mysqltuner script to check the mysql config. Assuming you use myisam table a table cache of 64 is way to small. If you use innodb you want to add

innodb_buffer_pool_size to your config.

All the best, trip

[deepakchezhian]

I don't know jmeter but what are you testing exacly? 100 concurrent users at the same time? I think this case is really rare to none existing. Besides that why do you use a dedictated MySQL Server? I assume that this is in most cases a performance decrease espacially as prestashop generates relativly big queries with large result sets. I spare my comments on running mysql as root here but I would not rely to much on synthetic load tests. Imo it is better to monitor your live site and see how many resources you really need. In my case I have a maximum of 19 busy servers but an average of 2. From my experience running to much spare servers is contraproductive and does not necessarily enhance performance. These values have to be adjusted by observing the live scenario and should only changed in small steps. I use mysqltuner script to check the mysql config. Assuming you use myisam table a table cache of 64 is way to small. If you use innodb you want to add innodb_buffer_pool_size to your config. All the best, trip

Thanks for your reply.

 

I am planning to create to create multi shop so i am expecting atleast 300 concurrent users at a time and that does not mean they are hitting at the same time(testing is done for 100 spread over 50 seconds and each have some think time also). I tried Local MySql server and also remote one, both of them having the same problem.

 

At present, we are evaluating various open source frameworks and the performance is our major criteria, so we have to rely load test find out which framework is good performant.

 

I want to understand whether the problem is expected in prestashop or i am missing some configuration?

 

Thanks,

Deepak

[Trip]

So I don't have tuned my local server but 10 concurrent users making 100 request is no problem

 

ab -n 100 -c 10 http://testsite/de/

Concurrency Level:	  10
Time taken for tests:   7.468 seconds
Complete requests:	  100
Failed requests:	    0
Write errors:		   0
Total transferred:	  3663630 bytes
HTML transferred:	   3608900 bytes
Requests per second:    13.39 [#/sec] (mean)
Time per request:	   746.844 [ms] (mean)
Time per request:	   74.684 [ms] (mean, across all concurrent requests)
Transfer rate:		  479.05 [Kbytes/sec] received
Connection Times (ms)
		  min  mean[+/-sd] median   max
Connect:	    0    0   0.1	  0	   0
Processing:   198  736 203.0    709    1105
Waiting:	  193  725 198.4    704    1078
Total:	    198  736 203.1    709    1105
Percentage of the requests served within a certain time (ms)
 50%    709
 66%    857
 75%    904
 80%    933
 90%   1000
 95%   1031
 98%   1097
 99%   1105
100%   1105 (longest request)

100 Concurrent users making 100 requests does not throw errors (in mysql.err) but the performance decrease (response time)is remarkable but that is the standard apache config, with few worker threads and really an edge case. I can not raise the number to 300 because it is not possible with apache bench. Maybe they don't allow that because they don't want this tool misused for DOS attacks? Who knows

ab -n 100 -c 100 http://testsite/de/

Document Path:		  /de/
Document Length:	    36089 bytes
Concurrency Level:	  100
Time taken for tests:   7.921 seconds
Complete requests:	  100
Failed requests:	    0
Write errors:		   0
Total transferred:	  3663688 bytes
HTML transferred:	   3608900 bytes
Requests per second:    12.62 [#/sec] (mean)
Time per request:	   7920.989 [ms] (mean)
Time per request:	   79.210 [ms] (mean, across all concurrent requests)
Transfer rate:		  451.69 [Kbytes/sec] received
Connection Times (ms)
		  min  mean[+/-sd] median   max
Connect:	    2    4   0.5	  4	   5
Processing:   531 4832 2497.7   4774    7914
Waiting:	  525 4818 2497.4   4767    7905
Total:	    533 4836 2498.0   4777    7919
Percentage of the requests served within a certain time (ms)
 50%   4777
 66%   6328
 75%   7856
 80%   7871
 90%   7896
 95%   7916
 98%   7918
 99%   7919
100%   7919 (longest request)

 

So my guess is, something in your configuration is not ok. My opinion, at least compared to magento community edition PS is performing very good but the multishop part in PS is very new and therefore maybe not 100% working as expected. A fresh install might work better but with my upgraded test shop I have my issues. As Magento was designed for multishop right from the start it might be more stable but the community edition lacks some features like good caching afaik. So before thinking about performance I would also have a look on the different concepts and approaches the systems have and if they really do what you need and more important if the features are working as expected. The PS multishop is imo really flexible but some people already missing features like sharing carts over different domains and so on.

All the best, trip

[PTE.]

suffering from this issue:

Link to database cannot be established: SQLSTATE[HY000] [2002] Resource temporarily unavailable