I want to make sure it is optimized so it will run as fast as possible. In the past it is runs smoothly 80% of the time... the other 20% of the time it can be molasses slow.
The server is a dedicated server over at VenturesOnline...
Setup includes
vB 2.2.4 (to be upgraded to 2.2.8)
Pentium 1.13 single CPU
512mb RAM
Single 18 GB drive
What do you guys need from me to help me optimize my setup?
mysqlinfo-
Wed Nov 20 19:20:38 MST 2002
7:20pm up 36 days, 14:50, 1 user, load average: 2.68, 1.91, 1.93
114 processes: 111 sleeping, 2 running, 1 zombie, 0 stopped
Mem: 771700K av, 759168K used, 12532K free, 0K shrd, 27632K buff
Swap: 530104K av, 44236K used, 485868K free 599040K cached
Http processes currently running = 46
Mysql processes currently running = 13
Netstat information summary
1 CLOSE_WAIT
4 FIN_WAIT1
9 ESTABLISHED
18 SYN_RECV
24 LISTEN
1077 TIME_WAIT
+---------------------------+-----------------+
Variable_name Value
+---------------------------+-----------------+
Aborted_clients 2766853
Aborted_connects 1612
Bytes_received 3230729406
Bytes_sent 2388875273
Connections 8094650
Created_tmp_disk_tables 1315
Created_tmp_tables 2663106
Created_tmp_files 0
Delayed_insert_threads 0
Delayed_writes 0
Delayed_errors 0
Flush_commands 1
Handler_delete 1436692
Handler_read_first 6166739
Handler_read_key 1320907280
Handler_read_next 1884309372
Handler_read_prev 3580
Handler_read_rnd 777655621
Handler_read_rnd_next 579788831
Handler_update 18056795
Handler_write 2447004508
Key_blocks_used 7793
Key_read_requests 251648484
Key_reads 9480603
Key_write_requests 21898728
Key_writes 21052689
Max_used_connections 100 Max. connections reached
Not_flushed_key_blocks 0
Not_flushed_delayed_rows 0
Open_tables 64 100% of table_cache in use
Open_files 98
Open_streams 0
Opened_tables 132354
Questions 122287362
Select_full_join 1411
Select_full_range_join 14
Select_range 21323579
Select_range_check 0
Select_scan 12862647
Slave_running OFF
Slave_open_temp_tables 0
Slow_launch_threads 91
Slow_queries 369 (execution time > 10 secs)
Sort_merge_passes 0
Sort_range 19308317
Sort_rows 132113128
Sort_scan 8219508
Table_locks_immediate 129214796
Table_locks_waited 288752
Threads_cached 0
Threads_created 8094649
Threads_connected 7
Threads_running 1
Uptime 3164470 36 days 15 hrs 1 min 10 secs
+---------------------------+-----------------+
Key Reads/Key Read Requests = 0.037674 (Cache hit = 99.962326%)
Key Writes/Key Write Requests = 0.961366
Connections/second = 2.558 (/hour = 9208.727)
KB received/second = 0.663 (/hour = 2385.785)
KB sent/second = 0.663 (/hour = 2385.785)
Temporary Tables Created/second = 0.842 (/hour = 3029.633)
Opened Tables/second = 0.042 (/hour = 150.570)
Slow Queries/second = 0.000 (/hour = 0.420)
% of slow queries = 0.000%
Queries/second = 38.644 (/hour = 139117.926)
-endmysqlinfo
^ bump edit or create your /etc/my.cnf file and place the below in it and restart mysql
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
set-variable = max_connections=500
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer=2M
set-variable = table_cache=1024
set-variable = thread_cache_size=256
set-variable = wait_timeout=9600
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10
[safe_mysqld]
open_files_limit=8192
[mysqldump]
quick
set-variable = max_allowed_packet=16M
[myisamchk]
set-variable = key_buffer=64M
set-variable = sort_buffer=64M
set-variable = read_buffer=16M
set-variable = write_buffer=16M
I did not see a line for max_connections ... should I just add it or should there already be a line in there for that? Yes, add it then restart MySQL.
please provide the following
Here are the specs from this morning (some of the specs from last night are listed above).
server specs
vB 2.2.8 (upgraded this morning)
Pentium 1.13 single CPU
512mb RAM
Single 18 GB drive
mySQL 3.23.41
PHP
compiled/installed
I have no clue, I just inherited administration of this server
top - another snapshot is listed above from last night
7:07am up 37 days, 2:37, 1 user, load average: 0.03, 0.28, 0.45
100 processes: 99 sleeping, 1 running, 0 zombie, 0 stopped
CPU states: 0.5% user, 0.3% system, 0.0% nice, 99.0% idle
Mem: 771700K av, 764672K used, 7028K free, 0K shrd, 30992K buff
Swap: 530104K av, 44172K used, 485932K free 627612K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
29425 apache 11 0 7080 5908 3696 S 0.3 0.7 0:05 httpd
30317 root 13 0 1088 1088 836 R 0.3 0.1 0:00 top
26856 apache 10 0 7152 5980 3716 S 0.1 0.7 0:15 httpd
1 root 9 0 488 436 420 S 0.0 0.0 0:33 init
2 root 9 0 0 0 0 SW 0.0 0.0 0:00 keventd
3 root 19 19 0 0 0 SWN 0.0 0.0 0:01 ksoftirqd_CPU0
4 root 9 0 0 0 0 SW 0.0 0.0 5:07 kswapd
5 root 9 0 0 0 0 SW 0.0 0.0 0:00 bdflush
6 root 9 0 0 0 0 SW 0.0 0.0 1:10 kupdated
9 root 9 0 0 0 0 SW 0.0 0.0 0:00 scsi_eh_0
10 root 9 0 0 0 0 SW 0.0 0.0 0:28 kjournald
134 root -1 -20 0 0 0 SW< 0.0 0.0 0:00 mdrecoveryd
144 root 9 0 0 0 0 SW 0.0 0.0 0:00 kjournald
145 root 9 0 0 0 0 SW 0.0 0.0 1:20 kjournald
146 root 9 0 0 0 0 SW 0.0 0.0 0:33 kjournald
147 root 9 0 0 0 0 SW 0.0 0.0 6:43 kjournald
627 root 9 0 388 332 292 S 0.0 0.0 1:50 syslogd
my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
mysqladmin variables
+---------------------------------+--------------------------------------------$
Variable_name Value $
+---------------------------------+--------------------------------------------$
back_log 50 $
basedir /usr/ $
bdb_cache_size 8388600 $
bdb_log_buffer_size 32768 $
bdb_home /var/lib/mysql/ $
bdb_max_lock 10000 $
bdb_logdir $
bdb_shared_data OFF $
bdb_tmpdir /tmp/ $
bdb_version Sleepycat Software: Berkeley DB 3.2.9a: (Au$
binlog_cache_size 32768 $
character_set latin1 $
character_sets latin1 big5 cp1251 cp1257 croat czech danis$
concurrent_insert ON $
connect_timeout 5 $
datadir /var/lib/mysql/ $
connect_timeout 5 $
datadir /var/lib/mysql/ $
delay_key_write ON $
delayed_insert_limit 100 $
delayed_insert_timeout 300 $
delayed_queue_size 1000 $
flush OFF $
flush_time 0 $
have_bdb YES $
have_gemini NO $
have_innodb NO $
have_isam YES $
have_raid NO $
have_ssl NO $
init_file $
interactive_timeout 28800 $
join_buffer_size 131072 $
key_buffer_size 8388600 $
language /usr/share/mysql/english/ $
key_buffer_size 8388600 $
language /usr/share/mysql/english/ $
large_files_support ON $
locked_in_memory OFF $
log OFF $
log_update OFF $
log_bin OFF $
log_slave_updates OFF $
log_long_queries OFF $
long_query_time 10 $
low_priority_updates OFF $
lower_case_table_names 0 $
max_allowed_packet 1048576 $
max_binlog_cache_size 4294967295 $
max_binlog_size 1073741824 $
max_connections 100 $
max_connect_errors 10 $
max_delayed_threads 20 $
max_heap_table_size 16777216 $
max_delayed_threads 20 $
max_heap_table_size 16777216 $
max_join_size 4294967295 $
max_sort_length 1024 $
max_user_connections 0 $
max_tmp_tables 32 $
max_write_lock_count 4294967295 $
myisam_recover_options 0 $
myisam_max_extra_sort_file_size 256 $
myisam_max_sort_file_size 2047 $
myisam_sort_buffer_size 8388608 $
net_buffer_length 16384 $
net_read_timeout 30 $
net_retry_count 10 $
net_write_timeout 60 $
open_files_limit 0 $
pid_file /var/run/mysqld/mysqld.pid $
port 3306 $
protocol_version 10 $
port 3306 $
protocol_version 10 $
record_buffer 131072 $
record_rnd_buffer 131072 $
query_buffer_size 0 $
safe_show_database OFF $
server_id 0 $
slave_net_timeout 3600 $
skip_locking ON $
skip_networking OFF $
skip_show_database OFF $
slow_launch_time 2 $
socket /var/lib/mysql/mysql.sock $
sort_buffer 2097144 $
sql_mode 0 $
table_cache 64 $
table_type MYISAM $
thread_cache_size 0 $
thread_stack 65536 $
thread_cache_size 0 $
thread_stack 65536 $
transaction_isolation READ-COMMITTED $
timezone MDT $
tmp_table_size 33554432 $
tmpdir /tmp/ $
version 3.23.41 $
wait_timeout 28800 $
+---------------------------------+--------------------------------------------$
mysqlinfo
You should be able to get to mysqlinfo here http://www.bimmerforums.com/forum/admin/mysqlinfo.php
My vBulletin is the only thing on the server... it is a dedicated server hosted through VenturesOnline.
I have no idea how many average and concurrent users I have. I haven't figured out how to get that info yet...
phpinfo
I installed the phpinfo script... and the URL to it is at http://www.bimmerforums.com/forum/admin/phpinfo.php
apache info
KeepAlive Off
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 20
StartServers 8
MaxClients 150
vB version
2.2.8
upgraded to 2.2.8 this morning from 2.2.4
Thanks
Thanks for all your help guys... It is much appreciated.
and I'm having a terrible time finding the my.cnf file. I recently inherited this server and I'm not sure if this is the right my.cnf file... but it is hte only one I could find.
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
I did not see a line for max_connections ... should I just add it or should there already be a line in there for that?
edit or create your /etc/my.cnf file and place the below in it and restart mysql Thanks eva...
I'll try it and let you know what happens.
Server load on average is anywhere between 2.0 - 3.0. Then all of a sudden it'll spike to like 10-15 and users are getting the following errors...
Warning: Too many connections in /home/httpd/vhosts/bimmerforums.com/httpdocs/forum/admin/db_mysql.php on line 40
Warning: MySQL Connection Failed: Too many connections in /home/httpd/vhosts/bimmerforums.com/httpdocs/forum/admin/db_mysql.php on line 40
Anybody have any clues?
average load time...
8:15pm up 36 days, 15:45, 1 user, load average: 2.86, 2.91, 3.62
137 processes: 115 sleeping, 20 running, 2 zombie, 0 stopped
CPU states: 84.3% user, 15.6% system, 0.0% nice, 0.0% idle
Mem: 771700K av, 766808K used, 4892K free, 0K shrd, 17596K buff
Swap: 530104K av, 44204K used, 485900K free 606836K cached
PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
7996 apache 20 0 7188 6084 3540 R 10.4 0.7 0:01 httpd
8233 mysql 20 0 32656 27M 12888 R 10.4 3.7 0:00 mysqld
3124 apache 15 0 7140 6044 3608 S 10.0 0.7 0:22 httpd
1449 apache 19 0 7832 6760 4032 R 8.9 0.8 0:22 httpd
1455 apache 19 0 7268 6176 3588 R 8.5 0.8 0:15 httpd
8059 apache 17 0 6824 5724 3536 R 6.3 0.7 0:01 httpd
1451 apache 20 0 8248 7176 3928 R 5.1 0.9 0:18 httpd
7112 apache 14 0 7196 6092 3564 S 4.1 0.7 0:04 httpd
6851 apache 19 0 7500 6412 3600 R 3.3 0.8 0:05 httpd
25596 apache 19 0 7280 6112 3644 R 2.9 0.7 0:24 httpd
3403 apache 20 0 7712 6640 3936 R 2.9 0.8 0:22 httpd
7605 apache 11 0 7100 6004 3560 R 2.7 0.7 0:04 httpd
8193 apache 12 0 6932 5772 3464 S 2.3 0.7 0:00 httpd
8248 mysql 10 0 32656 27M 12888 S 1.9 3.7 0:00 mysqld
4085 apache 18 0 7496 6416 3632 S 1.7 0.8 0:16 httpd
27715 apache 12 0 7240 6136 3660 R 1.5 0.7 0:29 httpd
8253 mysql 17 0 32656 27M 12888 S 1.5 3.7 0:00 mysqld
-endtopinfo
connections off in your config.php:
// use persistant connections to the database
// 0 = don't use
// 1 = use
$usepconnect = 0;But if you still have problems after that, you'll need to increase the max_connections variable setting in my.cnf:
set-variable = max_connections=xxx
...with 'xxx' being the connections you want to allow. Restart MySQl after making the change.
Here's more info:
http://www.mysql.com/doc/T/o/Too_many_connections.html
Well... first, let me say that I'm very new to this and I just inherited root control for a vB board and mySQL setup.
I want to make sure it is optimized so it will run as fast as possible. In the past it is runs smoothly 80% of the time... the other 20% of the time it can be molasses slow.
The server is a dedicated server over at VenturesOnline...
Setup includes
vB 2.2.4 (to be upgraded to 2.2.8)
Pentium 1.13 single CPU
512mb RAM
Single 18 GB drive
What do you guys need from me to help me optimize my setup? please provide the following
1. your server specs, such as mysql and php version
2. if possible how mysql was compiled/installed
3. your top stats
4. your mysql configuration variables located at /etc/my.cnf or c:my.cnf if on Windows server if you don't have that file you need to log into telnet and as root user type
mysqladmin -u root -p variables
copy and paste output here
5. your mysql extended-status output either still telnet as root user type
mysqladmin -u root -p extended-status
copy and paste output here
or preferred is to installed extended-status output script which is either located
- in your vB 2.2.6 or higher vB version's zip file extra's folder, upload mysqlinfo.php script to your site or if you're on an pre vB 2.2.6 install go to
- http://vbulletin.com/forum/showthread.php?threadid=3477 and install that scrip making sure to edit $mysqllogin line with your own mysqlusername and password
and post url to that here
6. oh and is your vB the only thing on the server? or other scripts? sites?
7. how many average and max concurrent users on your vB forum ?
8. create a file named phpinfo.php and place this code in it and post the url/link to it from your web site
phpinfo();
?>
i.e. yourdomain.com/phpinfo.php
9. 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 :
KeepAlive
MaxKeepAliveRequests
KeepAliveTimeout
MinSpareServers
MaxSpareServers
StartServers
MaxClients
10. what version of vB are you running ?
#If you have any other info about this subject , Please add it free.# |
Posted in portal.zjrp.com | edit
