MySQL, ntpd, and kern.timecounter

[John Nielsen]

On Wednesday 07 June 2006 08:15, Lowell Gilbert wrote:
> John Nielsen <lists at jnielsen.net> writes:
> > I have a FreeBSD 6.1 machine set up as a web and MySQL database server.
> > Since the application is a bit database-intensive, I followed several of
> > the MySQL tuning recommendations from this page:
> >
> > http://wikitest.freebsd.org/MySQL
> >
> > One of those was to change kern.timecounter.choice from ACPI-fast to TSC.
> >
> > That was fine for MySQL, but the real-world timekeeping on this hardware
> > with TSC is so bad that it broke ntpd and the clock started drifting
> > several seconds every hour. Timekeeping with ACPI-fast was quite
> > reliable.
> >
> > I'm looking for recommendations in general, but I'll pose a few specific
> > questions below as well.
> >
> > Should I change the timecounter back? How big an impact does the choice
> > of timecounter have on performance with MySQL 4.1.19 and FreeBSD 6.1? Is
> > there a conservative way I can answer this question myself for a server
> > that's already in production?
>
> Benchmarking on a live system is tough.  You can switch the
> timecounter back and forth easily enough, but measuring performance
> requires a predictable load.
>
> I don't know anything about mysql in particular, but on a fast
> machine, with the database as the primary application, I wouldn't
> expect choice of clock tick to affect the performance very much.

This seems to be a "feature" of MySQL (on FreeBSD) in particular (see the wiki 
page above). I was hoping someone would have a general idea of the impact of 
the clock choice on FreeBSD 6.1 and Mysql 4.1.

> > Can ntpd be coaxed into working with such bad timekeeping (as long as
> > it's consistently bad)?
>
> You're not using a driftfile?  That should compensate for systematic
> drift pretty well.  You just specify the file (which ntpd has to be
> able to write) in the configuration file for ntpd (/etc/ntp.conf by
> default).

I always use a driftfile.  With the acpi timecounter it behaves fine (it's 
currently -31.176).  With the TSC timecounter ntpd stops working after the 
second server sync, probably when it realizes how much the clock has drifted 
in the interval since the first sync.  Some things I was reading suggested 
that ntp's maximum supported drift is +/- 500 PPM, and with TSC this system's 
drift would be between -800 and -4000 PPM (I'm guessing). I was wondering if 
the 500 was a hard limit or if there's some kind of workaround.

> > Would Bad Things happen if I ran ntpdate or ntpd -q once or twice a day?
> > Would this be considered an abuse of the ntp server(s)? Would I run a
> > risk of confusing / breaking cron or sendmail or syslogd or anything else
> > with the time jumps?
>
> Nothing horrible would happen, but it could be annoying.  I'd
> recommend you avoid it.

My feeling as well.  Thanks for the response!

JN