Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
- (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
- FROM information_schema.tables WHERE engine='InnoDB') A;
- +-------+
- | RIBPS |
- +-------+
- | 8 |
- +-------+
- 1 row in set (4.31 sec)
- mysql>
- With this output, you would set the following in /etc/my.cnf
- [mysqld]
- innodb_buffer_pool_size=8G
- After the restart, run MySQL for a week or two. Then, run this query:
- SELECT (PagesData*PageSize)/POWER(1024,3) DataGB FROM
- (SELECT variable_value PagesData
- FROM information_schema.global_status
- WHERE variable_name='Innodb_buffer_pool_pages_data') A,
- (SELECT variable_value PageSize
- FROM information_schema.global_status
- WHERE variable_name='Innodb_page_size') B;
- This will give you how many actual GB of memory is in use by InnoDB Data in the InnoDB Buffer Pool at this moment.
- This value DataGB more closely resembles how big the InnoDB Buffer Pool should be + (percentage specified in innodb_change_buffer_max_size). I am sure this will be far less than the 20000M you have reserved right now. The savings in RAM can be used for tuning other things like
- join_buffer_size
- sort_buffer_size
- read_buffer_size
- read_rnd_buffer_size
- max_connection
- SELECT CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1))
- Recommended_InnoDB_Buffer_Pool_Size FROM
- (
- SELECT RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw
- FROM
- (
- SELECT SUM(data_length+index_length)*1.1*growth RIBPS
- FROM information_schema.tables AAA,
- (SELECT 1.25 growth) BBB
- WHERE ENGINE='InnoDB'
- ) AA
- ) A;
- ===================
- Something like this? Using SHOW VARIABLES and SHOW GLOBAL STATUS:
- Expression: innodb_buffer_pool_size / _ram
- Meaning: % of RAM used for InnoDB buffer_pool
- Recommended range: 60~80%
- Expression: Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests
- Meaning: Read requests that had to hit disk
- Recommended range: 0-2%
- What to do if out of range: Increase innodb_buffer_pool_size if you have enough RAM.
- Expression: Innodb_pages_read / Innodb_buffer_pool_read_requests
- Meaning: Read requests that had to hit disk
- Recommended range: 0-2%
- What to do if out of range: Increase innodb_buffer_pool_size if you have enough RAM.
- Expression: Innodb_pages_written / Innodb_buffer_pool_write_requests
- Meaning: Write requests that had to hit disk
- Recommended range: 0-15%
- What to do if out of range: Check innodb_buffer_pool_size
- Expression: Innodb_buffer_pool_reads / Uptime
- Meaning: Reads
- Recommended range: 0-100/sec.
- What to do if out of range: Increase innodb_buffer_pool_size?
- Expression: (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) / Uptime
- Meaning: InnoDB I/O
- Recommended range: 0-100/sec.
- What to do if out of range: Increase innodb_buffer_pool_size?
- Expression: Innodb_buffer_pool_pages_flushed / Uptime
- Meaning: Writes (flushes)
- Recommended range: 0-100/sec.
- What to do if out of range: Increase innodb_buffer_pool_size?
- Expression: Innodb_buffer_pool_wait_free / Uptime
- Meaning: Counter for when there are no free pages in buffer_pool. That is, all pages are dirty.
- Recommended range: 0-1/sec.
- What to do if out of range: First be sure innodb_buffer_pool_size is set reasonably; if still trouble, decrease innodb_max_dirty_pages_pct
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement