?
Solved

tune innodb mysql

Posted on 2011-10-02
28
Medium Priority
?
1,949 Views
Last Modified: 2013-12-16
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?
-- MYSQL PERFORMANCE TUNING PRIMER --
             - 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 


INNODB STATUS
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


MEMORY USAGE
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



KEY BUFFER
No key reads?!
Seriously look into using some indexes

Open in new window

0
Comment
Question by:XK8ER
  • 12
  • 7
  • 7
  • +1
28 Comments
 
LVL 24

Expert Comment

by:johanntagle
ID: 36901185
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.

0
 
LVL 21

Expert Comment

by:Papertrip
ID: 36901204
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%.
0
 
LVL 21

Expert Comment

by:Papertrip
ID: 36901209
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.
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 1

Author Comment

by:XK8ER
ID: 36901219
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
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 36901224
Yeah I'm a bigot with regard to indexing so when I saw lines 32-34 I forgot about everything else =)
0
 
LVL 21

Expert Comment

by:Papertrip
ID: 36901241
Haha Johann ;)

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

Open in new window

0
 
LVL 21

Expert Comment

by:Papertrip
ID: 36901244
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 :)
0
 
LVL 1

Author Comment

by:XK8ER
ID: 36901245
[(10:44 PM)][(root@critical)] [(~)] $ grep processor /proc/cpuinfo |tail -1
processor       : 7
0
 
LVL 1

Author Comment

by:XK8ER
ID: 36901256
this is the current my.cnf I have
[client]
port		= 3306
socket		= /home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock

[mysqld]
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
log-bin=mysql-bin
binlog_format=mixed
log_warnings
slow_query_log
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
myisam_recover
basedir=/opt/mysql5516
datadir=/opt/mysql-data
event-scheduler=on
general-log
slow-query-log
log-queries-not-using-indexes
expire_logs_days=7
innodb_file_per_table=1
innodb_file_format=barracuda
innodb_strict_mode=0
innodb_use_sys_malloc=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

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit = 8192
log-error=/opt/mysql5516/mysql-error.log
pid-file=/opt/mysql5516/mysqld.pid

Open in new window

0
 
LVL 21

Expert Comment

by:Papertrip
ID: 36901259
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.

http://blog.scoutapp.com/articles/2009/07/31/understanding-load-averages
http://www.andymillar.co.uk/blog/2006/12/24/linux-load-average-explained
http://www.crucialp.com/resources/tutorials/server-administration/server-loads-explained-linux-unix.php
http://www.linuxjournal.com/article/9001
0
 
LVL 1

Author Comment

by:XK8ER
ID: 36901269
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.
0
 
LVL 21

Assisted Solution

by:Papertrip
Papertrip earned 664 total points
ID: 36901279
Understood.

An additional tool that can help you is mysqltuner.

[root@broken postfix]# yum info mysqltuner
Loaded plugins: fastestmirror, presto, priorities
<snip>
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

0
 
LVL 24

Expert Comment

by:johanntagle
ID: 36901301
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.

0
 
LVL 81

Expert Comment

by:arnold
ID: 36901314
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.

http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb.html

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.
etc.
0
 
LVL 1

Author Comment

by:XK8ER
ID: 36901321
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

0
 
LVL 1

Author Comment

by:XK8ER
ID: 36901329
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
0
 
LVL 1

Author Comment

by:XK8ER
ID: 36901340
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?
0
 
LVL 1

Author Comment

by:XK8ER
ID: 36901365
all I want is for this red message to disappear from tuning-primer.sh

see attached image
tune.jpg
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 36901385
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.
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 36901393
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
0
 
LVL 1

Author Comment

by:XK8ER
ID: 36901399
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
into
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
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 36901425
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!! =)
0
 
LVL 21

Expert Comment

by:Papertrip
ID: 36901426
omg indices!
0
 
LVL 81

Accepted Solution

by:
arnold earned 668 total points
ID: 36901477
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
0
 
LVL 1

Author Comment

by:XK8ER
ID: 36901612
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

$read_buffer_size+$read_rnd_buffer_size+$sort_buffer_size+$thread_stack+$join_buffer_size+$binlog_cache_size)*$max_connections

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

MEMORY USAGE
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
0
 
LVL 1

Author Comment

by:XK8ER
ID: 36901620
typo
35M * 200 max_connections = 3,079M
is
51M * 60 max_connections = 3,079M
0
 
LVL 1

Author Comment

by:XK8ER
ID: 36901624
it seems to be running smooth and the red message saying Im using more than 90% is now gone..
0
 
LVL 24

Assisted Solution

by:johanntagle
johanntagle earned 668 total points
ID: 36903161
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! =) )
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month17 days, 4 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question