[help] How can I configure mysql server ?

[odl2000]

Hello,
(Excuse me for my english)

I have problem with my configuration "max_connections error"
I have modify my.cnf, but I'm not sure it best.

Could you give me the optim configuration ?

I have a dedicated server :
Celeron2 : 1 GHz
Memory : 512 Mo
Cache : 256 Ko

Operating System Linux
Apache Version 1.3.23 (Unix)
PHP Version 4.1.2
Mysql Version 3.23.45

top : (but it's morning)
------
10:52am up 32 days, 5:29, 1 user, load average: 2.95, 3.78, 3.79
102 processes: 99 sleeping, 3 running, 0 zombie, 0 stopped
CPU states: 71.2% user, 15.0% system, 0.0% nice, 13.6% idle
Mem: 511800K av, 328136K used, 183664K free, 0K shrd, 12868K buff
Swap: 530104K av, 6580K used, 523524K free 191228K cached

my.cnf
--------
[mysqld]
set-variable = max_connections=200
set-variable = key_buffer=16M
set-variable = table_cache=128
set-variable = sort_buffer=4M
set-variable = record_buffer=1M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

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

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


mysqladmin -u root -p extended-status
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Bytes_received | 15460614 |
| Bytes_sent | 144765773 |
| Connections | 33195 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 2112 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 110 |
| Handler_read_first | 1912 |
| Handler_read_key | 1366258 |
| Handler_read_next | 3941948 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 355837 |
| Handler_read_rnd_next | 6165274 |
| Handler_update | 9210 |
| Handler_write | 2374725 |
| Key_blocks_used | 7228 |
| Key_read_requests | 3559582 |
| Key_reads | 7217 |
| Key_write_requests | 1993 |
| Key_writes | 1750 |
| Max_used_connections | 155 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 128 |
| Open_files | 173 |
| Open_streams | 0 |
| Opened_tables | 454 |
| Questions | 135934 |
| Select_full_join | 2434 |
| Select_full_range_join | 0 |
| Select_range | 4040 |
| Select_range_check | 0 |
| Select_scan | 13226 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 1 |
| Slow_queries | 1 |
| Sort_merge_passes | 0 |
| Sort_range | 5118 |
| Sort_rows | 2396192 |
| Sort_scan | 5298 |
| Table_locks_immediate | 77896 |
| Table_locks_waited | 315 |
| Threads_cached | 0 |
| Threads_created | 33194 |
| Threads_connected | 1 |
| Threads_running | 1 |
| Uptime | 1143 |
+--------------------------+-----------+


I have 2 sites
------------------

site1 : php +mysql, phpAdsNew, no forum
130 000 page/day
php module !!!

site2 : mysql is use only for forum and phpAdsNew
120 000 page/day
php cgi !!!! (for use suexec)

hign traffic is 13h00 --> 24h00

how many average and max concurrent users on your vB forum ?
average : 100
max : 300

KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 10
StartServers 20
MaxClients 200

[Chen]

What version of vBulletin are you running?

[odl2000]

Hello, I use vbulletin 2.5

[eva2000]

you mean 2.2.5 right ?

Please enter your vBulletin.com forum username on the form on this page to receive support for this issue: http://www.vbulletin.com/members/forums.php
- you will need your vB license customer number and password to access this form.

[odl2000]

Hello,

It's done !
If you seach odl2000, you can see my licence

Could you help me please ?

thanks

PS : yes it's 2.2.5

[eva2000]

okay change or replace your /etc/my.cnf file with the below one and restart mysql


[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable = max_connections=400
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer=2M
set-variable = table_cache=512
set-variable = thread_cache_size=256
set-variable = wait_timeout=3600
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10

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

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

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

[myisamchk]
set-variable = key_buffer=64M
set-variable = sort_buffer=64M
set-variable = read_buffer=16M
set-variable = write_buffer=16M

[odl2000]

Thanks.

I go test it.

[odl2000]

11:36pm up 35 days, 18:12, 1 user, load average: 21.99, 25.71, 22.19
358 processes: 345 sleeping, 13 running, 0 zombie, 0 stopped
CPU states: 77.8% user, 22.1% system, 0.0% nice, 0.0% idle
Mem: 511800K av, 369504K used, 142296K free, 0K shrd, 7624K buff
Swap: 530104K av, 12108K used, 517996K free 143452K cached

Have you another configuration for me ?