Re: RAID RAID_CHUNKS speed differences
On Thu, 15 Nov 2001 23:29:27 -0800, Jeremy Zawodny used a few recycled electrons to form: | On Thu, Nov 15, 2001 at 04:14:58PM -0600, Michael Brunson wrote: | > | > Has anyone ran any benchmarks as to what affect different number of | > RAID_CHUNKS has on speed? | | The more chunks, the slower your retrieval speed is likely to be. But | how many chunks do you really need? More than a few? The .MYD right now is right at 2G, so 2 chunks would work. We would need to rebuild again once the combined size hits 4G. I could see the table growing, but probably not more than another 10 million rows in the next year. (It's at 30M now.) | > mysql> select count(*) from names where sld like | > 'foo%'; | > +--+ | > | count(*) | | > +--+ | > |56033 | | > +--+ | > 1 row in set (0.33 sec) | | Is there an index on "sld"? It would seem so. If that's the case, | the test is relatively meaningless. The index will be used to answer | the query, not the raid'ed data files. Yes, sld is the primary key for the table. In fact, sld is the only column in the table. | > mysql> select count(*) from names where sld like | > '%foo%'; | > +--+ | > | count(*) | | > +--+ | > | 151460 | | > +--+ | > 1 row in set (2 min 3.70 sec) | | Same here. | | If you have a lot of "%foo%" queries, you probably want to look at | fulltext indexing. Any ideas why the times would jump up for 3 chunks and back down for 5 and 15? Anyone think this might be more of a memory/swap issue? Thanks for any input, Michael - 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: RAID RAID_CHUNKS speed differences
On Thu, Nov 15, 2001 at 04:14:58PM -0600, Michael Brunson wrote: > > Has anyone ran any benchmarks as to what affect different number of > RAID_CHUNKS has on speed? The more chunks, the slower your retrieval speed is likely to be. But how many chunks do you really need? More than a few? > mysql> select count(*) from names; > +--+ > | count(*) | > +--+ > | 30304640 | > +--+ > 1 row in set (0.00 sec) > > mysql> select count(*) from names where sld like > 'sex%'; > +--+ > | count(*) | > +--+ > |56033 | > +--+ > 1 row in set (0.33 sec) Is there an index on "sld"? It would seem so. If that's the case, the test is relatively meaningless. The index will be used to answer the query, not the raid'ed data files. > mysql> select count(*) from names where sld like > '%sex%'; > +--+ > | count(*) | > +--+ > | 151460 | > +--+ > 1 row in set (2 min 3.70 sec) Same here. If you have a lot of "%foo%" queries, you probably want to look at fulltext indexing. 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 71 days, processed 1,557,459,503 queries (253/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: RAID RAID_CHUNKS speed differences
Here is the same data in a little easy format. Raid Chunks count like %like % % 10.00 sec 0.33 sec 2 min 3.70 sec 30.00 sec 0.39 sec 14 min 59.83 sec 50.00 sec 0.38 sec 6 min 44.92 sec 15 0.00 sec 0.39 sec 6 min 33.72 sec Thanks, Michael -- Michael Brunson 504.473.6643 [EMAIL PROTECTED] ICQ: 83163789 --- Intercosmos Media Group, Inc. --- www.intercosmos.comwww.directnic.com On Thu, 15 Nov 2001 16:14:58 -0600, Michael Brunson <[EMAIL PROTECTED]> wrote: | Has anyone ran any benchmarks as to what affect | different number of RAID_CHUNKS has on speed? | | Also, what affect does the RAID_CHUNKSIZE have? | | Here are a few results I've gotten all with the default | CHUCKSIZE. | | - | No RAID Tables (yes, fewer rows.. hit the 2G limit) | | mysql> select count(*) from names; | +--+ | | count(*) | | +--+ | | 30304640 | | +--+ | 1 row in set (0.00 sec) | | mysql> select count(*) from names where sld like | 'sex%'; | +--+ | | count(*) | | +--+ | |56033 | | +--+ | 1 row in set (0.33 sec) | | mysql> select count(*) from names where sld like | '%sex%'; | +--+ | | count(*) | | +--+ | | 151460 | | +--+ | 1 row in set (2 min 3.70 sec) | | - | RAID_CHUNKS=3 | | mysql> select count(*) from names; | +--+ | | count(*) | | +--+ | | 30352536 | | +--+ | 1 row in set (0.00 sec) | | mysql> select count(*) from names where sld like | 'sex%'; | +--+ | | count(*) | | +--+ | |55989 | | +--+ | 1 row in set (0.39 sec) | | mysql> select count(*) from names where sld like | '%sex%'; | +--+ | | count(*) | | +--+ | | 151422 | | +--+ | 1 row in set (14 min 59.83 sec) | | - | RAID_CHUNKS=5 | | mysql> select count(*) from names; | +--+ | | count(*) | | +--+ | | 30352536 | | +--+ | 1 row in set (0.00 sec) | | mysql> select count(*) from names where sld like | 'sex%'; | +--+ | | count(*) | | +--+ | |55989 | | +--+ | 1 row in set (0.38 sec) | | mysql> select count(*) from names where sld like | '%sex%'; | +--+ | | count(*) | | +--+ | | 151422 | | +--+ | 1 row in set (6 min 44.92 sec) | | | - | RAID_CHUNKS=15 | mysql> select count(*) from names; | +--+ | | count(*) | | +--+ | | 30352536 | | +--+ | 1 row in set (0.00 sec) | | mysql> select count(*) from names where sld like | 'sex%'; | +--+ | | count(*) | | +--+ | |55989 | | +--+ | 1 row in set (0.39 sec) | | | mysql> select count(*) from names where sld like | '%sex%'; | +--+ | | count(*) | | +--+ | | 151422 | | +--+ | 1 row in set (6 min 33.72 sec) | | - | | | This is running on a pretty wimpy box, but all ran | several times so I was able to make sure the server was | fully up. The box is a 750 PIII with 756M of RAM, | running linux and the 3.23.44 version of mysql. | | Any suggestions would be great. - 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
RAID RAID_CHUNKS speed differences
Has anyone ran any benchmarks as to what affect different number of RAID_CHUNKS has on speed? Also, what affect does the RAID_CHUNKSIZE have? Here are a few results I've gotten all with the default CHUCKSIZE. - No RAID Tables (yes, fewer rows.. hit the 2G limit) mysql> select count(*) from names; +--+ | count(*) | +--+ | 30304640 | +--+ 1 row in set (0.00 sec) mysql> select count(*) from names where sld like 'sex%'; +--+ | count(*) | +--+ |56033 | +--+ 1 row in set (0.33 sec) mysql> select count(*) from names where sld like '%sex%'; +--+ | count(*) | +--+ | 151460 | +--+ 1 row in set (2 min 3.70 sec) - RAID_CHUNKS=3 mysql> select count(*) from names; +--+ | count(*) | +--+ | 30352536 | +--+ 1 row in set (0.00 sec) mysql> select count(*) from names where sld like 'sex%'; +--+ | count(*) | +--+ |55989 | +--+ 1 row in set (0.39 sec) mysql> select count(*) from names where sld like '%sex%'; +--+ | count(*) | +--+ | 151422 | +--+ 1 row in set (14 min 59.83 sec) - RAID_CHUNKS=5 mysql> select count(*) from names; +--+ | count(*) | +--+ | 30352536 | +--+ 1 row in set (0.00 sec) mysql> select count(*) from names where sld like 'sex%'; +--+ | count(*) | +--+ |55989 | +--+ 1 row in set (0.38 sec) mysql> select count(*) from names where sld like '%sex%'; +--+ | count(*) | +--+ | 151422 | +--+ 1 row in set (6 min 44.92 sec) - RAID_CHUNKS=15 mysql> select count(*) from names; +--+ | count(*) | +--+ | 30352536 | +--+ 1 row in set (0.00 sec) mysql> select count(*) from names where sld like 'sex%'; +--+ | count(*) | +--+ |55989 | +--+ 1 row in set (0.39 sec) mysql> select count(*) from names where sld like '%sex%'; +--+ | count(*) | +--+ | 151422 | +--+ 1 row in set (6 min 33.72 sec) - This is running on a pretty wimpy box, but all ran several times so I was able to make sure the server was fully up. The box is a 750 PIII with 756M of RAM, running linux and the 3.23.44 version of mysql. Any suggestions would be great. Thanks, Michael -- Michael Brunson 504.473.6643 [EMAIL PROTECTED] ICQ: 83163789 --- Intercosmos Media Group, Inc. --- www.intercosmos.comwww.directnic.com - 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