Slow page loads on forum - need to revise /etc/my.cnf

Hi all,

I have just tweaked my my.cnf file for optimising mysql, however, I have failed in speeding up the system. I have reports of slow loading at peak times. The server is a P4 1.7 w/512 ram, there are 150 sites all running static, php, cgi, mysql sites. Based on all this information what would you consider changing to the my.cnf file?


Mysql Status report

Threads: 16
Questions: 4384436
Slow queries: 209
Opens: 28007
Flush tables: 1
Open tables: 256
Queries per second avg: 14.783

Top output

===============================
4:45am up 3 days, 10:55, 1 user, load average: 0.25, 0.17, 0.17
71 processes: 68 sleeping, 2 running, 1 zombie, 0 stopped
CPU states: 16.2% user, 4.7% system, 0.0% nice, 79.0% idle
Mem: 512920K av, 494672K used, 18248K free, 48K shrd, 82168K buff
Swap: 1028152K av, 129508K used, 898644K free 171728K cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
16513 mysql 14 0 161M 43M 1768 S 9.6 8.6 0:00 mysqld
10698 nobody 11 0 8344 8084 5304 S 7.3 1.5 0:05 httpd
16565 root 14 0 1060 1060 824 R 1.1 0.2 0:00 top

copy of my.cnf
# Example mysql config file for large systems.
#
# This is for large system with memory = 512M where the system runs mainly
# MySQL.
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# One can in this file use all long options that the program supports.
# If you want to know which options a program support, run the program
# with --help option.

# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
set-variable = max_connections=200
set-variable = interactive_timeout=100
set-variable = wait_timeout=100
set-variable = thread_cache_size=256
set-variable = connect_timeout=10
set-variable = myisam_sort_buffer_size=32M
set-variable = key_buffer=16M
set-variable = join_buffer=3M
set-variable = record_buffer=3M
set-variable = sort_buffer=5M
set-variable = table_cache=1024
skip-locking



port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
set-variable = key_buffer=256M
set-variable = max_allowed_packet=1M
set-variable = table_cache=256
set-variable = sort_buffer=1M
set-variable = record_buffer=1M
set-variable = myisam_sort_buffer_size=64M
set-variable = thread_cache=8
# Try number of CPU's*2 for thread_concurrency
set-variable = thread_concurrency=8
log-bin
server-id = 1

# Uncomment the following if you are using BDB tables
#set-variable = bdb_cache_size=64M
#set-variable = bdb_max_lock=100000

# Uncomment the following if you are using Innobase tables
#innodb_data_file_path = ibdata1:1000M
#innodb_data_home_dir = /var/lib/mysql/
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
#set-variable = innodb_mirrored_log_groups=1
#set-variable = innodb_log_files_in_group=3
#set-variable = innodb_log_file_size=5M
#set-variable = innodb_log_buffer_size=8M
#innodb_flush_log_at_trx_commit=1
#innodb_log_archive=0
#set-variable = innodb_buffer_pool_size=16M
#set-variable = innodb_additional_mem_pool_size=2M
#set-variable = innodb_file_io_threads=4
#set-variable = innodb_lock_wait_timeout=50

# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[myisamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[mysqlhotcopy]
interactive-timeout

 

 

 

 

Top