Daemon News Ezine BSD News BSD Mall BSD Support Forum BSD Advocacy BSD Updates

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: MySQL and FreeBSD native threads

Bryan C. Hahn wrote:
On Sat, 14 Jan 2006, Justin Bastedo wrote:

I've been using MySQL 5 now for about 6 months in a productin
environment and haven't had any problems. Our co-located master server
replicates to an inhouse slave. and neither has given us any problems.

Uptime: 6355651 Threads: 2 Questions: 143125480 Slow queries: 0 Opens:
0 Flush tables: 2 Open tables: 55 Queries per second avg: 22.519


5:42PM  up 129 days,  4:17, 1 user, load averages: 0.03, 0.03, 0.00

seeing a mild load, nothing too heavy, and mainly inserts and selects
and has been really stable.



I'm glad to hear you're having success with this MySQL 5 and FreeBSD
combination.  We'd really love to hear what your configuration is:

 * Is mysql installed from ports?  If so, what knobs did you twist?

 * What threading configuration are you using?  (Linux, native, etc.)

 * Anything special in the my.cnf file?

 * Are you using Innodb?  BDB?

 * Exactly what version of FreeBSD?

 * SMP or UP?  AMD or Intel?  Motherboard?  Amount of memory?

 * RAID?  If so, ATA or SATA or SCSI?  What controller and
   RAID configuration?

 * Is the slave configuration roughly the same?

We have almost no experience with MySQL 4 or 5 on any version of FreeBSD.
We have been successfully using MySQL 3.23 on both FreeBSD 4 and 5
*without* threading.

Obviously for us, upgrading MySQL would be a good thing, and finding a
suitable, reliable configuration would be even better.

  - Bryan

Hi, I've been using MySQL 4.1 on FreeBSD 6.0 (UP and SMP) always with native threads. On one project, I am using 4.1.16 on SMP FreeBSD 6.0 (IBM eServer x336 - dual Xeon 3GHz + 2GB RAM + 72GB SCSI). There are Apache 2.0 + PHP 5.1.1 for serving pages and Lighttpd on another port for serving images,css,js... MySQL tables are InnoDB and few MyISAM tables for stats and URL cache. Database Master is replicated to the same machine with the same configuration. Both machines are serving same content at the same time with round robin DNS. Webapplication is connected to Master DB.

Avarage system load is 1.5 - 2 mainly produced by Apache processes.

This is output od 'mytop' tool:
MySQL on localhost (4.1.16-log) up 4+05:19:51 [20:30:54] Queries: 119.8M qps: 344 Slow: 2.4k Se/In/Up/De(%): 39/02/01/00 qps now: 984 Slow qps: 0.0 Threads: 5 ( 2/ 3) 44/01/01/00 Cache Hits: 30.2M Hits/s: 86.7 Hits now: 271.3 Ratio: 63.9% Ratio now: 62.7%
 Key Efficiency: 99.9%  Bps in/out:  9.2k/ 3.0k   Now in/out: 61.6k/505.2k

As you can see - MySQL has about 30 000 000 queries per day, in peaks it has more than 1400 queries per second without any problems.

MySQL is installed from ports with following options:

MySQL daemon is not running from rc script, but controlled by daemontools.

my.cnf is tunned for using 1.5GB of RAM, max 150 connections. For this amount of memory, you must also tweak some options in /boot/loader.conf like this:

On one UP machine I have MySQL 5.0 withou problems, but this machine has not as high load.

Miroslav Lachman