HOWTO: Optimize MySQL 4.x

[loopforever]

Tweak MySQL

I've found that these configuration options posted by "aussie" on the Rackshack forums work well with MySQL 4.x on my machines. I suggest implementing them to alieviate the load MySQL may cause when you run big SQL sites on your server:

vi /etc/my.cnf [ENTER]

[mysqld]
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=500
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
key_buffer=16M
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=2
myisam_sort_buffer_size=64M
log-bin
server-id=1

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
#pid-file=/var/lib/mysql/mysql.pid
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout

/etc/rc.d/init.d/mysqld restart [ENTER]

[MQ-James]

thanks, this helps. How can I enable certain other host maks to access the server? I don't want all TLDs to be able to access, just ones on server and 2 other TLDs

[loopforever]

Directadmin can handle host access in the database area of the user control panel. You can also control this through phpMyAdmin under your MySQL root user.

Hope this helps.

[FarCry]

log into phpmyadmin with the username da_admin and your admin password (the original one)

[DirectAdmin Support]

Hello,

DirectAdmin does have the mysql hosts feature.. Just click your database name, and the "Access Hosts" table should be at the bottom.

John

[neorder]

i'm sorry but i don't find "/etc/my.cnf" in my server, is that how to compatible with DA?

do i just creat a new file and copy the content there?

btw, i've nothing at /var/log/mysql.log

is there anything wrong here at my server?

[existenz]

The my.cnf it not added during the default install. You can search the forums for more information about this but you can basically add what was posted as a new file called my.cnf.

[ProWebUK]

MySQL simply runs on defaults unless the my.cnf exists, there are actually 3 places it looks for the config with /etc/my.cnf just being the first... add them and it will override the default settings..

Chris

[neorder]

thanks, i will read more.

[Anzix]

Hello, I need some help:

[root]# /etc/rc.d/init.d/mysqld restart
Killing mysqld with pid 2812
Wait for mysqld to exit\c
.\c
.\c
.\c
.\c
.\c
done
[root]# /etc/rc.d/init.d/mysqld restart
No mysqld pid file found. Looked for /var/lib/mysql/purple.nemesis.net.pid.
[root]# Starting mysqld daemon with databases from /var/lib/mysql
040423 18:18:11 mysqld ended

[root]# service mysqld restart
No mysqld pid file found. Looked for /var/lib/mysql/purple.nemesis.net.pid.
[root]# Starting mysqld daemon with databases from /var/lib/mysql
040423 18:40:31 mysqld ended

any idea on how to fix it?

[loopforever]

Have you modified this my.cnf file at all? I think mysqld is looking for the pid file in one location that you have specified, but it is non-existant.

[sander815]

i had a problem with mysql server going down, because google bots tried to login a lot, causing many many cookies etc

we now blocked google from entering the forums, that solved it, but will this maybe prevent mysql going down?

[sander815]

in what way is this cnf better then the default 1?

[neorder]

Originally posted by sander815
in what way is this cnf better then the default 1?

good question, i shouldn't just copy blindly, i just read the original post from ev1, it looks for different server specs, the setting varies.

[neorder]

they recommand

suggest the following my.cnf changes for people with a single-processor server and 512MB RAM:

thread_cache_size=50
key_buffer=40M
table_cache=384
sort_buffer_size=768K
read_buffer_size=512K
read_rnd_buffer_size=512K
thread_concurrency=2

how are these values are calculated?

anyway, i did this optimization, it was a WOW when i see the out come, i have one site used to have 0.2 page generate speed, now it's only 0.08!

a small question is

/etc/rc.d/init.d/mysqld restart [ENTER]

doesn't look like work, i ran it, but it hangs...i don't know if it's restarted, i guess it did.

[sander815]

to m it seems this config is best suited if you have 1.5 gb ram

key_buffer_size = (record_buffer + sort_buffer)*max_connections
= 1500

[sander815]

on what thread is this onfo based upon? on ev1 servers

[ProWebUK]

Originally posted by sander815
on what thread is this onfo based upon? on ev1 servers

it doesn't matter who provided the server - mysql is simply - mysql and this will work on any DA server, from any provider. (providing it has mysql and uses the linux mysql setup - if it had DA it would )

[sander815]

no, i mean, i wanted to know the link to the thread on rack shack forums where this info comes from, so i can check what peoples replys are there

[netswitch]

any idea of the values to change for a dual processor ?