Date: Sat, 01 Jul 2006 15:01:08 +0200 From: Miroslav Lachman <000.fbsd@quip.cz> To: Maikel Lambregts <maikel.lambregts@yahoo.com> Cc: freebsd-database@freebsd.org Subject: Re: MySQL and FreeBSD Message-ID: <44A67214.5070007@quip.cz> In-Reply-To: <20060701114428.51387.qmail@web52205.mail.yahoo.com> References: <20060701114428.51387.qmail@web52205.mail.yahoo.com>
next in thread | previous in thread | raw e-mail | index | archive | help
This is a multi-part message in MIME format. --------------020701080103060500040707 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Maikel Lambregts wrote: > Hi, > I saw your information on http://lists.freebsd.org/pipermail/freebsd-database/2006-January/000386.html > > 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 --> Hi, 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: 205.705 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 /etc/libmap.conf # candidate mapping # [/usr/local/libexec/mysqld] libpthread.so.2 libthr.so.2 libpthread.so libthr.so 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: http://www.mysqlperformanceblog.com/2006/06/15/freebsd-tests/ http://wikitest.freebsd.org/MySQL --------------020701080103060500040707 Content-Type: text/plain; name="my.cnf_2006-07-01.txt" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="my.cnf_2006-07-01.txt" [client] #password = [your_password] port = 3306 socket = /tmp/mysql.sock # *** Application-specific options follow here *** # # The MySQL server # [mysqld] # generic configuration options port = 3306 socket = /tmp/mysql.sock # back_log is the number of connections the operating system can keep back_log = 256 #skip-networking 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: # READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE #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 log_bin #log_slave_updates #log #log_warnings log_slow_queries long_query_time = 2 log_long_format #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: # # CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>, # 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: # # CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, # 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 master.info file, and any later # changes in this file to the variable values below will be ignored and # overridden by the content of the master.info file, unless you shutdown # the slave server, delete master.info 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 #read_only #*** 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. myisam_recover # *** 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. skip-bdb # *** 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_force_recovery=1 innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 2 #innodb_fast_shutdown innodb_log_buffer_size = 8M innodb_log_file_size = 128M innodb_log_files_in_group = 3 #innodb_log_group_home_dir innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 20 [mysqldump] # Do not buffer the whole result set in memory before writing it to # file. Required for dumping very large tables quick max_allowed_packet = 16M [mysql] no-auto-rehash # Only allow UPDATEs and DELETEs that use keys. #safe-updates [isamchk] key_buffer = 128M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 6M write_buffer = 6M [mysqlhotcopy] interactive-timeout [mysqld_safe] # 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 --------------020701080103060500040707--
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?44A67214.5070007>