MySQL and FreeBSD

Miroslav Lachman 000.fbsd at
Sat Jul 1 13:34:24 UTC 2006

Maikel Lambregts wrote:
> Hi,
>   I saw your information on
> Atm we still have and olf MySQL server with FreeBSD 4.8 and MySQL 4.0 running with Linuxthreads. We now bought a new Dell server (Dual Xeon 2.8, 4GB ram, 15.000 SCSI RAID 1) and I'm wondering what would be the best to install MySQL.
> I was thinking of FreeBSD 6.1 in combination with MySQL 5.0. I was thinking of using your advice by using native threads instead of Linuxthreads and furthermore I just would copy your build options -->

we are still running MySQL with options from that thread, but now we 
have MySQL master on separate machine replicated to 2 slaves (those 
machines with webaplication).
MySQL machine is Supermicro 6014H-8 with dual Xeon(TM) CPU 2.80GHz, 2GB 
of RAM and only one 72GB SCSI HDD

Webaplication developers fixed some problems (mainly indexes on tables 
and reduce number of queries), so now in peaks we have about 500 queries 
per second with avarage CPU load lower then 0.3.

Now we have MySQL 4.1.20-log
# mysqladmin status
Uptime: 2465465  Threads: 6  Questions: 507158262  Slow queries: 56373 
Opens: 1281  Flush tables: 1  Open tables: 1024  Queries per second avg: 

One important note - I do not recommend to enable HyperThreading. With 
HTT enabled, MySQL is overloaded with "slow queries" several times per 
day without any logical reason.

my.cnf is in attachment, I recommend to do some tuning, because you have 
4GB of RAM and probably different target usage, so you can increase some 
buffers, max number of connection, query cache etc.

I do not know, if my configuration is the best, because I have no 
testing machine with dual CPU to tests and because this configuration 
runs well, I have no need to test another.

If you have new machine and have some time to testing, you can try not 
to build MySQL as static and try different thread libraries throught 

# candidate          mapping

If you do so, please let me know of your results.

And last - if you are not using features of MySQL 5.0, then stay with 
MySQL 4.1. With MySQL 5.0 (on machine I wrote in old thread) we have 
some troubles after upgrades (MySQL bugs with lost triggers etc.), 4.1 
seems more "stable" to me. (but it is my personal view)

Miroslav Lachman

Some useful informations:
-------------- next part --------------
#password	= [your_password]
port		= 3306
socket		= /tmp/mysql.sock

# *** Application-specific options follow here ***

# The MySQL server

# generic configuration options
port		= 3306
socket		= /tmp/mysql.sock

# back_log is the number of connections the operating system can keep
back_log = 256
max_connections = 130
#max_connect_errors = 10
table_cache = 1024
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 32M
sort_buffer_size = 8M
join_buffer_size = 6M
thread_cache = 8
thread_concurrency = 4
query_cache_size = 64M
query_cache_limit = 4M
ft_min_word_len = 3
#thread_stack = 192K
# Set the default transaction isolation level. Levels available are:
#transaction_isolation = REPEATABLE-READ
# 2006-03-16 11:43 changed from REPEATABLE-READ to READ-COMMITTED
# in faith of stop slow queries jam
transaction_isolation = READ-COMMITTED
tmp_table_size = 96M
long_query_time = 2
#tmpdir = /tmp

# ***  Replication related settings 
server-id = 1

# Replication Slave (comment out master section to use this)
# To configure this host as a replication slave, you can choose between
# two methods :
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
#    the syntax is:
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#    where you replace <host>, <user>, <password> by quoted strings and
#    <port> by the master's port number (3306 by default).
#    Example:
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
# OR
# 2) Set the variables below. However, in case you choose this method, then
#    start replication for the first time (even unsuccessfully, for example
#    if you mistyped the password in master-password and the slave fails to
#    connect), the slave will create a file, and any later
#    changes in this file to the variable values below will be ignored and
#    overridden by the content of the file, unless you shutdown
#    the slave server, delete and restart the slaver server.
#    For that reason, you may want to leave the lines below untouched
#    (commented) and instead use CHANGE MASTER TO (see above)
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id = 2
# The replication master for this slave - required
#master-host = <hostname>
# The username the slave will use for authentication when connecting
# to the master - required
#master-user = <username>
# The password the slave will authenticate with when connecting to
# the master - required
#master-password = <password>
# The port the master is listening on.
# optional - defaults to 3306
#master-port = <port>

# Make the slave read-only. Only users with the SUPER privilege and the
# replication slave thread will be able to modify data on it. You can
# use this to ensure that no applications will accidently modify data on
# the slave instead of the master

#*** MyISAM Specific options

key_buffer_size = 96M
read_buffer_size = 4M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 32M
#myisam_sort_buffer_size = 32M
myisam_max_sort_file_size = 4G
myisam_max_extra_sort_file_size = 4G
myisam_repair_threads = 2
# Automatically check and repair not properly closed MyISAM tables.

# *** BDB Specific options ***

# Use this option if you run a MySQL server with BDB support enabled but
# you do not plan to use it. This will save memory and may speed up some
# things.

# *** INNODB Specific options ***

# value. SHOW INNODB STATUS will display the current amount used.
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 512M
innodb_data_file_path = ibdata1:512M;ibdata2:64M:autoextend:max:5G
innodb_autoextend_increment = 64
#innodb_data_home_dir = /var/data/db/mysql
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 8M
innodb_log_file_size = 128M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 20

# Do not buffer the whole result set in memory before writing it to
# file. Required for dumping very large tables
max_allowed_packet = 16M


# Only allow UPDATEs and DELETEs that use keys.

key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 6M
write_buffer = 6M


# Increase the amount of open files allowed per process. Warning: Make
# sure you have set the global system limit high enough! The high value
# is required for a large number of opened tables
#open-files-limit = 8192

More information about the freebsd-database mailing list