# Innodb optimization suggestion requested **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [October 8, 2007, 2:50pm UTC](https://forums.percona.com/t/innodb-optimization-suggestion-requested/484 "2007-10-08T14:50:27Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![danciulinaru](https://avatars.discourse-cdn.com/v4/letter/d/ba9def/32.png) [@danciulinaru](https://forums.percona.com/u/danciulinaru)\ **Post date:** [October 8, 2007, 2:50pm UTC](https://forums.percona.com/t/innodb-optimization-suggestion-requested/484/1 "2007-10-08T14:50:27Z")
Hi there, I have these Innodb settings: ### Innodb innodb\_file\_per\_table innodb\_data\_home\_dir = /data/mysql innodb\_data\_file\_path = ibdata1:10M:autoextend innodb\_log\_group\_home\_dir = /data/mysql #innodb\_buffer\_pool\_size = 1024M innodb\_buffer\_pool\_size = 2560M innodb\_additional\_mem\_pool\_size = 16M innodb\_log\_buffer\_size = 8M innodb\_log\_file\_size = 64M innodb\_log\_files\_in\_group = 2 #innodb\_flush\_log\_at\_trx\_commit = 0 innodb\_flush\_log\_at\_trx\_commit = 2 sync\_binlog=1 innodb\_lock\_wait\_timeout = 50 transaction\_isolation=read-committed innodb\_thread\_concurrency=6 innodb\_table\_locks=0 innodb\_status\_file=1 This is the version: mysql\> select version(); ±-------------------+ | version() | ±-------------------+ | 5.0.17-pro-gpl-log | ±-------------------+ 1 row in set (0.00 sec) This is the memory: [[linux@db117](mailto:linux@db117) ~]$ free -m total used free shared buffers cached Mem: 7972 7951 21 0 52 4724 -/+ buffers/cache: 3174 4798 Swap: 2047 11 2035 These is the CPU info: [[linux@db117](mailto:linux@db117) ~]$ cat /proc/cpuinfo | egrep “processor|vendor\_id|model name|cpu MHz|cpu cores” processor : 0 vendor\_id : GenuineIntel model name : Intel(R) Xeon(R) CPU 5160 @ 3.00GHz cpu MHz : 2992.540 cpu cores : 2 processor : 1 vendor\_id : GenuineIntel model name : Intel(R) Xeon(R) CPU 5160 @ 3.00GHz cpu MHz : 2992.540 cpu cores : 2 processor : 2 vendor\_id : GenuineIntel model name : Intel(R) Xeon(R) CPU 5160 @ 3.00GHz cpu MHz : 2992.540 cpu cores : 2 processor : 3 vendor\_id : GenuineIntel model name : Intel(R) Xeon(R) CPU 5160 @ 3.00GHz cpu MHz : 2992.540 cpu cores : 2 The plot: By default, MySQL was running with innodb\_flush\_log\_at\_trx\_commit = 1 which led to performance problems for even simple DML statements(i.e. delete from table where id = …, id being a primary or having an index) I set innodb\_flush\_log\_at\_trx\_commit = 0, which really helped , being able to sustain the load, but now mysql is prone to lose data in case it crashes (and I’ve been told it’s not acceptable that we could lose transactions). The so called happy compromise it the I’ve set it to innodb\_flush\_log\_at\_trx\_commit = 2, which in theory, mysql should be able not to lose any transactions as long as a transaction made it to log\_file. If it crashes, MySQL will roll-back or commit whatever it finds in the log\_file. The questions: 1. How reliable is this process? Should I risk and go with innodb\_flush\_log\_at\_trx\_commit = 2 and count on the fact MySQL/innodb will be reliable enough to commit those changes found in the log\_file or just use the …trx\_commit=1 and pray I will not face bigger loads? 2. In case I have to go with innodb\_flush\_log\_at\_trx\_commit = 1, what workarounds do I have to further optimize Innodb and make it speedier? Increasing the allocated memory for the innodb\_buffer\_pool\_size is not an option. The developers are using some g.d. triggers that have the habit of leaking memory and this eventually leads to a MySQL instance crash. The current value of 2560M seems to lead to a fairly stable instance. Taking out the triggers is also not option (a coliding course with developers’ ego/insufficient human resources(?!)). Waiting for suggestions, Thanks ---
**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\ **Post date:** [October 8, 2007, 3:49pm UTC](https://forums.percona.com/t/innodb-optimization-suggestion-requested/484/2 "2007-10-08T15:49:52Z")
If the innodb\_flush\_log\_at\_trx\_commit=0 sped things up for you then it indicates that your application is performing a lot of insert/updates. The problem here is that if you are using: innodb\_flush\_log\_at\_trx\_commit=1 sync\_binlog=1 then you have two flushes that has to happen to disk for each transaction. And the problem with this is that if you don’t have a RAID card with battery backed up cache (or the OS is faking sync’s, but then you wont have any use for it) is that flushing data to disk after each transaction slows things down a _lot_ due to seek times for the disk. And in this case if you haven’t separated the InnoDB log files and the binary log to different disks then they will compete for the disk time. What you can do: 1. Buy a RAID card with battery backed up cache. 1. Separate InnoDB log files on one disk, tablespace on another and binary log on a third. 1. Change the application (if possible) to run longer transactions to avoid the disk writes. 1. Skip sync\_binlog=1, do you actually need it or is it ok to manually sync the master and slave on those rare occasions if they end up out of sync due to this? 1. Run with innodb\_flush\_log\_at\_trx\_commit = 2 The thing is that with this setting an OS crash can cause data loss. But if you have a good UPS with lot of batteries and you got a stable OS and a new solid hardware. How often does this kind of machine crash (as long as you don’t let any developers have shell access to it 😉 )? The answer is so very seldom, so that usually when you have to trade performance for solidity then this setting is quite enough. Otherwise I suggest buying the RAID card. ---
**Author:** ![danciulinaru](https://avatars.discourse-cdn.com/v4/letter/d/ba9def/32.png) [@danciulinaru](https://forums.percona.com/u/danciulinaru)\ **Post date:** [October 8, 2007, 4:42pm UTC](https://forums.percona.com/t/innodb-optimization-suggestion-requested/484/3 "2007-10-08T16:42:06Z")
Thanks for the reponse. Phasing out sync\_binlog=1 is giving me some degree of restlessness ) . The application is suppose to rely on the slave in case of master-slave switch, so having data consistency is a must. 1. Ain’t gonna happen to soon, at least not before the application launches. 2. It’s the next logical step, will do. 3. Almost impossible, I have to check out though. 4. Might take it out eventually, especially that I got used to idea that I have to manually reseed the slave. Would have been nice though, if reliability would’ve been the first objective MySQL AB struggle to meet for their product and then speed/performance. sync\_binlog=0 is a ticking bomb. 5. Is there a test case that I could build in order to show other people around that innodb\_flush\_log\_at\_trx\_commit = 2 is REALLY able to recover all the commmited transaction after a mysql crash? Otherwise, I don’t really trust MySQL/innodb what it says in the manual. And yes, developers have access to the database but so far the machine didn’t experience any crash. Don’t ask me how this didn’t happen though. 6. I case I have to go with innodb\_flush\_log\_at\_trx\_commit = 1, how can I mess with innodb\_log\_buffer\_size/innodb\_log\_file\_size in order to provide the minimum the performance increase and meet the load? Provided that I implement the innodb log file/binlogs/data separation you suggested.