Optimizing MySQL and MyServer...

[Kevlar]

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?

[Kevlar]

Ok... Now I'm getting load spikes and too many connections errors.

[Kevlar]

Just as I thought things were running smooth... sure enough problems arise.

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?

[Steve Machol]

The server has maxed out the number of MySQL connections it allows. You can try turning persistent
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

[Kevlar]

Persistent queries are already turned off.

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?

[Steve Machol]

Originally posted by Kevlar
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.

[Kevlar]

Ok, I'll try doing that... I was reading through some other threads and I understand that this might help diagnose the problem...

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

[Kevlar]

-topinfo

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

[eva2000]

Originally posted by Kevlar
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 ?

[Kevlar]

Originally posted by eva2000
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.

[Kevlar]

^ bump

[eva2000]

Originally posted by Kevlar
^ 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

[Kevlar]

Originally posted by eva2000
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.