server load, mysql optimising

March 10th, 2010 Posted in portal.juzhang.com | edit

1. is this on dedicated or shared virual server
dedicated

2. your server specs. For example:

cpu speed/type single or dual cpus): Dual xeon 2.0
how much memory installed: 2gb ram
hard drive type/configuration: 2x73GB scsi
linux distributor or windows version: redhat linux 7.3
apache/IIS version: apache 1.3.28
PHP version: php 4.3.2
MySQL version: mysql 4.0.14

3. if you use mysql 4.x instead of mysql 3.23.x, do you have any innodb type databases/tables on your server ?
not sure

4. if possible how mysql was compiled/installed
default cpanel installation

5. your top stats
11:12pm up 7 days, 16:27, 1 user, load average: 4.98, 5.22, 5.14
167 processes: 157 sleeping, 5 running, 5 zombie, 0 stopped
CPU0 states: 70.0% user, 7.0% system, 0.0% nice, 22.0% idle
CPU1 states: 15.0% user, 16.1% system, 0.0% nice, 68.0% idle
CPU2 states: 37.0% user, 12.0% system, 0.0% nice, 50.0% idle
CPU3 states: 23.0% user, 10.0% system, 0.0% nice, 66.0% idle
Mem: 2064716K av, 1929012K used, 135704K free, 0K shrd, 14660K buff
Swap: 2048276K av, 39328K used, 2008948K free 1666168K 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

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

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
http://207.44.248.33/stats.php

Mon Aug 18 23:12:12 AST 2003

11:12pm up 7 days, 16:27, 1 user, load average: 4.98, 5.22, 5.14
167 processes: 157 sleeping, 5 running, 5 zombie, 0 stopped
CPU0 states: 70.0% user, 7.0% system, 0.0% nice, 22.0% idle
CPU1 states: 15.0% user, 16.1% system, 0.0% nice, 68.0% idle
CPU2 states: 37.0% user, 12.0% system, 0.0% nice, 50.0% idle
CPU3 states: 23.0% user, 10.0% system, 0.0% nice, 66.0% idle
Mem: 2064716K av, 1929012K used, 135704K free, 0K shrd, 14660K buff
Swap: 2048276K av, 39328K used, 2008948K free 1666168K cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
3730 mysql 19 0 39260 38M 1396 R 67.6 1.9 0:25 mysqld
15997 nobody 16 0 1092 1092 828 R 13.6 0.0 0:00 top
3715 mysql 16 0 39252 38M 1396 R 12.8 1.9 0:40 mysqld
16006 nobody 17 0 5952 5952 3932 R 10.4 0.2 0:00 httpd
16024 nobody 16 0 5568 5568 3880 R 5.6 0.2 0:00 httpd

Http processes currently running = 47
Mysql processes currently running = 52

Netstat information summary
1 LAST_ACK
6 FIN_WAIT2
8 CLOSE_WAIT
8 SYN_RECV
12 CLOSING
14 FIN_WAIT1
22 LISTEN
31 ESTABLISHED
758 TIME_WAIT

+--------------------------+-----------+
Variable_name Value
+--------------------------+-----------+
Aborted_clients 3574
Aborted_connects 0
Bytes_received 8608165
Bytes_sent 195190045
Com_admin_commands 0
Com_alter_table 0
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_change_db 2704
Com_change_master 0
Com_check 0
Com_commit 0
Com_create_db 0
Com_create_function 0
Com_create_index 0
Com_create_table 0
Com_delete 477
Com_delete_multi 0
Com_drop_db 0
Com_drop_function 0
Com_drop_index 0
Com_drop_table 0
Com_flush 0
Com_grant 0
Com_ha_close 0
Com_ha_open 0
Com_ha_read 0
Com_insert 1047
Com_insert_select 6
Com_kill 0
Com_load 0
Com_load_master_data 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 18
Com_replace_select 0
Com_reset 0
Com_restore_table 0
Com_revoke 0
Com_rollback 0
Com_savepoint 0
Com_select 14032
Com_set_option 0
Com_show_binlog_events 0
Com_show_binlogs 0
Com_show_create 0
Com_show_databases 0
Com_show_fields 0
Com_show_grants 0
Com_show_keys 0
Com_show_logs 0
Com_show_master_status 0
Com_show_new_master 0
Com_show_open_tables 0
Com_show_processlist 8
Com_show_slave_hosts 0
Com_show_slave_status 0
Com_show_status 7
Com_show_innodb_status 0
Com_show_tables 0
Com_show_variables 7
Com_slave_start 0
Com_slave_stop 0
Com_truncate 0
Com_unlock_tables 0
Com_update 4793
Connections 2738
Created_tmp_disk_tables 1378
Created_tmp_tables 2261
Created_tmp_files 0
Delayed_insert_threads 0
Delayed_writes 0
Delayed_errors 0
Flush_commands 1
Handler_commit 0
Handler_delete 504
Handler_read_first 266
Handler_read_key 4190784
Handler_read_next 756128
Handler_read_prev 16619
Handler_read_rnd 100901
Handler_read_rnd_next 92540713
Handler_rollback 0
Handler_update 3856118
Handler_write 110205
Key_blocks_used 11318
Key_read_requests 1027180
Key_reads 11317
Key_write_requests 2427
Key_writes 2068
Max_used_connections 44
Not_flushed_key_blocks 0
Not_flushed_delayed_rows 0
Open_tables 172 3% of table_cache in use
Open_files 215
Open_streams 0
Opened_tables 178
Questions 45157
Qcache_queries_in_cache 1126
Qcache_inserts 12164
Qcache_hits 21120
Qcache_lowmem_prunes 0
Qcache_not_cached 1868
Qcache_free_memory 152655648
Qcache_free_blocks 381
Qcache_total_blocks 2713
Rpl_status NULL
Select_full_join 108
Select_full_range_join 134
Select_range 1505
Select_range_check 0
Select_scan 2913
Slave_open_temp_tables 0
Slave_running OFF
Slow_launch_threads 0
Slow_queries 2 (execution time > 10 secs)
Sort_merge_passes 0
Sort_range 1040
Sort_rows 63239177
Sort_scan 3004
Table_locks_immediate 26625
Table_locks_waited 1437
Threads_cached 33
Threads_created 45
Threads_connected 12
Threads_running 9
Uptime 833 13 mins 53 secs
+--------------------------+-----------+

Key Reads/Key Read Requests = 0.011018 (Cache hit = 99.988982%)
Key Writes/Key Write Requests = 0.852081
Connections/second = 3.287 (/hour = 11832.893)
KB received/second = 10.091 (/hour = 36328.451)
KB sent/second = 228.830 (/hour = 823786.315)
Temporary Tables Created/second = 2.714 (/hour = 9771.429)
Opened Tables/second = 0.214 (/hour = 769.268)
Slow Queries/second = 0.002 (/hour = 8.643)
% of slow queries = 0.004%
Queries/second = 54.210 (/hour = 195156.303)
Next automatic buffers flush in 2 hrs 26 mins 7 secsMySQL Query Cache hits = 21120/35152(60%)

8. oh and is your vB the only thing on the server? or other scripts? sites?
yeah only thing

9. how many average and max concurrent users on your vB forum ?
250

10. 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. http://207.44.248.33/phpinfo.php

11. 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 Off
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 15
MaxSpareServers 40
StartServers 15
MaxClients 256

12. what version of vB are you running ?

yeah deleted log that bigger than 2GB

what about the other load thingie?
i'll need to see the contents of your existing /etc/my.cnf file.. from your mysqlinfo.php url it shows memory allocation to mysql is 640+ MB which suggest your my.cnf memory buffers are set too high...

holy cow that is tooooooooo much memory allocated!
change your my.cnf to below and restart mysql

[mysqld]
max_connections = 600
key_buffer = 32M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 64
wait_timeout = 1800
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
skip-innodb

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

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

holy cow that is tooooooooo much memory allocated!
change your my.cnf to below and restart mysql

that was my older my.cnf and the load was way high

and its way less variables than the current my.cnf i pasted above

any of the variable i had was any effective for me?

[mysqld]
set-variable = query_cache_type=1
set-variable = query_cache_limit=10M
set-variable = query_cache_size=150M

set-variable = max_connections=650
set-variable = ft_max_word_len=30
set-variable = ft_max_word_len_for_sort=20
set-variable = ft_min_word_len=2
bind-address = 127.0.0.1
port = 3306

set-variable = delay_key_write=ON
set-variable = delayed_queue_size=10000
set-variable = delayed_insert_limit=50
set-variable = max_delayed_threads=8

set-variable = connect_timeout=10
set-variable = interactive_timeout=100
set-variable = wait_timeout=60

set-variable = low-priority-updates=ON
set-variable = key_buffer=1024M
set-variable = table_cache=5500
set-variable = sort_buffer_size=5M
set-variable = join_buffer_size=5M
set-variable = read_buffer_size=5M
set-variable = read_rnd_buffer_size=5M
set-variable = tmp_table_size=400M
set-variable = myisam_sort_buffer_size=256M
set-variable = thread_cache=256
set-variable = thread_stack=256000
set-variable = thread_concurrency=4
server-id = 1
set-variable = max_allowed_packet=20M
set-variable = flush_time=300
set-variable = max_connect_errors=10

skip-bdb
skip-innodb

[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

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
open_files_limit=8192
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

yeah deleted log that bigger than 2GB

what about the other load thingie?

httpd_error.log has this nonstop error msg
[Tue Aug 19 00:38:52 2003] [notice] child pid 17305 exit signal File size limit exceeded (25)
[Tue Aug 19 00:38:52 2003] [notice] child pid 17302 exit signal File size limit exceeded (25)
[Tue Aug 19 00:38:52 2003] [notice] child pid 17297 exit signal File size limit exceeded (25)
[Tue Aug 19 00:38:52 2003] [notice] child pid 17294 exit signal File size limit exceeded (25)
[Tue Aug 19 00:38:52 2003] [notice] child pid 17153 exit signal File size limit exceeded (25)
[Tue Aug 19 00:38:52 2003] [notice] child pid 17152 exit signal File size limit exceeded (25)

after recompling apache..

#If you have any other info about this subject , Please add it free.#

server load, mysql optimising

Archives

Meta