HELP! Customer is Pissed! Load on Server is High. Need some ideas for solutions.

------=_NextPart_000_0047_01C5AF20.7F67CAD0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

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




------=_NextPart_000_0047_01C5AF20.7F67CAD0--

Re: HELP! Customer is Pissed! Load on Server is High. Need some ideas for solutions.

Try installing the mysql 4.1.x

sinse it is running from localhost (webpages) but, even that, try to install
the service to ignore reverse dns lookups (mysqld-nt --install
MySQL --skip-name-resolve) if you install mysql 4.1.x
I have some problems with the load of mysqld-nt on the server, with only 1
user in some query, with them joined with where's (t1.x=t2.z), i have about
8 tables in one query, what i did to improve perfomance was to use
subqueries, but they are only available in 4.1.x.
Let me know if you had some sucess

Jorge Bastos


----- Original Message -----
From: "Shaun Adams"
To:
Sent: Thursday, September 01, 2005 11:10 PM
Subject: HELP! Customer is Pissed! Load on Server is High. Need some ideas
for solutions.


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



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: HELP! Customer is Pissed! Load on Server is High. Need some ideas for solutions.

Your version has some big issues, try upgrading it, also, using the
variables to setup memory, index, sort and buffer use would be good, I
don't know exact values for your needs, but the MySQL Manual has some
tips on this, and also describes each variable use.

Jorge's suggestion is a good one, if your server don't need to resolve
the database server's name, you'll reduce time needed (assuming you
have the web server and mysql running at the same machine).

Check logs for hanging clients, it was a major issue before I upgrade
to 4.1.11. 4.1.7 was slow on queries with sort and subqueries, and
even was hanging every time a subquerie had an ORDER BY and LIMIT
clause. Try logging warnings (for a while, it takes memory and
resources too) and check for connections killed due to hanging, reduce
the wait_timeout and interactive_timeout (whatever you use) to check
it.

Check your tables frequently (when there are less users on it) for
errors and optimize it. NTFS and FAT fses are likely to corrupt files
on high demands systems (IMHO), so, a chkdsk often is good (but that
demands a reboot).

Are you indexing your tables? Optimize index use and query_cache and
you'll get better performance, also, full text searches were killing
our server, it MUST be reduced, the LIKE clause is good, but it takes
resources as hell.

Any suggestion that would give you more information on demand will
slow your server (logging, checking clients, checking tables), so, be
careful, its a production system and customers hate when you say:
"Sorry, its slow because I'm trying to make it faster". *lol*

Good luck,

--=20
Daniel da Veiga
Computer Operator - RS - Brazil
-----BEGIN GEEK CODE BLOCK-----
Version: 3.1
GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V-
PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++
------END GEEK CODE BLOCK------

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org