السلام عليكم ورحمة الله
الاخوة الكرام احتاج مساعدة من اجل ظبط سرفري بمايتناسب مع منتداي كلبت مساعدة من شركة الفي بي لكن طلبوا مني هذه الاشياء التي عرفت منها القليل وعجوت عن فهم الكثير منها . المرجو من لديه خبرة باوامر السرفر وباللغة الانجليزية ان يساعدني لان منتداي يعاني من بطؤ شديد رغم قوة السرفر
في انتظاركم اخواني


هذه طلباتهم ومن لديه عضوية يمكنه تصفح الموضوع الاصلي هنا
http://www.vbulletin.com/forum/showt...sts-13-10-2010

[Required]: Info for optimisation / troubleshooting requests [13/10/2010]
Tip: It's a good idea when you post a new thread with this info that you subscribe with email notify to your thread so get notified when i reply to your thread

In order to help you with server problems and optimization, please provide the following info in your thread (courtesy of eva2000):

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

cpu speed/type single or dual cpus): dual p3 1ghz
how much memory installed: 512mb ram
hard drive type/configuration: 60GB EIDE non raid
linux distributor or windows version: redhat linux 7.3
apache/IIS version: apache 1.3.33
PHP version: php 4.3.11
MySQL version: mysql 4.0.25

Post output for these commands

For Linux:

cat /proc/cpuinfo

For FreeBSD:

dmesg | grep -i CPU

3. what version of vB are you running ?

4. if you use mysql 4.x instead of mysql 3.23.x, do you have any innodb type databases/tables on your server ? If you use innodb tables, please see question #15 below.
5. if possible how mysql was compiled/installed
6. your top stats. So log into your server via ssh telnet as root user and type the command in bold - top

You'll see stats like the following

top - 09:10:39 up 30 days, 8:40, 1 user, load average: 0.28, 0.34, 0.34
Tasks: 74 total, 1 running, 71 sleeping, 0 stopped, 2 zombie
Cpu(s): 0.7%us, 0.0%sy, 0.0%ni, 98.9%id, 0.3%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 1048576k total, 742764k used, 305812k free, 0k buffers
Swap: 0k total, 0k used, 0k free, 0k cached
Now while top is running hit the number 1 on keyboard to show break down of cpu load over all cpu cores. For example a Q6600 quad core kentsfield processor will have 4 cores. A dual Xeon 53xx/54xx quad core clovertown and harpertown processor based server will have 2x 4 = 8 cores listed.

i.e. after hitting 1

top - 09:14:37 up 30 days, 8:44, 1 user, load average: 0.15, 0.21, 0.27
Tasks: 72 total, 1 running, 69 sleeping, 0 stopped, 2 zombie
Cpu0 : 0.3%us, 0.0%sy, 0.0%ni, 97.0%id, 2.7%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu1 : 5.7%us, 0.3%sy, 0.0%ni, 94.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 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu5 : 2.3%us, 0.3%sy, 0.0%ni, 97.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu6 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Cpu7 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 1048576k total, 732140k used, 316436k free, 0k buffers
Swap: 0k total, 0k used, 0k free, 0k cached
Best to provide the 2nd example top stats in your threads where possible.

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).
8. MySQL stats from ssh telnet as root user type:

mysqladmin -u root -p var ext stat ver proc
copy and paste output here

Also if you want to better see what's going on install mysqlreport at http://www.vbulletin.com/forum/showthread.php?t=175177 and run it by typing

./mysqlreport -r 10 > /statistics.txt
in ssh telnet and then copy and paste the output you see or look into statistics.txt file located at root directory /

Example below

./mysqlreport -r 10
mysqlreport is writing relative reports to '/tmp/RdzeAfNfbK'.
#
# Beginning report, 0 0:0:0
#
MySQL 5.0.45-community uptime 0 4:23:19 Mon Mar 31 06:11:12 2008

__ Key __________________________________________________ _______________
Buffer used 65.65M of 512.00M %Used: 12.82
Current 124.58M %Usage: 24.33
Write hit 90.65%
Read hit 99.92%

__ Questions __________________________________________________ _________
Total 1.68M 106.3/s
QC Hits 715.53k 45.3/s %Total: 42.60
DMS 657.63k 41.6/s 39.15
Com_ 181.55k 11.5/s 10.81
COM_QUIT 125.30k 7.9/s 7.46
-Unknown 213 0.0/s 0.01
Slow (10) 0 0/s 0.00 %DMS: 0.00 Log: OFF
DMS 657.63k 41.6/s 39.15
SELECT 447.11k 28.3/s 26.62 67.99
UPDATE 132.82k 8.4/s 7.91 20.20
INSERT 65.03k 4.1/s 3.87 9.89
REPLACE 10.01k 0.6/s 0.60 1.52
DELETE 2.66k 0.2/s 0.16 0.40
Com_ 181.55k 11.5/s 10.81
change_db 130.68k 8.3/s 7.78
show_fields 18.77k 1.2/s 1.12
show_tables 17.07k 1.1/s 1.02

__ SELECT and Sort __________________________________________________ ___
Scan 100.95k 6.4/s %SELECT: 22.58
Range 80.37k 5.1/s 17.97
Full join 22 0.0/s 0.00
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 54.88k 3.5/s
Sort range 68.02k 4.3/s
Sort mrg pass 24 0.0/s

__ Query Cache __________________________________________________ _______
Memory usage 177.52M of 256.00M %Used: 69.34
Block Fragmnt 18.31%
Hits 715.53k 45.3/s
Inserts 399.24k 25.3/s
Insrt:Prune 69.52:1 24.9/s
Hit:Insert 1.79:1

__ Table Locks __________________________________________________ _______
Waited 7.61k 0.5/s %Total: 0.65
Immediate 1.17M 74.1/s

__ Tables __________________________________________________ ____________
Open 566 of 4000 %Cache: 14.15
Opened 6.49k 0.4/s

__ Connections __________________________________________________ _______
Max used 28 of 650 %Max: 4.31
Total 125.62k 8.0/s

__ Created Temp __________________________________________________ ______
Disk table 63.68k 4.0/s
Table 92.11k 5.8/s Size: 64.0M
File 48 0.0/s

__ Threads __________________________________________________ ___________
Running 1 of 1
Cached 27 of 512 %Hit: 99.98
Created 28 0.0/s
Slow 0 0/s

__ Aborted __________________________________________________ ___________
Clients 631 0.0/s
Connects 0 0/s

__ Bytes __________________________________________________ _____________
Sent 4.00G 253.2k/s
Received 375.24M 23.8k/s

__ InnoDB Buffer Pool __________________________________________________
Usage 0 of 0 %Used: 0.00
Read hit 0.00%
Pages
Free 0 %Total: 0.00
Data 0 0.00 %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

#
# Interval report 1, +0 0:0:10
#
MySQL 5.0.45-community uptime 0 4:23:29 Mon Mar 31 06:11:22 2008

__ Key __________________________________________________ _______________
Buffer used 65.65M of 512.00M %Used: 12.82
Current 124.58M %Usage: 24.33
Write hit 94.90%
Read hit 99.95%

__ Questions __________________________________________________ _________
Total 1.30k 130.1/s
QC Hits 546 54.6/s %Total: 41.97
DMS 424 42.4/s 32.59
Com_ 236 23.6/s 18.14
COM_QUIT 95 9.5/s 7.30
Slow (10) 0 0/s 0.00 %DMS: 0.00 Log: OFF
DMS 424 42.4/s 32.59
SELECT 302 30.2/s 23.21 71.23
UPDATE 76 7.6/s 5.84 17.92
INSERT 38 3.8/s 2.92 8.96
REPLACE 8 0.8/s 0.61 1.89
DELETE 0 0/s 0.00 0.00
Com_ 236 23.6/s 18.14
change_db 99 9.9/s 7.61
show_fields 59 5.9/s 4.53
show_tables 57 5.7/s 4.38

__ SELECT and Sort __________________________________________________ ___
Scan 155 15.5/s %SELECT: 51.32
Range 71 7.1/s 23.51
Full join 0 0/s 0.00
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 61 6.1/s
Sort range 40 4.0/s
Sort mrg pass 0 0/s

__ Query Cache __________________________________________________ _______
Memory usage 178.32M of 256.00M %Used: 69.66
Block Fragmnt 18.11%
Hits 546 54.6/s
Inserts 238 23.8/s
Insrt:Prune 238:1 23.7/s
Hit:Insert 2.29:1

__ Table Locks __________________________________________________ _______
Waited 1 0.1/s %Total: 0.13
Immediate 788 78.8/s

__ Tables __________________________________________________ ____________
Open 566 of 4000 %Cache: 14.15
Opened 16 1.6/s

__ Connections __________________________________________________ _______
Max used 28 of 650 %Max: 4.31
Total 97 9.7/s

__ Created Temp __________________________________________________ ______
Disk table 118 11.8/s
Table 188 18.8/s Size: 64.0M
File 0 0/s

__ Threads __________________________________________________ ___________
Running 1 of 3
Cached 25 of 512 %Hit: 100
Created 0 0/s
Slow 0 0/s

__ Aborted __________________________________________________ ___________
Clients 0 0/s
Connects 0 0/s

__ Bytes __________________________________________________ _____________
Sent 27.67M 2.8M/s
Received 268.76k 26.9k/s

__ InnoDB Buffer Pool __________________________________________________
Usage 0 of 0 %Used: 0.00
Read hit 0.00%
Pages
Free 0 %Total: 0.00
Data 0 0.00 %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
9. is your vB the only thing on the server? or other scripts & sites which utilise php and mysql?

10. how many average and max concurrent users on your vB forum ? and what your cookie timeout is ?

11. create a file named phpinfo.php and place this code in it and post the url/link to it from your web site. Please url link only - DO NOT copy and paste entire phpinfo.php page's contents to the thread!

PHP Code:
<?
phpinfo();
?>
i.e. yourdomain.com/phpinfo.php
12. if you run Apache and you have your own dedicated server or access to your httpd.conf (apache configuration file) can you post the values you have set for the following :

Apache 2 users: If you can't find these values in httpd.conf it's because they have moved. You may find the values in /usr/local/apache/conf/extra for http-mpm.conf and http-default.conf. Copy and paste the exact format and values of those settings in your post.

***

If you can't find these values in httpd.conf on your server, it could be different apache version so also look into

/usr/local/apache/conf/extra/httpd-mpm.conf
/usr/local/apache/conf/extra/httpd-default.conf

also for WHM/Cpanel control panel users try logging into WHM -> Service Configuration -> Apache configuration -> Global Configuration which will list fields for Start Servers, MinSpareSevers, MaxSpareServers, Maxclients, MaxRequestsPerChild

To verify what is set, you can also check your phpinfo.php page under apache2handler look at perchild, keep-alive time out and max per connection values which respond to maxrequestsperchild, keep alive timeout, and maxkeepaliverequests respectively.

***

in httpd-default.conf

Timeout 300
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 3
in httpd-mpm.conf you'll find

<IfModule mpm_prefork_module>
StartServers 50
MinSpareServers 15
MaxSpareServers 30
MaxClients 256
MaxRequestsPerChild 1000
</IfModule>

<IfModule mpm_worker_module>
StartServers 10
MaxClients 256
MinSpareThreads 25
MaxSpareThreads 75
ThreadsPerChild 25
MaxRequestsPerChild 1000
</IfModule>

Apache 1 users:

In httpd.conf you'll find below settings. Copy and paste the exact format and values of those settings in your post.

KeepAlive
MaxKeepAliveRequests
KeepAliveTimeout
MinSpareServers
MaxSpareServers
StartServers
MaxClients

AND Maxrequestsperchild value =
To find values on linux you need to locate httpd.conf file first and then open it with linux text editor like vi, pico or nano.

1. In ssh telnet find httpd.conf type these commands as root user.

updatedb

locate httpd.conf

whereis httpd.conf

Usually for apache or apache 2 it will be at one of these locations

/usr/local/apache/conf/httpd.conf
/usr/local/apache2/conf/httpd.conf

2. Open httpd.conf in linux text editor such as pico, nano or vi. On safe side open a back up copy of the httpd.conf not the live one in case you mess it up.

So to back up file with command:

cp /usr/local/apache/conf/httpd.conf /usr/local/apache/conf/httpd.conf-mybackup

Now to open backup copy httpd.conf-mybackup, type one of these 3 commands in ssh telnet as root user

pico /usr/local/apache/conf/httpd.conf-mybackup
nano /usr/local/apache/conf/httpd.conf-mybackup
vi /usr/local/apache/conf/httpd.conf-mybackup

3. To jump to the revelant location quicker for nano or pico text editors hit

CTRL+W

a search prompt shows up bottom left of screen, type in there 'maxclients' without quote marks and hit enter to jump to the area where the options needed are listed and use up and down arrows to find the values asked.



13. check to see if any files i.e. apache log files are hitting 2GB or 4GB max file size limits i.e. see if you have max file size exceeded messages in apache error log

You can use this command to find such files
find / -size +2000000k
14. Post output from these 2 commands

uname -a

ulimit -aH



15. If you have converted to Innodb tables from the default MyISAM tables via ALTER TABLE tablename ENGINE = InnoDB; command, then you may need additional innodb buffer tuning and knowing your table data/index sizes will help. In ssh telnet while in mysql client command prompt, some useful queries to find out database and table data and index size totals, where databasename = databasename

Calculate Total Database Index Size

Code:
mysql> SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size'
FROM information_schema.TABLES
WHERE table_schema LIKE 'databasename';
result

Code:
+------------------+
| Total Index Size |
+------------------+
| 820.68 MB |
+------------------+
1 row in set (0.02 sec)
Calculate the Total Size of Table Data
Code:
mysql> SELECT CONCAT(ROUND(SUM(data_length)/(1024*1024), 2), ' MB') AS 'Total Data Size'
FROM information_schema.TABLES
WHERE table_schema LIKE 'databasename';
result

Code:
+-----------------+
| Total Data Size |
+-----------------+
| 1143.07 MB |
+-----------------+
1 row in set (0.01 sec)
Per Table Sizes

Code:
SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name', CONCAT(ROUND(table_rows,2),' Rows') AS 'Number of Rows',
CONCAT(ROUND(data_length/(1024*1024),2),'MB') AS 'Data Size',
CONCAT(ROUND(index_length/(1024*1024),2),'MB') AS 'Index Size' ,
CONCAT(ROUND((data_length+index_length)/(1024*1024),2),'MB') AS'Total'
FROM information_schema.TABLES
WHERE table_schema LIKE 'databasename';
Code:
+------------------------------------------------+----------------+-----------+------------+-----------+
| Table Name | Number of Rows | Data Size | Index Size | Total |
+------------------------------------------------+----------------+-----------+------------+-----------+
| vbdbname.vb3_access | 0 Rows | 0.00MB | 0.00MB | 0.00MB |
| vbdbname.vb3_adminhelp | 1394 Rows | 0.07MB | 0.06MB | 0.13MB |
| vbdbname.vb3_administrator | 16 Rows | 0.00MB | 0.00MB | 0.00MB |
| vbdbname.vb3_adminlog | 858 Rows | 0.05MB | 0.02MB | 0.06MB |
| vbdbname.vb3_adminmessage | 2 Rows | 0.00MB | 0.01MB | 0.01MB |
| vbdbname.vb3_adminutil | 4 Rows | 0.66MB | 0.00MB | 0.66MB |
| vbdbname.vb3_announcement | 5 Rows | 0.00MB | 0.00MB | 0.01MB |
| vbdbname.vb3_announcementread | 5 Rows | 0.00MB | 0.00MB | 0.00MB |
| vbdbname.vb3_arcade_favorites | 155 Rows | 0.00MB | 0.00MB | 0.01MB |
| vbdbname.vb3_attachment | 410 Rows | 0.04MB | 0.05MB | 0.09MB |
| vbdbname.vb3_attachmentpermission | 0 Rows | 0.00MB | 0.00MB | 0.00MB |
| vbdbname.vb3_attachmenttype | 12 Rows | 0.00MB | 0.00MB | 0.00MB |
| vbdbname.vb3_attachmentviews | 12 Rows | 0.00MB | 0.00MB | 0.00MB |
| vbdbname.vb3_avatar | 2121 Rows | 0.42MB | 0.04MB