Tips on managing very large tables for myISAM:
1) Ensure that the table type is not DYNAMIC but Fixed.
=> Issue the show table status command.
=> Look at Row Format
=> if Row Format != Dynamic the your ok else get rid of varchar type
columns
=> Reason:
Your myISAM table can grow only to 4GB then it will run out
of space even if your file system allows files to grow past 4GB.
2) For selects avoid ranges i.e. SELECT * FROM <BLAH> WHERE column > NOW() -
INTERVAL 30 DAY
==> or increase range_alloc field in my.cnf
3) For pruning as described below, in a maintenance window run optimize
table or
myisamchk -r -S -a <yourtable>.MYI to get rid of deleted blocks. This will
help keep your query speed consistent and disk utilization lower.
4) Ensure that mysql_safe is off so you can get the benefits of simulatenous
reads or simulatenous writes.
5) add --low-priority-update to allow writes to happen in batches after
reads have finished.
> -----Original Message-----
> From: Chad Attermann [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, March 30, 2004 9:42 AM
> To: [EMAIL PROTECTED]
> Subject: Managing Very Large Tables
>
> Hello,
>
> I am trying to determine the best way to manage very large (MyISAM)
> tables, ensuring that they can be queried in reasonable amounts of time.
> One table in particular has over 18 million records (8GB data) and is
> growing by more than 150K records per day, and that rate is increasing.
> Besides the obvious things like better hardware and load-balancing across
> multiple replicating databases, I am trying to determine how to keep these
> data sets optimized for fastest queries. In my particular situation, the
> most recent data is queried most often, and data over 30-45 days old is
> not queried much at all but still must remain accessible. Each record has
> an integer time column that is indexed for querying over periods of time.
> Currently I run a script regularly that moves records older than 45 days
> from tables in the main database into identical tables in another
> (archive) database running in the same server process. This seems to
> speed up the tables in the main database, but I realize that deleting
> records leaves holes in the tables, and that this slows inserts as well as
> makes it impossible to read and write concurrently from these tables. My
> question is, is it better to keep all of the data in the original tables
> to avoid holes, or is 'archiving' records to another database a wise
> approach? How much does the size of a table really affect performance
> when querying the more recent data? If archiving is reasonable, is there
> a way to optimize the tables to get rid of the holes without
> siginificantly impacting ongoing activity on these tables?
>
> Thanks for your time!
>
> Chad Attermann
> [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
