… 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… query cache to avoid contention caused by query cache on write heavy workload. OK, so that was all about the configuration of… = InnoDB, PARTITION p10 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ Also, I ran 5 instances of iiBench simultaneously to simulate 5 concurrent connections writing…
Post: Testing Samsung SSD SATA 256GB 830 - not all SSD created equal
April 25, 2012 By 10 Comments
… tests I use sysbench fileio, 16KiB block size (to match workload from InnoDB, as this is primary usage for me), and recently… 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
… tests I use sysbench fileio, 16KiB block size (to match workload from InnoDB, as this is primary usage for me), and recently… mode. There are two reasons for that. First, MySQL/InnoDB uses async writes, so this will emulate database load, and second, async… G6, filesystem: ext4, mounted with nobarrier. The results for random write case (8 async IO threads): In general it shows stable…
Post: Load management Techniques for MySQL
May 6, 2012 By 3 Comments
… I would not use more than 4 parallel processes heavily writing to database. Introduce Throttling Sometimes even single process overloads system…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… complete locking tables. This means if you have workload which includes some very long SELECT queries you… way or another. If you’re just using Innodb tables and you’re not actively changing users,…slave with –safe-slave-backup option, as preventing writes to the slave can be done by simply pausing …
Post: ext4 vs xfs on SSD
March 15, 2012 By 23 Comments
…” with O_DIRECT case (sysbench fileio 16 KiB blocksize random write workload): ext4 1 thread: 87 MiB/sec ext4 4 threads: 74… 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…
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
… ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*!40101 SET character_set_client = @saved_cs_client */; — – Dumping data for table `C` — LOCK TABLES `C` WRITE… scenario neither the less it can be problem for some workloads which run ALTER TABLE regularly during normal operation. I also…
Post: Tuning for heavy writing workloads
October 14, 2009 By 9 Comments
…. It should be also tuning procedure for general heavy writing workloads. At first,
Post: Analyzing the distribution of InnoDB log file writes
July 16, 2010 By 5 Comments
… distribution of writes to InnoDB‘s log files. On a high-traffic commodity MySQL server running Percona XtraDB for a gaming workload (mostly… gather statistics about how the log file writes are distributed in terms of write size. InnoDB writes to the log files in multiples of… 3/4ths of InnoDB log file writes on this workload are 512 or 1024 bytes. (It might vary for other workloads.) Now, what does…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
… all of them are Innodb specific and it well can affect your workload. So benchmark to be sure. In Innodb besides scalability changes… memory for write buffering efficiently. Q30: Question about using solid state disks for storing ib_log* files; I know that innodb writes files in the log file group in a round robin fashion. I also know that there is some writing that…
