1

I'm using a dedicated server with bellow specification for a single website with joomla CMS that is News/blog based website: Intel(R) Xeon(R) CPU E5-2670 v2 @ 2.50GHz 10 of Cores, 20 of Threads Ram: 60GB SSD:250 (OS and i think DB) HDD: 2TB

PHP 7.3

MySQL 5.7

Website load time is very slow and i optimized page but the server response time is slow so i just installed Mysql-Tunner to check DB status. I just check the report but i don't know if there is something good or worst . Need your valuable and in-detail guide.

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

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mysqld.log(2M)
[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 2231 warning(s).
[!!] /var/log/mysqld.log contains 5891 error(s).

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA 
[--] Data in MyISAM tables: 431.8K (Tables: 60)
[--] Data in InnoDB tables: 1.4G (Tables: 363)
[--] Data in MEMORY tables: 0B (Tables: 8)
[OK] Total fragmented tables: 0

-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION

-------- Performance Metrics     -----------------------------------------------------------------------
[--] Up for: 12d 2h 14m 35s (153M q [146.876 qps], 4M conn, TX: 1441G, RX: 39G)
[--] Reads / Writes: 92% / 8%
[--] Binary logging is disabled
[--] Physical Memory     : 55.5G
[--] Max MySQL memory    : 8.2G
[--] Other process memory: 0B
[--] Total buffers: 8.1G global + 1.1M per thread (80 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 8.1G (14.59% of installed RAM)
[OK] Maximum possible memory usage: 8.2G (14.73% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/153M)
[OK] Highest usage of available connections: 12% (10/80)
[OK] Aborted connections: 0.08%  (3729/4397307)
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 119M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (23K temp sorts / 25M sorts)
[!!] Joins performed without indexes: 52200
[!!] Temporary tables created on disk: 92% (14M on disk / 15M total)
[OK] Thread cache hit rate: 99% (10 created / 4M connections)
[!!] Table cache hit rate: 1% (1K open / 101K opened)
[OK] Open file limit used: 0% (11/10K)
[OK] Table locks acquired immediately: 100% (107K immediate / 107K locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (1M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/221.0K
[OK] Read Key buffer hit rate: 99.2% (61K cached / 507 reads)
[!!] Write Key buffer hit rate: 65.7% (105 cached / 69 writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 8.0G/1.4G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (3.125 %): 128.0M * 2/8.0G should be equal to 25%
[OK] InnoDB buffer pool instances: 8
[--] Number of InnoDB Buffer Pool Chunk : 64 for 8 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: 100.00% (426722546739 hits/ 426722621278 total)
[!!] InnoDB Write Log efficiency: 80.95% (42826081 hits/ 52905097 total)
[OK] InnoDB log waits: 0.00% (0 waits / 10079016 writes) 

[--] AriaDB is disabled.
[--] TokuDB is disabled.
[--] XtraDB is disabled.
[--] 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 ---------------------------------------------------------------------------
<b>General recommendations:</b>
Control warning line(s) into /var/log/mysqld.log file
Control error line(s) into /var/log/mysqld.log file
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: 
Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/
This is MyISAM only table_cache scalability problem, InnoDB not affected.
See more details here: https://bugs.mysql.com/bug.php?id=49177
This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
Beware that open_files_limit (10000) variable 
should be greater than table_open_cache (2000)
Performance schema should be activated for better diagnostics
Before changing innodb_log_file_size and/or innodb_log_files_in_group     read this:  
<b>Variables to adjust:</b>
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
join_buffer_size (> 256.0K, or always use indexes with JOINs)
tmp_table_size (> 64M)
max_heap_table_size (> 64M)
table_open_cache (> 2000)
performance_schema = ON enable PFS
innodb_log_file_size should be (=1G) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

and my.cnf :

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid  
open_files_limit=10000
default-storage-engine=InnoDB
innodb_file_per_table=1                                                        
max_allowed_packet=268435456
wait_timeout = 18
interactive_timeout=18
thread_cache_size=10
skip-name-resolve=1
tmp_table_size=64M
max_heap_table_size=64M
innodb_buffer_pool_size=8G
innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_log_file_size           = 128M
innodb_flush_log_at_trx_commit = 1
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Muhammad Abbas
  • 11
  • 1
  • 5
  • Be sure to have `long_query_time = 1` when using the slowlog. Yes, the slowlog will tell you what query to work on optimizing. Tuner hints that you have some queries that are inefficient, but that is as far as it goes. You have far more RAM than you currently need. – Rick James Aug 20 '19 at 22:28
  • 1
    Query_time:4.012262 Lock_time:0.000219 Rows_sent:28 Rows_examined:121584 SELECT i.*, c.name as categoryname,c.id as categoryid,c.alias as categoryalias, c.params as categoryparams FROM y_k2_items as i RIGHT JOIN y_k2_categories AS c ON c.id =i.catid WHERE i.published =1 AND i.access IN(1,1) AND i.trash =0 AND c.published =1 AND c.access IN(1,1) AND c.trash =0 AND( i.publish_up = '0000-00-00 00:00:00' OR i.publish_up <= '2019-08-21 09:07:45' ) AND ( i.publish_down ='0000-00-00 00:00:00' OR i.publish_down >='2019-08-21 09:07:45' ) AND c.id IN (100,1,3,So on) ORDER BY i.id DESC LIMIT 0, 28; – Muhammad Abbas Aug 21 '19 at 09:20
  • 1
    The above query repeated in slow query log file and Query_time is vary from 2 second to 8 second ... how can i optimize or solve this issue – Muhammad Abbas Aug 21 '19 at 09:22
  • Please post TEXT results of EXPLAIN SELECT SQL_NO_CACHE (your query from above) and we will be able to identify MISSING INDEXES and likely other details. – Wilson Hauck Aug 21 '19 at 15:18
  • Additional information request. Post on pastebin.com and share the links. Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; F) SHOW ENGINE INNODB STATUS; AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, df -h for a linux/unix free space list by device, for server workload tuning analysis. – Wilson Hauck Aug 22 '19 at 08:53
  • 1
    @WilsonHauck kindly check https://pastebin.com/vsw3UyE1 – Muhammad Abbas Aug 23 '19 at 08:48
  • If you POST your information to pastebin as an 'unlisted paste', I should be able to open it with your new link. Please put 14 day expiration on the post. – Wilson Hauck Aug 23 '19 at 11:19
  • Only you can read your Private Post. – Wilson Hauck Aug 23 '19 at 11:36
  • 1
    kindly check it now... – Muhammad Abbas Aug 23 '19 at 14:10
  • Thank you. Will try to have suggestions for you within 24 hours, after analysis. Skype available to you? – Wilson Hauck Aug 23 '19 at 14:19
  • Second request - Please post TEXT results of EXPLAIN SELECT SQL_NO_CACHE (your query from above) and we will be able to identify MISSING INDEXES and likely other details. – Wilson Hauck Aug 23 '19 at 23:11
  • Muhammad, Please also post TEXT results of A) SHOW CREATE TABLE y_k2_items; B) SHOW CREATE TABLE y_k2_categories; – Wilson Hauck Aug 24 '19 at 09:38
  • Muhammad, Thanks for your time on Skype today. Looking forward to your EXPLAIN results and two SHOW CREATE TABLE results. With this information, we should be able to identify missing indexes needed to enable your query to perform without creating TEMP DISK Files. – Wilson Hauck Aug 24 '19 at 17:02
  • 1
    pastebin.com/vsw3UyE1 – Muhammad Abbas Aug 26 '19 at 08:38

1 Answers1

0

Suggestions to consider for your my.cnf [mysqld] section

tmp_table_size=256M  # from 64M to expand capacity
max_heap_table_size=256M  # to expand and reduce created_tmp_disk_tables
innodb_flushing_avg_loops=5  # from 30 to reduce innodb_buffer_pool_pages_dirty
innodb_io_capacity=1900  # from 200 to enable additional SSD IOPS
innodb_buffer_pool_size=16G  # from 8G to expand capacity
innodb_change_buffer_max_size=50  # from 25 percent to accommodate adding ROWS frequently

Disclaimer: I am the content author of website mentioned in my profile, Network profile that includes downloadable FREE Utility Scripts, additional suggestion options, contact info.

Wilson Hauck
  • 2,094
  • 1
  • 11
  • 19