PDA

View Full Version : Server Optimization Request

spcnet
Sun 24th Jun '07, 12:53pm
Thanks for the help. Server's been crashing lately and seeing a lot of forum SQL queries showing up in the MySQL slow query log.

1. Dedicated server

2. Processor: Intel Pentium 4 - 3.0 GHz
RAM: 1 GB
Total Disk Space: 240 GB (120 GB usable)
RAID: RAID1
Server Type: Linux
Operating System: Red Hat Fedora Core 4
Apache version: Apache v2.0.54
PHP version: 5.0.4
MySQL version: 5.0.27-standard-log

3. vBulletin 3.6.7 PL1

4. There are innobd tables

5. ?

6. top -b -n 1

top - 12:11:14 up 134 days, 15:36, 1 user, load average: 1.61, 1.48, 1.20
Tasks: 175 total, 1 running, 174 sleeping, 0 stopped, 0 zombie
Cpu(s): 12.4% us, 1.9% sy, 0.0% ni, 82.7% id, 2.9% wa, 0.0% hi, 0.1% si
Mem: 1024136k total, 1009992k used, 14144k free, 40560k buffers
Swap: 2048276k total, 291448k used, 1756828k free, 294408k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2643 apache 16 0 30484 12m 3292 S 52.4 1.3 0:00.43 httpd
2573 apache 16 0 31132 13m 3416 S 31.0 1.4 0:01.24 httpd
2474 apache 16 0 30668 13m 3440 S 25.2 1.3 0:05.30 httpd
26999 mysql 16 0 268m 105m 2952 S 7.8 10.6 864:27.27 mysqld
2648 root 15 0 2016 960 708 R 1.9 0.1 0:00.02 top
1 root 16 0 1744 372 340 S 0.0 0.0 0:09.80 init
2 root RT 0 0 0 0 S 0.0 0.0 0:03.57 migration/0
3 root 34 19 0 0 0 S 0.0 0.0 0:00.50 ksoftirqd/0
4 root RT 0 0 0 0 S 0.0 0.0 0:10.43 migration/1
5 root 34 19 0 0 0 S 0.0 0.0 0:02.19 ksoftirqd/1
6 root 10 -5 0 0 0 S 0.0 0.0 0:00.02 events/0
7 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 events/1
8 root 11 -5 0 0 0 S 0.0 0.0 0:00.01 khelper
9 root 10 -5 0 0 0 S 0.0 0.0 0:00.22 kthread
16 root 10 -5 0 0 0 S 0.0 0.0 18:49.41 kblockd/0
17 root 10 -5 0 0 0 S 0.0 0.0 0:08.43 kblockd/1
18 root 14 -5 0 0 0 S 0.0 0.0 0:00.00 kacpid
250 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 khubd
314 root 12 -5 0 0 0 S 0.0 0.0 0:00.00 aio/0
315 root 12 -5 0 0 0 S 0.0 0.0 0:00.00 aio/1
313 root 15 0 0 0 0 S 0.0 0.0 68:55.70 kswapd0
402 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 kseriod
463 root 11 -5 0 0 0 S 0.0 0.0 0:00.00 kpsmoused
482 root 14 -5 0 0 0 S 0.0 0.0 0:00.00 ata/0
483 root 14 -5 0 0 0 S 0.0 0.0 0:00.00 ata/1
493 root 11 -5 0 0 0 S 0.0 0.0 0:00.00 scsi_eh_0
494 root 19 0 0 0 0 S 0.0 0.0 0:00.00 hpt_wt
511 root 15 0 0 0 0 S 0.0 0.0 14:32.23 kjournald
700 root 12 -4 1644 248 244 S 0.0 0.0 0:00.20 udevd
921 root 18 0 3140 68 64 S 0.0 0.0 0:00.00 kmodule
928 root 12 -5 0 0 0 S 0.0 0.0 0:00.00 scsi_eh_1
929 root 12 -5 0 0 0 S 0.0 0.0 0:00.00 scsi_eh_2
1227 root 15 0 0 0 0 S 0.0 0.0 0:00.00 khpsbpkt
1250 root 16 0 0 0 0 S 0.0 0.0 0:00.00 knodemgrd_0
1279 root 10 -5 0 0 0 S 0.0 0.0 0:00.87 kauditd
1419 root 15 0 0 0 0 S 0.0 0.0 0:00.01 kjournald
1627 root 16 0 1612 472 388 S 0.0 0.0 2:25.35 syslogd
1629 root 16 0 1564 288 284 S 0.0 0.0 0:00.01 klogd
1637 root 13 -3 12968 512 400 S 0.0 0.0 0:26.15 auditd
1648 root 16 0 2152 332 328 S 0.0 0.0 0:00.00 hcid
1652 root 17 0 1604 312 308 S 0.0 0.0 0:00.00 sdpd
1675 root 10 -10 0 0 0 S 0.0 0.0 0:00.00 krfcommd
1700 root 16 0 2736 408 340 S 0.0 0.0 1:46.98 nifd
1731 nobody 16 0 13436 392 360 S 0.0 0.0 0:00.24 mDNSResponder
1739 root 16 0 1556 284 280 S 0.0 0.0 0:00.00 acpid
1773 root 16 0 4400 528 436 S 0.0 0.1 0:30.62 sshd
1781 root 15 0 2176 360 356 S 0.0 0.0 0:00.00 xinetd
1854 root 15 0 280m 52m 3852 S 0.0 5.3 20:37.85 java
1879 root 16 0 4560 420 368 S 0.0 0.0 0:02.40 crond
1896 root 16 0 1804 308 268 S 0.0 0.0 0:00.33 atd
1903 dbus 16 0 2560 248 244 S 0.0 0.0 0:00.00 dbus-daemon
1920 root 16 0 4216 608 404 S 0.0 0.1 0:01.29 hald
1925 root 17 0 2136 256 252 S 0.0 0.0 0:00.00 hald-addon-acpi
1946 root 18 0 33648 488 436 S 0.0 0.0 0:10.01 hptsvr
1964 root 18 0 1552 260 256 S 0.0 0.0 0:00.00 mingetty
1965 root 18 0 1548 260 256 S 0.0 0.0 0:00.00 mingetty
1966 root 18 0 1548 260 256 S 0.0 0.0 0:00.00 mingetty
1967 root 18 0 1552 260 256 S 0.0 0.0 0:00.00 mingetty
1968 root 18 0 1552 260 256 S 0.0 0.0 0:00.00 mingetty
1969 root 18 0 1552 260 256 S 0.0 0.0 0:00.00 mingetty
16298 root 16 0 5636 684 580 S 0.0 0.1 3:31.68 master
16388 cyrus 16 0 4908 568 456 S 0.0 0.1 0:15.54 cyrus-master
16415 cyrus 16 0 28732 92 64 S 0.0 0.0 0:11.27 idled
16417 cyrus 18 0 30124 444 440 S 0.0 0.0 0:00.00 imapd
16418 cyrus 18 0 30128 448 444 S 0.0 0.0 0:00.00 imapd
16419 cyrus 18 0 29888 444 440 S 0.0 0.0 0:00.01 pop3d
16420 cyrus 18 0 29892 448 444 S 0.0 0.0 0:00.01 pop3d
16423 cyrus 18 0 30128 448 444 S 0.0 0.0 0:00.01 imapd
16425 cyrus 18 0 29888 448 444 S 0.0 0.0 0:00.00 pop3d
16426 cyrus 18 0 30128 444 440 S 0.0 0.0 0:00.01 imapd
16427 cyrus 16 0 29888 444 440 S 0.0 0.0 0:00.00 pop3d
16431 root 16 0 27016 1728 964 S 0.0 0.2 0:03.82 spamd
16461 cyrus 18 0 30124 444 440 S 0.0 0.0 0:00.01 imapd
16462 cyrus 17 0 30124 444 440 S 0.0 0.0 0:00.01 imapd
16463 cyrus 17 0 30124 444 440 S 0.0 0.0 0:00.00 imapd
16464 cyrus 16 0 29888 444 440 S 0.0 0.0 0:00.00 pop3d
2568 root 16 0 4080 224 220 S 0.0 0.0 0:00.11 vsftpd
17258 postfix 15 0 5864 1088 892 S 0.0 0.1 0:18.86 nqmgr
543 named 19 0 62568 4856 1228 S 0.0 0.5 4:01.44 named
22214 root 16 0 28940 17m 1660 S 0.0 1.7 0:28.76 spamd
12200 root 16 0 28892 17m 1664 S 0.0 1.8 0:17.23 spamd
15174 root 16 0 28632 16m 1652 S 0.0 1.7 0:16.27 spamd
26133 root 16 0 30512 17m 1652 S 0.0 1.7 0:15.92 spamd
26969 root 22 0 4336 764 760 S 0.0 0.1 0:00.01 mysqld_safe
5449 cyrus 16 0 29892 1092 1088 S 0.0 0.1 0:00.00 pop3d
3001 cyrus 16 0 30128 1092 1088 S 0.0 0.1 0:00.00 imapd
3002 cyrus 16 0 30124 1092 1088 S 0.0 0.1 0:00.00 imapd
3003 cyrus 17 0 30124 1092 1088 S 0.0 0.1 0:00.00 imapd
3004 cyrus 17 0 30128 1092 1088 S 0.0 0.1 0:00.00 imapd
3005 cyrus 17 0 30124 1092 1088 S 0.0 0.1 0:00.01 imapd
7590 cyrus 16 0 29892 1092 1088 S 0.0 0.1 0:00.00 pop3d
7598 cyrus 17 0 29892 1092 1088 S 0.0 0.1 0:00.00 pop3d
15461 root 15 0 0 0 0 S 0.0 0.0 0:00.21 pdflush
15468 root 16 0 28156 17m 1856 S 0.0 1.7 0:01.07 spamd
19652 root 15 0 0 0 0 S 0.0 0.0 0:00.00 pdflush
28083 root 16 0 24744 10m 6432 S 0.0 1.0 0:03.01 httpd
28090 root 18 0 19512 3108 396 S 0.0 0.3 0:00.00 httpd
1861 postfix 16 0 5688 1520 1272 S 0.0 0.1 0:00.00 pickup
2033 root 16 0 7272 2260 1812 S 0.0 0.2 0:00.06 sshd
2035 spcnettv 16 0 7432 1656 1180 S 0.0 0.2 0:00.07 sshd
2036 spcnettv 15 0 4384 1440 1212 S 0.0 0.1 0:00.00 bash
2331 apache 16 0 29184 11m 3464 S 0.0 1.2 0:09.85 httpd
2368 apache 16 0 29428 12m 3460 S 0.0 1.2 0:07.03 httpd
2408 apache 15 0 29320 12m 3448 S 0.0 1.2 0:05.99 httpd
2452 apache 15 0 29144 11m 3412 S 0.0 1.2 0:04.71 httpd
2454 apache 15 0 29408 12m 3840 S 0.0 1.3 0:04.43 httpd
2459 apache 15 0 30840 13m 3448 S 0.0 1.4 0:04.13 httpd
2468 apache 15 0 29276 12m 3444 S 0.0 1.2 0:03.11 httpd
2482 apache 15 0 29184 11m 3440 S 0.0 1.2 0:05.81 httpd
2484 apache 15 0 29764 12m 3392 S 0.0 1.2 0:01.13 httpd
2485 root 17 0 4428 1180 936 S 0.0 0.1 0:00.00 su
2487 root 16 0 4380 1424 1200 S 0.0 0.1 0:00.02 bash
2525 cyrus 15 0 29960 2212 1912 S 0.0 0.2 0:00.00 lmtpd
2528 apache 15 0 29148 11m 3440 S 0.0 1.2 0:02.59 httpd
2530 apache 15 0 29168 11m 3444 S 0.0 1.2 0:03.25 httpd
2539 apache 15 0 29160 11m 3440 S 0.0 1.2 0:01.70 httpd
2540 apache 15 0 29052 12m 3820 S 0.0 1.2 0:01.26 httpd
2542 apache 16 0 29164 11m 3444 S 0.0 1.2 0:03.24 httpd
2548 apache 15 0 29092 11m 3408 S 0.0 1.2 0:02.64 httpd
2549 apache 15 0 29432 12m 3456 S 0.0 1.2 0:03.40 httpd
2552 apache 15 0 29096 11m 3420 S 0.0 1.2 0:01.31 httpd
2557 apache 15 0 30412 13m 3420 S 0.0 1.3 0:00.76 httpd
2559 apache 15 0 29132 11m 3396 S 0.0 1.2 0:02.25 httpd
2560 apache 15 0 29000 11m 3420 S 0.0 1.2 0:01.46 httpd
2561 apache 17 0 29140 11m 3412 S 0.0 1.2 0:01.30 httpd
2562 apache 15 0 29132 11m 3420 S 0.0 1.2 0:01.04 httpd
2569 apache 16 0 29104 11m 3408 S 0.0 1.2 0:01.05 httpd
2574 apache 16 0 29092 11m 3440 S 0.0 1.2 0:00.86 httpd
2576 apache 16 0 28464 11m 3360 S 0.0 1.1 0:00.69 httpd
2577 apache 15 0 26700 9328 3076 S 0.0 0.9 0:00.36 httpd
2578 apache 15 0 29124 11m 3376 S 0.0 1.2 0:01.45 httpd
2579 apache 15 0 28480 11m 3444 S 0.0 1.1 0:01.02 httpd
2580 apache 16 0 29136 11m 3424 S 0.0 1.2 0:01.73 httpd
2584 apache 16 0 29596 12m 3380 S 0.0 1.2 0:01.85 httpd
2585 apache 15 0 29620 12m 3412 S 0.0 1.2 0:00.81 httpd
2586 apache 15 0 28344 10m 3288 S 0.0 1.1 0:00.13 httpd
2587 apache 15 0 28072 10m 3344 S 0.0 1.1 0:00.35 httpd
2588 apache 16 0 28268 11m 3424 S 0.0 1.1 0:00.35 httpd
2589 apache 15 0 30680 13m 3404 S 0.0 1.3 0:01.29 httpd
2592 postfix 17 0 5888 1984 1604 S 0.0 0.2 0:00.00 cleanup
2593 postfix 16 0 5832 1872 1524 S 0.0 0.2 0:00.00 trivial-rewrite
2594 postfix 15 0 5816 1700 1432 S 0.0 0.2 0:00.00 smtp
2597 apache 16 0 28528 11m 3392 S 0.0 1.1 0:00.22 httpd
2598 apache 15 0 26700 9292 3048 S 0.0 0.9 0:00.15 httpd
2599 apache 15 0 29144 11m 3344 S 0.0 1.2 0:00.99 httpd
2601 apache 15 0 25416 7896 2980 S 0.0 0.8 0:00.06 httpd
2603 apache 16 0 28164 10m 3072 S 0.0 1.1 0:00.28 httpd
2612 postfix 15 0 5772 1904 1588 S 0.0 0.2 0:00.00 local
2613 postfix 15 0 5740 1564 1308 S 0.0 0.2 0:00.00 lmtp
2614 cyrus 18 0 29956 2112 1852 S 0.0 0.2 0:00.00 lmtpd
2615 postfix 17 0 5712 1536 1292 S 0.0 0.1 0:00.00 bounce
2616 postfix 16 0 5712 1488 1256 S 0.0 0.1 0:00.00 bounce
2619 apache 15 0 28508 11m 3400 S 0.0 1.1 0:00.26 httpd
2620 apache 15 0 28916 11m 3396 S 0.0 1.2 0:00.63 httpd
2621 apache 15 0 25052 7408 2832 S 0.0 0.7 0:00.02 httpd
2622 apache 15 0 24744 5972 1748 S 0.0 0.6 0:00.00 httpd
2623 apache 15 0 28932 11m 3376 S 0.0 1.2 0:00.37 httpd
2624 apache 16 0 29488 12m 3360 S 0.0 1.2 0:02.04 httpd
2627 apache 15 0 28928 11m 3344 S 0.0 1.2 0:00.34 httpd
2629 apache 16 0 28104 10m 3356 S 0.0 1.1 0:00.38 httpd
2630 apache 15 0 24744 5964 1744 S 0.0 0.6 0:00.00 httpd
2631 apache 15 0 26764 9232 3000 S 0.0 0.9 0:00.09 httpd
2632 apache 15 0 24744 5960 1744 S 0.0 0.6 0:00.00 httpd
2633 apache 15 0 24744 5964 1744 S 0.0 0.6 0:00.00 httpd
2634 apache 15 0 24744 5964 1744 S 0.0 0.6 0:00.00 httpd
2635 apache 15 0 26764 9232 3000 S 0.0 0.9 0:00.09 httpd
2638 apache 15 0 24744 5960 1744 S 0.0 0.6 0:00.00 httpd
2639 apache 15 0 24744 5960 1744 S 0.0 0.6 0:00.00 httpd
2640 apache 15 0 24744 5960 1744 S 0.0 0.6 0:00.00 httpd
2641 apache 16 0 28012 10m 3104 S 0.0 1.0 0:00.14 httpd
2642 apache 15 0 24744 5852 1636 S 0.0 0.6 0:00.00 httpd
2644 apache 16 0 24744 5168 992 S 0.0 0.5 0:00.00 httpd
2645 apache 16 0 24744 5168 992 S 0.0 0.5 0:00.00 httpd
2646 apache 16 0 24744 5168 992 S 0.0 0.5 0:00.00 httpd
2647 apache 16 0 24744 5168 992 S 0.0 0.5 0:00.00 httpd



7. /etc/my.cnf

[mysqld]
datadir=/usr/local/mysql/data
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
max_connections=700
max_allowed_packet=16M
key_buffer = 64M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 1M
table_cache = 256
thread_cache = 8
thread_concurrency = 2
myisam_data_pointer_size = 7
log-slow-queries=/var/log/mysqlSlowQuery.log


[mysqldump]
quick
max_allowed_packet = 16M

[mysql.server]
user=mysql
basedir=/usr/local/mysql

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/usr/local/mysql/data/mysqld.pid
#log-slow-queries=/var/log/mysqlSlowQuery.log


8. mysqladmin -u root -p var ext stat ver


+---------------------------------+-------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------------------------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /usr/local/mysql-standard-5.0.27-linux-i686-icc-glibc23/ |
| 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/local/mysql-standard-5.0.27-linux-i686-icc-glibc23/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| completion_type | 0 |
| concurrent_insert | 1 |
| connect_timeout | 5 |
| datadir | /usr/local/mysql/data/ |
| 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 |
| engine_condition_pushdown | OFF |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | YES |
| have_bdb | NO |
| have_blackhole_engine | NO |
| have_compress | YES |
| have_crypt | YES |
| have_csv | NO |
| have_dynamic_loading | YES |
| have_example_engine | NO |
| have_federated_engine | NO |
| have_geometry | YES |
| have_innodb | YES |
| have_isam | NO |
| have_merge_engine | YES |
| have_ndbcluster | NO |
| have_openssl | NO |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| innodb_thread_sleep_delay | 10000 |
| interactive_timeout | 28800 |
| join_buffer_size | 1044480 |
| key_buffer_size | 67108864 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/local/mysql-standard-5.0.27-linux-i686-icc-glibc23/share/mysql/english/ |
| large_files_support | ON |
| large_page_size | 0 |
| large_pages | OFF |
| lc_time_names | en_US |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_bin_trust_function_creators | OFF |
| log_error | |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | ON |
| log_warnings | 1 |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 16776192 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 700 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 18446744073709551615 |
| max_length_for_sort_data | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_sp_recursion_depth | 0 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 7 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 67108864 |
| myisam_stats_method | nulls_unequal |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | ON |
| open_files_limit | 3510 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | /usr/local/mysql/data/ip-208-109-28-33.ip.secureserver.net.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| prepared_stmt_count | 0 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 1044480 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| server_id | 0 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_compressed_protocol | OFF |
| slave_load_tmpdir | /tmp/ |
| slave_net_timeout | 3600 |
| slave_skip_errors | OFF |
| slave_transaction_retries | 10 |
| slow_launch_time | 2 |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer_size | 1048568 |
| sql_big_selects | ON |
| sql_mode | |
| sql_notes | ON |
| sql_warnings | OFF |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_key | |
| storage_engine | MyISAM |
| sync_binlog | 0 |
| sync_frm | ON |
| system_time_zone | EDT |
| table_cache | 256 |
| table_lock_wait_timeout | 50 |
| table_type | MyISAM |
| thread_cache_size | 8 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmp_table_size | 33554432 |
| tmpdir | /tmp/ |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| updatable_views_with_limit | YES |
| version | 5.0.27-standard-log |
| version_comment | MySQL Community Edition - Standard (GPL) |
| version_compile_machine | i686 |
| version_compile_os | pc-linux-gnu |
| wait_timeout | 28800 |
+---------------------------------+-------------------------------------------------------------------------------+
+-----------------------------------+------------+
| Variable_name | Value |
+-----------------------------------+------------+
| Aborted_clients | 1850 |
| Aborted_connects | 6 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 129 |
| Bytes_sent | 6480 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 0 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 0 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 0 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 0 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 0 |
| Com_show_databases | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 0 |
| Com_show_triggers | 0 |
| Com_show_variables | 1 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 0 |
| Com_update_multi | 0 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
| Connections | 954295 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 2517 |
| Created_tmp_tables | 2 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 221 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 350 |
| Innodb_buffer_pool_pages_data | 265 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 194 |
| Innodb_buffer_pool_pages_free | 245 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 2 |
| Innodb_buffer_pool_pages_total | 512 |
| Innodb_buffer_pool_read_ahead_rnd | 3 |
| Innodb_buffer_pool_read_ahead_seq | 3 |
| Innodb_buffer_pool_read_requests | 44270 |
| Innodb_buffer_pool_reads | 145 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 13297 |
| Innodb_data_fsyncs | 55 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 5394432 |
| Innodb_data_reads | 172 |
| Innodb_data_writes | 101 |
| Innodb_data_written | 8018432 |
| Innodb_dblwr_pages_written | 194 |
| Innodb_dblwr_writes | 11 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 4325 |
| Innodb_log_writes | 25 |
| Innodb_os_log_fsyncs | 33 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 1656320 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 69 |
| Innodb_pages_read | 196 |
| Innodb_pages_written | 194 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 3858 |
| Innodb_rows_read | 19507 |
| Innodb_rows_updated | 0 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 0 |
| Key_blocks_used | 57990 |
| Key_read_requests | 8004150856 |
| Key_reads | 514746 |
| Key_write_requests | 915098 |
| Key_writes | 353736 |
| Last_query_cost | 10.499000 |
| Max_used_connections | 256 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 381 |
| Open_streams | 0 |
| Open_tables | 256 |
| Opened_tables | 0 |
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
| Questions | 8281114 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 2 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 8 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
| Table_locks_immediate | 10351900 |
| Table_locks_waited | 4862 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 3 |
| Threads_connected | 5 |
| Threads_created | 1733 |
| Threads_running | 2 |
| Uptime | 312730 |
+-----------------------------------+------------+
Uptime: 312730 Threads: 5 Questions: 8281115 Slow queries: 1761 Opens: 12891 Flush tables: 1 Open tables: 256 Queries per second avg: 26.480
mysqladmin Ver 8.41 Distrib 4.1.20, for redhat-linux-gnu on i386
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version 5.0.27-standard-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 3 days 14 hours 52 min 10 sec

Threads: 5 Questions: 8281116 Slow queries: 1761 Opens: 12891 Flush tables: 1 Open tables: 256 Queries per second avg: 26.480


9. The main website also uses mysql and php. There is another website hosted that also uses php/mysql but on a different database. Traffic for that site is just moderate (~2500 page views a day).

10. On average, 350 users, max 1210 concurrent users, 900 seconds for Session Timeout

11. http://www.spcnet.tv/phpinfo.php

12. httpd.conf

KeepAlive = ON
MaxKeepAliveRequests = 100
KeepAliveTimeout = 15
MinSpareServers = 10
MaxSpareServers = 20
StartServers = 8
MaxClients = 256
Maxrequestsperchild value = 4000

13. find / -size +2000000k

none

14.

uname -a


Linux ip-208-109-28-33.ip.secureserver.net 2.6.16-1.2115_FC4_HPTRAID #1 SMP Mon Jun 12 16:13:33 MST 2006 i686 i686 i386 GNU/Linux


ulimit -aH

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


cat /proc/cpuinfo

processor : 0
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Pentium(R) 4 CPU 3.00GHz
stepping : 1
cpu MHz : 3007.322
cache size : 1024 KB
physical id : 0
siblings : 2
core id : 0
cpu cores : 1
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 5
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 constant_tsc pni monitor d s_cpl cid xtpr
bogomips : 6024.09

processor : 1
vendor_id : GenuineIntel
cpu family : 15
model : 4
model name : Intel(R) Pentium(R) 4 CPU 3.00GHz
stepping : 1
cpu MHz : 3007.322
cache size : 1024 KB
physical id : 0
siblings : 2
core id : 0
cpu cores : 1
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 5
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 constant_tsc pni monitor d s_cpl cid xtpr
bogomips : 6014.08
eva2000
Mon 25th Jun '07, 8:07am
You're swapping to disk (using more memory than you have).

Before any upgrades, try the following in this exact order. You can ignore any of the suggestions that you have already done.

1. Upgrade to Apache 2.0.59 and upgrade PHP to 5.2.3 or if you have problems install PHP 4.4.7 (in either case install as ISAPI module and NO CGI)
2. Edit /etc/my.cnf and place the following mysql server settings in /etc/my.cnf and restart mysql server afterwards


[mysqld]
datadir=/usr/local/mysql/data
socket=/var/lib/mysql/mysql.sock
old_passwords
back_log = 50
skip-innodb
max_connections = 500
key_buffer = 80M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1800
thread_cache_size = 384
wait_timeout = 35
connect_timeout = 10
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
max_connect_errors = 10
thread_concurrency = 2
concurrent_insert = 2
table_lock_wait_timeout = 35
read_rnd_buffer_size = 524288
bulk_insert_buffer_size = 8M
query_cache_limit = 2M
query_cache_size = 16M
query_cache_type = 1
query_prealloc_size = 131072
query_alloc_block_size = 65536
default-storage-engine = MyISAM

[mysql.server]
user=mysql
basedir=/usr/local/mysql

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/usr/local/mysql/data/mysqld.pid
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

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


3. Install APC Cache lastest version from http://pecl.php.net/package/APC. Read install guide at http://www.vbulletin.com/forum/showthread.php?t=165367 OR try the alternative PHP caching software, Xcache which seems to be a bit faster than APC Cache - http://www.vbulletin.com/forum/showthread.php?t=213267

4. Upgrade to vB 3.0.17 http://www.vbulletin.com/forum/showthread.php?t=209720 if you're on vB 3.0.xx or upgrade to vB 3.5.8 http://www.vbulletin.com/forum/showthread.php?t=221903 if you're on vB 3.5.x. Upgrade to vB 3.6.7 http://www.vbulletin.com/forum/showthread.php?t=229950 if you're on vB 3.6.x

5. If you just upgraded to vB 3.5.x/3.6.x try to disable these 4 options:

Admin CP -> vBulletin Options -> Forums Home Page Options -> Display Logged in Users?

Admin CP -> vBulletin Options -> Forum Display Options (forumdisplay) -> Show Users Browsing Forums

Admin CP -> vBulletin Options -> Thread Display Options -> Show Users Browsing Thread

Admin CP -> vBulletin Options -> Message Searching Options -> Automatic Similar Thread search

6. Edit httpd.conf values to following and restart apache

KeepAlive: On
MaxKeepAliveRequests: 100
KeepAliveTimeout: 1
MinSpareServers: 10
MaxSpareServers: 15
StartServers: 10
MaxClients: 150
MaxRequestsPerChild: 1000

7. Revert your innodb tables back to MyISAM
spcnet
Mon 25th Jun '07, 11:24am
Thanks for the response!


You're swapping to disk (using more memory than you have).


Is the best option to throw more hardware (memory) at it?



7. Revert your innodb tables back to MyISAM


This is not an option since we want to take advantage of the foreign key relationships on the tables for our other applications. Does having innodb tables in the same database hurt performance?

Thanks!
eva2000
Tue 26th Jun '07, 9:52am
Another 1GB of memory should help :)

When i mean innodb tables i mean vB's tables are they converted to innodb or you mean additional non-vB tables in vB database that are innodb based ? The conversion of innodb back to myisam was a suggestion for the vB tables not all your other non-vB tables.

Depending on your other applications and how they used can effect the performance of your server. But that is well and truly outside the realm for vB support considering server optimisations provided here are done as a courtesy and not apart of official vB support :)
spcnet
Tue 26th Jun '07, 12:09pm
Another 1GB of memory should help :)
When i mean innodb tables i mean vB's tables are they converted to innodb or you mean additional non-vB tables in vB database that are innodb based ? The conversion of innodb back to myisam was a suggestion for the vB tables not all your other non-vB tables.


Got it. Right, none of the vB tables were changed into innodb but there are other non-vB innodb tables in the same database.

Thanks again!
eva2000
Wed 27th Jun '07, 7:55am
no probs.. adding 1GB ram would be the biggest benefit in your case :)