Help with Configuration

[Ron DiPaola]

I am Linux newby, so please bear with me.

I have set the vbulletin *NIX Load Limit to 5.0 because it keeps crashing the server. I am looking at ways to increase the efficiency of my forums.

1) Server Specs: Cobalt Raq3 300mhz 64MB RAM
PHP version: 4.2.1
mySQL: 3.23.39

2) complied with red hat rpm

3) top stats: Members: 1,221, Threads: 6,695, Posts: 37,830
Is this what you want?

4) my.cnf:

# Example mysql config file for small systems.
#
# This is for a system with little memory (<= 64M) where MySQL is only used
# from time to time and it's important that the mysqld deamon
# doesn't use much resources.
#
# 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 /usr/local/mysql/var) 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 = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
set-variable = key_buffer=16K
set-variable = max_allowed_packet=1M
set-variable = thread_stack=64K
set-variable = table_cache=4
set-variable = sort_buffer=64K
set-variable = net_buffer_length=2K
server-id = 1

# Uncomment the following if you want to log updates
#log-bin

# Uncomment the following if you are NOT using BDB tables
#skip-bdb
# Uncomment the following if you are using Innobase tables
#innodb_data_file_path = ibdata1:100M
#innodb_data_home_dir = /usr/local/mysql/var/
#innodb_log_group_home_dir = /usr/local/mysql/var/
#innodb_log_arch_dir = /usr/local/mysql/var/
#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

[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=8M
set-variable = sort_buffer=8M

[myisamchk]
set-variable = key_buffer=8M
set-variable = sort_buffer=8M

[mysqlhotcopy]
interactive-timeout

5)
extended status output can be found here:
mysql stats (http://www.sccoa.com/mysqlstats.php)

6) vbulletin is basically the only thing on the site, i have some tiny queries pulling 50 records out of a tiny database

7) 91 is the most ever on the site, I usually see 50-60 users on it.

8) I appended the phpinfo to the bottom of the extended status script above in #5

9)
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 10
StartServers 5
MaxClients 150

Thank in advance for your suggestions,
Ron

[eva2000]

raq3 :eek:

haven't heard from one of those for ages :)

you definitely need more memory, but would be better off moving to better hardware - even a P3 866 with 256MB ram would be better

but for now you need to optimise your my.cnf file and replace it with below and restart mysql (upgrading to mysql 3.23.51 would also be better)


[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
set-variable = max_connections=200
set-variable = key_buffer=3M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=384K
set-variable = record_buffer=384K
set-variable = sort_buffer=1M
set-variable = table_cache=512
set-variable = thread_cache_size=32
set-variable = wait_timeout=3600
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10

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

[mysql]
no-auto-rehash

[isamchk]
set-variable = key_buffer=2M
set-variable = sort_buffer=2M

[myisamchk]
set-variable = key_buffer=2M
set-variable = sort_buffer=2M

[mysqlhotcopy]
interactive-timeout


this above is for your 64MB... you should at least upgrade to 256MB

[Pingu]

Hey now, what's with the eeky face?! I'm running a raq3 ;)

[Ron DiPaola]

Ok I replaced the my.cnf file and restarted the mysql daemon.

What should I be looking at to see what effect this has had?

Probably a really dumb question, but I am new to all of this.

Hopefully we will be upgrading up and out of the Raq3 situation, but I need to do all I can with it for now. I will upgrade to the lastest version of mySQL as soon as I can.

Thanks very much,
Ron

[eva2000]

Originally posted by Ron DiPaola
Ok I replaced the my.cnf file and restarted the mysql daemon.

What should I be looking at to see what effect this has had?

Probably a really dumb question, but I am new to all of this.

Hopefully we will be upgrading up and out of the Raq3 situation, but I need to do all I can with it for now. I will upgrade to the lastest version of mySQL as soon as I can.

Thanks very much,
Ron you look at http://www.sccoa.com/mysqlstats.php however from that page your table_cache open_tables is full at 64 which means you didn't properly restart mysql or add the above settings as it should not be full until you reach table_cache = 512 as set it the my.cnf i recommended you

[Ron DiPaola]

I did replace the contents on the my.cnf in /var/db/

[root@www /root]# locate *.cnf
/var/db/my.cnf
/usr/doc/MySQL-3.23.36/my-huge.cnf
/usr/doc/MySQL-3.23.36/my-large.cnf
/usr/doc/MySQL-3.23.36/my-medium.cnf
/usr/doc/MySQL-3.23.36/my-small.cnf
/usr/lib/openssl.cnf
/usr/local/frontpage/version4.0/frontpage.cnf
/usr/local/frontpage/we443.cnf
/usr/local/share/mysql/my-small.cnf
/usr/local/share/mysql/my-medium.cnf
/usr/local/share/mysql/my-large.cnf
/usr/local/share/mysql/my-huge.cnf
/usr/local/mysql-3.23.51/support-files/my-small.cnf
/usr/local/mysql-3.23.51/support-files/my-medium.cnf
/usr/local/mysql-3.23.51/support-files/my-large.cnf
/usr/local/mysql-3.23.51/support-files/my-huge.cnf
/usr/share/mysql/my-huge.cnf
/usr/share/mysql/my-large.cnf
/usr/share/mysql/my-medium.cnf
/usr/share/mysql/my-small.cnf
/usr/share/ssl/openssl.cnf

From some reading I did I see I want to place the my.cnf file in /etc/ but when I do so, I get the following error:

Warning: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) in /home/sites/home/web/forums/admin/db_mysql.php on line 38

What am I doing wrong?

Thanks,
Ron

[eva2000]

do

locate mysql.sock

where is it located ?

might need to change the /tmp/mysql.sock path in my.cnf

and try restarting mysql

[Ron DiPaola]

I located mysql.sock it was located here:
/var/lib/mysql/mysql.sock

I edited my.cnf and placed a copy in:
/etc/

I stopped mysql:
/etc/rc.d/init.d/mysql stop

I restarted mysql:
/etc/rc.d/init.d/mysql start

It looks like this worked. What numbers in the status script should I be looking at closely to see the difference?

Thanks for everything,
Ron

[eva2000]

Originally posted by Ron DiPaola
I located mysql.sock it was located here:
/var/lib/mysql/mysql.sock

I edited my.cnf and placed a copy in:
/etc/

I stopped mysql:
/etc/rc.d/init.d/mysql stop

I restarted mysql:
/etc/rc.d/init.d/mysql start

It looks like this worked. What numbers in the status script should I be looking at closely to see the difference?

Thanks for everything,
Ron take a read of http://i4net.tv/marticle/get.php?action=getarticle&articleid=3

mainly looking at max_used_connections, open_tables to see if they're within the limits of my.cnf's imposed max_connections and table_cache variables respectively

i also take note of my mysql queries/second, and how many threads are connected at a time

[Ron DiPaola]

My server was not responding to users because the server load was too high, when I checked the vBulleting control panel, the server load was 8.8 with one user (me). I cut back some of these values and this is the my.cnf I am using now. Do I need to adjust these in proportion to one another?

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
set-variable = max_connections=200
set-variable = key_buffer=3M
set-variable = myisam_sort_buffer_size=32M
set-variable = join_buffer=384K
set-variable = record_buffer=384K
set-variable = sort_buffer=1M
set-variable = table_cache=256
set-variable = thread_cache_size=32
set-variable = wait_timeout=3600
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10

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

[mysql]
no-auto-rehash

[isamchk]
set-variable = key_buffer=2M
set-variable = sort_buffer=2M

[myisamchk]
set-variable = key_buffer=2M
set-variable = sort_buffer=2M

[mysqlhotcopy]
interactive-timeout