Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


Plesk mysql cpu usage and mysql memory usage too high
New on LowEndTalk? Please Register and read our Community Rules.

All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.

Plesk mysql cpu usage and mysql memory usage too high

pcszervizpcszerviz Member
edited September 2017 in Help

Hi everybody!

Are there anyone, that can help me about setup mysql at plesk. I have this root server, and there are 1 webpage, that has about 100-200 simultaniously unique visitor, i see at google analytics, but always get message mysql cpu usage is too high...

This is my.cnf config:

`[mysqld]
sql_mode=ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
bind-address = ::ffff:127.0.0.1
local-infile=0

query_cache_size=64M
query_cache_limit=64k

sort_buffer_size=2048M

read_rnd_buffer_size=256M

join_buffer_size=128k
tmp_table_size=256M
max_heap_table_size=512M
open_files_limit=2000

max_connections = 200
connect_timeout = 5
wait_timeout = 600
max_allowed_packet = 16M
thread_cache_size = 128
sort_buffer_size = 4M
bulk_insert_buffer_size = 16M
tmp_table_size = 256M
max_heap_table_size = 256M

key_buffer_size = 128M
table_open_cache = 16384
myisam_sort_buffer_size = 512M
concurrent_insert = 2
read_buffer_size = 2M
read_rnd_buffer_size = 1M

* InnoDB

#

InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.

Read the manual for more InnoDB related options. There are many!

default_storage_engine = InnoDB

you can't just change log file size, requires special procedure

innodb_log_file_size = 1G
innodb_buffer_pool_size = 8G
innodb_log_buffer_size = 512M
innodb_file_per_table = 1
innodb_open_files = 400
innodb_io_capacity = 400
innodb_flush_method = O_DIRECT

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[isamchk]
key_buffer = 16M
`
Mysqltuner: perl mysqltuner.pl

-------- CVE Security Recommendations --------------------------------------------------------------

[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 31m 52s (697K q [364.554 qps], 4K conn, TX: 4G, RX: 108M)
[--] Reads / Writes: 92% / 8%
[--] Binary logging is disabled
[--] Physical Memory : 17.6G
[--] Max MySQL memory : 10.4G
[--] Other process memory: 951.4M
[--] Total buffers: 8.9G global + 7.3M per thread (200 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 9.0G (50.91% of installed RAM)
[OK] Maximum possible memory usage: 10.4G (58.76% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/697K)
[OK] Highest usage of available connections: 3% (6/200)
[OK] Aborted connections: 0.02% (1/4419)
[!!] 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 / 637K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (78 temp sorts / 71K sorts)
[!!] Joins performed without indexes: 454
[OK] Temporary tables created on disk: 11% (536 on disk / 4K total)
[OK] Thread cache hit rate: 99% (6 created / 4K connections)
[!!] Table cache hit rate: 0% (400 open / 146K opened)
[OK] Open file limit used: 0% (0/1K)
[OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)

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

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

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (24M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/1.6M
[!!] Read Key buffer hit rate: 75.4% (452 cached / 111 reads)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 8.0G/4.0G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 1.0G * 2/8.0G should be equal 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: 99.99% (384675311 hits/ 384728132 total)
[!!] InnoDB Write Log efficiency: 45.83% (38314 hits/ 83608 total)
[OK] InnoDB log waits: 0.00% (0 waits / 45294 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- 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.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /var/log/mysql/error.log file
Control error line(s) into /var/log/mysql/error.log file
Restrict Host for user@% to user@SpecificDNSorIp
MySQL started within last 24 hours - recommendations may be inaccurate
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Adjust your join queries to always utilize indexes
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Beware that open_files_limit (1024) variable
should be greater than table_open_cache (407)
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 64K, or use smaller result sets)
join_buffer_size (> 128.0K, or always use indexes with joins)
table_open_cache (> 407)

My mysql cpu usage is always high, more than 20%...
Are there anybody, that can be help for best settings?
Thanks

Comments

  • qtwrkqtwrk Member
    edited September 2017

    Maybe post in plesk forum will get you better help, I also run plesk and I always got satisfactory answer in plesk forum

  • ok, and you haven't got high usage?

  • niknik Member, Host Rep

    Why do you even use Plesk for only one website?

  • Not one website, just there are one largest site that has about 300.000 unique visitor/month.

  • what cpu is that runing on and why do you consider 20% much?
    how does 'top' overall look like?
    where do you get that warning of high mysql cpu usage from?

    would you please consider using < pre > tags? ;-)

  • pcszervizpcszerviz Member
    edited September 2017

    CPU= Intel® Xeon® E5-2680V4 - 6 core

    `top - 11:40:55 up 4 days, 22:53, 1 user, load average: 0.59, 0.69, 0.67
    Tasks: 210 total, 1 running, 208 sleeping, 0 stopped, 1 zombie
    %Cpu(s): 4.1 us, 0.8 sy, 0.0 ni, 94.0 id, 0.8 wa, 0.0 hi, 0.0 si, 0.3 st
    KiB Mem : 18496528 total, 802492 free, 3468336 used, 14225700 buff/cache
    KiB Swap: 18870268 total, 18860728 free, 9540 used. 14262404 avail Mem

    PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
    13568 mysql 20 0 11.862g 2.602g 18876 S 12.0 14.8 80:28.81 mysqld
    29426 civilhe+ 20 0 481216 80672 65392 S 11.0 0.4 0:03.13 php-cgi
    29425 civilhe+ 20 0 485312 84564 65188 S 5.3 0.5 0:01.36 php-cgi
    598 root 20 0 0 0 0 S 0.7 0.0 10:37.88 jbd2/dm-0-8
    1642 root 10 -10 5724 3520 2428 S 0.3 0.0 0:56.06 iscsid
    4850 www-data 20 0 2224436 45496 11092 S 0.3 0.2 1:18.78 /usr/sbin/+
    22805 www-data 20 0 2222024 42264 11092 S 0.3 0.2 0:56.90 /usr/sbin/+
    28649 nginx 20 0 51584 10412 5124 S 0.3 0.1 11:33.32 nginx
    29272 root 20 0 0 0 0 S 0.3 0.0 0:00.01 kworker/u1+
    29434 root 20 0 104176 7644 6492 S 0.3 0.0 0:00.03 sshd
    29436 root 20 0 41800 3736 3084 R 0.3 0.0 0:00.01 top
    1 root 20 0 186788 6984 3564 S 0.0 0.0 1:40.50 systemd
    2 root 20 0 0 0 0 S 0.0 0.0 0:00.07 kthreadd
    3 root 20 0 0 0 0 S 0.0 0.0 0:14.88 ksoftirqd/0
    5 root 0 -20 0 0 0 S 0.0 0.0 0:00.00 kworker/0:+
    7 root 20 0 0 0 0 S 0.0 0.0 11:19.64 rcu_sched
    8 root 20 0 0 0 0 S 0.0 0.0 0:00.00 rcu_bh
    `

  • 20% cpu usage with 200 simultaneous requests seems reasonable

  • pcszervizpcszerviz Member
    edited September 2017

    There are now 241 online visitor and this is mysql cpu usage= pic

  • 12% cpu usage of 1 out of 6 cores, 94% of your CPU is sitting idle.

    I really don't see the problem here?

    Thanked by 1Falzo
  • I dont know why look plesk that Mysql copu usage 27.2%, and it will be best for under 20%...

  • Maybe your website is mysql-heavy or you did not optimize your queries/database.

  • This website is a drupal page.
    I have use mysqltuner, but i always get that:

    Variables to adjust:

    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 64K, or use smaller result sets)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    table_open_cache (> 407)

  • @pcszerviz said:
    ok, and you haven't got high usage?

    No, well, I don't have that much visitors anyway.

    By the way , i am on Mariadb 10.1

    Maybe consider implement something like Memcached or Redis to reduce mysql usage

  • Well Drupal with that many queries can be considered as avarage resource usage..not high,but you can do few things to optimize it even better.I worked on Drupal sites many times and few years one of my websites was Drupal 7.xx and I can tell you that Drupal(well as many other CMS/Frameworks) is like big magnet for bots and such popular site as your....so I would firs suggest few steps to make sure that Drupal is optimized and your enverionment..than I will give you few tips about Plesk which can cause also higher resource usage.
    When it comes to Drupal(and generaly other CMS or Frameworks)you should first do some steps to protect site from spam bots...in avarege you can get 10-20% lower usage on such big site.Database optimization must be high priority and should be done periodical.Drupal modules are very often reason for high resource usage ..you can find more about this on Drupal site since high CPU usage by Drupal is quite often issue as you'll see.Also I always suggest users that don't install module sor sake of adding one line of code.Also one thing to rember Drupal cache store in database so it is not always more-->better,if you spent some RAM on innoDB buffer size it will pay off...and you should look TEMP expecially..like max_heap_table_size, tmp_table_size, query_cache_limit, table_open_cache, and table_definition_cache..Here is quote from official mysql doc.
    "The defaults value of query_cache_size is 0, which disable the query cache. For small to medium size database it should set to 16MB to 32MB. But it depends on other two MySQL system variables: query_cache_type and query_cache_limit. Query_cache_type must be set to 1 and query_cache_limit must be a reasonable value. It is recommended to set 1MB"
    Here ar some generaal Drupal optimization guides:
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
    query_cache_size (> 12M)
    join_buffer_size (> 5.0M, or always use indexes with joins)
    table_cache (> 2048)

    Now when it comes to Plesk I had few times issue with High usage..if I remeber corectly once problem was dropbox backup extension,many user use this extension as external backup and once we had issue with high CPU usage with it,it was maybe one year ago and I don't remeber what exactly we implement except that we had to edit few ines of code,now since newer version of extension is out I suppose it is fixed.Also I suggest <ou check Plesk sw-engine process..sometimes it may stack and you have to kill it manualy.
    Now these are general guides and some may nit be related to your issue,in fact many are not but can help you.

  • Plesk switched to mariadb a while back, maybe change the thread name?

  • nelsahostnelsahost Member
    edited September 2017

    @xaoc said:
    Plesk switched to mariadb a while back, maybe change the thread name?

    Not for all distributions.but you can replace...All our Plesk Onyx servers are on MySql

  • doghouchdoghouch Member
    edited September 2017

    Your server should only be getting like ~420 requests an hour, or around 7 requests a minute. Popular or not, the server is well below capacity.

    Thanked by 1WSS
  • @nelsahost said:

    @xaoc said:
    Plesk switched to mariadb a while back, maybe change the thread name?

    Not for all distributions.but you can replace...All our Plesk Onyx servers are on MySql

    Why would you even considering going back to mysql?

  • @xaoc said:

    @nelsahost said:

    @xaoc said:
    Plesk switched to mariadb a while back, maybe change the thread name?

    Not for all distributions.but you can replace...All our Plesk Onyx servers are on MySql

    Why would you even considering going back to mysql?

    Why not?
    I once google'd mariadb vs mysql on performance, and benchmark shows 5.7 is better than 10.2

  • @qtwrk said:

    @xaoc said:

    @nelsahost said:

    @xaoc said:
    Plesk switched to mariadb a while back, maybe change the thread name?

    Not for all distributions.but you can replace...All our Plesk Onyx servers are on MySql

    Why would you even considering going back to mysql?

    Why not?
    I once google'd mariadb vs mysql on performance, and benchmark shows 5.7 is better than 10.2

    I'd sure love a link to that bench if possible, thanks.

  • ClouviderClouvider Member, Patron Provider

    Why you keep implying that Plesk is a problem here @OP?

  • @xaoc said:

    @qtwrk said:

    @xaoc said:

    @nelsahost said:

    @xaoc said:
    Plesk switched to mariadb a while back, maybe change the thread name?

    Not for all distributions.but you can replace...All our Plesk Onyx servers are on MySql

    Why would you even considering going back to mysql?

    Why not?
    I once google'd mariadb vs mysql on performance, and benchmark shows 5.7 is better than 10.2

    I'd sure love a link to that bench if possible, thanks.

    This is awkward, now everything I google'd says mariadb is better, but I do remember that I was trying to switch to 5.7 but ended up Plesk doesn't support 5.7 so gave up eventually.

  • pcszerviz said: Mysqltuner

    I prefer tuning MySQL by adjusting 1 parameter at a time. I think the config I adjust are less than 10 items.

  • Also, consider getting logs of which are the slow queries. Sometimes these things are in application level. It may be due to just 1 lousy drupal plugin executing a lousy query. I mean, no amount of hardware or configuration can save you from badly written queries.

  • FalzoFalzo Member
    edited September 2017

    one could simply not give a damn f*ck about that number somehow calculated in plesk and monitor the real usage live for quite some time with appropriate tools. so far it doesn't sound like an issue at all, just a weird warning in a control panel probably based on hot air.

  • nelsahostnelsahost Member
    edited September 2017

    @xaoc said:

    @nelsahost said:

    @xaoc said:
    Plesk switched to mariadb a while back, maybe change the thread name?

    Not for all distributions.but you can replace...All our Plesk Onyx servers are on MySql

    Why would you even considering going back to mysql?

    No one is going back..;Mysql is default database in many distribution still,we didn't switch to mysql...mysql was instaled with plesk as default option...
    At the and I don't want elaborate but we don't use MariaDB any where doesn't metter is it cPanel or Plesk ...ofcourse ti may change at some point...but for now we stay with mysql and mssql for linux/window web hosting.

Sign In or Register to comment.