Solved

mysql write to log and flush every second

Posted on 2010-11-25
8
709 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:pvinodp
  • 4
  • 3
8 Comments
 
LVL 77

Expert Comment

by:arnold
ID: 34215652
What do you mean?  You can enable log-bin and it will be written to by mysql for every transaction.
0
 
LVL 26

Accepted Solution

by:
ushastry earned 500 total points
ID: 34215673
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
 

Author Comment

by:pvinodp
ID: 34215749
So does this make sure that the effect of hitting the disk once a second instead of thrashing it continuously?
0
 
LVL 26

Expert Comment

by:ushastry
ID: 34215759
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 26

Expert Comment

by:ushastry
ID: 34215764
I'll be away for couple of hours from now and expect a delay in reply.

Thanks,
Umesh
0
 

Author Comment

by:pvinodp
ID: 34216471
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
 
LVL 26

Expert Comment

by:ushastry
ID: 34216715
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
 

Author Closing Comment

by:pvinodp
ID: 34229143
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Runtime 3044 error 14 35
Working with SharePoint Data 8 46
dbcc checkdb datawarehouse 2 40
ODBC Question 3 20
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now