… innodb_flush_method = O_DIRECT innodb_max_dirty_pages_pct = 50 innodb_io_capacity = 800 innodb_read_io_threads = 8 innodb_write_io_threads = 4 innodb_file… cause read performance to improve, but they have an impact on the write performance. Well most of the apps rely on read performance and…
Post: Testing Samsung SSD SATA 256GB 830 - not all SSD created equal
April 25, 2012 By 10 Comments
… is the most popular. I do not particularly like its write performance – I wrote about it before, that’s why I am… mode. There are two reasons for that. First, MySQL/InnoDB uses async writes, so this will emulate database load, and second, async… with nobarrier. The results for random write case (8 async IO threads): It seems that InnoDB is not alone with its flashing…
Post: Testing STEC SSD MACH16 200GB SLC
April 25, 2012 By 7 Comments
… use sysbench fileio, 16KiB block size (to match workload from InnoDB, as this is primary usage for me), and recently I… mode. There are two reasons for that. First, MySQL/InnoDB uses async writes, so this will emulate database load, and second, async… gather metrics every 10 sec to see how stable the performance is, and it really helps to observe some artifacts, as…
Post: Load management Techniques for MySQL
May 6, 2012 By 3 Comments
One of the very frequent cases with performance problems with MySQL is what they happen every so often … I would not use more than 4 parallel processes heavily writing to database. Introduce Throttling Sometimes even single process overloads system…
Comment: Benchmarking single-row insert performance on Amazon EC2
May 16, 2012 By 11 Comments
…Andy, I am not testing IO performance and instead trying to optimize writes, also the durability requirements are not that stringent and… second worth of data, hence I did not test with innodb_flush_log_at_trx_commit=1. I would also not recommend running on EBS with innodb_flush_log_at_trx_commit=1, because IO times on…
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
… most important features of InnoDB is the row level locking. This feature provides better concurrency under heavy write load but needs additional… UPDATE. InnoDB provides REPEATABLE READ for read-only SELECT, but it behaves as if you use READ COMMITTED for all write queries… gaps locks in your transactions affecting the concurrency and the performance you can disable them in two different ways: 1- Change…
Post: ext4 vs xfs on SSD
March 15, 2012 By 23 Comments
… write workload): ext4 1 thread: 87 MiB/sec ext4 4 threads: 74 MiB/sec xfs 4 threads: 97 MiB/sec Dropping performance… one more point to consider. Starting the MySQL 5.1 + InnoDB-plugin and later MySQL 5.5 (or equally Percona Server… asked, namely: “If MySQL 5.5 uses async IO, is innodb_write_io_threads still important?”, and it seems it is not…
Comment: Should you move from MyISAM to Innodb ?
March 10, 2012 By 53 Comments
… that is) and that if not using InnoDB you aren’t too smart. However, InnoDB brings a lot of cons to the…. If you have a huge number of concurrent writes and selects and query performance must be consistently fast in this use case… your problems. In fact there are other engines better than InnoDB for certain usage patterns – if you go down this path…
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
… use mysqldump –single-transaction to get consistent backup for their Innodb tables without making database read only. In most cases it… with DDL, In particular ALTER TABLE. When ALTER TABLE is Performed in many cases it will Create temporary table with modified… ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; — – Dumping data for table `C` — LOCK TABLES `C` WRITE…
Comment: INSERT INTO ... SELECT Performance with Innodb tables.
March 7, 2012 By 21 Comments
…am using AWS MySQL RDS, InnoDB engine. Our requirement is to insert data in same … column of composite (5 columns) primary key). Reading and Writing in same table. For 22,518,912 rows it… reduce further? Is there anyway I can improve the performance? We can use OUTFILE method as suggested by Peter …
