Hi guys,

I'm trying to lighten the load on a server. Usual scenario, we've had a customer who's site has literally taken off over night, and is very mysql intensive. We're in the process of moving him, but in the meantime I thought it would be a good idea to optimise mysql.

Server is a P4 2.4 with 2GB RAM.

After some research and looking about. I've made some ammendments, and have my config as follows:

[mysqld]
log-slow-queries = /tmp/slow
set-variable = max_connections=300
set-variable = wait_timeout=10
set-variable = key_buffer=512M
set-variable = max_allowed_packet=15M
set-variable = table_cache=1024
set-variable = thread_cache=64
set-variable = thread_concurrency=2
set-variable = sort_buffer=2M
set-variable = ft_min_word_len=3

Does anyone have any particular changes / tweaks they would suggest to improve performance?

Thanks in advance.

Originally posted by [inx]Olly
Hi guys,

I'm trying to lighten the load on a server. Usual scenario, we've had a customer who's site has literally taken off over night, and is very mysql intensive. We're in the process of moving him, but in the meantime I thought it would be a good idea to optimise mysql.

Server is a P4 2.4 with 2GB RAM.

After some research and looking about. I've made some ammendments, and have my config as follows:

[mysqld]
log-slow-queries = /tmp/slow
set-variable = max_connections=300
set-variable = wait_timeout=10
set-variable = key_buffer=512M
set-variable = max_allowed_packet=15M
set-variable = table_cache=1024
set-variable = thread_cache=64
set-variable = thread_concurrency=2
set-variable = sort_buffer=2M
set-variable = ft_min_word_len=3

Does anyone have any particular changes / tweaks they would suggest to improve performance?

Thanks in advance.

Either you haven't upgraded to Mysql4 or you haven't got query cache enabled - not sure which is worse :)

Hi Dave,

Definitely on mysql4. How do I enable query cache please?

Originally posted by [inx]Olly
Hi Dave,

Definitely on mysql4. How do I enable query cache please?

stick this in my.cnf, retart mysql and be amazed :)

query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1

Wow, thank you. Any other performance enhancing drugs? :D

Is that all that's required to enable query cache?

Originally posted by eniki
Is that all that's required to enable query cache?

yup - good innit :)

[inx]Olly, do you use PHP content caching? You may want to check out http://turck-mmcache.sourceforge.net/.

Does Zend offer similar performance enhancements?

Originally posted by eniki
Does Zend offer similar performance enhancements?

mmcache is often faster than zend however mmcache is no longer developed and may cause serious problems with your server - be careful and monitor your apache error log VERY closely if you are using it.

Originally posted by DaveC#
mmcache is often faster than zend however mmcache is no longer developed and may cause serious problems with your server - be careful and monitor your apache error log VERY closely if you are using it.

Have you had problems with mmcache v2.4.6? It's been rock-solid for me with PHP v4.3.4.

Originally posted by GameraFan
Have you had problems with mmcache v2.4.6? It's been rock-solid for me with PHP v4.3.4.

depends on the server and the code - I'm running the same as you on one website without a problem but on others I have loads of FATALS etc

Does Zend offer similar performance enhancements?


Why don't you check this Benchmark out?
http://turck-mmcache.sourceforge.net/#bench

Thanks.

The real zend optimizer which costs about $500 is faster than turck, but turck is the fastest free accelerator.

I run turck with no problems.

well if you are on 4 then you need to check the variables they got changed. and the set-variable are no more used

http://www.mysql.com/doc/en/Upgrading-from-3.23.html

try this use it on your own what ever. am not stating this is the most perfect one, am using this for p4 2.4 with 1 Gb ram you may need to twaek it a bit just monitor your box for a day or 2 and see

if any have some thing better please post

[mysqld]
skip-innodb
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
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=100
wait_timeout=60
connect_timeout=10
thread_cache_size=50
key_buffer=16M
join_buffer=1M
max_allowed_packet=16M
table_cache=512
record_buffer=1M
sort_buffer_size=1M
read_buffer_size=1M
max_connect_errors=10
myisam_sort_buffer_size=64M
log-bin

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

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

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
#safe-updates <<<<< THIS IS UP TO YOU

[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

it sounds like my query cache doesn't like me...so I didnt turn it on yet.

when do we enlarge the variables?
query_cache_size

I READ THE MYSQL PAGE ABOUT THIS ALREADY..but still don't get it