4 forum sites locking mysql

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
roi
Forum Newbie
Posts: 2
Joined: Fri Sep 01, 2006 7:48 am

4 forum sites locking mysql

Post by roi »

hello,

we have a dedicated server with the configuration of ;
- xeon 3.0, 3 gigs of ram running on Apache/2.0.52 (CentOS) and mysql 4.1.20

we have 4 forum sites running PHP & MySql, 2 of them are PHP BB2, the others are developed by themselves.

The problem is ;

the usage of mysql sometimes picks to %100 and we have no chance but restart the mysql service.

do you have any ideas to resolve the problem ?



our mySQL config file like that ;

Code: Select all

[mysqld]
query_cache_type=1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
query_cache_size=128M
connect_timeout=5
max_connections=300
thread_cache=100
thread_concurrency=120
thread_stack=512000
key_buffer_size=512K
sort_buffer_size=100K
read_buffer_size=100K
max_allowed_packet=64M


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

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

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

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/my
User avatar
Buddha443556
Forum Regular
Posts: 873
Joined: Fri Mar 19, 2004 1:51 pm

Post by Buddha443556 »

Are any of the forums using persistent connections?

mysql_pconnect
mysql_connect
roi
Forum Newbie
Posts: 2
Joined: Fri Sep 01, 2006 7:48 am

Post by roi »

Buddha443556 wrote:Are any of the forums using persistent connections?

mysql_pconnect
mysql_connect
none of the using persistent connections, that means all of the sites connect to mysql on every page (mysql_connect)

do you think it will be good if we use persistent connection to mysql ?
User avatar
Buddha443556
Forum Regular
Posts: 873
Joined: Fri Mar 19, 2004 1:51 pm

Post by Buddha443556 »

roi wrote:do you think it will be good if we use persistent connection to mysql?
If your database was remote then persistent connections may be needed.

Looking closer at ...
[mysqld]
query_cache_type=1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
query_cache_size=128M
connect_timeout=5
max_connections=300
thread_cache=100
thread_concurrency=120
thread_stack=512000
key_buffer_size=512K
sort_buffer_size=100K
read_buffer_size=100K
max_allowed_packet=64M
... some of those numbers look odd. Here's MySQL defualt settings:
back_log: 5
bdb_cache_size: 1048540
binlog_cache_size: 32768
connect_timeout: 5
delayed_insert_limit: 100
delayed_insert_timeout: 300
delayed_queue_size: 1000
flush_time: 0
interactive_timeout: 28800
join_buffer_size: 131072
key_buffer_size: 1048540
long_query_time: 10
lower_case_table_names: 0
max_allowed_packet: 1048576
max_binlog_cache_size: 4294967295
max_connect_errors: 10
max_connections: 100
max_delayed_threads: 20
max_heap_table_size: 16777216
max_join_size: 4294967295
max_sort_length: 1024
max_tmp_tables: 32
max_write_lock_count: 4294967295
myisam_sort_buffer_size: 8388608
net_buffer_length: 16384
net_read_timeout: 30
net_retry_count: 10
net_write_timeout: 60
read_buffer_size: 131072
read_rnd_buffer_size: 262144
slow_launch_time: 2
sort_buffer: 2097116
table_cache: 64
thread_concurrency: 10
thread_stack: 131072
tmp_table_size: 1048576
wait_timeout: 28800

from http://dev.mysql.com/doc/refman/4.1/en/ ... eters.html
You only have one CPU so thread_concurrency should be around 2 (4 maybe for Hyperthreading?) not 120 though. That could be one problem. The default read_buffer_size and sort_buffer_size are 128K and 2M respectively - 100K is kind of odd value. Default key_buffer is 1M, a medium size is 16M - 512K seems small. thread_stack should probably be a multiple of 1024 -- 512K = 524288. Finding the right query_cache_size is an art so good luck with that. :wink:

I think thread_cache is suppose to be thread_cache_size (bug from 4.1.16 ini file). Only reason to use thread_cache_size I can find is if you have a memory leak. In which case, thread_cache_size should equal max_connections+1.

That just what sticks out to me but I'm certainly no expert.

Have you looked at the defualt cfn/ini file that come with MySQL now? There's four files specifically for different size databases from small to huge. Seems a good place to start tweekin'.
Post Reply