Can anyone shed a little light on this for me? I've got a mysql installation that has been running for over 2 years now. As the database has grown to over 250Meg (not TOO large at all) things have kind of slowed down. We're running this on a RH box with mysql v3.23.32. So, we bought a new server. Upgraded from a 433Mhz with 256M RAM and an old 10G IDE drive to a 1Ghz with 512M RAM and nice fast drives. Got everything moved over and running. While putting this new box together I used a sample of our live database (about 20Meg) and things looked great. MUCH faster than it was when I first developed the program on the old box and had only about 1Meg of dummy data. Now that I've moved the whole live database over, our queries are taking about 20% LONGER than before. How could that be? This is at night when there is nothing else hitting the box. No locks, no 10 other people running reports against it at the same time, nothing. During a query, mysqld is showing it's using 99%+ CPU time. Throwing hardware at a problem usually works! ;) You most certainly don't think it's going to make the problem worse! I've tried some of the startup option optimizations that are in the documentation. They really don't seem to have much of an impact at all (+/- 2-4%). I'm connecting locally to mysql, so networking issues shouldn't play a part in it. The database consists of about 35 tables, most of them fairly small with about 10-20 columns by 500-750 rows in each. The bulk of the data is in 2 tables each with about 10 columns by 40,000 rows. The largest part of this being text fields. Tables are MyISAM. Programs written in perl using DBI/DBD, but it's not a perl issue as queries take about the same amount of time from the console. The queries aren't that intensive, and I have indexes on all the important bits. Anyway, I'm in a position where I have to explain to the money guys why after spending the money and time to do this that they have to wait longer for their reports to spit out. Any ideas or suggestions would be greatly appreciated, --James back_log current value: 50 binlog_cache_size current value: 32768 connect_timeout current value: 5 delayed_insert_timeout current value: 300 delayed_insert_limit current value: 100 delayed_queue_size current value: 1000 flush_time current value: 0 interactive_timeout current value: 28800 join_buffer_size current value: 131072 key_buffer_size current value: 8388600 long_query_time current value: 10 lower_case_table_names current value: 0 max_allowed_packet current value: 1048576 max_binlog_cache_size current value: 4294967295 max_connections current value: 100 max_connect_errors current value: 10 max_delayed_threads current value: 20 max_heap_table_size current value: 16777216 max_join_size current value: 4294967295 max_sort_length current value: 1024 max_tmp_tables current value: 32 max_write_lock_count current value: 4294967295 myisam_sort_buffer_size current value: 8388608 net_buffer_length current value: 16384 net_retry_count current value: 10 net_read_timeout current value: 30 net_write_timeout current value: 60 open_files_limit current value: 0 query_buffer_size current value: 0 record_buffer current value: 131072 slow_launch_time current value: 2 sort_buffer current value: 2097144 table_cache current value: 64 thread_concurrency current value: 10 thread_cache_size current value: 0 tmp_table_size current value: 1048576 thread_stack current value: 65536 wait_timeout current value: 28800 --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
