On 17/07/2007, at 9:44 PM, K2O2 wrote: > Here again,
OK. There is no table locks showing up here, so that should be okay, so I am suggesting it is coming back to the indexing or the query structure. Is the query being logged at all? Regards Jonathon > > mysql> SHOW STATUS; > +--------------------------------+---------+ > | Variable_name | Value | > +--------------------------------+---------+ > | Aborted_clients | 10 | > | Aborted_connects | 1 | > | Binlog_cache_disk_use | 0 | > | Binlog_cache_use | 0 | > | Bytes_received | 209061 | > | Bytes_sent | 7734641 | > | Com_admin_commands | 59 | > | Com_alter_db | 0 | > | Com_alter_table | 0 | > | Com_analyze | 0 | > | Com_backup_table | 0 | > | Com_begin | 0 | > | Com_change_db | 127 | > | 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 | 0 | > | Com_dealloc_sql | 0 | > | Com_delete | 0 | > | Com_delete_multi | 0 | > | Com_do | 0 | > | Com_drop_db | 0 | > | Com_drop_function | 0 | > | Com_drop_index | 0 | > | Com_drop_table | 0 | > | Com_drop_user | 0 | > | Com_execute_sql | 0 | > | Com_flush | 1 | > | Com_grant | 0 | > | Com_ha_close | 0 | > | Com_ha_open | 0 | > | Com_ha_read | 0 | > | Com_help | 0 | > | Com_insert | 13 | > | Com_insert_select | 0 | > | Com_kill | 0 | > | Com_load | 0 | > | Com_load_master_data | 0 | > | Com_load_master_table | 0 | > | Com_lock_tables | 0 | > | Com_optimize | 0 | > | 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 | 0 | > | 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 | 654 | > | Com_set_option | 0 | > | Com_show_binlog_events | 0 | > | Com_show_binlogs | 0 | > | Com_show_charsets | 2 | > | Com_show_collations | 2 | > | Com_show_column_types | 0 | > | Com_show_create_db | 0 | > | Com_show_create_table | 0 | > | Com_show_databases | 1 | > | Com_show_errors | 0 | > | Com_show_fields | 15 | > | Com_show_grants | 0 | > | Com_show_innodb_status | 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_privileges | 0 | > | Com_show_processlist | 0 | > | Com_show_slave_hosts | 0 | > | Com_show_slave_status | 0 | > | Com_show_status | 12 | > | Com_show_storage_engines | 0 | > | Com_show_tables | 2 | > | Com_show_variables | 6 | > | Com_show_warnings | 0 | > | Com_slave_start | 0 | > | Com_slave_stop | 0 | > | Com_truncate | 0 | > | Com_unlock_tables | 0 | > | Com_update | 71 | > | Com_update_multi | 0 | > | Connections | 84 | > | Created_tmp_disk_tables | 0 | > | Created_tmp_files | 3 | > | Created_tmp_tables | 32 | > | Delayed_errors | 0 | > | Delayed_insert_threads | 0 | > | Delayed_writes | 0 | > | Flush_commands | 1 | > | Handler_commit | 0 | > | Handler_delete | 0 | > | Handler_discover | 0 | > | Handler_read_first | 37 | > | Handler_read_key | 83457 | > | Handler_read_next | 7220515 | > | Handler_read_prev | 0 | > | Handler_read_rnd | 246 | > | Handler_read_rnd_next | 6801364 | > | Handler_rollback | 0 | > | Handler_update | 65 | > | Handler_write | 168 | > | Key_blocks_not_flushed | 0 | > | Key_blocks_unused | 5210 | > | Key_blocks_used | 2039 | > | Key_read_requests | 466639 | > | Key_reads | 2039 | > | Key_write_requests | 215 | > | Key_writes | 107 | > | Max_used_connections | 9 | > | Not_flushed_delayed_rows | 0 | > | Open_files | 73 | > | Open_streams | 0 | > | Open_tables | 43 | > | Opened_tables | 59 | > | Qcache_free_blocks | 0 | > | Qcache_free_memory | 0 | > | Qcache_hits | 0 | > | Qcache_inserts | 0 | > | Qcache_lowmem_prunes | 0 | > | Qcache_not_cached | 0 | > | Qcache_queries_in_cache | 0 | > | Qcache_total_blocks | 0 | > | Questions | 976 | > | Rpl_status | NULL | > | Select_full_join | 1 | > | Select_full_range_join | 0 | > | Select_range | 285 | > | Select_range_check | 0 | > | Select_scan | 34 | > | Slave_open_temp_tables | 0 | > | Slave_running | OFF | > | Slave_retried_transactions | 0 | > | Slow_launch_threads | 0 | > | Slow_queries | 0 | > | Sort_merge_passes | 0 | > | Sort_range | 31 | > | Sort_rows | 217 | > | Sort_scan | 32 | > | Ssl_accept_renegotiates | 0 | > | Ssl_accepts | 0 | > | Ssl_callback_cache_hits | 0 | > | Ssl_cipher | | > | Ssl_cipher_list | | > | Ssl_client_connects | 0 | > | Ssl_connect_renegotiates | 0 | > | Ssl_ctx_verify_depth | 0 | > | Ssl_ctx_verify_mode | 0 | > | Ssl_default_timeout | 0 | > | Ssl_finished_accepts | 0 | > | Ssl_finished_connects | 0 | > | Ssl_session_cache_hits | 0 | > | Ssl_session_cache_misses | 0 | > | Ssl_session_cache_mode | NONE | > | Ssl_session_cache_overflows | 0 | > | Ssl_session_cache_size | 0 | > | Ssl_session_cache_timeouts | 0 | > | Ssl_sessions_reused | 0 | > | Ssl_used_session_cache_entries | 0 | > | Ssl_verify_depth | 0 | > | Ssl_verify_mode | 0 | > | Ssl_version | | > | Table_locks_immediate | 1017 | > | Table_locks_waited | 0 | > | Threads_cached | 0 | > | Threads_connected | 9 | > | Threads_created | 83 | > | Threads_running | 1 | > | Uptime | 2615 | > +--------------------------------+---------+ > 180 rows in set (0.00 sec) > > > I am also reading the article and related topic about indexes. > > regards, > kko!@ > > > > ============================================================== > On 7/17/07, Jonathon Coombes <[EMAIL PROTECTED] > wrote: > On 17/07/2007, at 9:19 PM, K2O2 wrote: > > > Well, > > > > Here is my server spec. HP proliant dl 380 G5 Intel Xeon 3.0GHz. > > 2GB RAM. Running CentOS 4. Hoping that server is quite ok. Please > > check that variables. > > mysql> SHOW VARIABLES; > > Hi K2O2, > > You could increase your key_buffer and join_buffer sizes, but more > importantly at this stage is probably to do a SHOW STATUS and send > that through. > > Regards > Jonathon > > > > +--------------------------------- > > +--------------------------------------------------------+ > > | Variable_name | > > Value | > > +--------------------------------- > > +--------------------------------------------------------+ > > | back_log | > > 50 | > > | basedir | / > > usr/ | > > | bdb_cache_size | > > 8388600 | > > | bdb_home | /var/lib/ > > mysql/ | > > | bdb_log_buffer_size | > > 32768 | > > | bdb_logdir > > | | > > | bdb_max_lock | > > 10000 | > > | bdb_shared_data | > > OFF | > > | bdb_tmpdir | / > > tmp/ | > > | binlog_cache_size | > > 32768 | > > | bulk_insert_buffer_size | > > 8388608 | > > | character_set_client | > > latin1 | > > | character_set_connection | > > latin1 | > > | character_set_database | > > latin1 | > > | character_set_results | > > latin1 | > > | character_set_server | > > latin1 | > > | character_set_system | > > utf8 | > > | character_sets_dir | /usr/share/mysql/ > > charsets/ | > > | collation_connection | > > latin1_swedish_ci | > > | collation_database | > > latin1_swedish_ci | > > | collation_server | > > latin1_swedish_ci | > > | concurrent_insert | > > ON | > > | connect_timeout | > > 5 | > > | datadir | /var/lib/ > > mysql/ | > > | 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 | > > | 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 | > > NO | > > | have_bdb | > > YES | > > | have_blackhole_engine | > > NO | > > | have_compress | > > YES | > > | have_crypt | > > YES | > > | have_csv | > > NO | > > | have_example_engine | > > NO | > > | have_geometry | > > YES | > > | have_innodb | > > YES | > > | have_isam | > > YES | > > | have_ndbcluster | > > NO | > > | have_openssl | > > YES | > > | have_query_cache | > > YES | > > | have_raid | > > NO | > > | have_rtree_keys | > > YES | > > | have_symlink | > > YES | > > | 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_data_file_path | > > ibdata1:10M:autoextend | > > | innodb_data_home_dir > > | | > > | innodb_fast_shutdown | > > ON | > > | 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_table_locks | > > ON | > > | innodb_thread_concurrency | > > 8 | > > | interactive_timeout | > > 28800 | > > | join_buffer_size | > > 131072 | > > | key_buffer_size | > > 8388600 | > > | key_cache_age_threshold | > > 300 | > > | key_cache_block_size | > > 1024 | > > | key_cache_division_limit | > > 100 | > > | language | /usr/share/mysql/ > > english/ | > > | large_files_support | > > ON | > > | license | > > GPL | > > | local_infile | > > ON | > > | locked_in_memory | > > OFF | > > | log | > > OFF | > > | log_bin | > > OFF | > > | log_error > > | | > > | log_slave_updates | > > OFF | > > | log_slow_queries | > > OFF | > > | log_update | > > 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 | > > 1048576 | > > | max_binlog_cache_size | > > 4294967295 | > > | max_binlog_size | > > 1073741824 | > > | max_connect_errors | > > 10 | > > | max_connections | > > 100 | > > | max_delayed_threads | > > 20 | > > | max_error_count | > > 64 | > > | max_heap_table_size | > > 16777216 | > > | max_insert_delayed_threads | > > 20 | > > | max_join_size | > > 4294967295 | > > | max_length_for_sort_data | > > 1024 | > > | max_relay_log_size | > > 0 | > > | max_seeks_for_key | > > 4294967295 | > > | max_sort_length | > > 1024 | > > | max_tmp_tables | > > 32 | > > | max_user_connections | > > 0 | > > | max_write_lock_count | > > 4294967295 | > > | myisam_data_pointer_size | > > 4 | > > | myisam_max_extra_sort_file_size | > > 2147483648 | > > | myisam_max_sort_file_size | > > 2147483647 | > > | myisam_recover_options | > > OFF | > > | myisam_repair_threads | > > 1 | > > | myisam_sort_buffer_size | > > 8388608 | > > | net_buffer_length | > > 16384 | > > | net_read_timeout | > > 30 | > > | net_retry_count | > > 10 | > > | net_write_timeout | > > 60 | > > | new | > > OFF | > > | old_passwords | > > ON | > > | open_files_limit | > > 8192 | > > | pid_file | /var/run/mysqld/ > > mysqld.pid | > > | port | > > 3306 | > > | preload_buffer_size | > > 32768 | > > | protocol_version | > > 10 | > > | query_alloc_block_size | > > 8192 | > > | query_cache_limit | > > 1048576 | > > | query_cache_min_res_unit | > > 4096 | > > | query_cache_size | > > 0 | > > | query_cache_type | > > ON | > > | query_cache_wlock_invalidate | > > OFF | > > | query_prealloc_size | > > 8192 | > > | range_alloc_block_size | > > 2048 | > > | read_buffer_size | > > 131072 | > > | read_only | > > OFF | > > | read_rnd_buffer_size | > > 262144 | > > | relay_log_purge | > > ON | > > | relay_log_space_limit | > > 0 | > > | rpl_recovery_rank | > > 0 | > > | secure_auth | > > OFF | > > | server_id | > > 0 | > > | skip_external_locking | > > ON | > > | skip_networking | > > OFF | > > | skip_show_database | > > OFF | > > | slave_net_timeout | > > 3600 | > > | slave_transaction_retries | > > 0 | > > | slow_launch_time | > > 2 | > > | socket | /var/lib/mysql/ > > mysql.sock | > > | sort_buffer_size | > > 2097144 | > > | sql_mode > > | | > > | storage_engine | > > MyISAM | > > | sql_notes | > > ON | > > | sql_warnings | > > ON | > > | sync_binlog | > > 0 | > > | sync_replication | > > 0 | > > | sync_replication_slave_id | > > 0 | > > | sync_replication_timeout | > > 0 | > > | sync_frm | > > ON | > > | system_time_zone | > > LKT | > > | table_cache | > > 64 | > > | table_type | > > MyISAM | > > | thread_cache_size | > > 0 | > > | thread_stack | > > 196608 | > > | time_format | %H:%i:% > > s | > > | time_zone | > > SYSTEM | > > | tmp_table_size | > > 33554432 | > > | tmpdir > > | | > > | transaction_alloc_block_size | > > 8192 | > > | transaction_prealloc_size | > > 4096 | > > | tx_isolation | REPEATABLE- > > READ | > > | version | > > 4.1.12 | > > | version_bdb | Sleepycat Software: Berkeley DB > > 4.1.24: (May 13, 2005) | > > | version_comment | Source > > distribution | > > | version_compile_machine | > > i686 | > > | version_compile_os | redhat-linux- > > gnu | > > | wait_timeout | > > 28800 | > > +--------------------------------- > > +--------------------------------------------------------+ > > > > Regards, > > kko!@ > > > > > > > > > > > ====================================================================== > > > > > > > > On 7/17/07, Robert Meggle < [EMAIL PROTECTED]> wrote:Hello all, > > > > > > MySQL 4.0 becomes slowing down on 200000 of records on patient > > > > visit. Especially when browsing, data of wards and its waiting > > list > > > > patients. I am trying to tune the mysql Server increasing file > > size > > > > and also buffer. > > > > > > > > Is there any one who solved the problem to make it faster Mysql > > > > server. > > > > > > > > Please suggest me how to improve the speed on Mysql. As I am on > > > > urgent, please reply me ASAP. > > > > > > A slow query on 200000 records is most likely nothing to do with > > your > > > buffers, but is more likely to do with a missing or misconfigured > > > index. Check what indexes are available and being used and see > what > > > can be adjusted there to get the most benefit. > > > > > > Regards > > > Jonathon > > > > Yes and no, but that's true indeed ;-) > > 200.000 records make really no headache for mysql. Fist you should > > check the > > suggestion of Jonathon of index. If that is okay, there is also a > > way of > > digging deeper on your mysql-server: > > http://dev.mysql.com/doc/refman/5.0/en/optimizing-the-server.html > > > > I have here running also mysql on other projects with billions of > > records > > and it's working extremely fast. So mysql does normally not know a > > frontier > > when it's staring of getting slower (if the tables are indexed). > > > > Maybe you could tell little bit more about your server perimeters > > (CPU, > > RAM...) > > > > Robert > > > > > > > > > ---------------------------------------------------------------------- > > --- > > This SF.net email is sponsored by DB2 Express > > Download DB2 Express C - the FREE version of DB2 express and take > > control of your XML. No limits. Just data. Click to get it now. > > http://sourceforge.net/powerbar/db2/ > > _______________________________________________ > > Care2002-developers mailing list > > Care2002-developers@lists.sourceforge.net > > https://lists.sourceforge.net/lists/listinfo/care2002-developers > > > > > ---------------------------------------------------------------------- > > --- > > This SF.net email is sponsored by DB2 Express > > Download DB2 Express C - the FREE version of DB2 express and take > > control of your XML. No limits. Just data. Click to get it now. > > http://sourceforge.net/powerbar/db2/ > > _______________________________________________ > > Care2002-developers mailing list > > Care2002-developers@lists.sourceforge.net > > https://lists.sourceforge.net/lists/listinfo/care2002-developers > > ------------------------------------ > Jonathon Coombes - Cybersite Consulting > MySQL Consulting + Training Partner > Authorised MySQL Training Centre > http://www.cybersite.com.au > > ---------------------------------------------------------------------- > --- > This SF.net email is sponsored by DB2 Express > Download DB2 Express C - the FREE version of DB2 express and take > control of your XML. No limits. Just data. Click to get it now. > http://sourceforge.net/powerbar/db2/ > _______________________________________________ > Care2002-developers mailing list > Care2002-developers@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/care2002-developers > > ---------------------------------------------------------------------- > --- > This SF.net email is sponsored by DB2 Express > Download DB2 Express C - the FREE version of DB2 express and take > control of your XML. No limits. Just data. Click to get it now. > http://sourceforge.net/powerbar/db2/ > _______________________________________________ > Care2002-developers mailing list > Care2002-developers@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/care2002-developers ------------------------------------ Jonathon Coombes - Cybersite Consulting MySQL Consulting + Training Partner Authorised MySQL Training Centre http://www.cybersite.com.au ------------------------------------------------------------------------- This SF.net email is sponsored by DB2 Express Download DB2 Express C - the FREE version of DB2 express and take control of your XML. No limits. Just data. Click to get it now. http://sourceforge.net/powerbar/db2/ _______________________________________________ Care2002-developers mailing list Care2002-developers@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/care2002-developers
