0

I have a dedicated server with these info:

128G RAM
6 cores/12 threads CPU, 3.4GHz
512 SSD storage
WHM/Cpanel latest
Apache/MySQL
Centos 7.x

I have a single website on this server which have heavy traffic at specific times of each day for less than one hour. At the peak traffic about 6000 users according to google analytics real time stats.

The average page load time by the server at about 500 users is about 0.15 seconds and the website code is very optimized for mysql.

The problem the server until 2000 concurrent users never have problem. After that number of users the Server load sometimes becomes 100% and above and mysql load becomes 600-900% and the website takes forever to load and displays server busy page and most of the time I have to restart mysql and apache servers.

These are my current settings in WHM for Apache:

Start Servers = 5
Minimum Spare Servers = 5
Maximum Spare Servers =10
Server Limit (Maximum: 20,000) = 5000
Max Request Workers = 5000
Max Connections Per Child = 10000
Max Keep-Alive Requests = unlimited
Timeout = 300

These are my settings for mysql in my.cnf which is default by WHM:

# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

[mysqld]
log-error=/var/lib/mysql/hosting.hostrose.com.err
performance-schema=0
default-storage-engine=MyISAM
innodb_file_per_table=1
innodb_buffer_pool_size=134217728
max_allowed_packet=268435456
open_files_limit=10000
max_connections=151

My question, what is the best values for these vars to serve that number of users without overloading or hanging the server specially for apache and mysql connections numbers.

aagouda
  • 11
  • 1
  • 1
    Settings tweaks come after diagnosis. You need to figure out what your bottlenecks are - slow queries? – ceejayoz Apr 30 '20 at 16:04

2 Answers2

0

The information provided is self-contradictory:

2000 concurrent users

max_connections=151

Meanwhile, Apache is limiting things to 20.

I suggest the following:

  • Change your tables to InnoDB. MyISAM is not good for concurrency. After changing, increase innodb_buffer_pool_size=134217728 to 90G More discussion: http://mysql.rjweb.org/doc.php/myisam2innodb

  • Change Apache to 30.

  • Leave the 151 alone; but check

    SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW GLOBAL STATUS LIKE 'Threads_running';

The former says how many connections were simultaneously connected to MySQL. The latter says how many were doing something at the moment.

mysql load becomes 600-900% and the website takes forever to load and displays server busy page

That means that you still have some queries that need optimizing.

For further analysis (if settings and queries), follow the instructions in http://mysql.rjweb.org/doc.php/mysql_analysis

Rick James
  • 2,058
  • 5
  • 11
  • No limit on Apache I have mod_fcgi enabled and used. The website uses Perl script rungs as .fcgi. – aagouda May 01 '20 at 06:16
  • @aagouda - No limit? Not good. If thousands of web server pages are waiting for database results, MySQL will handle it, but slowly. Then you "have to restart mysql and apache servers". It is better to throttle the thundering herd earlier in the stack -- at the web server. – Rick James May 01 '20 at 14:40
-1

Increase the MySQL max connection upto > 4000 and check the performance

Ck_7
  • 101
  • 1
  • 1
    If MySQL is incurring heavy load at the current setting, sending *more* load to it is unlikely to help. – ceejayoz Apr 30 '20 at 18:16