Re: indexing = NP complete??
On Fri, 15 Jun 2001, Jeremy Zawodny wrote: On Fri, Jun 15, 2001 at 12:53:04PM -0600, Chris Bolt wrote: Is this machine basically dedicated to MySQL? If so, I'd try bumping up the record_buffer to 16 or 32MB and see what that does. I've got it set at 32MB on a server with 1GB of RAM. I'm looking at my-huge.cnf included with mysql 3.23.39 and it has a record_buffer of 1M. Are the example configs included with mysql conservative, and if so, do you have any other suggestions for configurations with lots of ram (768MB in my case)? I don't know that they're conservative. They are good starting points which are optimized for the general case. Most folks aren't going to do lots of heavy indexing as much as they are SELECT queries (hopefully on indexed columns). I don't have any other suggestions at the moment... Jeremy Just for kicks, I bumped my record_buffer up to 64MB; I don't know how much of an effect this will have though. Do you know what buffers are used in indexing? Also, is it faster to insert a load of data, then index, or index as it is being inserted? Cheers, -robin -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878Fax: (408) 349-5454Cell: (408) 439-9951 MySQL 3.23.29: up 1 days, processed 8,464,357 queries (85/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 - 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
RE: indexing = NP complete??
Is this machine basically dedicated to MySQL? If so, I'd try bumping up the record_buffer to 16 or 32MB and see what that does. I've got it set at 32MB on a server with 1GB of RAM. I'm looking at my-huge.cnf included with mysql 3.23.39 and it has a record_buffer of 1M. Are the example configs included with mysql conservative, and if so, do you have any other suggestions for configurations with lots of ram (768MB in my case)? - 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
Re: indexing = NP complete??
On Fri, Jun 15, 2001 at 12:53:04PM -0600, Chris Bolt wrote: Is this machine basically dedicated to MySQL? If so, I'd try bumping up the record_buffer to 16 or 32MB and see what that does. I've got it set at 32MB on a server with 1GB of RAM. I'm looking at my-huge.cnf included with mysql 3.23.39 and it has a record_buffer of 1M. Are the example configs included with mysql conservative, and if so, do you have any other suggestions for configurations with lots of ram (768MB in my case)? I don't know that they're conservative. They are good starting points which are optimized for the general case. Most folks aren't going to do lots of heavy indexing as much as they are SELECT queries (hopefully on indexed columns). I don't have any other suggestions at the moment... Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878Fax: (408) 349-5454Cell: (408) 439-9951 MySQL 3.23.29: up 1 days, processed 8,464,357 queries (85/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
Re: indexing = NP complete??
On Thu, Jun 14, 2001 at 11:31:32AM -0400, Robin Senior wrote: Just kidding! But it seems that way... Indexing a 7 million row VARCHAR(255) table took ~12 minutes on my Athlon 1.33Ghz w/512MB. Indexing a 77 million row VARCHAR(255) table has taken 9 DAYS, and still hasn't finished. Obviously this isn't a linearly scaling operation, but it is seeming dang near exponential at the moment. Does anyone have any experience with this? Should it be taking this long? MySQL rips through the first few million rows, but after that it seems to slow to a near halt. I have a few comments and questions: (1) Is this a unique index? If not, consider doing a partial index on the column. Index the first 32 characters or so. I'll save a lot of disk I/O, memory, and CPU time but still yeild pretty good results (depending on your data, of course). (2) What are your key_buffer and record_buffer set to? I believe that when MySQL is doing serious scanning (as needed in building an index), a larger record_buffer will help. (3) Try running `vmstat 5' on the box for a bit. Is the CPU pegged? Is it I/O bound? Is it swapping at all? Please don't tell me this will take twice the age of the universe to finish... How about 1/2 the age of the universe. But since there's still a fair amount of debate over the true age of the universe, I probably haven't helped much, have I? :-) Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878Fax: (408) 349-5454Cell: (408) 439-9951 MySQL 3.23.29: up 19 days, processed 136,385,255 queries (79/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
Re: indexing = NP complete??
On Thu, 14 Jun 2001, Jeremy Zawodny wrote: I have a few comments and questions: (1) Is this a unique index? If not, consider doing a partial index on the column. Index the first 32 characters or so. I'll save a lot of disk I/O, memory, and CPU time but still yeild pretty good results (depending on your data, of course). No, it isn't a unique index. Most of the entries are 32 chars, but I used a varchar(255) to be safe, as it only occupies the number of bytes required to record the string, plus 1 byte to record the length. There are 77million records, and I can't guarantee that they are all 32. Will the index know to only index the required number of chars, or will it do 255? What if I had used a TEXT datatype? (2) What are your key_buffer and record_buffer set to? I believe that when MySQL is doing serious scanning (as needed in building an index), a larger record_buffer will help. Unfortunately, my key_buffer is only adjusted to 64M, and the record_buffer is set at a piddling 131072... I only got into configuring mysql after I began the indexing. The other box (same specs) that I'm running is set with: set-variable=key_buffer=128M set-variable=table_cache=512 set-variable=sort_buffer=8M set-variable=record_buffer=2M I'm trying to index on that machine at the same time, by indexing as I insert. I'm guessing 2M is still far too small? Can you recommend a better config? (3) Try running `vmstat 5' on the box for a bit. Is the CPU pegged? Is it I/O bound? Is it swapping at all? Forgot to mention I'm running Win2K, don't have vmstat, sorry. Perfmon is shite, too. Please don't tell me this will take twice the age of the universe to finish... How about 1/2 the age of the universe. But since there's still a fair amount of debate over the true age of the universe, I probably haven't helped much, have I? :-) Okay, 1/2 the age is fine. Twice the age would just be silly. Thanks a ton, -robin Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878Fax: (408) 349-5454Cell: (408) 439-9951 MySQL 3.23.29: up 19 days, processed 136,385,255 queries (79/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
Re: indexing = NP complete??
On Thu, Jun 14, 2001 at 12:43:41PM -0400, Robin Senior wrote: On Thu, 14 Jun 2001, Jeremy Zawodny wrote: I have a few comments and questions: (1) Is this a unique index? If not, consider doing a partial index on the column. Index the first 32 characters or so. I'll save a lot of disk I/O, memory, and CPU time but still yeild pretty good results (depending on your data, of course). No, it isn't a unique index. Most of the entries are 32 chars, but I used a varchar(255) to be safe, as it only occupies the number of bytes required to record the string, plus 1 byte to record the length. Ah, okay. Then my suggestion won't help nearly as much as I thought. There are 77million records, and I can't guarantee that they are all 32. Will the index know to only index the required number of chars, or will it do 255? What if I had used a TEXT datatype? It'll do the Right Thing. :-) (2) What are your key_buffer and record_buffer set to? I believe that when MySQL is doing serious scanning (as needed in building an index), a larger record_buffer will help. Unfortunately, my key_buffer is only adjusted to 64M, and the record_buffer is set at a piddling 131072... I only got into configuring mysql after I began the indexing. The other box (same specs) that I'm running is set with: set-variable=key_buffer=128M set-variable=table_cache=512 set-variable=sort_buffer=8M set-variable=record_buffer=2M I'm trying to index on that machine at the same time, by indexing as I insert. I'm guessing 2M is still far too small? Can you recommend a better config? Is this machine basically dedicated to MySQL? If so, I'd try bumping up the record_buffer to 16 or 32MB and see what that does. I've got it set at 32MB on a server with 1GB of RAM. (3) Try running `vmstat 5' on the box for a bit. Is the CPU pegged? Is it I/O bound? Is it swapping at all? Forgot to mention I'm running Win2K, don't have vmstat, sorry. Perfmon is shite, too. Heh, okay. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878Fax: (408) 349-5454Cell: (408) 439-9951 MySQL 3.23.29: up 0 days, processed 2,801,545 queries (75/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
