mysql write to log and flush every second

I have to set the log of mysql as write to log and flush every second.
Can you tell me which file do i need to change and what should be the input?

THanks
pvinodpAsked:
Who is Participating?
 
UmeshConnect With a Mentor MySQL Principle Technical Support EngineerCommented:
Set the value of sync_binlog to 1 - Manual says - If the value of this variable is greater than 0, the MySQL server synchronizes its binary log to disk (using fdatasync()) after every sync_binlog writes to the binary log. There is one write to the binary log per statement if autocommit is enabled, and one write per transaction otherwise. The default value of sync_binlog is 0, which does no synchronizing to disk. A value of 1 is the safest choice because in the event of a crash you lose at most one statement or transaction from the binary log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast).

This is a dynamic variable so can be set using below sql statement on mysql prompt and for making it to permanent add it to to conf file..

SET GLOBAL sync_binlog=1;
0
 
arnoldCommented:
What do you mean?  You can enable log-bin and it will be written to by mysql for every transaction.
0
 
pvinodpAuthor Commented:
So does this make sure that the effect of hitting the disk once a second instead of thrashing it continuously?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
UmeshMySQL Principle Technical Support EngineerCommented:
Yes.. it gurantees that.
But this safety comes with a high price, Synchronizing the bin log and trasaction log  requires MySQL to flush 2 files in two distinct locations.
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
I'll be away for couple of hours from now and expect a delay in reply.

Thanks,
Umesh
0
 
pvinodpAuthor Commented:
ok i shall remove the line
log = /tmp/mysql.log
 from my.cnf???

Then it will be only one log written. Will this stop the writing of log ot 2 places.???
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
log parameter enables "general query log" ..The "mysqld" records information to this log when clients connect or disconnect, and it logs each SQL statement received from clients. The general query log can be very useful when you suspect an error in a client and want to know exactly what the client sent to mysqld.

In my earlier comment; I was refering to the binary log and trasactional log(redo and undo)  -  to understand the trasactional log and its use I would suggest you to read this very nice article http://www.pythian.com/news/1337/   

0
 
pvinodpAuthor Commented:
In addition to setting
sync_binlog=0; [makes sure theres is no sync mandated]
innodb_flush_log_at_trx_commit =0
set the above values in my.cnf
restart mysqld service after this
 [Command: service mysqld restart]


refer for easy understanding of the above parametres:
http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html

http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/
0
All Courses

From novice to tech pro — start learning today.