Need help with my.cnf: 32GB RAM and 3GB of InnoDB tables

Question

I have a Joomla app that takes my MySQL instance down all the time. It uses too much CPU and performs too many queries without using indexes, queries very large tables and is using non-optimized queries.

I have converted all my tables to InnoDB yesterday and applied some optimizations in my.cnf according to recommendations made by MySQLTuner and since then it seems to me that the site is much more stable, not too much CPU intensive and MySQL has stopped going down and the instance is more performent (until the site becomes more loaded with users, but I still need some help to improve my.cnf file and boost speed anyway).

The sites with MyISAM are ok because they are small, but I still need help with InnoDB to improved the speed.

my.cnf

[mysqld]
innodb_file_per_table=1
default-storage-engine=InnoDB
max_allowed_packet=268435456
#open_files_limit=1024000

default-storage-engine=MyISAM
innodb_file_per_table=1

#thread_cache_size=5

table_definition_cache=1000
query_cache_size=128M
table_open_cache=300
connect_timeout=120

innodb-buffer-pool-size=7GB
innodb_buffer_pool_instances=7

performance_schema=1
query_cache_type=0 #seems to me thats ignoring this =0 and working anyway because Qcache is hitting etc

join_buffer_size=200M
tmp_table_size=100M
max_heap_table_size=100M
thread_cache_size=1
performance_schema=ON


max_connections=90

mysqlTuner.pl Results

[root@hawk ~]# perl mysqltuner.pl 
 >>  MySQLTuner 1.7.0 - Major Hayden <[email protected]>


[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 10.1.23-MariaDB
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE 
[--] Data in MyISAM tables: 384M (Tables: 1262)
[--] Data in **InnoDB tables: 3G (Tables: 4043)**
[--] Data in MEMORY tables: 0B (Tables: 60)
[OK] Total fragmented tables: 0

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 8h 9m 18s (46M q [1K qps], 264K conn, TX: 137G, RX: 14G)
[--] Reads / Writes: 98% / 2%
[--] Binary logging is disabled
[--] Physical Memory     : 31.2G
[--] Max MySQL memory    : 25.4G
[--] Other process memory: 2.6G
[--] Total buffers: 7.5G global + 202.7M per thread (90 max threads)
[--] P_S Max memory usage: 85M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 25.6G (81.98% of installed RAM)
[OK] Maximum possible memory usage: 25.4G (81.35% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/46M)
[!!] Highest connection usage: 100%  (91/90)
[OK] Aborted connections: 0.28%  (744/264918)
[!!] 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.
[OK] Query cache efficiency: 44.6% (35M cached / 79M selects)
[!!] Query cache prunes per day: 737807
[OK] Sorts requiring temporary tables: 0% (7 temp sorts / 780K sorts)
[!!] Joins performed without indexes: 179795
[!!] Temporary tables created on disk: 56% (1M on disk / 2M total)
[!!] Thread cache hit rate: 49% (134K created / 264K connections)
[!!] Table cache hit rate: 0% (300 open / 75K opened)
[OK] Open file limit used: 1% (15/1K)
[OK] Table locks acquired immediately: 100% (20M immediate / 20M locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 85.1M
[--] Sys schema isn't installed.

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


-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 7.0G/4.0G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (1.33928571428571 %): 48.0M * 2/7.0G should be equal 25%
[OK] InnoDB buffer pool instances: 7
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 100.00% (17217820855 hits/ 17217969376 total)
[OK] InnoDB Write log efficiency: 90.75% (2413696 hits/ 2659622 total)
[OK] InnoDB log waits: 0.00% (0 waits / 245926 writes)


-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control warning line(s) into /var/lib/mysql/hawk.sobrehost.com.br.err file
    Control error line(s) into /var/lib/mysql/hawk.sobrehost.com.br.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 started within last 24 hours - recommendations may be inaccurate
    Reduce or eliminate persistent connections to reduce connection usage
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Increasing the query_cache size over 128M may reduce performance
    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
    Beware that open_files_limit (1024) variable 
    should be greater than table_open_cache (300)
    Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
    max_connections (> 90)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    query_cache_type (=0)
    query_cache_size (> 128M) [see warning above]
    join_buffer_size (> 200.0M, or always use indexes with joins)
    tmp_table_size (> 100M)
    max_heap_table_size (> 100M)
    thread_cache_size (> 1)
    table_open_cache (> 300)
    innodb_log_file_size * innodb_log_files_in_group should be equals to 1/4 of buffer pool size (=3G) if possible.

Query Cache

MariaDB [(none)]>  SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 10442    |
| Qcache_free_memory      | 74619672 |
| Qcache_hits             | 40528186 |
| Qcache_inserts          | 8696587  |
| Qcache_lowmem_prunes    | 277279   |
| Qcache_not_cached       | 929978   |
| Qcache_queries_in_cache | 12458    |
| Qcache_total_blocks     | 36968    |
+-------------------------+----------+
8 rows in set (0.00 sec)

Thanks.

Answer 1

"You can't tune your way out of performance problems". That being said, here is some tuning advice, followed by other ways to solve the issues you are facing.

"Thread cache hit rate: 49% (134K created / 264K connections)" and thread_cache_size=1 -- if *nix, set to 10. Otherwise the process creation may be noticeable.

"Highest connection usage: 100% (91/90)" -- Something outside of MySQL is causing this. Do you have connection pooling? Is the web server tuned to have too many connections? What web server?

"Query cache prunes per day: 737807" -- that is several per second! This is costly, especially with query_cache_size being 128M. Recommend lowering to 50M. Better yet, if possible, change to DEMAIND and use SQL_CACHE and SQL_NO_CACHE as appropriate for your SELECTs. Usually an app has some cachable queries and a lot of non-cachable queries. The latter clutter the QC without providing any benefit.

A QC "prune" occurs whenever any write occurs -- and it deletes all QC entries for the table involved. Hence, the bigger the QC, the longer it takes. Hence, my recommendation to shrink the size.

The rest of the settings seem reasonable.

Don't worry about the rest of the mysqlTuner recommendations.

More tips on converting to InnoDB are here .

Do not use MyISAM; it is going away. And, in virtually all situations, InnoDB can run faster.

Assuming the 3GB includes both data and indexes, as seen via SHOW TABLE STATUS, the 7G for buffer pool says that you have plenty of growing room before I/O may become an issue.

"Joomla app that makes Mysql down all the time" -- MySQL (even using MyISAM) may stall, hang, etc, but I question whether it every "crashed".

"too much CPU usage and too much queries without index, very large tables and non-optimized queries" -- Find one of the worst queries, provide EXPLAIN SELECT ..., and provide SHOW CREATE TABLE for each table involved. The solution may be as simple as adding a 'composite' index. And, since indexes work differently in MyISAM and InnoDB, this needs to be done (at least) after converting to InnoDB. (Start a new Question so we can focus on that query.)

Answer 2

I guess you wanted to ensure if my.cnf is rightly set without any wrong or bad settings. Yep makes sense. And Rick has added points to help you in configuring my.cnf as per the server, which are good practices to follow.

But I see this problem as.

Hey I have a problem and I need a solution !!!

I guess you might have found the solution if the actual problem is nailed.

MySQL started within last 24 hours - recommendations may be inaccurate

If you have seen the tuner disclaimer, it could be inaccurate because the output is not aware of better history of statistics to give ensure stats are right.

Lets take small steps to see if it helps:

1. DB reboot is all of a sudden but it was running good until last (XDATE) or you may see the stats in Mysql graph if you have and see when this reboot started. Point that date and see if any deployment is being pushed on that date. And try to see if it can be rolled back. If it can be goahead and then keep monitoring.

2. See what causes reboot? Is it due to maximum connections? Or a particular query was running?

3. If its hard to nail the exact reason. Try to separate the readers and writers. Writes to Master and Reads to Slave if application permits. I assume its a framework. Not sure if its an easy option for you. But worth giving a try.

4. But once (3) is done you will have clarity on Master

5. Lets say the reboot still persist on Master. See on which hour you get reboot. If the queries are same and concurrency is the bottleneck. Then try read and see if the combination of these variables are set rightly.

innodb_thread_concurrency

innodb_flush_neighbors -> This shouldn't be enabled if SSD

innodb_log_file_size -> Configure as recommended

innodb_io_capacity -> Worth to verify the disk IO, see if IO waits for disk seeks are fine. Try to see if its bursting IOPs on Mysql end for capacity given on server.

innodb_flush_log_at_trx_commit -> You can set it to 0/2 if consistency is a trade-off. But if you need consistency then you need to ensure the disk IOPs is given to burst fsync at every commit. Recommended is 1.

6. Lets say reboot is on Slave. Which I suspect a lot. Then start tuning queries to pick indexes or create indexes if required. Any changes in schema should be performed on Master so the schema can be consistent.

7. When you are sure you have tuned queries on slave. You may put back to Master in smaller query sets week by week. And remove slave if you don't want to.