Mysql InnoDB optimisation

innodbMySQL

(asked on stackoverflow but was suggested to move here!)

I'm having some trouble understanding InnoDB usage – we have a drupal based DB (5:1 read:write) running on mysql (Server version: 5.1.41-3ubuntu12.10-log (Ubuntu)). Our current Innodb data/index sizing is:

Current InnoDB index space = 196 M Current InnoDB data space = 475 M

Looking around on the web and reading books like 'High performance sql' suggest to have 10% increase on data size – i have set the buffer pool to be (data+index)+10% and noticed that the buffer pool was at 100%…even increasing about this to 896Mb still makes it 100% (even though the data + indexes are only ~671Mb?

I've attached the output of the innodb section of mysqlreport below. Pages free of 1 seems to be suggesting a major problem also as well. The innodb_flush_method is set at its default – I will investigate setting this to O_DIRECT but want to sort out this issue before.

__ InnoDB Buffer Pool __________________________________________________
Usage         895.98M of 896.00M  %Used: 100.00
Read hit      100.00%
Pages
  Free              1            %Total:   0.00
  Data         55.96k                     97.59 %Drty:   0.01
  Misc           1383                      2.41
  Latched           0                      0.00
Reads         405.96M    1.2k/s
  From file    15.60k     0.0/s            0.00
  Ahead Rnd       211     0.0/s
  Ahead Sql      1028     0.0/s
Writes         29.10M    87.3/s
Flushes       597.58k     1.8/s
Wait Free           0       0/s

__ InnoDB Lock _________________________________________________________
Waits              66     0.0/s
Current             0
Time acquiring
  Total          3890 ms
  Average          58 ms
  Max            3377 ms

__ InnoDB Data, Pages, Rows ____________________________________________
Data
  Reads        21.51k     0.1/s
  Writes      666.48k     2.0/s
  fsync       324.11k     1.0/s
  Pending
    Reads           0
    Writes          0
    fsync           0

Pages
  Created      84.16k     0.3/s
  Read         59.35k     0.2/s
  Written     597.58k     1.8/s

Rows
  Deleted      19.13k     0.1/s
  Inserted      6.13M    18.4/s
  Read        196.84M   590.6/s
  Updated     139.69k     0.4/s

Any help on this would be greatly apprectiated.

Thanks!

Best Answer

Are your InnoDB Log Files (ib_logfile0 and ib_logfile1) sized correctly ?

They should be 25% of the InnoDB Buffer Pool
In your case, that would be 224M

You can it them as follows:

Add this setting to /etc/my.cnf : innodb_log_file_size=224M
service mysql stop
rm -f /var/lib/mysql/ib_logfile[01]
service mysql start (ib_logfile0 and ib_logfile1 are recreated)

You should keep the default for innodb_flush_method to insure ACID compliance. Otherwise, you could lose up to one second's worth of transactions.

CAVEAT: If your DB server is dedicated to only to the database, you should reserve 75-80% or RAM for the innodb_buffer_pool.

UPDATE

You will definitely want to change your InnoDB layout !!!!

You need to raise innodb_open_files to 6000
You need to turn on innodb_file_per_table
You need to restructure all InnoDB Data to reside in separate tablespaces

Related Solutions

Mysql – How to use most of memory available on MySQL

I can easily believe a 95% key cache hit rate. Listen to your statistics:

You stated you have 50GB of Indexes for MyISAM tables

The key cache is designed to cache index pages for MyISAM tables. This option is set by the key_buffer_size option

You said it was set at 2G. No wonder the hit rate is 95%. Index blocks are being paged in and out of the key cache constantly. Dozens of queries needing a specific row gets loaded into the keycache after MySQL finds out the index (.MYI) info by the first query needed was not initially there. All subsequent queries requiring the same .MYI info to lookup data (.MYD) will already be cached after the first query loaded it. MySQL DOES NOT CACHE MyISAM Data in its own caches.

You should be able to set key_buffer_size to 8G.

Here is an excerpt from http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_key_buffer_size:

You can increase the value to get better index handling for all reads and multiple writes; on a system whose primary function is to run MySQL using the MyISAM storage engine, 25% of the machine's total memory is an acceptable value for this variable. However, you should be aware that, if you make the value too large (for example, more than 50% of the machine's total memory), your system might start to page and become extremely slow. This is because MySQL relies on the operating system to perform file system caching for data reads, so you must leave some room for the file system cache. You should also consider the memory requirements of any other storage engines that you may be using in addition to MyISAM.

This same URL explains that the max key_buffer_size for 32-bit OS is 4G. You should not go past 8G for key_buffer_size with your given server config.

As for your data reads, the only status variable you need is Key_reads. This gives an indication how often an index page has to be fetched from a .MYI file.

What you need to monitor for you key cache hit ratio is the following

KRR_NOW = Key_read_requests Now
KRR_SEC = Key_read_requests one ago
KRD_NOW = Key_reads Now
KRD_SEC = Key_reads one second ago
KRR_DELTA = KRR_NOW - KRR_SEC
KRD_DELTA = KRD_NOW - KRD_SEC

KeyCache Hit Ratio (KHR) is therefore this formula

KHR = 100 * (KRR_DELTA - KRD_DELTA) / KRR_DELTA

You want a KeyCache Hit Ratio 99+%

Now on the flipsize of things, let discuss InnoDB.

Your innodb_buffer_pool_size should be set to 4G. Thi is because the InnoDB buffer pool caches both data and index pages. You can give innodb_buffer_pool a more specific figure rounded up using the following:

SELECT CONCAT(CEILING(ibbytes / POWER(1024,3)),'G') FROM (SELECT SUM(data_length+index_length) ibbytes FROM information_schema.tables where engine='InnoDB') A;

MySQL DB causing high IO running Zabbix server

innodb_buffer_pool_size > 1G, because your dataset size is 1,8GB.

To decrease number of reads you need to increase innodb_buffer_pool_size. To decrease number of writes you need to edit your zabbix templates (disabling some unnecessary items like free inodes, increase intervals between checks).

You have Reads / Writes ratio at 57% / 43%, so enabling Query Cache will not help (it may make things worse, because writes to tables invalidates cache).

Think about increasing tmp_table_size and max_heap_table_size to avoid creating tmp tables on disk (13% of tmp tables). Temporary tables are in MB? it is count? If it is counter, its too high.

Decrease number of connection to 50 (your highest number was 33).

innodb_support_xa = false
innodb_buffer_pool_size = 256M # It depends how many memory is available to MySQL, more is better.
innodb_flush_log_at_trx_commit = 0 # disable writing to logs on every commit and disable fsync on each write
innodb_max_dirty_pages_pct = 90 # avoid flushing dirty pages to disk
innodb_flush_method = O_DIRECT # direct access to disk without OS cache
thread_cache_size = 4
query_cache_size = 0
table_cache = 80? # a little more than number_of_tables_in_zabbix_database

Usefull link about InnoDB optimization.

Best Answer

Related Solutions

Mysql – How to use most of memory available on MySQL

MySQL DB causing high IO running Zabbix server

Related Topic