GNU Info
(mysql.info)InnoDB Consistent read example
An example of how the consistent read works in InnoDB
.....................................................
When you issue a consistent read, that is, an ordinary `SELECT'
statement, InnoDB will give your transaction a timepoint according to
which your query sees the database. Thus, if transaction B deletes a
row and commits after your timepoint was assigned, then you will not
see the row deleted. Similarly with inserts and updates.
You can advance your timepoint by committing your transaction and then
doing another `SELECT'.
This is called multiversioned concurrency control.
User A User B
set autocommit=0; set autocommit=0;
time
| SELECT * FROM t;
| empty set
| INSERT INTO t VALUES (1, 2);
|
v SELECT * FROM t;
empty set
COMMIT;
SELECT * FROM t;
empty set;
COMMIT;
SELECT * FROM t;
----------------------
| 1 | 2 |
----------------------
Thus user A sees the row inserted by B only when B has committed the
insert, and A has committed his own transaction so that the timepoint
is advanced past the the commit of B.
If you want to see the 'freshest' state of the database, you should use
a locking read:
SELECT * FROM t LOCK IN SHARE MODE;
Performance tuning tips
-----------------------
*1.* If the Unix `top' or the Windows `Task Manager' shows that the CPU
usage percentage with your workload is less than 70 %, your workload is
probably disk-bound. Maybe you are making too many transaction commits,
or the buffer pool is too small. Making the buffer pool bigger can
help, but do not set it bigger than 80 % of physical memory.
*2.* Wrap several modifications into one transaction. InnoDB must flush
the log to disk at each transaction commit, if that transaction made
modifications to the database. Since the rotation speed of a disk is
typically at most 167 revolutions/second, that constrains the number of
commits to the same 167/second if the disk does not fool the operating
system.
*3.* If you can afford the loss of some latest committed transactions,
you can set the `my.cnf' parameter `innodb_flush_log_at_trx_commit' to
zero. InnoDB tries to flush the log anyway once in a second, though the
flush is not guaranteed.
*4.* Make your log files big, even as big as the buffer pool. When
InnoDB has written the log files full, it has to write the modified
contents of the buffer pool to disk in a checkpoint. Small log files
will cause many unnecessary disk writes. The drawback in big log files
is that recovery time will be longer.
*5.* Also the log buffer should be quite big, say 8 MB.
*6.* (Relevant from 3.23.39 up.) In some versions of Linux and Unix,
flushing files to disk with the Unix `fdatasync' and other similar
methods is surprisingly slow. The default method InnoDB uses is the
`fdatasync' function. If you are not satisfied with the database write
performance, you may try setting `innodb_flush_method' in `my.cnf' to
`O_DSYNC', though O_DSYNC seems to be slower on most systems.
*7.* In importing data to InnoDB, make sure that MySQL does not have
`autocommit=1' on. Then every insert requires a log flush to disk. Put
before your plain SQL import file line
set autocommit=0;
and after it
commit;
If you use the `mysqldump' option `--opt', you will get dump files
which are fast to import also to an InnoDB table, even without wrapping
them to the above `set autocommit=0; ... commit;' wrappers.
*8.* Beware of big rollbacks of mass inserts: InnoDB uses the insert
buffer to save disk i/o in inserts, but in a corresponding rollback no
such mechanism is used. A disk-bound rollback can take 30 times the time
of the corresponding insert. Killing the database process will not help
because the rollback will start again at the database startup. The only
way to get rid of a runaway rollback is to increase the buffer pool so
that the rollback becomes CPU-bound and runs fast, or delete the whole
InnoDB database.
*9.* Beware also of other big disk-bound operations. Use `DROP TABLE'
or `TRUNCATE' (from MySQL-4.0 up) to empty a table, not `DELETE FROM
yourtable'.
*10.* Use the multi-line `INSERT' to reduce communication overhead
between the client and the server if you need to insert many rows:
INSERT INTO yourtable VALUES (1, 2), (5, 5);
This tip is of course valid for inserts into any table type, not just
InnoDB.
The InnoDB Monitor
..................
Starting from version 3.23.41 InnoDB includes the InnoDB Monitor which
prints information on the InnoDB internal state. When swithed on,
InnoDB Monitor will make the MySQL server to print data to the standard
output about once every 10 seconds. This data is useful in performance
tuning.
The printed information includes data on:
* table and record locks held by each active transaction,
* lock waits of a transactions,
* semaphore waits of threads,
* pending file i/o requests,
* buffer pool statistics, and
* purge and insert buffer merge activity of the main thread of
InnoDB.
You can start InnoDB Monitor through the following SQL command:
CREATE TABLE innodb_monitor(a int) type = innodb;
and stop it by
DROP TABLE innodb_monitor;
The `CREATE TABLE' syntax is just a way to pass a command to the InnoDB
engine through the MySQL SQL parser: the created table is not relevant
at all for InnoDB Monitor. If you shut down the database when the
monitor is running, and you want to start the monitor again, you have
to drop the table before you can issue a new `CREATE TABLE' to start
the monitor. This syntax may change in a future release.
A sample output of the InnoDB Monitor:
================================
010809 18:45:06 INNODB MONITOR OUTPUT
================================
--------------------------
LOCKS HELD BY TRANSACTIONS
--------------------------
LOCK INFO:
Number of locks in the record hash table 1294
LOCKS FOR TRANSACTION ID 0 579342744
TABLE LOCK table test/mytable trx id 0 582333343 lock_mode IX
RECORD LOCKS space id 0 page no 12758 n bits 104 table test/mytable index
PRIMARY trx id 0 582333343 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 74; 1-byte offs FALSE;
info bits 0
0: len 4; hex 0001a801; asc ;; 1: len 6; hex 000022b5b39f; asc ";; 2: len 7;
hex 000002001e03ec; asc ;; 3: len 4; hex 00000001;
...
-----------------------------------------------
CURRENT SEMAPHORES RESERVED AND SEMAPHORE WAITS
-----------------------------------------------
SYNC INFO:
Sorry, cannot give mutex list info in non-debug version!
Sorry, cannot give rw-lock list info in non-debug version!
-----------------------------------------------------
SYNC ARRAY INFO: reservation count 6041054, signal count 2913432
4a239430 waited for by thread 49627477 op. S-LOCK file NOT KNOWN line 0
Mut ex 0 sp 5530989 r 62038708 sys 2155035; rws 0 8257574 8025336; rwx 0 1121090 1848344
-----------------------------------------------------
CURRENT PENDING FILE I/O'S
--------------------------
Pending normal aio reads:
Reserved slot, messages 40157658 4a4a40b8
Reserved slot, messages 40157658 4a477e28
...
Reserved slot, messages 40157658 4a4424a8
Reserved slot, messages 40157658 4a39ea38
Total of 36 reserved aio slots
Pending aio writes:
Total of 0 reserved aio slots
Pending insert buffer aio reads:
Total of 0 reserved aio slots
Pending log writes or reads:
Reserved slot, messages 40158c98 40157f98
Total of 1 reserved aio slots
Pending synchronous reads or writes:
Total of 0 reserved aio slots
-----------
BUFFER POOL
-----------
LRU list length 8034
Free list length 0
Flush list length 999
Buffer pool size in pages 8192
Pending reads 39
Pending writes: LRU 0, flush list 0, single page 0
Pages read 31383918, created 51310, written 2985115
----------------------------
END OF INNODB MONITOR OUTPUT
============================
010809 18:45:22 InnoDB starts purge
010809 18:45:22 InnoDB purged 0 pages
Some notes on the output:
* If the section LOCKS HELD BY TRANSACTIONS reports lock waits, then
your application may have lock contention. The output can also
help to trace reasons for transaction deadlocks.
* Section SYNC INFO will report reserved semaphores if you compile
InnoDB with <code>UNIV_SYNC_DEBUG</code> defined in
<tt>univ.i</tt>.
* Section SYNC ARRAY INFO reports threads waiting for a semaphore
and statistics on how many times threads have needed a spin or a
wait on a mutex or a rw-lock semaphore. A big number of threads
waiting for semaphores may be a result of disk i/o, or contention
problems inside InnoDB. Contention can be due to heavy parallelism
of queries, or problems in operating system thread scheduling.
* Section CURRENT PENDING FILE I/O'S lists pending file i/o
requests. A large number of these indicates that the workload is
disk i/o -bound.
* Section BUFFER POOL gives you statistics on pages read and
written. You can calculate from these numbers how many data file
i/o's your queries are currently doing.
automatically generated by info2www version 1.2.2.9