tune innodb mysql

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.

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

Select all 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 :)

[(10:44 PM)][(root@critical)] [(~)] $ grep processor /proc/cpuinfo |tail -1
processor       : 7

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

Select all 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.

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

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.

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.

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.

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)

Select all Open in new window

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

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?

all I want is for this red message to disappear from tuning-primer.sh

see attached image
tune.jpg

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

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

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!

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"

Select all 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

typo
35M * 200 max_connections = 3,079M
is
51M * 60 max_connections = 3,079M

it seems to be running smooth and the red message saying Im using more than 90% is now gone..