INNODB STATUS VARIABLES:
innodb_adaptive_hash_index:
If this variable
is in ‘ON’ then hash index is build for the pages that are often accessed. If this
is variable is turned off, then all searches will take through non hash index
searches. It can be changed globally not in session level.
Eg:
Innodb_adaptive_hash_index=OFF
-------------------------------------
INSERT
BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf:
size 1, free list len 5, seg size 7,
0
inserts, 0 merged recs, 0 merges
Hash
table size 1117, node heap has 0 buffer(s)
0.00 hash
searches/s, 7.88 non-hash searches/s
Here non-hash
searches per second are more than the hash searches per second. It should not
be like that so we have turn on the variable.
Innodb_additional_mem_pool_size:
Innodb
uses this variable to store data dictionary information and other internal
structures. We can set this variable upto our need. If we failed to use this
variable in my.ini then its starts allocating 1MB of memory from operating
system.
Eg:
innodb_additional_mem_pool_size = 1M
(allocates memory from OS)
Innodb_autoextend_increment:
Innodb
uses this variable to extend the size of data file. Default value is 8MB. We
can change this variable upto our needs. This value affects the OS memory so it
can be allocated according to hard disk free memory.
Eg:
Innodb_autoextend_increment=8M
(default)
Innodb_autoinc_lock_mode:
Innodb uses this
variable to implement the auto_increment locking in table level. If the value
is set to ‘0’ i.e. (traditional lock), for all insert statements, a table level
auto_inc lock is obtained and maintained till the end of the insert statement.
Eg:
FOR TRADITIONAL LOCK MODE:
mysql>
show global variables like 'innodb_autoinc_lock_mode';
+--------------------------+-------+
|
Variable_name | Value |
+--------------------------+-------+
|
innodb_autoinc_lock_mode | 0 |
+--------------------------+-------+
1 row in
set (0.00 sec)
mysql>
create table t1 (no int(3) auto_increment primary key, name varchar(15));
Query OK,
0 rows affected (0.06 sec)
mysql>
alter table t1 auto_increment=101;
Query OK,
0 rows affected (0.17 sec)
Records:
0 Duplicates: 0 Warnings: 0
mysql>
insert into t1(no, name) values(1,'a'),(NULL,'b'),(5,'c'),(NULL,'d');
Query OK,
4 rows affected (0.01 sec)
Records:
4 Duplicates: 0 Warnings: 0
mysql>
select * from t1;
+-----+------+
| no | name |
+-----+------+
| 1 | a
|
| 5 | c
|
| 101 |
b |
| 102 |
d |
+-----+------+
4 rows in
set (0.00 sec)
mysql>
insert into t1 values(NULL,'e');
Query OK,
1 row affected (0.05 sec)
mysql>
select * from t1;
+-----+------+
| no | name |
+-----+------+
| 1 | a
|
| 5 | c
|
| 101 |
b |
| 102 |
d |
| 103 |
e |
+-----+------+
5 rows in
set (0.00 sec)
If the value is
set to 1(consecutive lock mode), all bulk insert-like statements obtain and
maintain the auto_inc lock on the whole table.
Eg:
FOR CONSECUTIVE
LOCK MODE:
mysql>
show global variables like 'innodb_autoinc_lock_mode';
+--------------------------+-------+
|
Variable_name | Value |
+--------------------------+-------+
|
innodb_autoinc_lock_mode | 1 |
+--------------------------+-------+
1 row in
set (0.00 sec)
mysql>
create table t1(no int(3) auto_increment primary key,name varchar(15));
Query OK,
0 rows affected (0.06 sec)
mysql> alter table t1 auto_increment=101;
Query OK,
0 rows affected (0.17 sec)
Records:
0 Duplicates: 0 Warnings: 0
mysql>
insert into t1(no,name) values(1,'a'),(NULL,'b'),(5,'c'),(NULL,'d');
Query OK,
4 rows affected (0.02 sec)
Records:
4 Duplicates: 0 Warnings: 0
mysql>
select * from t1;
+-----+------+
| no | name |
+-----+------+
| 1 | a
|
| 5 | c
|
| 101 |
b |
| 102 |
d |
+-----+------+
4 rows in
set (0.00 sec)
mysql>
insert into t1 values(NULL,'e');
Query OK,
1 row affected (0.02 sec)
mysql>
select * from t1;
+-----+------+
| no | name |
+-----+------+
| 1 | a
|
| 5 | c
|
| 101 |
b |
| 102 |
d |
| 105 |
e |
+-----+------+
5 rows in
set (0.00 sec)
If
the value is set to 2 (interleaved lock mode), no insert-like statements use
the table-level auto-inc lock, and multiple statements can execute at the same
time. This is the fastest and most scalable lock mode.
It is not safe when using statement-based
replication (SBR) or recovery scenarios when SQL statements are replayed from
the binary log.
Innodb_buffer_pool_size:
Innodb
uses this variable to cache data and indexes of its tables. We can set this
variable upto 50 – 80% of the physical memory. The buffer pool size should be
set too high then it affects the physical memory paging.
Innodb buffer pool size can be
monitored through several variables. Using these status variables we can easily
identify the free buffers, used buffers, hit rate, dirty pages. Here is an example.
Eg:
Here
buffer pool size is 256MB.
mysql>
show global variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
|
Variable_name | Value |
+-------------------------+-----------+
|
innodb_buffer_pool_size | 268435456 |
+-------------------------+-----------+
1 row in
set (0.00 sec)
mysql> show global status like
'innodb_buffer%';
+-----------------------------------+--------+
|
Variable_name | Value |
+-----------------------------------+--------+
|
Innodb_buffer_pool_pages_data |
16379 |
|
Innodb_buffer_pool_pages_dirty |
0 |
|
Innodb_buffer_pool_pages_flushed |
1632 |
|
Innodb_buffer_pool_pages_free |
1 |
|
Innodb_buffer_pool_pages_misc |
4 |
|
Innodb_buffer_pool_pages_total |
16384 |
|
Innodb_buffer_pool_read_ahead_rnd | 5
|
|
Innodb_buffer_pool_read_ahead_seq | 86
|
|
Innodb_buffer_pool_read_requests |
679320 |
|
Innodb_buffer_pool_reads |
11410 |
|
Innodb_buffer_pool_wait_free |
0 |
|
Innodb_buffer_pool_write_requests | 25188
|
+-----------------------------------+--------+
12 rows
in set (0.00 sec)
Buffer
pool free percentage:
The
percent of the buffer pool free is the ratio of how many pages are not
currently allocated in the buffer pool. That is the memory is allocated but
currently unused.
Here
buffer pool free percentage should not be more the 30% of the allocated size if
it is so then we have reduce the size of the innodb_buffer_pool_size.
= 100 *
(Innodb_buffer_pool_pages_free/Innodb_buffer_pool_pages_total)
= 100 * (1/16384)
= 0.0061 %
Buffer
pool used for data:
The
percent of buffer pool used to cache data and indexes. Here buffer pool
percentage should not be less than 90% if it is so then we have to increase the
size of the innodb_buffer_pool_size.
= 100 *
(Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total)
= 100 * (16379/16384)
= 99.9695 %
Dirty
pages percentage:
The
percent of dirty pages is ratios of many pages are in the cache in a modified
state (dirty) and should be written to disk before shutdown. When 50% of buffer
is dirty then we can shut down the server quickly so we have to decrease the
value of innodb_max_dirty_pages_pct.
Mysql>
Set global innodb_max_dirty_pages_pct=0;
= 100 *
(Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_data)
= 100 * (0/16379)
= 0 %
Buffer
pool miss rate:
The
buffer pool miss rate is the percentage of the time that a page was requested
but was not present in the buffer pool and had to be read from the disk.
Increasing the size of innodb_buffer_pool_size
may decrease the number of buffer pool misses.
= 100 *
(Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests)
= 100 * (11410/679320)
= 1.6796 %
Total buffer pool pages:
The total number of pages can be
created to cache the data and indexes.
=(Innodb_buffer_pool_pages_free+Innodb_buffer_pool_pages_data+
Innodb_buffer_pool_pages_misc)
= (1 + 16379 + 4)
= 163784
Innodb_checksums:
Innodb
uses checksum validation on all pages read from disk to ensure extra fault
tolerance against broken hardware or data files. This validation is enabled by
default. This feature is unneeded when running benchmarks. This variable can be
changed only in my.cnf.
Innodb_data_file_path:
By
default innodb creates single data file with 10MB and it can be auto extended
8M by default. We can set the file size more than 4GB on those operating that
supports big files.
If
disk partition gets filled by data file then we have commit all the transaction
and then move the old data file another location then restart the server. Now
new data file will be created in old location.
In
another way, change the location of data file but all transactions should be
committed and no data should be replicated.
Eg:
for single datafile, auto extend takes 8MB when datafile run out of space.
Innodb_data_file_path=ibdata1:10M:autoextend
Here
we can give two data files, auto extend can used for the last data file in the
line.
Innodb_data_file_path=ibdata1:10M;ibdata2:10M:autoextend
Here
we can limit the data file grow upto our needs
Innodb_data_file_path=ibdata1:10M;ibdata2:10M:autoextend:max:1024M
Innodb_data_home_dir:
The
full path of the data files can be obtained using this variable
Eg:
Innodb_data_home_dir=”E:/xampp/mysql/data/”
.
If we failed give the data file path then we give the path in the following
way.
Eg:
Innodb_data_home_dir=
Innodb_data_file_path=E:/xampp/mysql/data/ibdata1:10M;
E:/xampp/mysql/data/ibdata2:10M:autoextend
Innodb_doublewrite:
If
this variable is enabled, innodb stores all data twice, first to the
doublewrite buffer and then to the actual data files. By default this variable
is enabled.
It is needed to archive data
safety in case of partial page writes. If page is inconsistent recovery can’t
proceed.
For example out of 16K Innodb
page only first 4KB are updated and other parts remain in their former state. At
the time of power failure, the gets splitted so we cannot recover the data.
When using the double write we can easily recover.
Eg:
Innodb_doublewrite=ON
(default)
Innodb_fast_shutdown:
By
default the value is ‘1’, which causes fast shut down. If the value is ‘0’,
then innodb does full purge and an insert buffer merge before shut down. If the value is ‘1’, then innodb skips all
these operations at shut down. If the value is ‘2’, then innodb flush its log
and shut down cold.
Eg:
Innodb_fast_shutdown=1
(default)
Innodb_file_io_threads:
The
number of file io threads in innodb is ‘4 ‘by default. Increasing the number
may benefit in windows. Increasing the number has no effect in unix.
Eg:
Innodb_file_io_threads=4
(default)
Innodb_file_per_table:
Using
this variable, each table has its own tablespace. Using the multiple tablespace
we can easily move specific tables to separate disk. To enable multiple
tablespace
[Mysqld]
Innodb_file_per_table
This
option does not affect the already existing tables. For each newly creating
table, the tablespace file will created in tablename.ibd
along with tables. In the tablespace file the data and indexes were stored
together in tablename.ibd file.
To
move an .ibd file and associated tables from one database to another database,
we have to rename the table
mysql> rename table sar.t1 to
innodb.t1;
Query OK, 0 rows affected (0.09 sec)
Now
take a manual backup of .ibd file which you want to move.
mysql> alter table t1 discard
tablespace;
Query OK, 0 rows affected (0.09 sec)
Before
importing the tablespace, copy the .ibd file to desired db location.
mysql> alter table t1 import
tablespace;
Query OK, 0 rows affected (0.09 sec)
Innodb_flush_trx_at_commit:
By
default innodb uses ‘1’, it represents log buffer is written to log file after
transaction commits. If the value is ‘0’, the log buffer is written out to the
log file once per second and the flush to disk operation is performed on the
log file, but nothing is done at a transaction commit.
If
the value is ‘2’, log buffer is written out to log file on each commit. But
flush log files takes once per second. Flushing
to disk operation is not performed.
Eg:
When
we reduce the size of log file size and buffer size, the following error will
occur.
InnoDB: ib_logfiles are too small for
innodb_thread_concurrency 8.
InnoDB: The combined size of ib_logfiles
should be bigger than
InnoDB: 200 kB *
innodb_thread_concurrency.
Now we have to reduce the thread concurrency
according to number of cpu’s * 2
By
viewing the innodb status we can get the following details.
Log sequence number 0 3971450734
Log flushed up to 0 3971450734
Last checkpoint at 0 3971450734
0 pending log writes, 0 pending chkp
writes
8 log i/o's done, 0.40 log i/o's/second
In
this scenario, buffers can be flushed on each commit. If unflushed data is more
than 30% then we have change the mode of innodb_flush_trx_at_commit and also we
have to increase the size of innodb_log_buffer_size.
Unflushed data= (log sequence
number – log flushed)/1024
Innodb_max_dirty_pages_pct:
Innodb tries to write the pages
from buffer pool to disk. The uncommitted pages are marked as ‘dirty’. The
percentage of pages should not exceed 90%. These uncommitted pages should be
written to disk before shut down the server.
Eg:
Innodb_max_dirty_pages_pct=90
(default)
Innodb_flush_method:
On
windows, default flush method is ASYNC_UNBUFFERED and it cannot be changed. If
the flush method is O_DIRECT, then data files can be opened and uses fsync() to
flush both data and log files. It is available only in GNU/Linux versions
If
the flush method is O_DSYNC, then innodb uses O_SYNC to open and flush log
files and fsync() to flush data files.
Eg:
Innodb_flush_method=O_DIRECT
Innodb_lock_wait_timeout:
If
the transaction tries to access a row that is locked by another transaction,
then the transaction would wait for given lock wait timeout.
Eg:
Innodb_lock_wait_timeout=50
(default)
Innodb_log_buffer_size:
Innodb
uses log buffer for temporary usage, log buffer can be flushed to log file when
transaction commit occurs. By default log buffer size will be 8M, optimal value
ranges from 1M to 8M. For big transaction, larger log buffer saves the disk I/O
Eg:
Innodb_log_buffer_size=8M
(default)
Innodb_log_file_size:
When
there is any change in the database, then it will be written to log buffer.
From the log buffer, it will be flushed to log file at each commit. When
database is crashed, innodb recover the crash using log file. The uncommitted
data will be marked as ‘dirty’, before shutting down database it should be
written to disk. Number of log file will depend on innodb_log_files_in_group.
By default two log files will be created with 5MB.
Eg:
Innodb_log_file_size=5M
(default)
Innodb_log_files_in_group:
By
default innodb uses two log files. The maximum number of log files in group is
100.
Eg:
Innodb_log_files_in_group=2
(default)
Innodb_log_group_home_dir:
By
default innodb stores the log file in MySQL data directory. In innodb, data and
log file should be same directory.
Eg:
Innodb_log_group_home_dir="E:/xampp/mysql/data/"
Innodb_mirrored_log_groups:
The
number of identical copies of log groups to keep for the database. By default
this should be set as ‘1’.
Eg:
Innodb_mirrored_log_groups=1
(default)
Innodb_open_files:
If
we use multiple tablespace, then number of .ibd files should be open at a time
is 300 by default. The minimum number of data file open at a time is ‘10’. The
maximum number of files should be open at a time is 4294967295
Eg:
Innodb_open_files=300
(default)
Innodb_rollback_on_timeout:
Innodb
rollbacks only last statement on a transaction timeout by default. If we enable
this variable, then the entire transaction can be roll backed.
Eg:
Innodb_rollback_on_timeout=OFF
(default)
Innodb_max_purge_lag:
Innodb
maintains the list of transactions that have marked as delete, update and
insert operations in index.
Eg:
Trx id counter 0 1081344
Purge done for trx's n:o < 0 1080838 undo n:o < 0 0
History list length 20
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, OS thread id 1564
MySQL thread id 4, query id 16 localhost 127.0.0.1 root
show
engine innodb status
Here list length is number of
transaction allowed to be in queue. Unpurged transaction can be calculated
using the following formula.
Unpurged trxn=
current trxn – last purged trxn
Innodb_max_purge_lag=0
(no delay by default)
If we increase this variable then
delay will be increased in milliseconds.
Innodb_thread_concurrency:
By
default innodb sets this variable as 8. If number of operating exceeds the
limit given in this variable, then thread should be waiting stage in FIFO queue.
The optimal value of this variable is
Number
of CPU’s * 2 + plus disk
Innodb_thread_concurrency=8
(default)
When concurrent transaction
exceeds the limit, then this variable should be tuned. The thread delay time
can be set in innodb_thread_sleep_delay.
Innodb_thread_sleep_delay:
By
default innodb sets this variable as 10000, if concurrent transaction exceeds
the limit, and then thread should wait in queue.
Eg:
Innodb_thread_sleep_delay=10000
Innodb_commit_concurrency:
If
this variable is set to ‘0’ then innodb allows number of threads that can
commit simultaneously. By default the value is ‘0’ we can increase this value
upto 1000. This variable can be changed only in my.cnf.
Innodb_commit_concurrency=0
(default)
Innodb_concurrency_tickets:
When
a thread is permitted to enter innodb, then free tickets will be provided for
the thread waiting in queue. By default the value is ‘500’, when a thread
completes the process then it can be freed from innodb until it has used up its
tickets. This variable can be changed in global level.
Innodb_concurrency_tickets=500
(default)
mysql>
show global variables like 'innodb_concurrency_tickets';
+----------------------------+-------+
|
Variable_name | Value |
+----------------------------+-------+
|
innodb_concurrency_tickets | 510 |
+----------------------------+-------+
Innodb_table_locks:
By
default this variable is ‘OFF’, in this state we can lock the tables in any
mode. When we turn on this variable, then transaction should not be auto
committed.
Eg:
In
session-1,
mysql>
set innodb_table_locks=ON;
Query OK, 0 rows affected (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> lock table service_new write;
Query OK, 0 rows affected (0.22 sec)
In
session-2,
mysql>
select * from service_new;
Here
this transaction should wait, until the session releases the lock.
In
session-1,
mysql>
unlock tables;
Query
OK, 0 rows affected (0.00 sec)
In
session-2,
The
lock gets released.
Innodb_sync_spin_loops:
The number of times a thread waits for an InnoDB
mutex to be freed before the thread is suspended. The default value is 20. This
variable can only be changed globally.
If thread waiting for more number of seconds, then
we have to tune this variable.
Eg:
Innodb_sync_spin_loops=20
(default)
Here
spin waits should not be more than OS – waits. It should be balanced spin
rounds and OS – waits.
No comments:
Post a Comment