Server optimization [Archive] - vBulletin Community Forum

jtallen123

Wed 29th Oct '03, 3:51pm

Just wondering if you had some suggestions for server optimization in our MySQL or Apache side of things. :)

1. is this on dedicated or shared virual server

Shared virtual (as it's just RP's forum), it's our dedicated server, but we have several sites on the dedicated server.

2. your server specs. For example:

cpu speed/type single or dual cpus): dual p3 1ghz
how much memory installed: 2GB ram
hard drive type/configuration: EIDE none RAID approx. 100G in total
(not
sure of details)
linux distributor or windows version: Redhat Linux 7.3
apache/IIS version: apache 1.3.27
PHP version: php 4.3.1
MySQL version: mysql --version
mysql Ver 11.18 Distrib 3.23.54, for pc-linux (i686)

3. if you use mysql 4.x instead of mysql 3.23.x, do you have any innodb
type
databases/tables on your server ?
4. if possible how mysql was compiled/installed
Standard, from what I know
5. your top stats
2:19pm up 2:19, 4 users, load average: 0.46, 0.47, 0.39
298 processes: 296 sleeping, 1 running, 1 zombie, 0 stopped
CPU0 states: 11.0% user, 1.0% system, 0.0% nice, 86.0% idle
CPU1 states: 2.0% user, 5.0% system, 0.0% nice, 91.0% idle
Mem: 2064832K av, 1849316K used, 215516K free, 0K shrd,
22396K
buff
Swap: 3164720K av, 0K used, 3164720K free
1409560K
cached

6. 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

set-variable = max_connections=1000
set-variable = wait_timeout=60
set-variable = connect_timeout=30
set-variable = max_connect_errors=10

mysqladmin -u root -p variables

copy and paste output here

7. your mysql extended-status output either still telnet as root user
type

mysqladmin -u root -p extended-status

+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| Aborted_clients | 1188 |
| Aborted_connects | 4 |
| Bytes_received | 19856790 |
| Bytes_sent | 184465636 |
| Com_admin_commands | 4462 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 1 |
| Com_change_db | 58484 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 1 |
| Com_create_db | 1 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 15 |
| Com_delete | 1471 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 2 |
| Com_flush | 1 |
| Com_grant | 1 |
| Com_insert | 3100 |
| Com_insert_select | 5 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 87 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_select | 201661 |
| Com_set_option | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 1 |
| Com_show_fields | 40 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 3 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 3 |
| Com_show_variables | 1 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 15106 |
| Connections | 34309 |
| Created_tmp_disk_tables | 1727 |
| Created_tmp_tables | 3376 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 441 |
| Handler_read_first | 9621 |
| Handler_read_key | 357412 |
| Handler_read_next | 3399734 |
| Handler_read_prev | 406298 |
| Handler_read_rnd | 343207 |
| Handler_read_rnd_next | 37479254 |
| Handler_update | 57636 |
| Handler_write | 453144 |
| Key_blocks_used | 7793 |
| Key_read_requests | 1281088 |
| Key_reads | 37074 |
| Key_write_requests | 12119 |
| Key_writes | 6144 |
| Max_used_connections | 90 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 64 |
| Open_files | 125 |
| Open_streams | 0 |
| Opened_tables | 15401 |
| Questions | 313791 |
| Select_full_join | 1682 |
| Select_full_range_join | 2 |
| Select_range | 3125 |
| Select_range_check | 0 |
| Select_scan | 59979 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 8288 |
| Sort_rows | 394901 |
| Sort_scan | 10095 |
| Table_locks_immediate | 228933 |
| Table_locks_waited | 12 |
| Threads_cached | 0 |
| Threads_created | 34308 |
| Threads_connected | 11 |
| Threads_running | 1 |
| Uptime | 6310 |
+--------------------------+-----------+

8. Lots of other hosted sites

9. Concurrent visitors not usually above 25 on the RP Forum I'd say

10. http://www.totalnfs.net/phil/phpinfo.php (http://www.totalnfs.net/phil/phpinfo.php)

11.

Timeout 250
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 15
MaxSpareServers 20
StartServers 5
MaxClients 300
MaxRequestsPerChild 100

12. 2.3.0

RichM

Wed 29th Oct '03, 4:33pm

Well firstly you could try upgrading to the latest version of PHP. (PHP 4.3.4RC3 at the time of this post)

Odysseus

Thu 30th Oct '03, 4:07am

Install PHP-Acceleraotr, Zend Optimizer or Truck MMCache in order to enhence your PHP performance.

As HostOrbit told, you should first upgrade to a later PHP Version. I'd personally never upgrade to a RC-Version, so try to use 4.3.3 instead and upgrade to 4.3.4 after it's final release.

eva2000

Thu 30th Oct '03, 5:21am

Just wondering if you had some suggestions for server optimization in our MySQL or Apache side of things. :)

1. is this on dedicated or shared virual server

Shared virtual (as it's just RP's forum), it's our dedicated server, but we have several sites on the dedicated server.

2. your server specs. For example:

cpu speed/type single or dual cpus): dual p3 1ghz
how much memory installed: 2GB ram
hard drive type/configuration: EIDE none RAID approx. 100G in total
(not
sure of details)
linux distributor or windows version: Redhat Linux 7.3
apache/IIS version: apache 1.3.27
PHP version: php 4.3.1
MySQL version: mysql --version
mysql Ver 11.18 Distrib 3.23.54, for pc-linux (i686)

3. if you use mysql 4.x instead of mysql 3.23.x, do you have any innodb
type
databases/tables on your server ?
4. if possible how mysql was compiled/installed
Standard, from what I know
5. your top stats
2:19pm up 2:19, 4 users, load average: 0.46, 0.47, 0.39
298 processes: 296 sleeping, 1 running, 1 zombie, 0 stopped
CPU0 states: 11.0% user, 1.0% system, 0.0% nice, 86.0% idle
CPU1 states: 2.0% user, 5.0% system, 0.0% nice, 91.0% idle
Mem: 2064832K av, 1849316K used, 215516K free, 0K shrd,
22396K
buff
Swap: 3164720K av, 0K used, 3164720K free
1409560K
cached

6. 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

set-variable = max_connections=1000
set-variable = wait_timeout=60
set-variable = connect_timeout=30
set-variable = max_connect_errors=10

mysqladmin -u root -p variables

copy and paste output here

7. your mysql extended-status output either still telnet as root user
type

mysqladmin -u root -p extended-status

+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| Aborted_clients | 1188 |
| Aborted_connects | 4 |
| Bytes_received | 19856790 |
| Bytes_sent | 184465636 |
| Com_admin_commands | 4462 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 1 |
| Com_change_db | 58484 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 1 |
| Com_create_db | 1 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 15 |
| Com_delete | 1471 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 2 |
| Com_flush | 1 |
| Com_grant | 1 |
| Com_insert | 3100 |
| Com_insert_select | 5 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 87 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_select | 201661 |
| Com_set_option | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 1 |
| Com_show_fields | 40 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 3 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 3 |
| Com_show_variables | 1 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 15106 |
| Connections | 34309 |
| Created_tmp_disk_tables | 1727 |
| Created_tmp_tables | 3376 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 441 |
| Handler_read_first | 9621 |
| Handler_read_key | 357412 |
| Handler_read_next | 3399734 |
| Handler_read_prev | 406298 |
| Handler_read_rnd | 343207 |
| Handler_read_rnd_next | 37479254 |
| Handler_update | 57636 |
| Handler_write | 453144 |
| Key_blocks_used | 7793 |
| Key_read_requests | 1281088 |
| Key_reads | 37074 |
| Key_write_requests | 12119 |
| Key_writes | 6144 |
| Max_used_connections | 90 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 64 |
| Open_files | 125 |
| Open_streams | 0 |
| Opened_tables | 15401 |
| Questions | 313791 |
| Select_full_join | 1682 |
| Select_full_range_join | 2 |
| Select_range | 3125 |
| Select_range_check | 0 |
| Select_scan | 59979 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 8288 |
| Sort_rows | 394901 |
| Sort_scan | 10095 |
| Table_locks_immediate | 228933 |
| Table_locks_waited | 12 |
| Threads_cached | 0 |
| Threads_created | 34308 |
| Threads_connected | 11 |
| Threads_running | 1 |
| Uptime | 6310 |
+--------------------------+-----------+

8. Lots of other hosted sites

9. Concurrent visitors not usually above 25 on the RP Forum I'd say

10. http://www.totalnfs.net/phil/phpinfo.php (http://www.totalnfs.net/phil/phpinfo.php)

11.

Timeout 250
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 15
MaxSpareServers 20
StartServers 5
MaxClients 300
MaxRequestsPerChild 100

12. 2.3.0
1. upgrade mysql to at least 3.23.58
2. change my.cnf to below and restart mysql

[mysqld]
set-variable = max_connections=400
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=2M
set-variable = sort_buffer=2M
set-variable = table_cache=1024
set-variable = thread_cache_size=64
set-variable = wait_timeout=1800
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

3. upgrade apache to 1.3.28
4. upgrade PHP to 4.3.3