UBB.Dev
Posted By: cdavis MySQL Optimization - 06/26/2004 10:45 PM
I need someone to optimize MySQL on my Apple Xserve.

Here are the specs:
1. Mac OS X Server 10.2.8
2. MySQL 4.0.18 - 2 Databases (24MB & 197MB)
3. Apache 2.0.49 - 55 Virtual Hosts

Occasionally, the larger database become inaccessible - pages in the forums cannot be accessed. During this time, there are usually quite a few "locked" MySQL processes relating to this database. As soon as MySQL is restarted, the forums function properly for several more hours.

I have optimized a couple of the largest tables in this database several times, but that doesn't seem to have an effect.

I can provide 24 hour system-wide access via SSH in order to optimize MySQL.
Posted By: 1QuickSI Re: MySQL Optimization - 07/08/2004 12:16 AM
Backup your my.cnf and try this...

Code
[mysqld]
datadir=/usr/local/mysql
socket=/var/lib/mysql/mysql.sock
set-variable = max_connections=450
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer=2M
set-variable = table_cache=1024
set-variable = thread_cache_size=256
set-variable = wait_timeout=9600
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10

[mysql.server]
user=mysql
basedir=/usr/local/mysql

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit=8192

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[myisamchk]
set-variable = key_buffer=64M
set-variable = sort_buffer=64M
set-variable = read_buffer=16M
set-variable = write_buffer=16M
© UBB.Developers