tune innodb mysql

hello there,
ever since I upgraded the server into centos v5.7 and 24GB of ram the server load dropped from 5.00 into about 0.68
I am still trying to lower it and the reason for the high load is because of mysql. what recommendations would you give me?
             - By: Matthew Montgomery -

MySQL Version 5.5.16-log x86_64

Uptime = 2 days 6 hrs 5 min 55 sec
Avg. qps = 67
Total Questions = 13113961
Threads Connected = 3 

Current InnoDB index space = 1.59 G
Current InnoDB data space = 15.95 G
Current InnoDB buffer pool free = 1 %
Current innodb_buffer_pool_size = 16.00 G
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory

Max Memory Ever Allocated : 17.59 G
Configured Max Per-thread Buffers : 6.87 G
Configured Max Global Buffers : 16.11 G
Configured Max Memory Limit : 22.98 G
Physical Memory : 23.52 G

Max memory limit exceeds 90% of physical memory

No key reads?!
Seriously look into using some indexes

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Line 34 says it all.  Look at the queries and statements you run and see what indexes you need to put in place.  For example if you have a:

select *
from table1
where col1=5
and col2='foo'
and col3='bar'

I would first do a:
select count(distinct col1), count(distinct col2), count(distinct col3) from table1;

Then create an index with the column that has the highest distinct values as first e.g.

alter table table1 create index (col2, col1, col3);

Note that if a column has very few distinct values, it may not be worth indexing.  If you're serious about performance suggest you check out the Oreilly High Performance MySQL book.

Why are you concerned with a load of .68?  Even on a single processor machine, that is absolutely nothing to worry about.

Configured Max Memory Limit : 22.98 G
Physical Memory : 23.52 G
Max memory limit exceeds 90% of physical memory
That is going to starve the system of physical memory, I suggest decreasing that to at least 90%.
I'm obviously not addressing the tuning of mysql as Johann did, but rather a different part of the question.

the high load is because of mysql
.68 is most definitely not a high load.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

XK8ERAuthor Commented:
well right now its at 2.00 but it goes between 0.50-2.00

>>Configured Max Memory Limit : 22.98 G

that is weird i dont see any setting in my.cnf as 22GB

the max I see is this

innodb_buffer_pool_size = 16G
Yeah I'm a bigot with regard to indexing so when I saw lines 32-34 I forgot about everything else =)
Haha Johann ;)

XK8ER --
grep processor /proc/cpuinfo |tail -1

Open in new window

that is weird i dont see any setting in my.cnf as 22GB
Configured Max Per-thread Buffers : 6.87 G
Configured Max Global Buffers : 16.11 G
Configured Max Memory Limit : 22.98 G

Perhaps Johann can expand on that :)
XK8ERAuthor Commented:
[(10:44 PM)][(root@critical)] [(~)] $ grep processor /proc/cpuinfo |tail -1
processor       : 7
XK8ERAuthor Commented:
this is the current my.cnf I have
port		= 3306
socket		= /home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock

port		= 3306
socket		= /home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock
back_log = 50
max_connections = 200
max_connect_errors = 10
table_open_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 64M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
default-storage-engine = innodb
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
long_query_time = 4
server-id = 1
key_buffer_size = 32M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 16G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120

max_allowed_packet = 16M


key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M


open-files-limit = 8192

Open in new window

OK so you have 8 cores.  That means that until the load hits 8.0, you almost certainly don't have much to worry about.

Load is comprised of a few different components, and there are other things to take into consideration as well.

XK8ERAuthor Commented:
that was a good sample with the road and cars passing by.. loved it..
anyways at this point i am not concerned about server load but more into tuning the mysql so that it can work smooth..
i know the server is powerful and can handle it but I would like to setup mysql configuration right.

An additional tool that can help you is mysqltuner.

[root@broken postfix]# yum info mysqltuner
Loaded plugins: fastestmirror, presto, priorities
Name       : mysqltuner
Arch       : noarch
Version    : 1.1.1
Release    : 1.el6
Size       : 78 k
Repo       : installed
From repo  : epel
Summary    : MySQL configuration assistant
URL        : http://mysqltuner.com/
License    : GPLv3+
Description: MySQLTuner is a script written in Perl that will assist you with your
           : MySQL configuration and make recommendations for increased performance
           : and stability.  Within seconds, it will display statistics about your
           : MySQL installation and the areas where it can be improved.

Open in new window

Configured Max Per-thread Buffers : 6.87 G
Configured Max Global Buffers : 16.11 G
Configured Max Memory Limit : 22.98 G

Perhaps Johann can expand on that :)

Take a look at values for your per-thread buffers such as read_buffer,sort_buffer, tmp_table_size.  Those will add up to 6.87 GB.  Add it to 16.11GB you get 22.98 GB.  Your configured innodb_buffer_pool_size actually needs to be bigger based on:

Current InnoDB index space = 1.59 G
Current InnoDB data space = 15.95 G
Current InnoDB buffer pool free = 1 %

( innodb_buffer_pool_size should be at least 15.95+1.59, even higher since you want this to cover both Innodb index and data as they grow so that they can be cached in memory).  So you really need to take a look at your per-thread buffers.  ALTHOUGH I'm not really sure what mysqltuner uses to compute the per-thread buffer total and it says Max Memory Ever Allocated is 17.59G (well below physical memory), it's something worth looking at.  But beyond the parameters listed in http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/, I would concentrate first on checking indexes.  No amount of server tuning will be worth it if the schema is shot.

Get if you do not have already, phpmyadmin from phpmyadmin.net it will help you in further tunning your system based on the statistics of the queries you've run.
it uses the "show status" data.
Before trying to tune, you have to know what is going on.

You could "explain query" to see what the system will do for the query.


The diffculty in answering your question directly is that I have no idea what adds to the resource spike when your load hits 2.  If your system is not solely a mysql database server, there might be other things that increase the load having nothing to do with mysql.  i.e. you have a php site and you generate a large query.  While the response to the query is nearly immediate, the PHP processing is what spikes the resources on the system.
XK8ERAuthor Commented:
ok so what settings exactly are the ones that I need to change and what values. im a bit confused cus there are a lot..

also here's the output of mysqltuner.pl
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 868B (Tables: 6)
[--] Data in InnoDB tables: 15G (Tables: 533)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 35

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 7h 4m 26s (13M q [67.701 qps], 807K conn, TX: 487B, RX: 16B)
[--] Reads / Writes: 74% / 26%
[--] Total buffers: 16.2G global + 34.2M per thread (200 max threads)
[!!] Maximum possible memory usage: 22.9G (97% of installed RAM)
[OK] Slow queries: 4% (569K/13M)
[OK] Highest usage of available connections: 21% (43/200)
[OK] Key buffer size / total MyISAM indexes: 32.0M/125.0K
[OK] Key buffer hit rate: 100.0% (2K cached / 0 reads)
[OK] Query cache efficiency: 50.5% (4M cached / 8M selects)
[!!] Query cache prunes per day: 149729
[!!] Sorts requiring temporary tables: 11% (14K temp sorts / 118K sorts)
[!!] Joins performed without indexes: 213670
[OK] Temporary tables created on disk: 19% (15K on disk / 76K total)
[OK] Thread cache hit rate: 98% (13K created / 807K connections)
[!!] Table cache hit rate: 19% (1K open / 6K opened)
[OK] Open file limit used: 0% (70/8K)
[OK] Table locks acquired immediately: 100% (11M immediate / 11M locks)
[OK] InnoDB data size / buffer pool: 16.0G/16.0G

-------- Recommendations -----------------------------------------------------
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:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (> 64M)
    sort_buffer_size (> 8M)
    read_rnd_buffer_size (> 16M)
    join_buffer_size (> 8.0M, or always use indexes with joins)
    table_cache (> 2048)

Open in new window

XK8ERAuthor Commented:
I understand but at this point i am not concerned about the high loads or what process spikes it to 2.00 I just want to make sure mysql is using the right settings and not more than 90% memory
XK8ERAuthor Commented:
ok so basically I should be running on 32GB ram and using about 24GB for innodb_buffer_pool_size
then I wont get those red warning saying im using more than 90% of physical memory.. right?
XK8ERAuthor Commented:
all I want is for this red message to disappear from tuning-primer.sh

see attached image
If you have 32GB RAM and set 24GB for innodb_buffer_pool_size but don't adjust the other buffer settings, you will get

Configured Max Per-thread Buffers : 6.87 G
Configured Max Global Buffers : 24.11 G (+)
Configured Max Memory Limit : 30.98 G (+)

So you will likely still see those warnings.

You don't need to strictly follow the innodb_buffer_pool_size=2/3 or physical memory.  What's important is it is at least equal to the sum of the following:

Current InnoDB index space = 1.59 G
Current InnoDB data space = 15.95 G

So even just 17GB will cover it.
Some more on innodb_buffer_pool_size, on why it's not just 2/3 or 3/4 of physical memory (could be lower, could be higher): http://blog.codesherpas.com/on_the_path/2011/03/tuning-mysql.html
XK8ERAuthor Commented:
I understand but at this point I just upgrade to 24GB ram server two days ago so the only thing I can do for now would be to do some changes..

innodb_buffer_pool_size = 16G
innodb_buffer_pool_size = 17G

and lower the settings from
>>Take a look at values for your per-thread buffers such as read_buffer,sort_buffer, tmp_table_size
Sorry got my addition wrong: 1.59 + 15.95 > 17GB.  So it should be 18GB.  Again, I'm not sure how the tuner script computes for the total per-thread buffers - maybe you can take a peek inside the script.  And once more - work on those indexes!! =)
omg indices!
How big is your database?
innodb_buffer_pool_size = 16G
could be an option i.e. reduce this to 15GB

The quickest way is reduce the number of connections: from 200 to 150.

I'm more familiar with phpmyadmin.

You should increase:
query_cache_size = 128M
query_cache_limit = 8M
sort_buffer_size = 16M
join_buffer_size = 16M

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
XK8ERAuthor Commented:
okay this is what im going to do..

I found the info by looking at the source code of tuning-primer.sh

per_thread_buffers=$(echo "($read_buffer_size+$read_rnd_buffer_size+$sort_buffer_size+$thread_stack+$join_buffer_size+$binlog_cache_size)*$max_connections"

Open in new window

>>the are the settings from my.cnf
2M + 16M + 8M + 192K + 8M + 1M = 35M
35M * 200 max_connections = 7,019M
Configured Max Per-thread Buffers = 7,019M

on the reports I see
Historic max_used_connections = 43
so im going to drop that number from 200 into 60

innodb_buffer_pool_size = 18G
max_connections = 60
query_cache_size = 128M
query_cache_limit = 8M
sort_buffer_size = 16M
join_buffer_size = 16M


2M + 16M + 16M + 192K + 16M + 1M = 51M
35M * 200 max_connections = 3,079M
Configured Max Per-thread Buffers = 3,079M

18G innodb_buffer_pool_size + 3,079M = 21,097M

24,000G ram - 90% = 21,600M of physical ram allowed to use and I would be using 21,097M
New Report

Max Memory Ever Allocated : 19.62 G
Configured Max Per-thread Buffers : 2.99 G
Configured Max Global Buffers : 18.17 G
Configured Max Memory Limit : 21.17 G
Physical Memory : 23.52 G
Max memory limit seem to be within acceptable norms
XK8ERAuthor Commented:
35M * 200 max_connections = 3,079M
51M * 60 max_connections = 3,079M
XK8ERAuthor Commented:
it seems to be running smooth and the red message saying Im using more than 90% is now gone..
Okay as long as your users are happy with the performance.  But if not, take note of the other things suggested here (especially the indexes! =) )
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.