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
