Need advice on mysql/apache performance tuning. Are my parameters right? [closed]

Question

I have DS with 3GB RAM and my processor is AMD Athlon(tm) 64 X2 Dual Core Processor 4200+.

My server is running:

Fedora release 15 (Lovelock)
Linux *** 2.6.40.6-0.fc15.x86_64 #1 SMP Tue Oct 4 00:39:50 UTC 2011 x86_64 x86_64          x86_64 GNU/Linux 

my.cnf file is:

[mysqld]
#skip-innodb
#log
user=mysql
log-slow-queries=/var/log/mysql/log-slow-queries.log
long_query_time = 5
#query_cache_size = 20000000
query_cache_size = 134217728
thread_cache_size=40
wait_timeout=30
max_user_connections=40
max_connections=150
#default-character-set=cp1251
#skip-grant-tables
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
init-connect="SET NAMES cp1251"
##############
##key_buffer = 128M
key_buffer_size = 256M
max_allowed_packet = 16M
table_cache = 3072
sort_buffer_size = 400M
read_buffer_size = 228M
read_rnd_buffer_size = 128M
myisam_sort_buffer_size = 456M
read_rnd_buffer_size = 128M
myisam_sort_buffer_size = 456M
#thread_cache_size = 8
thread_concurrency = 8
tmp_table_size = 32M
max_heap_table_size = 32M
################

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

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

the httpd(prefork) configuration is:

Timeout 30
KeepAlive off
MaxKeepAliveRequests 100
KeepAliveTimeOut 5
ServerLimit      300
MaxRequestsPerChild  500
MinSpareServers 20
MaxSpareServers 30
MaxClients      300

In plans(1 year) I have switchig to nginx + php-fpm, but currently i need to work with this.

Answer 1

In tuning everything depends.. depends on the hardware (like disk speed i.e), depends on installed and running software and services in the server.. depends on the database or databases (their schema, indexes..).

So, you should test the changes you do and avoid making lots of changes in a try, you should do it step by step.

After seeing your config, i guess that your config is for MyISAM Database/s. So, i think the most important variables are key_buffer_size and table_cache. You must be sure, that you're indexes fit in key_buffer_size. Other variables can give you some performance but i think you should take special care these ones. Check the MySQL status to figure out if everything is ok.. Besides, i think that some variables have values too big (sort_buffer_size, read_buffer_size, read_rnd_buffer_size, myisam_sort_buffer_size, read_rnd_buffer_size, myisam_sort_buffer_size)

It's important that you consider how much memory you want to reserve to MySQL, of course, it depends on some stuff. In your case it's not a standalone MySQL server. There is an apache+php service, so i try to reserve 1-1.5Gb to MySQL and monitoring the server to see if the server already has spare memory.

Definitely tuning database system is not easy task.. good luck.

I recommend to you give a walk in http://www.mysqlperformanceblog.com

About Apache, i'll do some changes, but like MySQL, the perfect configuration depends on lots of things:

Timeout 30
KeepAlive on
MaxKeepAliveRequests 100
KeepAliveTimeOut 5
ServerLimit      250
MaxRequestsPerChild  4000
MinSpareServers 15
MaxSpareServers 30
MaxClients      250

Answer 2

When tuning the configuration for Apache and MySQL. I usually setup Cacti.net to fetch statistics using Apache status page, MySQL variables and server resource usage (CPU,RAM and IO). There's a lot of script you can find at Cacti's Forum to get started.

For MySQL, I primarily use mysqlreport and mysqltuner to identify what needs to be change.

Always keep a note of what has been change and monitor them for a day or more once changes has been made.