Another optimisation request

محروم.كوم · #1 05-22-2009, 12:41 PM

<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 | + -> __DEFINE_LIKE_SHARE__

أدوات الموضوع
مشاهدة صفحة طباعة الموضوع أرسل هذا الموضوع إلى صديق
انواع عرض الموضوع
الانتقال إلى العرض العادي الانتقال إلى العرض المتطور العرض الشجري

المواضيع المتشابهه
الموضوع	كاتب الموضوع	المنتدى	مشاركات	آخر مشاركة
Apache Optimisation Request	محروم.كوم	منتدى أخبار المواقع والمنتديات العربية والأجنبية	0	06-22-2009 11:50 PM
Server Optimisation Request	محروم.كوم	منتدى أخبار المواقع والمنتديات العربية والأجنبية	0	06-21-2009 10:30 PM
Server Optimisation Request	محروم.كوم	منتدى أخبار المواقع والمنتديات العربية والأجنبية	0	05-06-2009 07:10 PM
Server (VPS) optimisation request	محروم.كوم	منتدى أخبار المواقع والمنتديات العربية والأجنبية	0	04-21-2009 01:10 AM
Server (VPS) optimisation request	محروم.كوم	منتدى أخبار المواقع والمنتديات العربية والأجنبية	0	04-21-2009 01:00 AM