Dyntex.net Support Centre: MySQL Reference Manual p.524

<< previous page -- table of contents -- next page >>

Chapter_1

Chapter_2

Chapter_3

Chapter_4

Chapter_5

Return to Table of Contents

Table of Contents
1. General Information
2. MySQL Installation
3. Tutorial Introduction
4. Database Administration
5. MySQL Optimisation
6. MySQL Language Reference
7. MySQL Table Types
8. MySQL APIs
9. Extending MySQL

524 MySQL Technical Reference for Version 4.0.3 making too many transaction commits, or the buer pool is too small. Making the buer pool bigger can help, but do not set it bigger than 80% of physical memory. 2. Wrap several modications into one transaction. InnoDB must ush the log to disk at each transaction commit, if that transaction made modications 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 aord 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 ush the log any- way once in a second, though the ush is not guaranteed. 4. Make your log les big, even as big as the buer pool. When InnoDB has written the log les full, it has to write the modied contents of the buer pool to disk in a checkpoint. Small log les will cause many unnecessary disk writes. The drawback in big log les is that recovery time will be longer. 5. Also the log buer should be quite big, say 8 MB. 6. (Relevant from 3.23.39 up.) In some versions of Linux and Unix, ushing les 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 satised 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 ush to disk. Put before your plain SQL import le line SET AUTOCOMMIT=0; and after it COMMIT; If you use the `mysqldump' option --opt, you will get dump les 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 buer 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 buer 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. 7.5.9.1 The InnoDB Monitor Starting from version 3.23.41 InnoDB includes the InnoDB Monitor which prints infor- mation on the InnoDB internal state. When swithed on, InnoDB Monitor will make the

MySQL Reference Manual

Our Partners

SpiritHost - web hosting for spiritual and education sites
SpiritHit.com - Religious and Spiritual Portal

Return to Table of Contents	Back to top

Web Hosting: Manuals & FAQ's

1. Unix-Based Web Hosting
2. Unix Dedicated Servers
3. Windows Dedicated Servers
4. CuteFTP User’s Guide
5. CuteHTML User’s Guide
6. WS_FTP Pro User's Guide
7. Miva Order User's Guide
8. Miva Merchant User's Guide