What about making the table INNODB? If you make INNODB's buffer large enough, the high activity data/index blocks are retained in memory. INNODB's non blocking read and single statement transaction default should make the "transaction overhead" minimal and INNODB doesn't have any of the HEAP table restrictions. Caching the data closer to the application layer normally gives better performance than OS level cache or Ram disk.
-----Original Message----- From: Matt W [mailto:[EMAIL PROTECTED] Sent: Sunday, November 09, 2003 4:55 PM To: [EMAIL PROTECTED] Subject: Re: Strategies for optimizing a read-only table Hi Jeremy, Sorry, it seems like I'm saying this a lot lately. Is it not true that if the whole table will fit in [free] RAM, that the OS will cache the file data and there is no need for a RAM disk. I don't really see how performance would be any different than using a RAM disk. Either way, you will still have the overhead of the filesystem calls, even if data isn't actually read from disk, unlike with a HEAP table. Jonathan, I think a packed table would actually make things slower once the data is cached (overhead of unpacking the data). It's just faster when the data has to be *physically* read from disk, since the data is smaller. If disk space isn't an issue and the table doesn't have any TEXT/BLOB columns, make the rows fixed length if they aren't (change VARCHAR > CHAR). This will give a small performance improvement. Also, if you don't have any TEXT/BLOB columns, you could load the data into a HEAP table, which *may* make reads *slightly* faster -- depending on your queries. Remember, with HEAP tables, indexes can't be used in all cases that MyISAM can. You can only search on indexes with =, <=>, IS NULL, and IN (); no range searches with <, >, BETWEEN, etc.; and you can only use the full index length, no prefixes. However, none of this is true in 4.1+, since you can have BTREE indexes with HEAP tables, not just HASH. :-) But you know what should actually be the best thing for your read-only table? MySQL 4's query cache! :-) Have you thought about this? Or do your queries differ too much that the cache can't be used? Hope this helps. Matt ----- Original Message ----- From: "Jeremy Zawodny" Sent: Saturday, November 08, 2003 11:48 PM Subject: Re: Strategies for optimizing a read-only table > On Tue, Nov 04, 2003 at 08:45:08PM -0500, Jonathan Terhorst wrote: > > > > I could have sworn I posted this once before, but apparently it got > > lost somewhere. Apologies if you're seeing this twice: > > > > I'm wondering what I can do with MySQL to optimize reads (SELECTs) > > on a read-only table where data will never be INSERTed or > > UPDATEd. Okay, that's not entirely correct--the database will be > > rebuilt every night but it's small (~20,000 rows) and all the > > writing will take place at once, when the DB is offline to users. In > > contrast we anticipate read activity on the DB to be high, making it > > worth putting some thought into this. So far my only thoughts have > > been a) myisampack and b) to index every single column that our > > application searches on, since the calculations needed to build said > > indices can be performed once and forgotten. (Disk space isn't > > really an issue but myisampack is said to speed up individual row > > retrieval.) > > > > Any other ideas? I've searched for a way to manually mark MySQL > > tables read-only, but to no avail. Thanks, > > Will the whole table fit comfortable in RAM? If so, you could store > it in a ram disk to prevent disk I/O from ever getting in the way. > > Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
