HOWTO: Optimize MySQL 4.x

loopforever

Verified User
Joined
May 30, 2003
Messages
298
Location
/home/admin
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:

Code:
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
Code:
/etc/rc.d/init.d/mysqld restart [ENTER]
 
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
 
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.
 
Hello,

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

John
 
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?
 
Last edited:
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.
 
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
 
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?
 
Last edited:
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.
 
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 said:
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.
 
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.
 
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 said:
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 ;) )
 
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
 
Back
Top