Hi, We use Red Hat Linux release 7.1 (Seawolf) Kernel 2.4.3 on a 2-processor i686
Only around 5000 of the rows match out of the 58,000,000. As I understand it, we do have single indexes on each of the columns. Given the replies below, I feel that mysql is not getting the optimisation correct. Do I need to analyse the key distribution or something to give MySQL more up to date information. If so, can I do that without locking the table / taking the database down? When I use USE INDEX(log_id_idx) it still ignores me and does what it wants. Is that right? Thanks again for your help, Robin -----Original Message----- From: Almar van Pel [mailto:[EMAIL PROTECTED]] Sent: 05 February 2002 00:02 To: [EMAIL PROTECTED]; Robin Keech Subject: RE: performance help required. (2G -> 4G Ram upgrade) Hi, Your problem is that your query uses all of the rows in the table. That means that the indexes on the table are incorrect. You should try putting a single index on prev_e3_id and a single index on the other table. It'll probably improve your performance a lot. BTW. Schedule some time for maintenance periodically to optimize these heavy tables. Regards, Almar van Pel -----Oorspronkelijk bericht----- Van: Jeremy Zawodny [mailto:[EMAIL PROTECTED]] Verzonden: dinsdag 5 februari 2002 0:04 Aan: Robin Keech CC: '[EMAIL PROTECTED]' Onderwerp: Re: performance help required. (2G -> 4G Ram upgrade) On Mon, Feb 04, 2002 at 05:41:46PM -0000, Robin Keech wrote: > (Please reply directly, I am only on the digest list, thanks) > > I have an SQL statement that is taking far too long to run, around > 11 minutes (while locking inserts/updates). > > We have installed some more RAM, and I need help with my.cnf and the > troublesome SQL. > > Can anyone suggest some config values to best utilise the extra RAM > I have just fitted? We have gone from 2G to 4G On what operating system? > The table in question is very large, > ie > e3_id_chain.MYD = 3.7G ~ 53 million records > e3_id_chain.MYI = 3.2G > > These are my current settings.... > > # The MySQL server > [mysqld] > port = 3306 > socket = /tmp/mysql.sock > skip-locking > set-variable = key_buffer=512M > set-variable = max_allowed_packet=2M > set-variable = max_connections=500 > set-variable = table_cache=512 > set-variable = sort_buffer=2M > set-variable = record_buffer=2M > set-variable = thread_cache=8 > set-variable = wait_timeout=1800 > # Try number of CPU's*2 for thread_concurrency > set-variable = thread_concurrency=4 > set-variable = myisam_sort_buffer_size=64M > log-bin > low-priority-update > > When doing an SQL select it takes 11 minutes > > mysql> explain SELECT e3_id, prev_e3_id, log_id FROM e3_id_chain WHERE > prev_e3_id IS NOT NULL AND log_id IS NOT NULL; > +-------------+------+---------------------------+------+---------+------+-- > --------+------------+ > | table | type | possible_keys | key | key_len | ref | > rows | Extra | > +-------------+------+---------------------------+------+---------+------+-- > --------+------------+ > | e3_id_chain | ALL | prev_e3_id_idx,log_id_idx | NULL | NULL | NULL | > 58179805 | where used | > +-------------+------+---------------------------+------+---------+------+-- > --------+------------+ > 1 row in set (0.00 sec) > > But its not using the index??? Correct. And that key_buffer won't help much. How many of the rows actually match your query? > Can anyone explain this behaviour, and suggest a solution? MySQL will decide not to use an index in cases when it believes there is a faster way, such as a full table scan. This is often the case when a significant percentage of the rows are likely to match. Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 0 days, processed 22,771,714 queries (385/sec. avg) --------------------------------------------------------------------- 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 --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.313 / Virus Database: 174 - Release Date: 02/01/02 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.313 / Virus Database: 174 - Release Date: 02/01/02 --------------------------------------------------------------------- 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
