Mysql – Need help with the.cnf: 32GB RAM and 3GB of InnoDB tables

mariadbmy.cnfMySQLperformanceperformance-tuning

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 <major@mhtx.net>


[--] 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.

Best Answer

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

Related Solutions

Mysql – How to debug a db memory-leak causing thesql to go before it’s own limits

...even surpassing it's theorically maximum possible allocation.

[OK] Maximum possible memory usage: 7.3G (46% of installed RAM)

There is not actually a way to calculate maximum possible memory usage for MySQL, because there is no cap on the memory it can request from the system.

The calculation done by mysqltuner.pl is only an estimate, based on a formula that doesn't take into account all possible variables, because if all possible variables were taken into account, the answer would always be "infinite." It's unfortunate that it's labeled this way.

Here is my theory on what's contributing to your excessive memory usage:

thread_cache_size       = 128

Given that your max_connections is set to 200, the value of 128 for thread_cache_size seems far too high. Here's what makes me think this might be contributing to your problem:

When a thread is no longer needed, the memory allocated to it is released and returned to the system unless the thread goes back into the thread cache. In that case, the memory remains allocated.

^{http://dev.mysql.com/doc/refman/5.6/en/memory-use.html}

If your workload causes even an occasional client thread to require a large amount of memory, those threads may be holding onto that memory, then going back to the pool and sitting around, continuing to hold on to memory they don't technically "need" any more, on the premise that holding on to the memory is less costly than releasing it if you're likely to need it again.

I think it's worth a try to do the following, after first making a note of how much memory MySQL is using at the moment.

Note how many threads are currently cached:

mysql> show status like 'Threads_cached';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Threads_cached | 9     |
+----------------+-------+
1 row in set (0.00 sec)

Next, disable the thread cache.

mysql> SET GLOBAL thread_cache_size = 0;

This disables the thread cache, but the cached threads will stay in the pool until they're used one more time. Disconnect from the server, then reconnect and repeat.

mysql> show status like 'Threads_cached';

Continue disconnecting, reconnecting, and checking until the counter reaches 0.

Then, see how much memory MySQL is holding.

You may see a decrease, possibly significant, and then again you may not. I tested this on one of my systems, which had 9 threads in the cache. Once those threads had all been cleared out of the cache, the total memory held by MySQL did decrease... not by much, but it does illustrate that threads in the cache do release at least some memory when they are destroyed.

If you see a significant decrease, you may have found your problem. If you don't, then there's one more thing that needs to happen, and how quickly it can happen depends on your environment.

If the theory holds that the other threads -- the ones currently servicing active client connections -- have significant memory allocated to them, either because of recent work in their current client session or because of work requiring a lot of memory that was done by another connection prior to them languishing in the pool, then you won't see all of the potential reduction in memory consumption until those threads are allowed to die and be destroyed. Presumably your application doesn't hold them forever, but how long it will take to know for sure whether there's a difference will depend on whether you have the option of cycling your application (dropping and reconnecting the client threads) or if you'll have to just wait for them to be dropped and reconnected over time on their own.

But... it seems like a worthwhile test. You should not see a substantial performance penalty by setting thread_cache_size to 0. Fortunately, thread_cache_size is a dynamic variable, so you can freely change it with the server running.

Mysql – The total number of locks exceeds the lock table size, even after increasing buffer pool size

Try this in my.cnf file,

[mysqld] innodb_buffer_pool_size = 80M

And you need to restart the Workbench as well as the Sql service.