Solved

Help me optimize my server....(vbulletin community)

Posted on 2009-04-08
3
743 Views
Last Modified: 2012-08-14
Our server runs vbulletin and we have an average of about 200 users. Our server load is high and we occasionally (rarely) experience database errors.

Do you guys have any suggestions?

Thanks

(below is some stats)


=============================================================
SERVER INFO

Dedicated
Pentium 4 2.4Ghz
1GB RAM
80gb SATA
CENTOS Enterprise 4.6
Apache version 1.3.41 (Unix)
PHP version 5.2.5
MySQL version 5.0.67
VB Version 3.6.7

=============================================================

TOP

top - 10:21:31 up 26 days,  6:17,  1 user,  load average: 3.06, 3.88, 3.19
Tasks: 133 total,  10 running, 123 sleeping,   0 stopped,   0 zombie
Cpu0  : 28.6% us,  4.3% sy,  0.2% ni, 63.7% id,  3.2% wa,  0.1% hi,  0.0% si
Mem:   1027516k total,   933680k used,    93836k free,    39928k buffers
Swap:  2096472k total,    66108k used,  2030364k free,   587724k cached
=============================================================


MY.CNF

[QUOTE][mysqld]
datadir=/var/lib/mysql
skip-locking
safe-show-database
query_cache_limit=1M
query_cache_size=32M ## 32MB for every 1GB of RAM
query_cache_type=1
max_user_connections=30
max_connections=500
interactive_timeout=10
wait_timeout=20
connect_timeout=20
thread_cache_size=128
key_buffer=32M ## 32MB for every 1GB of RAM
join_buffer=1M
max_connect_errors=20
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=1M ## 1MB for every 1GB of RAM
read_buffer_size=1M ## 1MB for every 1GB of RAM
read_rnd_buffer_size=1M  ## 1MB for every 1GB of RAM
thread_concurrency=2 ## Number of CPUs x 2
myisam_sort_buffer_size=64M
server-id=1

collation_server=utf8_unicode_ci
character_set_server=utf8

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

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

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash[/QUOTE]

=============================================================
MYSQLREPORT

# Beginning report, 0 0:0:0
#
MySQL 5.0.67-community   uptime 0 0:5:59        Wed Apr  8 10:26:17 2009

__ Key _________________________________________________________________
Buffer used     8.03M of  32.00M  %Used:  25.08
  Current      11.71M            %Usage:  36.60
Write hit      17.93%
Read hit       98.64%

__ Questions ___________________________________________________________
Total          23.11k    64.4/s
  QC Hits      11.21k    31.2/s  %Total:  48.50
  DMS          10.21k    28.5/s           44.19
  Com_            863     2.4/s            3.73
  COM_QUIT        835     2.3/s            3.61
  -Unknown          9     0.0/s            0.04
Slow 10 s           0       0/s            0.00  %DMS:   0.00  Log: OFF
DMS            10.21k    28.5/s           44.19
  SELECT        7.75k    21.6/s           33.54         75.89
  UPDATE        1.47k     4.1/s            6.38         14.43
  INSERT          529     1.5/s            2.29          5.18
  DELETE          237     0.7/s            1.03          2.32
  REPLACE         223     0.6/s            0.96          2.18
Com_              863     2.4/s            3.73
  change_db       820     2.3/s            3.55
  set_option       32     0.1/s            0.14
  show_status       6     0.0/s            0.03

__ SELECT and Sort _____________________________________________________
Scan            2.43k     6.8/s %SELECT:  31.36
Range           1.69k     4.7/s           21.81
Full join           6     0.0/s            0.08
Range check         0       0/s            0.00
Full rng join       0       0/s            0.00
Sort scan       1.81k     5.1/s
Sort range        912     2.5/s
Sort mrg pass       0       0/s

__ Query Cache _________________________________________________________
Memory usage    5.32M of  32.00M  %Used:  16.62
Block Fragmnt   7.42%
Hits           11.21k    31.2/s
Inserts         6.11k    17.0/s
Insrt:Prune   6.11k:1    17.0/s
Hit:Insert     1.83:1

__ Table Locks _________________________________________________________
Waited             27     0.1/s  %Total:   0.15
Immediate      17.48k    48.7/s

__ Tables ______________________________________________________________
Open              114 of 1024    %Cache:  11.13
Opened            120     0.3/s

__ Connections _________________________________________________________
Max used           14 of  500      %Max:   2.80
Total             838     2.3/s

__ Created Temp ________________________________________________________
Disk table         58     0.2/s
Table           1.90k     5.3/s    Size:  32.0M
File                5     0.0/s

__ Threads _____________________________________________________________
Running             3 of    6
Cached              8 of  128      %Hit:  98.33
Created            14     0.0/s
Slow                0       0/s

__ Aborted _____________________________________________________________
Clients             2     0.0/s
Connects            1     0.0/s

__ Bytes _______________________________________________________________
Sent          201.67M  561.8k/s
Received        5.59M   15.6k/s

__ InnoDB Buffer Pool __________________________________________________
Usage         336.00k of   8.00M  %Used:   4.10
Read hit       94.07%
Pages
  Free            491            %Total:  95.90
  Data             21                      4.10 %Drty:   0.00
  Misc              0                      0.00
  Latched           0                      0.00
Reads             236     0.7/s
  From file        14     0.0/s            5.93
  Ahead Rnd         1     0.0/s
  Ahead Sql         0       0/s
Writes              0       0/s
Flushes             0       0/s
Wait Free           0       0/s

__ InnoDB Lock _________________________________________________________
Waits               0       0/s
Current             0
Time acquiring
  Total             0 ms
  Average           0 ms
  Max               0 ms

__ InnoDB Data, Pages, Rows ____________________________________________
Data
  Reads            27     0.1/s
  Writes            3     0.0/s
  fsync             3     0.0/s
  Pending
    Reads           0
    Writes          0
    fsync           0

Pages
  Created           0       0/s
  Read             21     0.1/s
  Written           0       0/s

Rows
  Deleted           0       0/s
  Inserted          0       0/s
  Read              0       0/s
  Updated           0       0/s

#
# Interval report 1, +0 0:0:10
#
MySQL 5.0.67-community   uptime 0 0:6:9         Wed Apr  8 10:26:27 2009

__ Key _________________________________________________________________
Buffer used     8.11M of  32.00M  %Used:  25.35
  Current      11.80M            %Usage:  36.87
Write hit      15.00%
Read hit       99.10%

__ Questions ___________________________________________________________
Total             248    24.8/s
  DMS             129    12.9/s  %Total:  52.02
  QC Hits          94     9.4/s           37.90
  Com_             15     1.5/s            6.05
  COM_QUIT         11     1.1/s            4.44
  -Unknown          1     0.1/s            0.40
Slow 10 s           0       0/s            0.00  %DMS:   0.00  Log: OFF
DMS               129    12.9/s           52.02
  SELECT           98     9.8/s           39.52         75.97
  UPDATE           21     2.1/s            8.47         16.28
  INSERT            4     0.4/s            1.61          3.10
  REPLACE           3     0.3/s            1.21          2.33
  DELETE            3     0.3/s            1.21          2.33
Com_               15     1.5/s            6.05
  change_db        12     1.2/s            4.84
  show_status       1     0.1/s            0.40
  set_option        1     0.1/s            0.40

__ SELECT and Sort _____________________________________________________
Scan               31     3.1/s %SELECT:  31.63
Range              22     2.2/s           22.45
Full join           0       0/s            0.00
Range check         0       0/s            0.00
Full rng join       0       0/s            0.00
Sort scan          24     2.4/s
Sort range          8     0.8/s
Sort mrg pass       0       0/s

__ Query Cache _________________________________________________________
Memory usage    5.32M of  32.00M  %Used:  16.64
Block Fragmnt   8.25%
Hits               94     9.4/s
Inserts            74     7.4/s
Insrt:Prune      74:1     7.3/s
Hit:Insert     1.27:1

__ Table Locks _________________________________________________________
Waited              9     0.9/s  %Total:   4.13
Immediate         209    20.9/s

__ Tables ______________________________________________________________
Open              121 of 1024    %Cache:  11.82
Opened              7     0.7/s

__ Connections _________________________________________________________
Max used           14 of  500      %Max:   2.80
Total              13     1.3/s

__ Created Temp ________________________________________________________
Disk table          0       0/s
Table              26     2.6/s    Size:  32.0M
File                0       0/s

__ Threads _____________________________________________________________
Running             1 of    9
Cached              5 of  128      %Hit:    100
Created             0       0/s
Slow                0       0/s

__ Aborted _____________________________________________________________
Clients             0       0/s
Connects            0       0/s

__ Bytes _______________________________________________________________
Sent            2.89M  289.0k/s
Received       73.07k    7.3k/s

__ InnoDB Buffer Pool __________________________________________________
Usage         336.00k of   8.00M  %Used:   4.10
Read hit        0.00%
Pages
  Free            491            %Total:  95.90
  Data             21                      4.10 %Drty:   0.00
  Misc              0                      0.00
  Latched           0                      0.00
Reads               0       0/s
  From file         0       0/s            0.00
  Ahead Rnd         0       0/s
  Ahead Sql         0       0/s
Writes              0       0/s
Flushes             0       0/s
Wait Free           0       0/s

__ InnoDB Lock _________________________________________________________
Waits               0       0/s
Current             0
Time acquiring
  Total             0 ms
  Average           0 ms
  Max               0 ms

__ InnoDB Data, Pages, Rows ____________________________________________
Data
  Reads             0       0/s
  Writes            0       0/s
  fsync             0       0/s
  Pending
    Reads           0
    Writes          0
    fsync           0

Pages
  Created           0       0/s
  Read              0       0/s
  Written           0       0/s

Rows
  Deleted           0       0/s
  Inserted          0       0/s
  Read              0       0/s
  Updated           0       0/s[/QUOTE]
=============================================================

FORUM TRAFFIC

Average max concurrent users: 200
Max: 280
cookie timeout: 1800
=============================================================

PHPINFO

http://69.93.158.106/phpinfo.php
=============================================================

HTTPD.CONF
 
[QUOTE]KeepAlive Off
MaxKeepAliveRequests 5
KeepAliveTimeout 5
MinSpareServers 25
MaxSpareServers 32
StartServers 30
#MaxClients 1000
#MaxRequestsPerChild 500
MaxClients 150
MaxRequestsPerChild 100[/QUOTE]


uname -a

Linux 2.6.9-67.0.22.EL #1 Wed Jul 23 17:17:45 EDT 2008 i686 i686 i386 GNU/Linux

=============================================================


ulimit -aH


core file size          (blocks, -c) 1000000
data seg size           (kbytes, -d) unlimited
file size               (blocks, -f) unlimited
pending signals                 (-i) 1024
max locked memory       (kbytes, -l) 32
max memory size         (kbytes, -m) unlimited
open files                      (-n) 4096
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 14335
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited
=============================================================


cat /proc/cpuinfo

processor       : 0
vendor_id       : GenuineIntel
cpu family      : 15
model           : 2
model name      : Intel(R) Pentium(R) 4 CPU 2.40GHz
stepping        : 9
cpu MHz         : 2399.734
cache size      : 512 KB
fdiv_bug        : no
hlt_bug         : no
f00f_bug        : no
coma_bug        : no
fpu             : yes
fpu_exception   : yes
cpuid level     : 2
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe cid xtpr
bogomips        : 4800.63

=============================================================
0
Comment
Question by:techempire
  • 2
3 Comments
 
LVL 21

Expert Comment

by:theGhost_k8
Comment Utility
Do you know about mysqltuner ?
http://kedar.nitty-witty.com/miscpages/using-mysqltuner.php

or directly the product site: http://wiki.mysqltuner.com/MySQLTuner
0
 

Author Comment

by:techempire
Comment Utility
yeah i have it, also have tuning-primer

0
 

Accepted Solution

by:
techempire earned 0 total points
Comment Utility
need to delete
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
this article is a guided solution for most of the common server issues in server hardware tasks we are facing in our routine job works. the topics in the following article covered are, 1) dell hardware raidlevel (Perc) 2) adding HDD 3) how t…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now