From: Jorge Bastos Date: September 1 2005 10:45pm Subject: Re: HELP! Customer is Pissed! Load on Server is High. Need some ideas for solutions. List-Archive: http://lists.mysql.com/win32/17476 Message-Id: <002201c5af46$e49c7ec0$0201a8c0@pcjorge> MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit 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 > > > >