"Best" MySQL Config

[Hvu]

Whats the best mysql config for a medium IPB forum, i recently upgraded to 4.1.10a from 4.0.22. With my previous config the forum was zippy and never getting errors. Now the pages are slower but not slow to a craw and i'm gettin IPB mysql errors once in a while. I tried to change my.cnf to a different config but no workie. Whats your mysql.cnf?

Currently

[mysqld]
port=3306
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=300
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=256
key_buffer=150M
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=1m
max_connect_errors=10
thread_concurrency=4
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

[charles]

150M for the key_buffer seems high for a 256M VPS.

[Hvu]

Here's my new my.cnf


[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=300
interactive_timeout=60
wait_timeout=60
connect_timeout=15
thread_cache_size=15
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

[nobody]

I did not make mine too complicated, I got it off some tutorials I saw and by recommendations.


[mysqld]
skip-locking
max_connections=300
connect_timeout=15
key_buffer=16M
join_buffer=1M
record_buffer=1M
sort_buffer=2M
table_cache=1028
thread_cache_size=286
max_allowed_packet=5M
wait_timeout=15
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
thread_concurrency=2


If anyone has any suggestions, please post them.

[charles]

Remove 'log-bin'. This is only needed is your doing replication. Mysql logs all transactions and the slave servers use this log to keep current. However if you don't have slaves, the log files don't get cleared and you'll just run out of space eventually.

'server-id' is also related to replication, but won't cause any harm.

hth
charles

[PvUtrix]

thread_cache_size=286 was causing a few problems for me, there were too many mysql processes... I've lowered it to 8

[vps-vince]

Here's mine, running on a Power-2

[mysqld]
set-variable = max_connections=500
skip-locking
connect_timeout=15
key_buffer=32M
join_buffer=1M
record_buffer=1M
sort_buffer=2M
table_cache=1028
thread_cache_size=286
max_allowed_packet=5M
wait_timeout=15
query_cache_limit=2M
query_cache_size=64M
query_cache_type=1
thread_concurrency=2

[BornOnline]

# Try number of CPU's*2 for thread_concurrency
thread_concurrency=2

Question about this... Should this be "8" We are on quad cpu system correct? Just wondering if anyone has really played with this or should I just not worry about it..lol

O..btw
Just found this Link (http://interworx.info/forums/showthread.php?p=2346) and thought it has some very good info. You guys have probably already seen it :D

[TomK]

Just FYI, the SQL optimizations can make a HUGE difference...here is an example:

Customer was running a large SQL site, lots of data (tables, rows, etc)...they were the only VPS running on a 8G Dual 248 Opteron with 6 10K RPM HDD drives. They had full resources (all cpu, no limits, full memory).

They were killing the server, took their VPS load to 40+ and the box load was sitting in the 30 range. Veena took a look, and optimized the conf file, a few seconds later both loads are under .5 - yes, point five!

Thanks Veena, if you want to let everyone know what you changed that made such a huge difference, feel free to give the changes here!

[veena]

Hi,

Thanks Tom :) The only change I made was the my.cnf conf posted by vps-vince for a P2 in this thread above.

[elix]

# Try number of CPU's*2 for thread_concurrency
thread_concurrency=2

Question about this... Should this be "8" We are on quad cpu system correct? Just wondering if anyone has really played with this or should I just not worry about it..lol

O..btw
Just found this Link (http://interworx.info/forums/showthread.php?p=2346) and thought it has some very good info. You guys have probably already seen it :D
Actually it's a Dual CPU system..the Xeon boxes have 2 extra CPUs but they are virtual ones that do not make tooo much of a difference.

[vps-vince]

Hi,
Thanks Tom. The only change I made was the my.cnf conf posted by vps-vince for a P2 in this thread above.

Hey, thanks Veena, nice to know I got something usefull to contribute. :cool:
I may need to look at this again, as I'm on a Power-3 now.

- Vince

[charles]

# Try number of CPU's*2 for thread_concurrency
thread_concurrency=2

Question about this... Should this be "8" We are on quad cpu system correct? Just wondering if anyone has really played with this or should I just not worry about it..lol


I would definately not set it to 8 - you are sharing those cpus, and I think it would result in more context switching instead of being a benefit. As someone pointed out, they are dual CPUs with hyperthreading so I'm not sure if myslq takes advantage of them as well as 4 real CPUs either.

To be honest I am not sure if 2 is even any better than 1 on a VPS. I am not sure of a fool proof benchmark to prove/disprove this though.

charles

[elix]

Let me re-add BornOnline's link.

http://interworx.info/forums/showthread.php?p=2346

I followed the steps there and MySQL performance is nealry 40% better!

[BornOnline]

Cool.. thanks for the info Charles.
Yeah.. that is a good link :)

[mikelbeck]

I guess that's for MySQL 4.1.x only?

[elix]

I guess that's for MySQL 4.1.x only?
some/most of those should work.

[Atomm]

This is good info, but I am fairly new at trying to optimize MySQL.

Does anyone have a recommended config for a Power-1 VPS running 4.0.25. Since this is a production server, I am a bit leary of trying some of the others since they seem to be for various servers running a newer version of MySQL.

Your Help Is Greatly Appreciated!

[ozgreg]

I am on a power-3 so I have some more memory to burn but I am still optimising this as all my sites are MYSQL based with dynamic content so mysql gets hit hard.

[mysqld]
skip-locking
connect_timeout=15
key_buffer=256M
join_buffer=1M
record_buffer=1M
sort_buffer=1M
myisam_sort_buffer_size = 64M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
table_cache=1024
thread_cache_size=100
max_allowed_packet=5M
wait_timeout=15
query_cache_limit=2M
query_cache_size=64M
query_cache_type=1
thread_concurrency=2
tmp_table_size=64M

[ozgreg]

This is good info, but I am fairly new at trying to optimize MySQL.

Does anyone have a recommended config for a Power-1 VPS running 4.0.25. Since this is a production server, I am a bit leary of trying some of the others since they seem to be for various servers running a newer version of MySQL.

Your Help Is Greatly Appreciated!

Have you considered moving up to 4.1 as it has a number of key benefits the greatest being the encoding support.

[guapo]

very nice thread.

would this config run fine with vps-1 ? or what is recommended to change at it ?


[mysqld]
set-variable = max_connections=500
skip-locking
connect_timeout=15
key_buffer=32M
join_buffer=1M
record_buffer=1M
sort_buffer=2M
table_cache=1028
thread_cache_size=286
max_allowed_packet=5M
wait_timeout=15
query_cache_limit=2M
query_cache_size=64M
query_cache_type=1
thread_concurrency=2

i have to say this config has been saving me up lots of usage.
nice shot

[Chris]

Hello All,

Searched and couldn't find a specific suggestion so I thought I'd throw it out to the group.

I have 2 VPS's I'm looking to optimize through my.cnf

cPanel Power-1 running MySQL 4.0

cPanel Power-2 running MySQL 4.0

Might change the Power-1 to MySQL 4.1 as I just got it and don't have any accounts on it yet. Scared to convert the Power-2 one over because I have tons of clients with existing Fantastico installed databases.

Thoughts?

Thanks so much!

[asterisk]

Chris, I see that the thread has an optimised my.cnf for Power-2 from vps-vince.


[mysqld]
set-variable = max_connections=500
skip-locking
connect_timeout=15
key_buffer=32M
join_buffer=1M
record_buffer=1M
sort_buffer=2M
table_cache=1028
thread_cache_size=286
max_allowed_packet=5M
wait_timeout=15
query_cache_limit=2M
query_cache_size=64M
query_cache_type=1
thread_concurrency=2


I too, like Chris and guapo, would appreciate to hear if any of these values may be tweaked to help optimise for Power-1 please.

Thanks in advance.

[falsedawn]

Whilst tweaking the conf file is important, it shouldn't be considered a panacea. As long as your settings are not ridiculously bad (and they're not), you are unlikely to make stellar gains by small tweaks.

Just as important are the actual queries you are running (both type, and quantity) and the indexes in place on the tables, especially when joining multiple tables.

[SlAiD]

Hello Vena,

What you do exacly on the VPS 2?

I'm using that:

[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=300
interactive_timeout=60
wait_timeout=60
connect_timeout=15
thread_cache_size=15
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

[SlAiD]

( ... )

That file configuration is good for a plan 2 ?


SL

[SlAiD]

Hello,

For who still have mutch problems, i recoomend add to crontab the fowlling:

59 * * * * /etc/rc.d/init.d/mysql restart


This will restart MySQL every hour, and clear all opening tables. This can help.

I do it myself and with phpBB Foruns it work very good and solve some problems.

Also, i recommend DOS-Deflate (http://forums.deftechgroup.com/showthread.php?t=825)


SL