3

I was trying to do mysql optimization. Yesterday the CPU usage was 100%. I am trying to optimize with mysqltuner but this time I gave a mysqltuner warning.

I have a high-traffic db. 500-600 connections in a day. Maybe more.

How can I fix it?

[!!] Joins performed without indexes: 5568

Key buffer used: 18.2% (97M used / 536M cache)

Ratio InnoDB log file size / InnoDB Buffer pool size (12.5 %): 256.0M * 2/4.0G should be equal 25%

InnoDB Write Log efficiency: 70.64% (3619 hits/ 5123 total)

Centos 6 8gb Ram and 4 prossesor

hdd: https://pastebin.com/AnFdUHp6

UPDATED:

my.cnf: https://pastebin.com/g7DbmZ2T

UPDATED:

mysqltuner: https://pastebin.com/HBdSjxaj

global variables: https://pastebin.com/xTzu2PGM

But Cpu usage still was %100.

ibennetch
  • 341
  • 1
  • 10
Tota1907
  • 39
  • 1
  • 1
  • 4
  • While you are at debugging it, have a look at the mysqld.log: `[!!] /var/log/mysqld.log contains 98079 error(s).` That seems to be way too many errors (and warnings too, by the way). – Phillip -Zyan K Lee- Stockmann Oct 07 '18 at 06:07
  • @Tota1907 The global variables posted above appears to have been posted from SHOW GLOBAL VARIABLES\G; PLEASE repost from SHOW GLOBAL VARIABLES; for more useful report for analyzing your instance. – Wilson Hauck Oct 07 '18 at 23:45
  • 2
    Additional information request, please. Post on pastebin.com or here. A) complete (not edited) my.cnf or my.ini Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; 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 -x when system is busy for an idea of IOPS by device, df -h for a linux/unix free space list by device, includes hda & sda (SSD) filesystem type clues. for server tuning analysis. – Wilson Hauck Oct 07 '18 at 23:48
  • As previously, set long_query_time to 1 (second) or less. Look at the queries for correct index usage and general sanity. Share some queries on the tablets/students along with their table structure. Seems the regular every 5 second actions of the 'service', accounts for the QPS stated. – danblack Oct 08 '18 at 08:05
  • 1
    Okey I updated long_query_time. I will check slow_query. But I addded indexs yesterday. @danblack – Tota1907 Oct 08 '18 at 08:21
  • 1
    https://ibb.co/h7Y8yp @WilsonHauck – Tota1907 Oct 08 '18 at 08:50
  • @Tota1907 The HTOP display has 10 copies of mysql in RAM and some in use for multiple hours. Any reason why this is necessary? We really NEED to Skype TALK. View my profile, Network profile for contact info, including my Skype ID. – Wilson Hauck Oct 08 '18 at 11:24
  • htop displays threads as their own process. I don't see a problem here. The time is CPU time spent in the thread. – danblack Oct 10 '18 at 03:06
  • @tota1907 Five of the 'threads' listed in htop have TIME listed as more than 1 hour. Do you really have a THREAD that runs more than 1 hour or has the required CLOSE() been forgotten to release resources when your process is finished? PLEASE post your SHOW GLOBAL STATUS; in pastebin.com so we can give you some REAL assistance. When you post a LINK indicate what the LINK is for, please. – Wilson Hauck Oct 10 '18 at 20:33

4 Answers4

3

Joins performed without indexes: 91. This is most likely the biggest culprit for your performance.

Your database doesn't have proper indexes for columns that are used in SQL queries that join data from multiple tables.

This means that instead of looking into only the index data, MySQL needs to scan the full table to get rows matching query keys.

So, you need to look into your SQL queries, and add proper indexes to tables / columns used in joins.

Tero Kilkanen
  • 36,796
  • 3
  • 41
  • 63
  • even on the updated mysqltuner data, Joins performed without indexes: 6078 on 1M total (6hrs) seems a little preemptive to come to this conclusion. – danblack Oct 08 '18 at 07:42
  • 1
    I updated my.cnf. But it still %100 Cpu. I added index. [!!] Joins performed without indexes: 6078 It was 15000 yesterday. @danblack – Tota1907 Oct 08 '18 at 07:44
  • You've improved 6922/1000000 or ~0.7% of all queries which obviously don't correspond to all of the CPU. Stop repeating yourself and read. – danblack Oct 10 '18 at 03:10
  • @tota1907 You have 4 cores. Each core could be 100% busy. 100/4=25% effective busy. Not REALLY busy in any way. Have you applied ANY of the Oct 8, 2018 suggestions of mine? If not, please consider implementing them. one every 10 minutes, check your error log and if no problem, implement the next suggestion. In one day, you will be done. Let us know 24 hours later, how your system is running, please. – Wilson Hauck Dec 01 '18 at 21:04
2

There are at least two issues with your configuration:

  • Overallocated max_connections. Your highest connection usage is 4 and you allocated 440 connections which caused memory overconsumption (each connection allocates 18.5 M and multiplied by 440 connection it uses 8G memory). Reduce max_connections down to 20 and monitor connection usage regularly.
  • Overallocated innodb_buffer_pool_size. Your dataset is 254.3M and you allocated 4G. Reduce it down to 1G and review it later when your database has been running under load without restarting for at least a couple days. Also reset innodb_buffer_pool_instances to 1.

Due to memory overallocation (12.6G allocated for MySQL + 1.3G other process memory > 8G of system memory) your system probably went into swap thrashing which caused high CPU usage.

AlexD
  • 8,747
  • 2
  • 29
  • 38
  • 1
    I restarted server yesterday. My project is student service tracking program. For now, we have 50 services and 50 tablets. Every tablet student is in service or not. Student services add data every 5 second. And parents can watch live. We have 2000 student at now. And It'il be 10000 in a few months. – Tota1907 Oct 07 '18 at 08:41
  • 1
    I solved it but cpu usage still %100 – Tota1907 Oct 08 '18 at 07:23
  • @tota1907 Another way to look at CPU % used includes this concept: You have nn CPUs; therefore, these CPU percentages are divided by nn to indicate the true percentage of all CPU power used. For you, 100/4 CPU's = 25% for a reasonable expectation when you have 4 CPUs. Any chance of a Skype TALK session today for 5 minutes? – Wilson Hauck Oct 08 '18 at 12:55
1

Sorry about the unfitting first answer - I obviously was too tired yesterday and misunderstood your question.

While using connection pools would help with the overall performance of the application, there are most probably other measures to take first:

MysqlTuner suggests valid options to set:

query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
join_buffer_size (> 1.0M, or always use indexes with JOINs)
innodb_log_file_size should be (=528M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
innodb_buffer_pool_instances(=4)

Also make sure the mysqld.log gets rotated regularly and check for warnings/errors in there. You might start with deleting it and restarting the mysqld, then see what errors/warnings will get printed back in there from here on.

You could test if lowering the innodb_buffer_pool_size to 1G (1073741824) and innodb_buffer_pool_instances to 1 affects the performance negatively. According to the mysqltuner, there are only 254.3M of data in the buffer - so you would have enough room to grow.

The suggestion by @tero-kilkanen might be worth a shot, too. Though as there only were 58 out of 19K queries missing a matching index, I don't think this will yield much of a performance boost. But check the indexes for their sanity and included keys anyway.

To get rid of the warning, that your mysqld might use more than the installed amount of RAM, you should lower the max_connections to a sane number: 20 - 30. Your application used 4 at the most during the uptime of he mysqld.
The maximum amount of memory to be assigned by mysqld is calculated rougly by multiplying the max_connections (440) with the cache assigned per connection (mostly sort_buffer_size plus some smaller ones) and then adding the global buffers (i.e. innodb_buffer_pool_size) to it. While 18.5MB per connection sounds not too much (it isn't) - multiplying it by 440 amounts to 8GB on their own.

As your application mainly uses read statements (99%) adding caching on the application side might reduce the overall load on the database and increase performance on the frontend side noticably.
Regularly queried values which rarely change at all (frontend user tables for instance) might be cached. Then you could invalidate the cache when editing student/parent users and their permissions.

If you still encounter problems, run the mysqld and application for a longer time before running mysqltuner. 1-3 days should yield better suggestions due to a more accurate usage profile.

Phillip -Zyan K Lee- Stockmann
  • 1,368
  • 10
  • 14
  • 1
    My project is student service tracking program. For now, we have 50 services and 50 tablets. Every tablet student is in service or not. And parents can watch live. @Phillip -Zyan K Lee- Stockmann – Tota1907 Oct 06 '18 at 21:47
  • 1
    I restarted server yesterday. Student services add data every 5 second. And parents can watch live. We have 2000 student at now. And It'il be 10000 in a few months. Minimum 50 tablets and parents will connect tomorrow . An average of 150 200 connections. And in a couple of months, it'il be two to three. – Tota1907 Oct 07 '18 at 08:50
  • original mysqltuner output states: `[--] Up for: 2h 2m 15s (19K q [2.618 qps], 2K conn, TX: 189M, RX: 1M)` rerun mysqltuner and update the question with the new data, please. – Phillip -Zyan K Lee- Stockmann Oct 07 '18 at 08:52
  • 1
    I updated it. Cpu was %100 and server was so slow. – Tota1907 Oct 08 '18 at 07:22
  • 1
    @tota1907 Please REPOST a COMPLETE report from mysqltuner AFTER UPTIME is a complete working day. Posting just part of the report is a waste of everyone's time. Implement my October 8 suggestions, please. Thanks – Wilson Hauck Dec 01 '18 at 21:10
0

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

REMOVE first line with open_files_limit (there are 2 in your my.cnf) AND REMOVE only line with innodb_open_files=800000 to allow default to work for your instance.

following could be change or ADD line to my.cnf

thread_cache_size=100  # for CAP of 100 per V5.7 refman to reduce threads_created
query_cache_type=0  # for NO query cache to conserve CPU cycles
query_cache_size=0  # from 1M to conserve RAM for more useful purpose
innodb_io_capacity=15000  # from 200 to use more of SSD IOPS capacity
sort_buffer_size=2M  # from 6M to conserve RAM per CONNECTION
read_buffer_size=128K  # from 1M to reduce handler_read_next RPS
join_buffer_size=256K  # from 2M per table join OPS per CONNECTION
table_open_cache=10000  # from 524288 for a practical LIMIT
table_definition_cache=1000  # from 2000 since you have less than 100 tables today
open_files_limit=65536  # from 1049026 million for a practical LIMIT
yagmoth555
  • 16,758
  • 4
  • 29
  • 50
Wilson Hauck
  • 472
  • 5
  • 11