… how. Let’s simulate the accident. For that I will delete /var/lib/mysql/ib* files, while sysbench generates read/write activity… make MySQL backup by just copying the files. So, we have to make sure all modifications are written to the disk. For…: FILE I/O ——– I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… of MySQL 5.6 and MariaDB 5.5. Note that query cache is disabled during these benchmark runs and that the disks are 4 5.4K disks in Software RAID5. Also note that the following changes were made in the MySQL config… ref rows filtered Extra 1 SIMPLE orders range PRIMARY,i_o_orderdate,i_o_custkey i_o_orderdate 4 NULL 232722 100.00 Using where; Rowid…
Post: MySQL performance on EC2/EBS versus RDS
August 4, 2011 By 11 Comments
… as compared to directly attached disks or SSD devices. InnoDB doesn’t handle this very well, because I/O tends to be a…, you might cause a lot of I/O, and there is a chance that will cause MySQL to perform slowly or unpredictably. For… a lot of I/O, performance can be quite acceptable or even very good. Do you have experience running MySQL in the Amazon…
Post: Death match! EBS versus SSD price, performance, and QoS
February 21, 2011 By 55 Comments
… trouble at other times. In this blog post I want to examine cloud-based I/O. I/O matters a lot when a) the database… getting from its disks. I‘m using the Aspersa diskstats tool to pull this data straight from /proc/diskstats. I‘ll aggregate over… same data to look at it disk-by-disk, aggregated over the entire 30 seconds that I captured samples of /proc/diskstats. Here…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 19 Comments
…a time. On Windows, InnoDB uses native async I/O, and can perform many read-aheads at the… out MySQL. There are two solutions here. Memlock and O_DIRECT. Memlock is apparently unstable on Linux. O_DIRECT…perfectly serial it’s having to seek on disk which slows down throughput. If we could control…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…). Starting to look at MySQL Assuming I don’t see anything noteworthy here, I‘ll move on to the MySQL server. I mean, let’s… example, when I‘m examining the status values for Created_tmp_tables and Created_disk_tmp_tables, I‘ll paste in mysql> show global… important to know a lot about the I/O subsystem. And by now I know that I should concentrate in certain areas — there may…
Post: MySQL opening .frm even when table is in table definition cache
November 21, 2011 By 8 Comments
… from strace: [pid 19876] open(“./db/t1.frm”, O_RDONLY) = 32 <0.000013> [pid 19876] read(32, “”…, 10)… DROP VIEW open table filling INFORMATION_SCHEMA tables (I think it is actually the TABLES table, but… of this bit of code is that MySQL may cause unnecessary disk IO for information it already has …
Comment: What to tune in MySQL Server after installation
November 30, 2012 By 118 Comments
I am working with MyISAM tables inserting millions of rows on Windows. MySQL writes to disk using very small I/O chunks. I am using a simple script inserting data into a table and monitoring disk I/O and I see that every I/O…
Post: Virtualization and IO Modes = Extra Complexity
March 21, 2011 By 12 Comments
… is no disk seek we need to wait for disk head to make a full round to do a rotation. I‘m however…. Calling fsync() at the end of test, Enabled. Using synchronous I/O mode Doing random write test Threads started! Time limit exceeded…. Calling fsync() at the end of test, Enabled. Using synchronous I/O mode Doing random write test Threads started! Time limit exceeded…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… tables… Optimize For I/O It is pretty much inevitable: if you do this kind of data processing in MySQL, you’re going to end up heavily I/O bound. Listen to any of the talks at past MySQL conferences…’t store as compactly on disk as [Compressed] MyISAM or Archive. These are just some ideas I‘m throwing around — the point…
