0

Please I need help, my server spec is 6 cores and 16gb ram on a Vps plan, and my sql is giving a lot of problem. somethings it spark up to 400%.

I have tried to configure it many times but it will later spark up again after a day or two. I am using mariaDB currently and running a wp site on the server and database size of 1.7GB.

below is my current my.cnf config.

# 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/vmi195666.contaboserver.net.err
#slow_query_log=1
performance_schema = ON
#default-storage-engine=MyISAM
#max_allowed_packet=268435456
max_allowed_packet      = 32M
#open_files_limit=10000
symbolic-links=0


skip-name-resolve=1
#skip-external-locking
#key_buffer_size = 2G
#table_open_cache = 4192
#join_buffer_size = 256M
#sort_buffer_size = 8M
#read_buffer_size = 8M
#read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 16M
query_cache_type = 0
#query_cache_limit = 4M
query_cache_size = 0


expire_logs_days=5  # from 0 for limited historical logs
key_cache_age_threshold=64800  # from 300 seconds discard to RD again
key_cache_division_limit=50  # for HOT and WARM usage boundary
key_cache_block_size=16384

#thread_concurrency = 8
#thread_cache_size = 100
tmp_table_size = 88M
max_heap_table_size = 88M


innodb_buffer_pool_size = 7G # (adjust value here, 50%-70% of total RAM)
innodb_log_file_size = 960M
innodb_log_files_in_group = 2

innodb_lru_scan_depth=100
log_warnings=2
max_connect_errors=100
#have_symlink=NO
innodb_purge_threads=4
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_thread_concurrency=0


innodb_buffer_pool_instances = 7
innodb_flush_log_at_trx_commit = 1 # may change to 2 or 0
innodb_flush_method = O_DIRECT

max_connections=400
#max_user_connections=30
#wait_timeout=40
#interactive_timeout=10
#long_query_time=5

Here is my MySQLTuner Report

 >>  MySQLTuner 1.7.10 - 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 10.2.17-MariaDB
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/lib/mysql/vmi195666.contaboserver.net.err(11M)
[OK] Log file /var/lib/mysql/vmi195666.contaboserver.net.err exists
[OK] Log file /var/lib/mysql/vmi195666.contaboserver.net.err is readable.
[OK] Log file /var/lib/mysql/vmi195666.contaboserver.net.err is not empty
[OK] Log file /var/lib/mysql/vmi195666.contaboserver.net.err is smaller than 32 Mb
[!!] /var/lib/mysql/vmi195666.contaboserver.net.err contains 69921 warning(s).
[!!] /var/lib/mysql/vmi195666.contaboserver.net.err contains 89 error(s).
[--] 25 start(s) detected in /var/lib/mysql/vmi195666.contaboserver.net.err
[--] 1) 2018-10-29 15:40:13 140332307847360 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2018-10-22 14:11:57 140273926736064 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2018-10-21  4:45:56 139694061492416 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2018-10-20 21:58:09 139845201819840 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2018-10-05  6:53:08 139745408678080 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2018-10-05  4:28:22 140625873856704 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2018-09-27 17:05:37 139928907647168 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2018-09-18 10:17:05 140013707499712 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 2018-09-14  9:25:29 140178830198976 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 2018-09-09 23:12:45 140288578197696 [Note] /usr/sbin/mysqld: ready for connections.
[--] 15 shutdown(s) detected in /var/lib/mysql/vmi195666.contaboserver.net.err
[--] 1) 2018-09-18 12:04:37 140170985395968 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2018-09-09 23:12:43 140009693865728 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2018-09-09 23:11:36 140024525231872 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2018-09-09 23:10:42 139645632177920 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2018-09-09 23:05:23 140597407938304 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2018-09-09 22:51:00 139671733114624 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2018-09-09 22:47:41 140515561195264 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 8) 2018-09-09 22:46:17 139697642653440 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 9) 2018-09-09 22:32:11 140358903416576 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 10) 2018-09-09 22:26:34 139953054557952 [Note] /usr/sbin/mysqld: Shutdown complete

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MyISAM tables: 1.4M (Tables: 32)
[--] Data in InnoDB tables: 1.7G (Tables: 54)
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[!!] User 'root@localhost' has no password set.
[!!] User 'oybqgroy_wp@%' does not specify hostname restrictions.
[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 9h 19m 59s (3M q [117.155 qps], 38K conn, TX: 10G, RX: 4G)
[--] Reads / Writes: 93% / 7%
[--] Binary logging is disabled
[--] Physical Memory     : 15.5G
[--] Max MySQL memory    : 8.9G
[--] Other process memory: 2.4G
[--] Total buffers: 7.4G global + 2.9M per thread (400 max threads)
[--] P_S Max memory usage: 445M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 7.9G (50.62% of installed RAM)
[OK] Maximum possible memory usage: 8.9G (57.52% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/3M)
[OK] Highest usage of available connections: 5% (23/400)
[OK] Aborted connections: 0.60%  (228/38171)
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (5 temp sorts / 242K sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 55% (99K on disk / 177K total)
[OK] Thread cache hit rate: 99% (28 created / 38K connections)
[OK] Table cache hit rate: 96% (247 open / 256 opened)
[OK] Open file limit used: 1% (104/10K)
[OK] Table locks acquired immediately: 100% (17K immediate / 17K locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 445.5M
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 6 thread(s).
[--] Using default value is good enough for your version (10.2.17-MariaDB)

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 1.5% (2M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/329.0K
[OK] Read Key buffer hit rate: 99.5% (4K cached / 21 reads)
[OK] Write Key buffer hit rate: 98.2% (899 cached / 883 writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 7.0G/1.7G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 960.0M * 2/7.0G should be equal 25%
[OK] InnoDB buffer pool instances: 7
[--] Number of InnoDB Buffer Pool Chunk : 56 for 7 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% (5317874677 hits/ 5317948865 total)
[OK] InnoDB Write log efficiency: 91.21% (940961 hits/ 1031677 total)
[OK] InnoDB log waits: 0.00% (0 waits / 90716 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[!!] Aria pagecache hit rate: 94.1% (1M cached / 98K reads)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.

-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.

-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: MIXED
[--] 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/lib/mysql/vmi195666.contaboserver.net.err file
    Control error line(s) into /var/lib/mysql/vmi195666.contaboserver.net.err file
    Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
    Restrict Host for user@% to user@SpecificDNSorIp
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
Variables to adjust:
    tmp_table_size (> 88M)
    max_heap_table_size (> 88M)
  • You cannot "tune your way out of a performance problem". Let's see one or two of the slowest queries, together with SHOW CREATE TABLE for the table(s) they use. The solution may be as simple as adding a 'composite' index. – Rick James Oct 30 '18 at 1:21
  • 1
    pastebin.com/raw/ePnB6DtU That is the tables and slow query. my most problems comes from search and tags of the site. – Godwin Alex Ogbonda Oct 30 '18 at 3:01
  • If you have 6 cores, then 400% CPU is only 66% utilization. There is not necessarily anything wrong with this. – Michael - sqlbot Oct 30 '18 at 3:20
  • 1
    @Wilson Hauck here is the pastebin link: pastebin.com/raw/7AJapw5U HERE IS MY htop command screenshot goo.gl/Sq9Pjw the SHOW ENGINE INNODB STATUS; _ displays _#1227 - Access denied; you need (at least one of) the PROCESS privilege(s) for this operation when I try to run it via phpmyadmin so I ran it via ssh and it worked fine. check the pastebin link for the results – Godwin Alex Ogbonda Oct 30 '18 at 18:32
  • 1
    @Wilson Hauck HERE is the link goo.gl/DQSu7M goo.gl/7uPLNw goo.gl/9nWZmy to all the things you requested above – Godwin Alex Ogbonda Oct 30 '18 at 19:16
2

(Preliminary review of the pastbin)

There are 13 identical indexes like this

KEY `post_status` (`post_status`,`post_type`,`post_date`),

Similarly for

 KEY `autoload` (`autoload`),

Is something automatically generating them? Keep one, DROP the others.

Prefix indexing is virtually useless:

 KEY `post_name` (`post_name`(191)),

See this for 5 workarounds. Ditto for all the other "prefix indexes" with (191).

Partial EAV optimization: See this for how to speed up postmeta tables.

Wildcards and OR: This (and similar constructs) is inefficient:

                      OR  (9ja_posts.post_excerpt LIKE '%paw%')
                      OR  (9ja_posts.post_content LIKE '%paw%'))

because:

  • The leading wildcard prevents use of an index
  • OR prevents the use of an index

Consider having a FULLTEXT index on the combination of columns.

IN ( SELECT ) may not be optimal:

NOT IN ( SELECT ... )

Better options: EXISTS ( SELECT ... ) or LEFT JOIN .. ON .. IS NULL

Pagination via OFFSET, as in

ORDER BY ... LIMIT  260, 10 

is inefficient in multiple ways. This discusses how to "remember where you left off" as a significantly faster way. (Caveat: It does not apply in all situations.)

It is rare that I encounter a Question that clearly needs help from 3 of my blogs. This Question wins the prize.

Summary

My 7 bits of advice are mostly independent of each other, and each may produce a noticeable improvement. It would be hard to predict which would give you the most bang for the buck. (Hmmm... I wonder if '7 tips' is a new record.)

Try to implement much of what I advise here, then (if you still have trouble), come back for more advice.

  • 1
    Thanks for your tips, but I am not good in database stuff, I followed your previous tips to achieve the my.cnf I posted. Please is there any other way to work this out via config? – Godwin Alex Ogbonda Oct 30 '18 at 7:11
  • 1
    thanks for ur tips, but right now i cleared all my tags and assign my wp search to only title, and the highest slow query time is "2" since monitoring the server. But I dont understand the "sleep" on the query via this screenshot goo.gl/BxbbLz the highest the sql can go for now is 242. after I edited my.cnf a bit, I dont know if there is any other way to improve it, because the sql time on the top command growing every time. HERE IS MY TOP COMMAND screenshot goo.gl/gz9mSX HERE IS MY htop command screenshot goo.gl/Sq9Pjw – Godwin Alex Ogbonda Oct 30 '18 at 18:16
  • 1
    @GodwinAlexOgbonda - Sleep (in PROCESSLIST) means that a user is still connected, but not doing any query at this instant (actually in the last N seconds, see "Time"). It may mean that you have "connection pooling" and the 'pooled connection' is idle. In any case, "Sleep" is "not a problem". – Rick James Oct 30 '18 at 18:22
  • 1
    the command PROCESSLIST is not working so I used SHOW FULL PROCESSLIST and got the output in the below screenshot goo.gl/L3WRFg goo.gl/r7vBNP goo.gl/qtmSCD – Godwin Alex Ogbonda Oct 30 '18 at 18:48
  • 1
    @GodwinAlexOgbonda - That output shows essentially nothing going on. Aside from the Daemons, here are 4 connections: 3 'Sleeping', and 1 is the SHOW that you ran to get the output. – Rick James Oct 30 '18 at 22:20
0

Suggestions for your OS to increase Open Files availability

ulimit -n 48000 # from current 1024 reported by ulimit -a report

To make this change persistent across OS shutdown/restart review this url

https://glassonionblog.wordpress.com/2013/01/27/increase-ulimit-and-file-descriptors-limit/

Your details may be slightly different due to OS version.

Rate Per Second=RPS - Suggestions for your my.cnf [mysqld] section

innodb_io_capacity=15000  # from 200 to use more of SSD IOPS available
read_rnd_buffer_size=192K  # from 256K to reduce handler_read_rnd_next RPS
innodb_flushing_avg_loops=5  # from 30 to reduce loop delay & history list length
innodb_adaptive_max_sleep_delay=10000  # from 150000 for 1 second delay when busy vs 15 seconds
innodb_buffer_pool_size=3G  # from 7G to support your 1.4G of data with room to grow
open_files_limit=30000  # from 10000 to reduce opened_files RPS
innodb_flush_neighbors=0  # from 1 to conserve CPU cycles since you have SSD
innodb_log_buffer_size=96M  # from 16M to support ~ 30 min in buffer

For additional assistance, please view my profile, Network profile for contact info including my Skype ID and get in touch by Skype, please.

The BEST to you and your team.

  • 1
    After following the above suggestions, it consumes more cpu cores than my previous settings – Godwin Alex Ogbonda Oct 31 '18 at 18:14
  • 1
    is there any difference between innodb_log_file_size and innodb_log_buffer_size ? – Godwin Alex Ogbonda Oct 31 '18 at 18:44
  • @GodwinAlexOgbonda You have 6 cores from what I can tell. What is the % busy you are seeing now? The buffer_size is in RAM to store log before writing to innodb_log_file to reduce number of call to OS for the log. You have enough RAM to support the larger innodb_log_buffer_size. Let's SKYPE, your tomorrow morning, please. – Wilson Hauck Oct 31 '18 at 20:31
  • alright then, it was consuming 4-5 core and the sql was highest was 300+ but sometimes it drops. – Godwin Alex Ogbonda Oct 31 '18 at 22:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.