Joins performed without indexes: 5568

Question

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.

Solution 1

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.

Solution 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.

Solution 3

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.

Comments

[Phillip -Zyan K Lee- Stockmann]

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).

[Wilson Hauck]

@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]

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.

[danblack]

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.

[Tota1907]

Okey I updated long_query_time. I will check slow_query. But I addded indexs yesterday. @danblack

[Tota1907]

ibb.co/h7Y8yp @WilsonHauck

[Wilson Hauck]

@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.

[danblack]

htop displays threads as their own process. I don't see a problem here. The time is CPU time spent in the thread.

[Wilson Hauck]

@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.

[Tota1907]

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]

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]

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.

[Phillip -Zyan K Lee- Stockmann]

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.

[Tota1907]

I updated it. Cpu was %100 and server was so slow.

[Tota1907]

I solved it but cpu usage still %100

[danblack]

even on the updated mysqltuner data, Joins performed without indexes: 6078 on 1M total (6hrs) seems a little preemptive to come to this conclusion.

[Tota1907]

I updated my.cnf. But it still %100 Cpu. I added index. [!!] Joins performed without indexes: 6078 It was 15000 yesterday. @danblack

[Wilson Hauck]

@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?

[danblack]

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.

[Wilson Hauck]

@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]

@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