Please help me with my server. the loads keeps on jumping up and down. It can go from .6 to 44 in just a matter of minutes. Also I have a question about purchasing servers.
is it better to have a different server to host the mysql database which has lower server spec or host the mysql and http on the same server with higher specs.
Thank you for your help!
1.dedicated
2.Core2 6550/2GBRAM/400HD-NON-RAID/CENTOS/cpanel V. 11.18.6-stable
-mysql 5.0.45-community
-php 5.2.5
-apache 2.2.8
3. 3.68 PL2
6.
top - 23:57:08 up 11 days, 31 min, 1 user, load average: 8.14, 5.67, 3.43
Tasks: 173 total, 1 running, 170 sleeping, 1 stopped, 1 zombie
Cpu(s): 68.8%us, 34.0%sy, 0.0%ni, 62.0%id, 0.0%wa, 0.0%hi, 0.2%si, 0.0%st
Mem: 2067376k total, 1935632k used, 131744k free, 125068k buffers
Swap: 2031608k total, 96k used, 2031512k free, 1029412k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2674 mysql 10 -5 219m 189m 3604 S 52 9.4 8362:09 mysqld
3659 nobody 15 0 95960 19m 8792 S 20 1.0 0:00.95 httpd
3718 nobody 15 0 95772 18m 8012 S 2 0.9 0:00.07 httpd
3344 nobody 15 0 99544 30m 15m S 1 1.5 0:01.42 httpd
3022 nobody 15 0 97492 31m 18m S 0 1.5 0:03.12 httpd
1 root 15 0 2060 580 500 S 0 0.0 0:03.86 init
2 root RT 0 0 0 0 S 0 0.0 0:00.10 migration/0
3 root 34 19 0 0 0 S 0 0.0 0:00.05 ksoftirqd/0
4 root RT 0 0 0 0 S 0 0.0 0:00.00 watchdog/0
5 root RT 0 0 0 0 S 0 0.0 4:50.89 migration/1
6 root 34 19 0 0 0 S 0 0.0 0:00.07 ksoftirqd/1
7 root RT 0 0 0 0 S 0 0.0 0:00.00 watchdog/1
8 root 10 -5 0 0 0 S 0 0.0 0:00.24 events/0
9 root 10 -5 0 0 0 S 0 0.0 0:00.23 events/1
10 root 10 -5 0 0 0 S 0 0.0 0:00.00 khelper
11 root 10 -5 0 0 0 S 0 0.0 0:00.00 kthread
15 root 18 -5 0 0 0 S 0 0.0 0:00.29 kblockd/0
7.
[mysqld]
safe-show-database
#old_passwords
back_log = 50
skip-innodb
max_connections = 500
key_buffer_size = 64M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 4000
thread_cache_size = 256
wait_timeout = 90
connect_timeout = 10
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
net_buffer_length = 16384
max_connect_errors = 10
thread_concurrency = 4
concurrent_insert = 2
table_lock_wait_timeout = 30
read_rnd_buffer_size = 786432
bulk_insert_buffer_size = 8M
query_cache_limit = 4M
query_cache_size = 64M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = MyISAM
max_write_lock_count = 16
collation-server=latin1_general_ci
[mysql.server]
user=mysql
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid
nice = -5
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
8.
#
# Beginning report, 0 0:0:0
#
MySQL 5.0.45-community uptime 11 0:46:58 Mon Jun 30 00:11:19 2008
__ Key __________________________________________________ _______________
Buffer used 56.63M of 64.00M %Used: 88.49
Current 63.61M %Usage: 99.40
Write hit 59.15%
Read hit 100.00%
__ Questions __________________________________________________ _________
Total 76.95M 80.7/s
DMS 56.37M 59.1/s %Total: 73.26
QC Hits 16.23M 17.0/s 21.10
Com_ 2.36M 2.5/s 3.06
COM_QUIT 1.99M 2.1/s 2.58
+Unknown 1.33k 0.0/s 0.00
Slow 10 s 51 0.0/s 0.00 %DMS: 0.00 Log: OFF
DMS 56.37M 59.1/s 73.26
UPDATE 35.01M 36.7/s 45.50 62.11
SELECT 17.45M 18.3/s 22.68 30.96
INSERT 1.82M 1.9/s 2.36 3.22
DELETE 1.20M 1.3/s 1.55 2.12
REPLACE 894.54k 0.9/s 1.16 1.59
Com_ 2.36M 2.5/s 3.06
change_db 2.30M 2.4/s 2.99
unlock_tabl 11.03k 0.0/s 0.01
lock_tables 11.03k 0.0/s 0.01
__ SELECT and Sort __________________________________________________ ___
Scan 4.89M 5.1/s %SELECT: 27.99
Range 1.98M 2.1/s 11.37
Full join 3.00k 0.0/s 0.02
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 2.37M 2.5/s
Sort range 1.61M 1.7/s
Sort mrg pass 314.57k 0.3/s
__ Query Cache __________________________________________________ _______
Memory usage 37.02M of 64.00M %Used: 57.84
Block Fragmnt 19.63%
Hits 16.23M 17.0/s
Inserts 17.09M 17.9/s
Insrt:Prune 19.31:1 17.0/s
Hit:Insert 0.95:1
__ Table Locks __________________________________________________ _______
Waited 362.96k 0.4/s %Total: 0.55
Immediate 65.05M 68.2/s
__ Tables __________________________________________________ ____________
Open 1152 of 4000 %Cache: 28.80
Opened 6.15k 0.0/s
__ Connections __________________________________________________ _______
Max used 66 of 500 %Max: 13.20
Total 1.99M 2.1/s
__ Created Temp __________________________________________________ ______
Disk table 64.65k 0.1/s
Table 764.52k 0.8/s Size: 64.0M
File 629.13k 0.7/s
__ Threads __________________________________________________ ___________
Running 2 of 5
Cached 61 of 256 %Hit: 100
Created 66 0.0/s
Slow 0 0/s
__ Aborted __________________________________________________ ___________
Clients 8.07k 0.0/s
Connects 829 0.0/s
__ Bytes __________________________________________________ _____________
Sent 118.97G 124.8k/s
Received 4.19G 4.4k/s
__ InnoDB Buffer Pool __________________________________________________
Usage 0 of 0 %Used: 0.00
Read hit 0.00%
Pages
Free 0 %Total: 0.00
Data 0 0.00 %Drty: 0.00
Misc 0 0.00
Latched 0 0.00
Reads 0 0/s
From file 0 0/s 0.00
Ahead Rnd 0 0/s
Ahead Sql 0 0/s
Writes 0 0/s
Flushes 0 0/s
Wait Free 0 0/s
__ InnoDB Lock __________________________________________________ _______
Waits 0 0/s
Current 0
Time acquiring
Total 0 ms
Average 0 ms
Max 0 ms
__ InnoDB Data, Pages, Rows ____________________________________________
Data
Reads 0 0/s
Writes 0 0/s
fsync 0 0/s
Pending
Reads 0
Writes 0
fsync 0
Pages
Created 0 0/s
Read 0 0/s
Written 0 0/s
Rows
Deleted 0 0/s
Inserted 0 0/s
Read 0 0/s
Updated 0 0/s
#
# Interval report 1, +0 0:0:10
#
MySQL 5.0.45-community uptime 11 0:47:8 Mon Jun 30 00:11:29 2008
__ Key __________________________________________________ _______________
Buffer used 56.63M of 64.00M %Used: 88.49
Current 63.62M %Usage: 99.41
Write hit 20.95%
Read hit 100.00%
__ Questions __________________________________________________ _________
Total 911 91.1/s
DMS 485 48.5/s %Total: 53.24
QC Hits 316 31.6/s 34.69
Com_ 62 6.2/s 6.81
COM_QUIT 48 4.8/s 5.27
Slow 10 s 0 0/s 0.00 %DMS: 0.00 Log: OFF
DMS 485 48.5/s 53.24
SELECT 294 29.4/s 32.27 60.62
UPDATE 101 10.1/s 11.09 20.82
INSERT 63 6.3/s 6.92 12.99
DELETE 16 1.6/s 1.76 3.30
REPLACE 11 1.1/s 1.21 2.27
Com_ 62 6.2/s 6.81
change_db 54 5.4/s 5.93
set_option 1 0.1/s 0.11
show_variab 1 0.1/s 0.11
__ SELECT and Sort __________________________________________________ ___
Scan 149 14.9/s %SELECT: 50.68
Range 19 1.9/s 6.46
Full join 0 0/s 0.00
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 53 5.3/s
Sort range 10 1.0/s
Sort mrg pass 1 0.1/s
__ Query Cache __________________________________________________ _______
Memory usage 33.21M of 64.00M %Used: 51.88
Block Fragmnt 26.62%
Hits 316 31.6/s
Inserts 242 24.2/s
Insrt:Prune 242:1 24.1/s
Hit:Insert 1.31:1
__ Table Locks __________________________________________________ _______
Waited 0 0/s %Total: 0.00
Immediate 700 70.0/s
__ Tables __________________________________________________ ____________
Open 1151 of 4000 %Cache: 28.77
Opened 3 0.3/s
__ Connections __________________________________________________ _______
Max used 66 of 500 %Max: 13.20
Total 50 5.0/s
__ Created Temp __________________________________________________ ______
Disk table 0 0/s
Table 16 1.6/s Size: 64.0M
File 2 0.2/s
__ Threads __________________________________________________ ___________
Running 2 of 7
Cached 59 of 256 %Hit: 100
Created 0 0/s
Slow 0 0/s
__ Aborted __________________________________________________ ___________
Clients 0 0/s
Connects 0 0/s
__ Bytes __________________________________________________ _____________
Sent 14.79M 1.5M/s
Received 184.64k 18.5k/s
__ InnoDB Buffer Pool __________________________________________________
Usage 0 of 0 %Used: 0.00
Read hit 0.00%
Pages
Free 0 %Total: 0.00
Data 0 0.00 %Drty: 0.00
Misc 0 0.00
Latched 0 0.00
Reads 0 0/s
From file 0 0/s 0.00
Ahead Rnd 0 0/s
Ahead Sql 0 0/s
Writes 0 0/s
Flushes 0 0/s
Wait Free 0 0/s
__ InnoDB Lock __________________________________________________ _______
Waits 0 0/s
Current 0
Apache tuning and optimization cPanel 11.x | cPanelConfig - cPanel ::
Please take the time and register. We would love to have your contribution . I utilized your suggestions on my web server and I hope they will help speed
http://www.cpanelconfig.com/optimize-a-cpanel-server/apache-tuning-and-optimization-cpanel-11x/HOME |
Time acquiring
Total 0 ms
Average 0 ms
Max 0 ms
__ InnoDB Data, Pages, Rows ____________________________________________
Data
Reads 0 0/s
Writes 0 0/s
fsync 0 0/s
Pending
Reads 0
Writes 0
fsync 0
Pages
Created 0 0/s
Read 0 0/s
Written 0 0/s
Rows
Deleted 0 0/s
Inserted 0 0/s
Read 0 0/s
Updated 0 0/s
9.Vbulletin and other simple html pages.
10.I dont know...
11.phpinfo (http://www.vbulletin.com/forum/vietfiles.org/phpinfo.php)
12.
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 1
StartServers 15
MinSpareServers 15
MaxSpareServers 20
ServerLimit 256
MaxClients 150
MaxRequestsPerChild 1000
13.none
14.
Linux server.vinashare.com 2.6.18-53.1.14.el5 #1 SMP Wed Mar 5 11:36:49 EST 2008 i686 i686 i386 GNU/Linux
core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 32637
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
real-time priority (-r) 0
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) 32637
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Core(TM)2 Duo CPU E6550 @ 2.33GHz
stepping : 11
cpu MHz : 1998.000
cache size : 4096 KB
physical id : 0
siblings : 2
core id : 0
cpu cores : 2
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 10
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 lm constant_tsc pni monitor ds_cpl vmx smx est tm2 cx16 xtpr lahf_lm
bogomips : 3725.49
processor : 1
vendor_id : GenuineIntel
cpu family : 6
model : 15
model name : Intel(R) Core(TM)2 Duo CPU E6550 @ 2.33GHz
stepping : 11
cpu MHz : 2331.000
cache size : 4096 KB
physical id : 0
siblings : 2
core id : 1
cpu cores : 2
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 10
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 lm constant_tsc pni monitor ds_cpl vmx smx est tm2 cx16 xtpr lahf_lm
bogomips : 3723.29
those top stats say you're swapping to disk by a fair amount so using more memory than you have available
you also running a torrent tracer xbt_tracker ?? tried turning that off and see how loads are ?
Thanks for your reply. Please help me
TOP
root@server [~]# top
top - 09:20:21 up 19 days, 9:54, 1 user, load average: 10.54, 6.76, 4.52
Tasks: 160 total, 3 running, 155 sleeping, 1 stopped, 1 zombie
Cpu(s): 50.9%us, 48.9%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.2%si, 0.0%st
Mem: 2067376k total, 1814528k used, 252848k free, 179176k buffers
Swap: 2031608k total, 136448k used, 1895160k free, 1060104k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
9644 mysql 10 -5 224m 122m 3348 S 178 6.1 215:21.82 mysqld
19701 nobody 15 0 97820 19m 8432 S 7 1.0 0:00.31 httpd
19619 nobody 15 0 95924 23m 14m S 4 1.2 0:01.73 httpd
19628 nobody 15 0 97092 23m 12m S 3 1.2 0:03.30 httpd
19703 nobody 15 0 95080 16m 8032 S 3 0.8 0:00.10 httpd
19704 nobody 15 0 93592 14m 7408 S 3 0.7 0:00.08 httpd
19592 nobody 15 0 98808 24m 11m R 1 1.2 0:03.97 httpd
4058 vietfile 15 0 56940 44m 1160 S 0 2.2 3062:04 xbt_tracker
19702 root 15 0 2188 1044 796 R 0 0.1 0:00.03 top
1 root 15 0 2060 484 456 S 0 0.0 0:04.92 init
2 root RT 0 0 0 0 S 0 0.0 0:00.15 migration/0
3 root 34 19 0 0 0 S 0 0.0 0:00.07 ksoftirqd/0
4 root RT 0 0 0 0 S 0 0.0 0:00.00 watchdog/0
5 root RT 0 0 0 0 S 0 0.0 8:20.54 migration/1
6 root 34 19 0 0 0 S 0 0.0 0:00.09 ksoftirqd/1
7 root RT 0 0 0 0 S 0 0.0 0:00.00 watchdog/1
8 root 10 -5 0 0 0 S 0 0.0 0:00.47 events/0
STATS.TXT
+---------------------------------+-----------------------------------------+
Variable_name Value
+---------------------------------+-----------------------------------------+
auto_increment_increment 1
auto_increment_offset 1
automatic_sp_privileges ON
back_log 50
basedir /
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/share/mysql/charsets/
collation_connection latin1_general_ci
collation_database latin1_general_ci
collation_server latin1_general_ci
completion_type 0
concurrent_insert 2
connect_timeout 10
datadir /var/lib/mysql/
[ubuntu] 51s+37s boot time ! - too slow, please help me optimize ::
9 posts - Last post: Jun 1, 2008too slow, please help me optimize General Help. Could you please install bootchart and post the results here (they will get stored in
http://ubuntuforums.org/showthread.php?t=803295HOME |
Course Overview::
Tuning & optimizing Queries using MS SQL server 2005. Course Code: M2784 Duration: 3. Price: €1000.00 (ex. Dates available on request. Please contact us
http://www.globalknowledge.be/Default.aspx?page=461&coursecode=M2784HOME |
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 YES
have_compress YES
have_crypt YES
have_csv YES
have_dynamic_loading YES
have_example_engine YES
have_federated_engine YES
have_geometry YES
have_innodb DISABLED
have_isam NO
have_merge_engine YES
have_ndbcluster DISABLED
have_openssl NO
have_ssl NO
have_query_cache YES
have_raid NO
have_rtree_keys YES
have_symlink YES
hostname server.vinashare.com
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
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_rollback_on_timeout OFF
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 134217728
key_cache_age_threshold 300
key_cache_block_size 1024
key_cache_division_limit 100
language /usr/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 OFF
log_warnings 1
long_query_time 10
low_priority_updates OFF
lower_case_file_system OFF
lower_case_table_names 0
max_allowed_packet 75496448
max_binlog_cache_size 4294967295
max_binlog_size 1073741824
max_connect_errors 10
max_connections 500
max_delayed_threads 20
max_error_count 64
max_heap_table_size 67108864
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 16
multi_range_count 256
myisam_data_pointer_size 6
myisam_max_sort_file_size 2147483647
myisam_recover_options OFF
myisam_repair_threads 1
myisam_sort_buffer_size 67108864
myisam_stats_method nulls_unequal
ndb_autoincrement_prefetch_sz 32
ndb_force_send ON
ndb_use_exact_count ON
ndb_use_transactions ON
ndb_cache_check_time 0
ndb_connectstring
net_buffer_length 16384
net_read_timeout 30
net_retry_count 10
net_write_timeout 60
new OFF
old_passwords OFF
open_files_limit 9510
optimizer_prune_level 1
optimizer_search_depth 62
pid_file /var/lib/mysql/server.vinashare.com.pid
port 3306
preload_buffer_size 32768
profiling OFF
profiling_history_size 15
protocol_version 10
query_alloc_block_size 65536
query_cache_limit 5242880
query_cache_min_res_unit 4096
query_cache_size 67108864
query_cache_type ON
query_cache_wlock_invalidate OFF
query_prealloc_size 262144
range_alloc_block_size 2048
read_buffer_size 1044480
read_only OFF
read_rnd_buffer_size 782336
relay_log_purge ON
relay_log_space_limit 0
rpl_recovery_rank 0
secure_auth OFF
secure_file_priv
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 4194296
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 MST
table_cache 4500
table_lock_wait_timeout 30
table_type MyISAM
thread_cache_size 256
thread_stack 196608
time_format %H:%i:%s
time_zone SYSTEM
timed_mutexes OFF
tmp_table_size 67108864
tmpdir /tmp/
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
tx_isolation REPEATABLE-READ
updatable_views_with_limit YES
version 5.0.45-community
version_comment MySQL Community Edition (GPL)
version_compile_machine i686
version_compile_os pc-linux-gnu
wait_timeout 90
+---------------------------------+-----------------------------------------+
+-----------------------------------+------------+
Variable_name Value
+-----------------------------------+------------+
Aborted_clients 40
Aborted_connects 2
Binlog_cache_disk_use 0
Binlog_cache_use 0
Bytes_received 383125244
Bytes_sent 955224944
Com_admin_commands 0
Com_alter_db 0
Com_alter_table 0
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_call_procedure 0
Com_change_db 75073
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 32
Com_create_user 0
Com_dealloc_sql 0
Com_delete 30564
Com_delete_multi 0
Com_do 0
Com_drop_db 0
Com_drop_function 0
Com_drop_index 0
Com_drop_table 32
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 47210
Com_insert_select 300
Com_kill 0
Com_load 0
Com_load_master_data 0
Com_load_master_table 0
Com_lock_tables 178
Com_optimize 30
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 32974
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 665702
Com_set_option 70
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 226
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 94
Com_show_slave_hosts 0
Com_show_slave_status 0
Com_show_status 258
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 32
Com_unlock_tables 178
Com_update 1139137
Com_update_multi 39
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 64283
Created_tmp_disk_tables 2882
Created_tmp_files 11552
Created_tmp_tables 25242
Delayed_errors 0
Delayed_insert_threads 0
Delayed_writes 0
Flush_commands 1
Handler_commit 0
Handler_delete 30842
Handler_discover 0
Handler_prepare 0
Handler_read_first 69028
Handler_read_key 69389744
Handler_read_next 888265177
Handler_read_prev 240706
Handler_read_rnd 5568609
Handler_read_rnd_next 2456453924
Handler_rollback 0
Handler_savepoint 0
Handler_savepoint_rollback 0
Handler_update 34258416
Handler_write 4196601
Innodb_buffer_pool_pages_data 0
Innodb_buffer_pool_pages_dirty 0
Innodb_buffer_pool_pages_flushed 0
Innodb_buffer_pool_pages_free 0
Innodb_buffer_pool_pages_latched 0
Innodb_buffer_pool_pages_misc 0
Innodb_buffer_pool_pages_total 0
Innodb_buffer_pool_read_ahead_rnd 0
Innodb_buffer_pool_read_ahead_seq 0
Innodb_buffer_pool_read_requests 0
Innodb_buffer_pool_reads 0
Innodb_buffer_pool_wait_free 0
Innodb_buffer_pool_write_requests 0
Innodb_data_fsyncs 0
Innodb_data_pending_fsyncs 0
Innodb_data_pending_reads 0
Innodb_data_pending_writes 0
Innodb_data_read 0
Innodb_data_reads 0
Innodb_data_writes 0
Innodb_data_written 0
Innodb_dblwr_pages_written 0
Innodb_dblwr_writes 0
Innodb_log_waits 0
Innodb_log_write_requests 0
Innodb_log_writes 0
Innodb_os_log_fsyncs 0
Innodb_os_log_pending_fsyncs 0
Innodb_os_log_pending_writes 0
Innodb_os_log_written 0
Innodb_page_size 0
Innodb_pages_created 0
Innodb_pages_read 0
Innodb_pages_written 0
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 0
Innodb_rows_read 0
Innodb_rows_updated 0
Key_blocks_not_flushed 0
Key_blocks_unused 90762
Key_blocks_used 25218
Key_read_requests 174405443
Key_reads 25467
Key_write_requests 206835
Key_writes 116665
Last_query_cost 0.000000
Max_used_connections 20
Ndb_cluster_node_id 0
Ndb_config_from_host
Ndb_config_from_port 0
Ndb_number_of_data_nodes 0
Not_flushed_delayed_rows 0
Open_files 608
Open_streams 0
Open_tables 358
Opened_tables 518
Prepared_stmt_count 0
Qcache_free_blocks 8650
Qcache_free_memory 29320488
Qcache_hits 580117
Qcache_inserts 660207
Qcache_lowmem_prunes 27527
Qcache_not_cached 5596
Qcache_queries_in_cache 14900
Qcache_total_blocks 39275
Questions 2636256
Rpl_status NULL
Select_full_join 69
Select_full_range_join 0
Select_range 57723
Select_range_check 0
Select_scan 146053
Slave_open_temp_tables 0
Slave_retried_transactions 0
Slave_running OFF
Slow_launch_threads 0
Slow_queries 0
Sort_merge_passes 5776
Sort_range 49293
Sort_rows 47094018
Sort_scan 73863
Table_locks_immediate 2186943
Table_locks_waited 11946
Tc_log_max_pages_used 0
Tc_log_page_size 0
Tc_log_page_waits 0
Threads_cached 17
Threads_connected 3
Threads_created 20
Threads_running 1
Uptime 28276
Uptime_since_flush_status 28276
+-----------------------------------+------------+
mysqladmin Ver 8.41 Distrib 5.0.45, for pc-linux-gnu on i686
(C) 2000-2006 MySQL 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.45-community
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 7 hours 51 min 16 sec
Threads: 3 Questions: 2636257 Slow queries: 0 Opens: 518 Flush tables: 1 Open tables: 358 Queries per second avg: 93.233
+-------+---------------+-----------+----+---------+------+-------+------------------+
Id User Host db Command Time State Info
+-------+---------------+-----------+----+---------+------+-------+------------------+
64282 vietnova_user localhost Query 0 show processlist
+-------+---------------+-----------+----+---------+------+-------+------------------+
I have also ask my host admin to look at my mysql server and this is what that have done and reply. The server load had increase alot more since their suggestion. Can you please help.
Hello
We have optimized the mysql in the server. We have changed the sort_buffer_size from 2M to 4M, because it will help to solve the queries which used to find and sort the data.
We could see that for simple queries, data packets will be small and less amount of memory is needed. So we have decreased the max_allowed_packet to 16M from 34M. Also we can see that number of data access is high, to handle more number of queries more threads want to be ready to run, so we have increased the thread_concurrency from 4M to 6M. Because of accessing the same data with the same type of query we have increased the query_cache_limit from 4M to 5M, so that the data that is fetched from the database will be there in the query cache and if similar type of query comes the data is fetched from the cache than from the database.
Thanks,
can't see anything out of the ordinary there, mysql stats look pretty okay to me despite the cpu load.
When you can cpu loads > 10.0 can you repost output for these 2 commands
top
mysqladmin -u root -p var ext ver proc > stats.txt
open the stats.txt file to copy and paste it's contents.
As well as for mysqlreport output if possible (mysqlreport question #8 at http://www.vbulletin.com/forum/showthread.php?t=70117)
mysqlreport
#
MySQL 5.0.45-community uptime 0 8:5:36 Tue Jul 8 09:36:15 2008
__ Key __________________________________________________ _______________
Buffer used 24.91M of 128.00M %Used: 19.46
Current 39.65M %Usage: 30.97
Write hit 43.38%
Read hit 99.99%
__ Questions __________________________________________________ _________
Total 2.69M 92.4/s
DMS 1.95M 66.8/s %Total: 72.34
QC Hits 599.11k 20.6/s 22.26
Com_ 78.96k 2.7/s 2.93
COM_QUIT 66.60k 2.3/s 2.47
-Unknown 270 0.0/s 0.01
Slow 10 s 0 0/s 0.00 %DMS: 0.00 Log: OFF
DMS 1.95M 66.8/s 72.34
UPDATE 1.14M 39.2/s 42.44 58.66
SELECT 690.89k 23.7/s 25.67 35.48
INSERT 48.67k 1.7/s 1.81 2.50
REPLACE 33.95k 1.2/s 1.26 1.74
DELETE 31.44k 1.1/s 1.17 1.61
Com_ 78.96k 2.7/s 2.93
change_db 77.77k 2.7/s 2.89
show_status 269 0.0/s 0.01
show_fields 240 0.0/s 0.01
__ SELECT and Sort __________________________________________________ ___
Scan 150.99k 5.2/s %SELECT: 21.85
Range 59.89k 2.1/s 8.67
Full join 70 0.0/s 0.01
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 76.94k 2.6/s
Sort range 50.98k 1.7/s
Sort mrg pass 6.00k 0.2/s
__ Query Cache __________________________________________________ _______
Memory usage 37.47M of 64.00M %Used: 58.55
Block Fragmnt 20.07%
Hits 599.11k 20.6/s
Inserts 685.20k 23.5/s
Insrt:Prune 24.15:1 22.5/s
Hit:Insert 0.87:1
__ Table Locks __________________________________________________ _______
Waited 12.34k 0.4/s %Total: 0.55
Immediate 2.23M 76.5/s
__ Tables __________________________________________________ ____________
Open 367 of 4500 %Cache: 8.16
Opened 527 0.0/s
__ Connections __________________________________________________ _______
Max used 20 of 500 %Max: 4.00
Total 66.62k 2.3/s
__ Created Temp __________________________________________________ ______
Disk table 2.98k 0.1/s
Table 26.27k 0.9/s Size: 64.0M
File 11.99k 0.4/s
__ Threads __________________________________________________ ___________
Running 1 of 6
Cached 14 of 256 %Hit: 99.97
Created 20 0.0/s
Slow 0 0/s
__ Aborted __________________________________________________ ___________
Clients 40 0.0/s
Connects 8 0.0/s
__ Bytes __________________________________________________ _____________
Sent 1.89G 64.7k/s
Received 394.02M 13.5k/s
__ InnoDB Buffer Pool __________________________________________________
Usage 0 of 0 %Used: 0.00
Read hit 0.00%
Pages
Free 0 %Total: 0.00
Data 0 0.00 %Drty: 0.00
Misc 0 0.00
Latched 0 0.00
Reads 0 0/s
From file 0 0/s 0.00
Ahead Rnd 0 0/s
Ahead Sql 0 0/s
Writes 0 0/s
Flushes 0 0/s
Wait Free 0 0/s
__ InnoDB Lock __________________________________________________ _______
Waits 0 0/s
Current 0
Time acquiring
Total 0 ms
Average 0 ms
Max 0 ms
__ InnoDB Data, Pages, Rows ____________________________________________
Data
Reads 0 0/s
Writes 0 0/s
fsync 0 0/s
Pending
Reads 0
Writes 0
fsync 0
Pages
Created 0 0/s
Read 0 0/s
Written 0 0/s
Rows
Deleted 0 0/s
Inserted 0 0/s
Read 0 0/s
Updated 0 0/s
 Where's The Advantage In Windows Genuine Advantage?
 Stocks Bounce After S&P Joins Bear Market
|
