Alıntı
--------------------------------------------------------------------
Sunucu özellikleri
--------------------------------------------------------------------
Centos 7
8 vcpu
32 Gb Ram
processor : 6
vendor_id : GenuineIntel
cpu family : 6
model : 85
model name : Intel Xeon Processor (Skylake, IBRS)
240 GB Nvme2ssd
--------------------------------------------------------------------
--------------------------------------------------------------------
top çıktısı
--------------------------------------------------------------------
2502 mysql 20 0 2434488 455188 9612 S 247.4 1.4 100:32.16 mysqld
8281 1.site 20 0 335972 31968 3712 R 54.6 0.1 0:08.24 php-fpm
8282 1.site 20 0 335424 31712 3716 S 52.0 0.1 0:06.21 php-fpm
8274 2.site 20 0 342592 37820 3692 R 45.7 0.1 0:09.61 php-fpm
8325 1.site 20 0 357444 52408 3712 R 45.4 0.2 0:03.54 php-fpm
8181 2.site 20 0 357172 51540 3692 S 44.7 0.2 0:15.69 php-fpm
8283 1.site 20 0 354112 50388 3712 R 40.1 0.2 0:05.88 php-fpm
8326 1.site 20 0 335440 31712 3716 R 38.2 0.1 0:02.08 php-fpm
8159 2.site 20 0 358196 53148 3688 R 37.8 0.2 0:16.47 php-fpm
8271 2.site 20 0 357180 51796 3688 S 33.2 0.2 0:10.99 php-fpm
8272 2.site 20 0 357440 51756 3688 R 30.9 0.2 0:10.99 php-fpm
8323 3.site 20 0 353340 49664 3760 S 28.6 0.2 0:02.51 php-fpm
8269 3.site 20 0 433568 57196 4920 S 21.7 0.2 0:07.47 php-fpm
8275 3.site 20 0 433620 57300 4964 S 18.4 0.2 0:05.41 php-fpm
8277 3.site 20 0 353628 49920 3760 R 18.4 0.2 0:04.94 php-fpm
8327 3.site 20 0 343040 39320 3764 R 17.8 0.1 0:00.54 php-fpm
8273 5.site 20 0 336136 32276 3568 S 10.2 0.1 0:01.26 php-fpm
8280 4.site 20 0 316852 11408 2884 S 3.0 0.0 0:00.27 php-fpm
8276 4.site 20 0 316884 11404 2880 R 1.6 0.0 0:00.39 php-fpm
6429 4.site 20 0 209260 10032 3528 S 1.0 0.0 0:00.27 httpd
6280 nobody 20 0 209784 10656 3672 S 0.3 0.0 0:00.40 httpd
6384 2.site 20 0 209648 10472 3552 S 0.3 0.0 0:00.41 httpd
6388 nobody 20 0 209276 10084 3532 S 0.3 0.0 0:00.26 httpd
7882 2.site 20 0 208580 9060 3016 S 0.3 0.0 0:00.09 httpd
7964 nobody 20 0 208452 9128 3028 S 0.3 0.0 0:00.09 httpd
8324 root 20 0 160448 2440 1544 R 0.3 0.0 0:00.09 top
--------------------------------------------------------------------
my.cnf içeriği
--------------------------------------------------------------------
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/...-defaults.html
[mysqld]
performance-schema=ON
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
performance-schema=ON
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
performance-schema=ON
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
default-storage-engine=MyISAM
innodb_file_per_table=1
max_allowed_packet=268435456
open_files_limit=10000
sort_buffer_size=2M # from 1M to reduce sort_merge_passes
read_buffer_size=128K # from 1M to reduce handler_read_next RPS
read_rnd_buffer_size=256K # from ? may reduce handler_read_rnd_next RPS
join_buffer_size=128K # from 1M for join row pointers
--------------------------------------------------------------------
mysqltuner çıktısı
--------------------------------------------------------------------
[root@server ~]# ./mysqltuner.pl
>> MySQLTuner 1.7.14 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Modified by George Liu (eva2000) at https://centminmod.com/
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.7.28
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mysqld.log(87K)
[OK] Log file /var/log/mysqld.log exists
[OK] Log file /var/log/mysqld.log is readable.
[OK] Log file /var/log/mysqld.log is not empty
[OK] Log file /var/log/mysqld.log is smaller than 32 Mb
[!!] /var/log/mysqld.log contains 15 warning(s).
[!!] /var/log/mysqld.log contains 38 error(s).
[--] 7 start(s) detected in /var/log/mysqld.log
[--] 1) 2020-01-05T21:55:05.155444Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2020-01-05T21:46:58.996981Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2020-01-05T21:45:53.538552Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2020-01-05T20:23:29.417043Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2020-01-05T19:59:53.353312Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2020-01-05T19:01:14.118707Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2020-01-05T19:00:14.627568Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6 shutdown(s) detected in /var/log/mysqld.log
[--] 1) 2020-01-05T21:55:02.678297Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2020-01-05T21:46:57.484989Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2020-01-05T21:45:51.933156Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2020-01-05T20:22:49.158737Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2020-01-05T19:59:51.750890Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2020-01-05T19:01:12.587680Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 1.9G (Tables: 260)
[--] Data in InnoDB tables: 16.0K (Tables: 1)
[OK] Total fragmented tables: 0
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- Security Recommendations ------------------------------------------------------------------
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 17m 23s (599K q [574.691 qps], 9K conn, TX: 1G, RX: 87M)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is disabled
[--] Physical Memory : 30.5G
[--] Max MySQL memory : 584.3M
[--] Other process memory: 0B
[--] Total buffers: 169.0M global + 2.8M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 221.3M (0.71% of installed RAM)
[OK] Maximum possible memory usage: 584.3M (1.87% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/599K)
[OK] Highest usage of available connections: 12% (19/151)
[OK] Aborted connections: 0.01% (1/9064)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 549K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 299K sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 79% (29K on disk / 37K total)
[OK] Thread cache hit rate: 98% (112 created / 9K connections)
[OK] Table cache hit rate: 43% (1K open / 4K opened)
[OK] Open file limit used: 17% (1K/10K)
[OK] Table locks acquired immediately: 99% (598K immediate / 598K locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[OK] Key buffer used: 100.0% (8M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/401.4M
[OK] Read Key buffer hit rate: 98.2% (110M cached / 1M reads)
[!!] Write Key buffer hit rate: 93.5% (707 cached / 661 writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 128.0M/16.0K
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.97% (1300976 hits/ 1301355 total)
[!!] InnoDB Write Log efficiency: 0% (1 hits/ 0 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /var/log/mysqld.log file
Control error line(s) into /var/log/mysqld.log file
Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
MySQL was started within the last 24 hours - recommendations may be inaccurate
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
----------------------------------------------------------------------------------------------------------------------------------------
mysql show procelesst çıktısı
----------------------------------------------------------------------------------------------------------------------------------------
mysql> show processlist;
+-------+-----------------+-----------+-------------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+-----------------+-----------+-------------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 11715 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 11905 | 1.siteee_p | localhost | 1.siteee_p | Query | 0 | Sending data | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AN |
| 11909 | 1.siteee_p | localhost | 1.siteee_p | Query | 0 | Sending data | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AN |
| 11910 | 1.siteee_p | localhost | 1.siteee_p | Query | 0 | Sending data | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AN |
| 11912 | 1.siteee_p | localhost | 1.siteee_p | Query | 0 | Sending data | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AN |
| 11917 | 2.siteeeeeee | localhost | 2.siteeeeeee | Query | 0 | Sending data | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AN |
| 11918 | 3.siteeeeeeeeee | localhost | 3.siteeeeeeeeee | Sleep | 0 | | NULL |
| 11919 | 1.siteee_p | localhost | 1.siteee_p | Sleep | 0 | | NULL |
| 11920 | 3.siteeeeeeeeee | localhost | 3.siteeeeeeeeee | Sleep | 0 | | NULL |
+-------+-----------------+-----------+-------------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
9 rows in set (0.01 sec)
----------------------------------------------------------------------------------------------------------------------------------------
whm process çıktısı
----------------------------------------------------------------------------------------------------------------------------------------
Pid Owner Priority CPU % Memory % Command
15469 (Trace) (Kill) mysql 0 218.41 1.40 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
21426 (Trace) (Kill) 1.site 0 58.78 0.17 php-fpm: pool 1.site_club
21427 (Trace) (Kill) 1.site 0 56.38 0.10 php-fpm: pool 1.site_club
21392 (Trace) (Kill) 1.site 0 55.05 0.16 php-fpm: pool 1.site_club
21395 (Trace) (Kill) 1.site 0 53.53 0.10 php-fpm: pool 1.site_club
21393 (Trace) (Kill) 1.site 0 47.41 0.10 php-fpm: pool 1.site_club
21364 (Trace) (Kill) 2.site 0 36.96 0.17 php-fpm: pool 2.site_com
21416 (Trace) (Kill) 2.site 0 35.07 0.16 php-fpm: pool 2.site_com
21389 (Trace) (Kill) 2.site 0 34.64 0.16 php-fpm: pool 2.site_com
21374 (Trace) (Kill) 2.site 0 34.59 0.09 php-fpm: pool 2.site_com
21387 (Trace) (Kill) 2.site 0 33.57 0.16 php-fpm: pool 2.site_com
21430 (Trace) (Kill) pinity 0 23.16 0.12 php-fpm: pool pinity_club
21375 (Trace) (Kill) 3.site 0 21.13 0.16 php-fpm: pool 3.site_com
21429 (Trace) (Kill) 3.site 0 20.58 0.08 php-fpm: pool 3.site_com
21391 (Trace) (Kill) 3.site 0 19.11 0.16 php-fpm: pool 3.site_com
21422 (Trace) (Kill) 3.site 0 15.62 0.15 php-fpm: pool 3.site_com
Sunucu özellikleri
--------------------------------------------------------------------
Centos 7
8 vcpu
32 Gb Ram
processor : 6
vendor_id : GenuineIntel
cpu family : 6
model : 85
model name : Intel Xeon Processor (Skylake, IBRS)
240 GB Nvme2ssd
--------------------------------------------------------------------
--------------------------------------------------------------------
top çıktısı
--------------------------------------------------------------------
2502 mysql 20 0 2434488 455188 9612 S 247.4 1.4 100:32.16 mysqld
8281 1.site 20 0 335972 31968 3712 R 54.6 0.1 0:08.24 php-fpm
8282 1.site 20 0 335424 31712 3716 S 52.0 0.1 0:06.21 php-fpm
8274 2.site 20 0 342592 37820 3692 R 45.7 0.1 0:09.61 php-fpm
8325 1.site 20 0 357444 52408 3712 R 45.4 0.2 0:03.54 php-fpm
8181 2.site 20 0 357172 51540 3692 S 44.7 0.2 0:15.69 php-fpm
8283 1.site 20 0 354112 50388 3712 R 40.1 0.2 0:05.88 php-fpm
8326 1.site 20 0 335440 31712 3716 R 38.2 0.1 0:02.08 php-fpm
8159 2.site 20 0 358196 53148 3688 R 37.8 0.2 0:16.47 php-fpm
8271 2.site 20 0 357180 51796 3688 S 33.2 0.2 0:10.99 php-fpm
8272 2.site 20 0 357440 51756 3688 R 30.9 0.2 0:10.99 php-fpm
8323 3.site 20 0 353340 49664 3760 S 28.6 0.2 0:02.51 php-fpm
8269 3.site 20 0 433568 57196 4920 S 21.7 0.2 0:07.47 php-fpm
8275 3.site 20 0 433620 57300 4964 S 18.4 0.2 0:05.41 php-fpm
8277 3.site 20 0 353628 49920 3760 R 18.4 0.2 0:04.94 php-fpm
8327 3.site 20 0 343040 39320 3764 R 17.8 0.1 0:00.54 php-fpm
8273 5.site 20 0 336136 32276 3568 S 10.2 0.1 0:01.26 php-fpm
8280 4.site 20 0 316852 11408 2884 S 3.0 0.0 0:00.27 php-fpm
8276 4.site 20 0 316884 11404 2880 R 1.6 0.0 0:00.39 php-fpm
6429 4.site 20 0 209260 10032 3528 S 1.0 0.0 0:00.27 httpd
6280 nobody 20 0 209784 10656 3672 S 0.3 0.0 0:00.40 httpd
6384 2.site 20 0 209648 10472 3552 S 0.3 0.0 0:00.41 httpd
6388 nobody 20 0 209276 10084 3532 S 0.3 0.0 0:00.26 httpd
7882 2.site 20 0 208580 9060 3016 S 0.3 0.0 0:00.09 httpd
7964 nobody 20 0 208452 9128 3028 S 0.3 0.0 0:00.09 httpd
8324 root 20 0 160448 2440 1544 R 0.3 0.0 0:00.09 top
--------------------------------------------------------------------
my.cnf içeriği
--------------------------------------------------------------------
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/...-defaults.html
[mysqld]
performance-schema=ON
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
performance-schema=ON
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
performance-schema=ON
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
default-storage-engine=MyISAM
innodb_file_per_table=1
max_allowed_packet=268435456
open_files_limit=10000
sort_buffer_size=2M # from 1M to reduce sort_merge_passes
read_buffer_size=128K # from 1M to reduce handler_read_next RPS
read_rnd_buffer_size=256K # from ? may reduce handler_read_rnd_next RPS
join_buffer_size=128K # from 1M for join row pointers
--------------------------------------------------------------------
mysqltuner çıktısı
--------------------------------------------------------------------
[root@server ~]# ./mysqltuner.pl
>> MySQLTuner 1.7.14 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Modified by George Liu (eva2000) at https://centminmod.com/
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.7.28
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mysqld.log(87K)
[OK] Log file /var/log/mysqld.log exists
[OK] Log file /var/log/mysqld.log is readable.
[OK] Log file /var/log/mysqld.log is not empty
[OK] Log file /var/log/mysqld.log is smaller than 32 Mb
[!!] /var/log/mysqld.log contains 15 warning(s).
[!!] /var/log/mysqld.log contains 38 error(s).
[--] 7 start(s) detected in /var/log/mysqld.log
[--] 1) 2020-01-05T21:55:05.155444Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2020-01-05T21:46:58.996981Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2020-01-05T21:45:53.538552Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2020-01-05T20:23:29.417043Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2020-01-05T19:59:53.353312Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2020-01-05T19:01:14.118707Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2020-01-05T19:00:14.627568Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6 shutdown(s) detected in /var/log/mysqld.log
[--] 1) 2020-01-05T21:55:02.678297Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2020-01-05T21:46:57.484989Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2020-01-05T21:45:51.933156Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2020-01-05T20:22:49.158737Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2020-01-05T19:59:51.750890Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2020-01-05T19:01:12.587680Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 1.9G (Tables: 260)
[--] Data in InnoDB tables: 16.0K (Tables: 1)
[OK] Total fragmented tables: 0
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- Security Recommendations ------------------------------------------------------------------
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 17m 23s (599K q [574.691 qps], 9K conn, TX: 1G, RX: 87M)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is disabled
[--] Physical Memory : 30.5G
[--] Max MySQL memory : 584.3M
[--] Other process memory: 0B
[--] Total buffers: 169.0M global + 2.8M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 221.3M (0.71% of installed RAM)
[OK] Maximum possible memory usage: 584.3M (1.87% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/599K)
[OK] Highest usage of available connections: 12% (19/151)
[OK] Aborted connections: 0.01% (1/9064)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 549K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 299K sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 79% (29K on disk / 37K total)
[OK] Thread cache hit rate: 98% (112 created / 9K connections)
[OK] Table cache hit rate: 43% (1K open / 4K opened)
[OK] Open file limit used: 17% (1K/10K)
[OK] Table locks acquired immediately: 99% (598K immediate / 598K locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[OK] Key buffer used: 100.0% (8M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/401.4M
[OK] Read Key buffer hit rate: 98.2% (110M cached / 1M reads)
[!!] Write Key buffer hit rate: 93.5% (707 cached / 661 writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 128.0M/16.0K
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.97% (1300976 hits/ 1301355 total)
[!!] InnoDB Write Log efficiency: 0% (1 hits/ 0 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /var/log/mysqld.log file
Control error line(s) into /var/log/mysqld.log file
Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
MySQL was started within the last 24 hours - recommendations may be inaccurate
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
----------------------------------------------------------------------------------------------------------------------------------------
mysql show procelesst çıktısı
----------------------------------------------------------------------------------------------------------------------------------------
mysql> show processlist;
+-------+-----------------+-----------+-------------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+-----------------+-----------+-------------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 11715 | root | localhost | NULL | Query | 0 | starting | show processlist |
| 11905 | 1.siteee_p | localhost | 1.siteee_p | Query | 0 | Sending data | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AN |
| 11909 | 1.siteee_p | localhost | 1.siteee_p | Query | 0 | Sending data | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AN |
| 11910 | 1.siteee_p | localhost | 1.siteee_p | Query | 0 | Sending data | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AN |
| 11912 | 1.siteee_p | localhost | 1.siteee_p | Query | 0 | Sending data | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AN |
| 11917 | 2.siteeeeeee | localhost | 2.siteeeeeee | Query | 0 | Sending data | SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AN |
| 11918 | 3.siteeeeeeeeee | localhost | 3.siteeeeeeeeee | Sleep | 0 | | NULL |
| 11919 | 1.siteee_p | localhost | 1.siteee_p | Sleep | 0 | | NULL |
| 11920 | 3.siteeeeeeeeee | localhost | 3.siteeeeeeeeee | Sleep | 0 | | NULL |
+-------+-----------------+-----------+-------------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
9 rows in set (0.01 sec)
----------------------------------------------------------------------------------------------------------------------------------------
whm process çıktısı
----------------------------------------------------------------------------------------------------------------------------------------
Pid Owner Priority CPU % Memory % Command
15469 (Trace) (Kill) mysql 0 218.41 1.40 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
21426 (Trace) (Kill) 1.site 0 58.78 0.17 php-fpm: pool 1.site_club
21427 (Trace) (Kill) 1.site 0 56.38 0.10 php-fpm: pool 1.site_club
21392 (Trace) (Kill) 1.site 0 55.05 0.16 php-fpm: pool 1.site_club
21395 (Trace) (Kill) 1.site 0 53.53 0.10 php-fpm: pool 1.site_club
21393 (Trace) (Kill) 1.site 0 47.41 0.10 php-fpm: pool 1.site_club
21364 (Trace) (Kill) 2.site 0 36.96 0.17 php-fpm: pool 2.site_com
21416 (Trace) (Kill) 2.site 0 35.07 0.16 php-fpm: pool 2.site_com
21389 (Trace) (Kill) 2.site 0 34.64 0.16 php-fpm: pool 2.site_com
21374 (Trace) (Kill) 2.site 0 34.59 0.09 php-fpm: pool 2.site_com
21387 (Trace) (Kill) 2.site 0 33.57 0.16 php-fpm: pool 2.site_com
21430 (Trace) (Kill) pinity 0 23.16 0.12 php-fpm: pool pinity_club
21375 (Trace) (Kill) 3.site 0 21.13 0.16 php-fpm: pool 3.site_com
21429 (Trace) (Kill) 3.site 0 20.58 0.08 php-fpm: pool 3.site_com
21391 (Trace) (Kill) 3.site 0 19.11 0.16 php-fpm: pool 3.site_com
21422 (Trace) (Kill) 3.site 0 15.62 0.15 php-fpm: pool 3.site_com
