Another optimisation request
 

<div>Hi All. We're having some issues with locked tables (http://www.vbulletin.com/forum/showt...31#post1738531). Ideally would like some advice on the mysql settings as I think this is the area that is problematic.

Thanks!

1. Is this on dedicated or shared virual server.

Dedicated

2. Server Specs:

Dual E5320
8GB FBDimm
8 x 15k SAS, adaptec raid 10 array
CentOS 5.something
Litespeed Web Server Enterprise v4.0.2
PHP 5.2.9
MySQL 5.1.34

3. Version of vBulletin:

Powered by vBulletin® Version 3.8.2

4. N/A

5. How MySQL was installed:

Via RPM

6. Top stats:

-----
top - 01:04:22 up 20 days, 8:01, 1 user, load average: 0.09, 0.21, 0.15
Tasks: 331 total, 1 running, 330 sleeping, 0 stopped, 0 zombie
Cpu0 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu1 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu2 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu3 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu4 : 1.0%us, 0.3%sy, 0.0%ni, 98.3%id, 0.3%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu5 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu6 : 0.0%us, 0.0%sy, 0.0%ni, 99.7%id, 0.0%wa, 0.0%hi, 0.3%si, 0.0%st
Cpu7 : 0.3%us, 0.0%sy, 0.0%ni, 99.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 8178308k total, 6976396k used, 1201912k free, 233100k buffers
Swap: 2031608k total, 81092k used, 1950516k free, 5694800k cached
-----

7. # cat /etc/my.cnf

-----
[mysqld]
skip-innodb
old-passwords=1
table_open_cache=2048
table_definition_cache=2048
query_cache_size=268435456
query_cache_limit=6291456
max_connections=1024
thread_cache_size=768
key_buffer_size=512000000
join_buffer_size=2097152
read_buffer_size=131072
sort_buffer_size=2097144
open_files_limit=2048
connect_timeout=10
wait_timeout=20
interactive_timeout=20
thread_concurrency=8
low_priority_updates=1
concurrent_insert=2
table_cache=2048
tmp_table_size=536870912
max_heap_table_size=536870912
table_lock_wait_timeout=30
max_allowed_packet=134217728
max_connect_errors=100000
read_rnd_buffer_size=1048576
bulk_insert_buffer_size=8388608
query_cache_type=1
query_prealloc_size=262144
query_alloc_block_size=65536
transaction_alloc_block_size=8192
transaction_prealloc_size=4096
default-storage-engine=MyISAM
max_write_lock_count=4

[mysqld_safe]
nice = -5
open_files_limit = 8192
-----

8. MySQL stats (I had just restarted the mysql server):

-----
+---------------------------------+-------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------------------------------------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| autocommit | ON |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | / |
| big_tables | OFF |
| binlog_cache_size | 32768 |
| binlog_format | STATEMENT |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| completion_type | 0 |
| concurrent_insert | 2 |
| connect_timeout | 10 |
| datadir | /var/lib/mysql/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| div_precision_increment | 4 |
| engine_condition_pushdown | ON |
| error_count | 0 |
| event_scheduler | OFF |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 0 |
| foreign_key_checks | ON |
| ft_boolean_syntax | + ->