Optimizing InnoDB Disk I/O
来源:互联网 发布:农业大数据平台 编辑:程序博客网 时间:2024/05/16 04:42
本文转载自:http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-diskio.html
Optimizing InnoDB
Disk I/O
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 Unixtop
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 theinnodb_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, seeSection 8.9.1, “TheInnoDB
Buffer Pool”.In some versions of GNU/Linux and Unix, flushing files to disk with the Unix
fsync()
call (whichInnoDB
uses by default) and similar methods is surprisingly slow. If database write performance is an issue, conduct benchmarks with theinnodb_flush_method
parameter set toO_DSYNC
.When using the
InnoDB
storage engine on Solaris 10 for x86_64 architecture (AMD Opteron), use direct I/O forInnoDB
-related files, to avoid degradation ofInnoDB
performance. To use direct I/O for an entire UFS file system used for storingInnoDB
-related files, mount it with theforcedirectio
option; seemount_ufs(1M)
. (The default on Solaris 10/x86_64 isnot to use this option.) To apply direct I/O only toInnoDB
file operations rather than the whole file system, setinnodb_flush_method = O_DIRECT
. With this setting,InnoDB
callsdirectio()
instead offcntl()
for I/O to data files (not for I/O to log files).When using the
InnoDB
storage engine with a largeinnodb_buffer_pool_size
value on any release of Solaris 2.6 and up and any platform (sparc/x86/x64/amd64), conduct benchmarks withInnoDB
data files and log files on raw devices or on a separate direct I/O UFS file system, using theforcedirectio
mount option as described earlier. (It is necessary to use the mount option rather than settinginnodb_flush_method
if you want direct I/O for the log files.) Users of the Veritas file system VxFS should use theconvosync=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 librariesmust 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.
注:近期参加MySQL运维学习,老师推荐该文章作为学习和技术提高的扩展阅读,先记录到自己的博客中,随后慢慢消化、学习、提高。本文与MySQL数据库 “性能优化”主题有关。
- Optimizing InnoDB Disk I/O
- 8.5.8 Optimizing InnoDB Disk IO 优化InnoDB Disk I/O
- 14.10.1 InnoDB Disk I/O
- Measuring & Optimizing I/O Performance
- Understanding Disk I/O
- disk i/o error
- linux disk I/O tuning?
- Disk file operations I/O
- Oracle sequential read disk I/O tuning
- Android6.0 Disk I/O error
- Throttle The Disk I/O Rate: Limit disk I/O For rsync Tool
- Xen Disk I/O: from guest domain to real devices
- Understanding Disk I/O - when should you be worried?
- 开机出现“Disk I/O error”的故障解决
- Understanding Disk I/O - when should you be worried?
- android.database.sqlite.SQLiteDiskIOException: disk I/O error: COMMIT
- 开机启动提示“Disk I/O error”故障怎么办
- ORA-27603: Cell storage I/O error, I/O failed on disk ORA-27626: Exadata error:
- 黑马程序员-java基础
- 在linux下运行sfml example
- 使用bootstrap设计的后台管理界面
- Configuring the Rate of InnoDB Buffer Pool Flushing
- K&R快速排序的解释
- Optimizing InnoDB Disk I/O
- 十个iOS面试问题
- jemalloc
- 【Leetcode长征系列】Single Number
- 设计并实现数据仓库ETL过程(IBM讲座)
- base64二进制图片在JSP页面解析
- 解决ajax返回乱码的问题
- mujyk
- Impact of memory allocators on MySQL performance