ARCHIVING UNDER HEAVY LOAD
Hello! There's a need to design a system (something like online forum) that will be storing huge amount of small messages in database. There must be fast access only to messages for the last 3 days (guess this means that main table with indexes on it must be kept small?), while other messages can be stored in rarely accessed archive (different table?). What's the best practice for implementing archiving in MySQL? In Oracle there's an ability to "switch off" part of the table and then move it to archive in background mode. What about MySQL? Thanks in advance, Alex P.S. This is the second time I post this question, sorry, problems with mail account prevented me from reading the replies. The only one I got was abount using MERGE tables which are still beta. - 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: ARCHIVING UNDER HEAVY LOAD
On Mon, Mar 19, 2001 at 04:06:29PM +0300, Anatoly Chubais wrote: Hello! There's a need to design a system (something like online forum) that will be storing huge amount of small messages in database. There must be fast access only to messages for the last 3 days (guess this means that main table with indexes on it must be kept small?), while other messages can be stored in rarely accessed archive (different table?). What's the best practice for implementing archiving in MySQL? In Oracle there's an ability to "switch off" part of the table and then move it to archive in background mode. What about MySQL? See my message to Nathan with: Message-ID: [EMAIL PROTECTED] and the related thread for the discussion of a similar topic. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 328-7878Fax: (408) 530-5454 Cell: (408) 439-9951 - 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
ARCHIVING UNDER HEAVY LOAD
Hello! There's a need to design a system (something like online forum) that will be storing huge amount of small messages in database. There must be fast access only to messages for the last 3 days (guess this means that main table with indexes on it must be kept small?), while other messages can be stored in rarely accessed archive (different table?). What's the best practice for implementing archiving in MySQL? In Oracle there's an ability to "switch off" part of the table and then move it to archive in background mode. What about MySQL? Thanks in advance, Alex - 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
ARCHIVING UNDER HEAVY LOAD
Hello! There's a need to design a system (something like online forum) that will be storing huge amount of small messages in database. There must be fast access only to messages for the last 3 days (guess this means that main table with indexes on it must be kept small?), while other messages can be stored in rarely accessed archive (different table?). What's the best practice for implementing archiving in MySQL? In Oracle there's an ability to "switch off" part of the table and then move it to archive in background mode. What about MySQL? Thanks in advance, Alex - 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: ARCHIVING UNDER HEAVY LOAD
On Fri, Mar 16, 2001 at 12:17:41PM +0300, wrote: Hello! There's a need to design a system (something like online forum) that will be storing huge amount of small messages in database. There must be fast access only to messages for the last 3 days (guess this means that main table with indexes on it must be kept small?), while other messages can be stored in rarely accessed archive (different table?). What's the best practice for implementing archiving in MySQL? In Oracle there's an ability to "switch off" part of the table and then move it to archive in background mode. What about MySQL? Take a look at MERGE tables in the manual. With some Perl or other scripting, you can create a table for each day which you use to INSERT new messages. Create a MERGE table on the tables for the last three days which you use for your SELECTs. You can keep a separate MERGE table on the older tables. You might want to combine some per-day tables into a larger (per week or month) table before creating a MERGE table on them, because MySQL will have less tables to open then, which makes it faster. You also may want to optimize the older tables to speed things up. Regards, Fred. -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - 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
