List:MySQL on Win32« Previous MessageNext Message »
From:Shaun Adams Date:September 1 2005 10:10pm
Subject:HELP! Customer is Pissed! Load on Server is High. Need some ideas for solutions.
View as plain text  
Hi All,
 
First of all, thanks for even reading this.  Any help, advise, wisdom,
assistance would be greatly appreciated.
 
Here's the problem:
 
I am a systems admin who's having serious MySQL load problems.  Our
application is a database that provides solutions to mostly hospitals. At
one of our largest customer hospitals, we've been noticing major performance
issues where the application drags and hangs for 30 seconds to sometimes 3
minutes when retreiving data and returning it to our web page.  We use PHP
as our front end.  We have Zend Accellerator installed on the front end. IIS
6.0, MYSQL 4.0.25.  The database size is about 600MB.  We find that the
database is slowest when more users (about 40-60) are logged into our
application.  When less users are on, the application is much faster.  
 
Now, our developers know that they have to reprogram the app (queries,
etc)., but in the meantime, we cant let our customer continue experiencing
these issues.  I know for sure that most of the problem has to do with the
load on the server.  It's a Dell 1850 with Dual Xeon Processors and about 4
Gigs of RAM.  We are using MyISM tables.  I am replicating to another server
 
Should I changeover to the NDB storage type and start clustering?  I mean
since it stores all of the data in memory, shouldn't that give me better
performance?  Should I setup a farm of slave servers and load balance them
(which we really dont want to do).  Any suggestions?
 
The following are the settings from my SHOW VARIABLES: 
 

back_log 50 
basedir d:\mysql\ 
bdb_cache_size 8388572 
bdb_log_buffer_size 262144 
bdb_home d:\mysql\data\ 
bdb_max_lock 10000 
bdb_logdir   
bdb_shared_data OFF 
bdb_tmpdir C:\WINDOWS\TEMP\ 
bdb_version Sleepycat Software: Berkeley DB 3.2.9a: (June 29, 2005) 
binlog_cache_size 32768 
bulk_insert_buffer_size 8388608 
character_set latin1 
character_sets latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620
ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew
win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257
latin5 
concurrent_insert ON 
connect_timeout 5 
convert_character_set   
datadir d:\mysql\data\ 
default_week_format 0 
delay_key_write ON 
delayed_insert_limit 100 
delayed_insert_timeout 300 
delayed_queue_size 1000 
flush OFF 
flush_time 1800 
ft_boolean_syntax + -><()~*:""&| 
ft_min_word_len 4 
ft_max_word_len 254 
ft_max_word_len_for_sort 20 
ft_stopword_file (built-in) 
have_bdb YES 
have_crypt NO 
have_innodb DISABLED 
have_isam YES 
have_raid NO 
have_symlink YES 
have_openssl NO 
have_query_cache YES 
init_file   
innodb_additional_mem_pool_size 1048576 
innodb_autoextend_increment 8 
innodb_buffer_pool_size 8388608 
innodb_data_file_path   
innodb_data_home_dir   
innodb_file_io_threads 4 
innodb_force_recovery 0 
innodb_thread_concurrency 8 
innodb_flush_log_at_trx_commit 1 
innodb_fast_shutdown ON 
innodb_flush_method   
innodb_lock_wait_timeout 50 
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_mirrored_log_groups 1 
innodb_max_dirty_pages_pct 90 
innodb_max_purge_lag 0 
innodb_table_locks ON 
interactive_timeout 28800 
join_buffer_size 131072 
key_buffer_size 402653184 
language d:\mysql\share\english\ 
large_files_support ON 
license GPL 
local_infile ON 
log OFF 
log_update OFF 
log_bin ON 
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 1 
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_insert_delayed_threads 20 
max_heap_table_size 16777216 
max_join_size 4294967295 
max_relay_log_size 0 
max_seeks_for_key 4294967295 
max_sort_length 1024 
max_user_connections 0 
max_tmp_tables 32 
max_write_lock_count 4294967295 
myisam_max_extra_sort_file_size 107374182400 
myisam_max_sort_file_size 107374182400 
myisam_repair_threads 1 
myisam_recover_options OFF 
myisam_sort_buffer_size 214958080 
net_buffer_length 16384 
net_read_timeout 30 
net_retry_count 10 
net_write_timeout 60 
new OFF 
open_files_limit 0 
pid_file d:\mysql\data\PEMINIC-220.pid 
log_error .\PEMINIC-220.err 
port 3306 
protocol_version 10 
query_alloc_block_size 8192 
query_cache_limit 1048576 
query_cache_size 33554432 
query_cache_type ON 
query_cache_wlock_invalidate OFF 
query_prealloc_size 8192 
range_alloc_block_size 2048 
read_buffer_size 126976 
read_only OFF 
read_rnd_buffer_size 520192 
rpl_recovery_rank 0 
server_id 220 
slave_net_timeout 3600 
skip_external_locking ON 
skip_networking OFF 
skip_show_database OFF 
slow_launch_time 2 
sort_buffer_size 262108 
sql_mode 0 
table_cache 512 
table_type MYISAM 
thread_cache_size 0 
thread_stack 196608 
tx_isolation REPEATABLE-READ 
timezone Eastern Daylight Time 
tmp_table_size 108003328 
tmpdir C:\WINDOWS\TEMP\ 
transaction_alloc_block_size 8192 
transaction_prealloc_size 4096 
version 4.0.25-debug-log 
version_comment Official MySQL binary 
version_compile_os Win32 
wait_timeout 28800 

 
Here are the settings from my SHOW STATUS;  The server was rebooted last
night:
 
query result ( 133 records )
Variable_name Value 
Aborted_clients 38 
Aborted_connects 0 
Bytes_received 1581180041 
Bytes_sent 759308829 
Com_admin_commands 1 
Com_alter_table 12 
Com_analyze 0 
Com_backup_table 0 
Com_begin 0 
Com_change_db 199443 
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 6 
Com_delete 198582 
Com_delete_multi 0 
Com_drop_db 0 
Com_drop_function 0 
Com_drop_index 0 
Com_drop_table 6 
Com_flush 0 
Com_grant 0 
Com_ha_close 0 
Com_ha_open 0 
Com_ha_read 0 
Com_insert 270685 
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 0 
Com_replace_select 0 
Com_reset 0 
Com_restore_table 0 
Com_revoke 0 
Com_rollback 0 
Com_savepoint 0 
Com_select 7562775 
Com_set_option 0 
Com_show_binlog_events 0 
Com_show_binlogs 0 
Com_show_create 0 
Com_show_databases 3 
Com_show_fields 448 
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 0 
Com_show_slave_hosts 1 
Com_show_slave_status 0 
Com_show_status 2 
Com_show_innodb_status 0 
Com_show_tables 822 
Com_show_variables 3 
Com_slave_start 0 
Com_slave_stop 0 
Com_truncate 0 
Com_unlock_tables 0 
Com_update 148859 
Com_update_multi 0 
Connections 25056 
Created_tmp_disk_tables 14297 
Created_tmp_tables 47789 
Created_tmp_files 1505 
Delayed_insert_threads 0 
Delayed_writes 0 
Delayed_errors 0 
Flush_commands 1 
Handler_commit 0 
Handler_delete 203212 
Handler_read_first 1634 
Handler_read_key 119597882 
Handler_read_next 385473718 
Handler_read_prev 192056 
Handler_read_rnd 240959228 
Handler_read_rnd_next 179152782 
Handler_rollback 0 
Handler_update 245049083 
Handler_write 233387494 
Key_blocks_used 373653 
Key_read_requests 813389205 
Key_reads 362072 
Key_write_requests 2227702 
Key_writes 1563640 
Max_used_connections 45 
Not_flushed_key_blocks 0 
Not_flushed_delayed_rows 0 
Open_tables 76 
Open_files 125 
Open_streams 0 
Opened_tables 3226 
Questions 13246292 
Qcache_queries_in_cache 13925 
Qcache_inserts 7018431 
Qcache_hits 4839559 
Qcache_lowmem_prunes 4796337 
Qcache_not_cached 544344 
Qcache_free_memory 17765096 
Qcache_free_blocks 5342 
Qcache_total_blocks 33291 
Rpl_status NULL 
Select_full_join 11740 
Select_full_range_join 10 
Select_range 12863 
Select_range_check 0 
Select_scan 117665 
Slave_open_temp_tables 0 
Slave_running OFF 
Slow_launch_threads 0 
Slow_queries 89 
Sort_merge_passes 1046 
Sort_range 257693 
Sort_rows 18264167 
Sort_scan 80339 
Table_locks_immediate 8913947 
Table_locks_waited 27323 
Threads_cached 0 
Threads_created 25055 
Threads_connected 4 
Threads_running 4 
Uptime 58588 

 
 


Thread
HELP! Customer is Pissed! Load on Server is High. Need some ideas for solutions.Shaun Adams2 Sep
Re: HELP! Customer is Pissed! Load on Server is High. Need some ideas for solutions.Jorge Bastos2 Sep
  • Re: HELP! Customer is Pissed! Load on Server is High. Need some ideas for solutions.Daniel da Veiga2 Sep