Server Optimizing Request
 

<div>
1. Is this on dedicated or shared virual server. If shared, how many sites share this server (ask web host if needed)
Quote:
Dedicated Server
2. your server specs. For example:

Quote:
cpu speed/type single or dual cpus): Xeon E3110 3.0Ghz
how much memory installed: 8gb Ram
hard drive type/configuration: 250Gb Sata II
linux distributor or windows version: Centos 5.3 i686
apache/IIS version: apache 2.0
PHP version: php 5.2.9
MySQL version: mysql 5.0.77
3. what version of vB are you running ?
Quote:
Version 3.8.2
4. if you use mysql 4.x instead of mysql 3.23.x, do you have any innodb type databases/tables on your server ?

Quote:
No, only MyiSAM tables and Memory tables for cpsession and sessions
5. if possible how mysql was compiled/installed
Quote:
with Cpanel
6. your top stats. So log into your server via ssh telnet as root user and type the command in bold - top
Quote:
top - 17:53:43 up 10 days, 22:25, 1 user, load average: 0.66, 0.68, 0.71
Tasks: 147 total, 1 running, 145 sleeping, 0 stopped, 1 zombie
Cpu0 : 13.0%us, 1.3%sy, 0.0%ni, 85.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu1 : 31.7%us, 1.3%sy, 0.0%ni, 66.0%id, 0.0%wa, 0.0%hi, 1.0%si, 0.0%st
Mem: 8310480k total, 6987416k used, 1323064k free, 626484k buffers
Swap: 2096472k total, 0k used, 2096472k free, 5352684k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
5264 nobody 15 0 99.9m 22m 9088 S 4.3 0.3 0:00.78 httpd
4588 nobody 16 0 101m 24m 10m S 4.0 0.3 0:03.24 httpd
4671 nobody 15 0 99.2m 23m 10m S 3.3 0.3 0:02.22 httpd
20946 mysql 15 0 878m 426m 3428 S 3.3 5.3 144:40.45 mysqld
5226 nobody 16 0 100m 21m 7392 S 2.3 0.3 0:01.16 httpd
5233 nobody 16 0 100m 20m 6916 S 2.3 0.3 0:00.64 httpd
5267 nobody 15 0 99.1m 20m 8040 S 2.3 0.3 0:00.51 httpd
2272 nobody 15 0 100m 26m 12m S 2.0 0.3 0:06.42 httpd
3925 nobody 15 0 99.7m 23m 10m S 2.0 0.3 0:05.14 httpd
5277 nobody 15 0 100m 20m 6740 S 2.0 0.3 0:00.34 httpd
5293 nobody 15 0 99.1m 19m 6248 S 2.0 0.2 0:00.23 httpd
5225 nobody 16 0 98.6m 19m 7308 S 1.3 0.2 0:00.94 httpd
4718 nobody 15 0 98.6m 19m 7008 S 1.0 0.2 0:01.18 httpd
5252 nobody 15 0 99.7m 20m 6588 S 1.0 0.2 0:00.50 httpd
4524 nobody 15 0 99.4m 22m 9280 S 0.7 0.3 0:02.61 httpd
4612 nobody 15 0 99.9m 21m 7912 S 0.7 0.3 0:02.31 httpd
7. your mysql configuration variables located at /etc/my.cnf or c:\my.cnf or my.ini so post the contents inside of my.cnf (minus any passwords of course).

Quote:
[mysqld]
skip-name-resolve
skip-host-cache
safe-show-database
skip-innodb
back_log = 50
max_connections = 500
key_buffer_size = 640M
myisam_sort_buffer_size = 128M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 5000
thread_cache_size = 384
wait_timeout = 20
connect_timeout = 10
tmp_table_size = 128M
max_heap_table_size = 128M
max_allowed_packet = 128M
net_buffer_length = 16384
max_connect_errors = 100
thread_concurrency = 4
concurrent_insert = 2
table_lock_wait_timeout = 30
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 8M
query_cache_limit = 8M
query_cache_size = 196M
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]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 640M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M


8. MySQL stats from ssh telnet as root user type:
<div style="margin:20px; margin-top:5px; "> Quote:

+---------------------------------+------------------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | / |
| binlog_cache_size | 32768 |
| 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 |
| keep_files_on_create | OFF |
| engine_condition_pushdown | OFF |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + ->