MySQL, ntpd, and kern.timecounter

[Lowell Gilbert]

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.

> 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). 

> 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.