Spec-Zone .ru
спецификации, руководства, описания, API
|
|
Spec-Zone .ru
спецификации, руководства, описания, API
|
If you follow the best practices for database design and the tuning techniques for SQL operations, but your
database is still slowed by heavy disk I/O activity, explore these low-level techniques related to disk I/O. If
the Unix top tool or the Windows Task Manager shows that the CPU usage percentage
with your workload is less than 70%, your workload is probably disk-bound.
When table data is cached in the InnoDB buffer pool,
it can be processed over and over by queries without requiring any disk I/O. Specify the size of the
buffer pool with the innodb_buffer_pool_size option. This memory area is important enough
that busy databases often specify a size approximately 80% of the amount of physical memory. For more
information, see Section 8.9.1, "The InnoDB
Buffer Pool".
In some versions of GNU/Linux and Unix, flushing files to disk with the Unix fsync() call (which InnoDB uses by default) and
similar methods is surprisingly slow. If database write performance is an issue, conduct benchmarks with
the innodb_flush_method
parameter set to O_DSYNC.
When using the InnoDB storage engine on Solaris 10 for
x86_64 architecture (AMD Opteron), use direct I/O for InnoDB-related files,
to avoid degradation of InnoDB performance. To use direct I/O for an entire
UFS file system used for storing InnoDB-related files, mount it with the
forcedirectio option; see mount_ufs(1M). (The
default on Solaris 10/x86_64 is not to use this option.) To apply
direct I/O only to InnoDB file operations rather than the whole file
system, set innodb_flush_method = O_DIRECT. With this setting, InnoDB calls directio() instead of fcntl() for I/O to data files (not for I/O to log files).
When using the InnoDB storage engine with a large innodb_buffer_pool_size
value on any release of Solaris 2.6 and up and any platform (sparc/x86/x64/amd64), conduct benchmarks
with InnoDB data files and log files on raw devices or on a separate direct
I/O UFS file system, using the forcedirectio mount option as described
earlier. (It is necessary to use the mount option rather than setting innodb_flush_method if you want direct I/O for the log files.) Users
of the Veritas file system VxFS should use the convosync=direct mount
option.
Do not place other MySQL data files, such as those for MyISAM tables,
on a direct I/O file system. Executables or libraries must
not be placed on a direct I/O file system.
If you have additional storage devices available to set up a RAID configuration or symbolic links to different disks, Section 8.11.3, "Optimizing Disk I/O" for additional low-level I/O tips.
If throughput drops periodically because of InnoDB checkpoint operations,
consider increasing the value of the innodb_io_capacity configuration option. Higher values cause more
frequent flushing, avoiding the
backlog of work that can cause dips in throughput.
If the system is not falling behind with InnoDB flushing operations, consider
lowering the value of the innodb_io_capacity configuration option. Typically, you keep this
option value as low as practical, but not so low that it causes periodic drops in throughput as
mentioned in the preceding bullet. In a typical scenario where you could lower the option value, you
might see a combination like this in the output from SHOW ENGINE INNODB STATUS:
History list length low, below a few thousand.
Insert buffer merges close to rows inserted.
Modified pages in buffer pool consistently well below innodb_max_dirty_pages_pct of the buffer pool. (Measure at a
time when the server is not doing bulk inserts; it is normal during bulk inserts for the
modified pages percentage to rise significantly.)
Log sequence number - Last checkpoint is at
less than 7/8 or ideally less than 6/8 of the total size of the InnoDB log files.
Other InnoDB configuration options to consider when
tuning I/O-bound workloads include innodb_adaptive_flushing, innodb_change_buffer_max_size, innodb_change_buffering, innodb_flush_neighbors, innodb_log_buffer_size, innodb_log_file_size, innodb_lru_scan_depth, innodb_max_dirty_pages_pct, innodb_max_purge_lag, innodb_open_files, innodb_page_size, innodb_random_read_ahead, innodb_read_ahead_threshold, innodb_read_io_threads, innodb_rollback_segments, innodb_write_io_threads, and sync_binlog.