Jump to content

Server Side Optimization - CentOS + MySQL

Recommended Posts

Looking for some experienced help and advice to optimize server side to reduce wait time to improvise the page load.

We are running on dedicated server with clouldflare pro plan to speed up front end.

We only have 1 main site on this server.

Server spec:

  • 2 X Intel(R) Xeon(R) CPU E3-1245 V2 @ 3.40GHz
  • Speed: 3392.173 MHz

  • Cache: 8192 KB

  • RAM: 32 GB

Operating system

  • CENTOS 6.10
  • PHP
  • MySql
  • Apache

CPU Load

top - 22:20:01 up 286 days,  1:59,  1 user,  load average: 0.14, 0.14, 0.17
Tasks: 244 total,   1 running, 241 sleeping,   0 stopped,   2 zombie
Cpu0  : 31.8%us,  2.0%sy,  0.0%ni, 65.7%id,  0.0%wa,  0.0%hi,  0.5%si,  0.0%st
Cpu1  :  4.0%us,  2.0%sy,  0.0%ni, 94.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu2  :  0.0%us,  0.5%sy,  0.0%ni, 99.5%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu3  :  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu4  :  0.0%us,  1.0%sy,  0.0%ni, 99.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu5  :  1.0%us,  0.5%sy,  0.0%ni, 98.5%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu6  :  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu7  :  2.0%us,  0.5%sy,  0.0%ni, 97.5%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  32909764k total, 14882656k used, 18027108k free,  1733764k buffers
Swap:   523260k total,   158160k used,   365100k free,  5096856k cached

MySql Config




#### Per connection configuration ####


# MyISAM #


MySQL Tuner Report

 >>  MySQLTuner 1.4.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Currently running supported MySQL version 5.7.29
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Data in MyISAM tables: 508M (Tables: 684)
[--] Data in InnoDB tables: 34M (Tables: 476)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 87)
[--] Data in MEMORY tables: 0B (Tables: 4)
[!!] Total fragmented tables: 101

-------- Security Recommendations  -------------------------------------------
ERROR 1054 (42S22) at line 1: Unknown column 'password' in 'where clause'
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 5d 22h 27m 10s (95M q [186.335 qps], 582K conn, TX: 545B, RX: 27B)
[--] Reads / Writes: 90% / 10%
[--] Total buffers: 1.6G global + 16.2M per thread (505 max threads)
[OK] Maximum possible memory usage: 9.7G (30% of installed RAM)
[OK] Slow queries: 0% (0/95M)
[OK] Highest usage of available connections: 43% (219/505)
[OK] Key buffer size / total MyISAM indexes: 1.0G/240.5M
[OK] Key buffer hit rate: 100.0% (1B cached / 72K reads)
[OK] Query cache efficiency: 91.9% (84M cached / 91M selects)
[!!] Query cache prunes per day: 608570
[OK] Sorts requiring temporary tables: 0% (176 temp sorts / 670K sorts)
[!!] Joins performed without indexes: 6834
[OK] Temporary tables created on disk: 5% (106K on disk / 1M total)
[OK] Thread cache hit rate: 99% (1K created / 582K connections)
[OK] Table cache hit rate: 38% (19K open / 51K opened)
[OK] Open file limit used: 27% (11K/41K)
[OK] Table locks acquired immediately: 99% (10M immediate / 10M locks)
[OK] InnoDB buffer pool / data size: 128.0M/34.9M
[OK] InnoDB log waits: 0


Edited by Gauravk
Link to comment
Share on other sites

Thanks @Makoto and @ASTRAPI apart from the cost, there are several other reasons that I am stuck with DIY approach due to internal policies.

If any of you have some mod, plugin, app or detailed guide, I'm happy to buy it.

See if you can still support here with any inputs that will be highly appreciated.

I made little progress so far from 1.96 Request/Sec - 6.43 Request/Sec, hope I can better this more.

Baseline - Anonymous - Cloudflare ON

Concurrency Level:      1
Time taken for tests:   51.114 seconds
Complete requests:      100
Failed requests:        9
   (Connect: 0, Receive: 0, Length: 9, Exceptions: 0)
Non-2xx responses:      100
Total transferred:      4339200 bytes
HTML transferred:       4244800 bytes
Requests per second:    1.96 [#/sec] (mean)
Time per request:       511.145 [ms] (mean)
Time per request:       511.145 [ms] (mean, across all concurrent requests)
Transfer rate:          82.90 [Kbytes/sec] received


Redis + MySql config - Anonymous + Cloudflare ON

Concurrency Level:      1
Time taken for tests:   15.553 seconds
Complete requests:      100
Failed requests:        0
Total transferred:      19199846 bytes
HTML transferred:       19138500 bytes
Requests per second:    6.43 [#/sec] (mean)
Time per request:       155.530 [ms] (mean)
Time per request:       155.530 [ms] (mean, across all concurrent requests)
Transfer rate:          1205.55 [Kbytes/sec] received


Link to comment
Share on other sites

  • 3 weeks later...
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Create New...