Optimizing MySQL for Read-Only?

Question

We have 14 GB worth of CSV's which total 138 million rows. I imported this into a MySQL table first with InnoDB, and then tried again with MyISAM. In both cases, a simple SELECT on primary key (which is just an autoincremented int) took 6-7 seconds, though MyISAM sometimes was a little faster at 5-6 seconds.

We only need to write the data once, and I've been using mysqlimport. With that in mind, how can I improve the query speed?

...it's worth nothing that we have 2 gigs of RAM and everything is one table (and it has to stay that way due to the nature of the queries). Is that the best performance I can expect given the hardware? Or is there something else I should try, like compression? Or really, I need a lot more RAM?

score 3 · Accepted Answer · edited Jun 15 '20 at 09:05

MyISAM ROW FORMAT

If the table is a lot of VARCHAR fields, I would love to suggest the following

For every MyISAM table you load, do this beforehand

ALTER TABLE mytable ROW_FORMAT=Fixed;

This will increase query performance up to 15-30% without changing anything else. The side effect of doing this is having bigger tables and bigger indexes.

I have earlier posts on this:

May 10, 2011 : What is the performance impact of using CHAR vs VARCHAR on a fixed-size field? (TRADEOFF #2)
Aug 12, 2011 : Which DBMS is good for super-fast reads and a simple data structure? (Par 3)
Sep 20, 2011 : Best of MyISAM and InnoDB
Jan 03, 2012 : Optimized my.cnf for high-end and busy server (Under the heading Replication)

INCREASED RAM

If you have many indexes, you may want to increase RAM on the DB Server and then attempt to preload all MyISAM index pages into the key buffer.

First compute how big a key buffer you would like (in GigaBytes)

SELECT CEILIING(SUM(index_length)/POWER(1024,3)) SuggestedKeyBufferSize
FROM information_schema.tables
WHERE engine='MyISAM' AND table_schema NOT
IN ('information_schema','performance_schema','mysql');

If SuggestedKeyBufferSize can be set up for the DB Server, then do the following (example 4G)

STEP 01) Set this in /etc/my.cnf

[mysqld]
key_buffer_size=4G

STEP 02) preload all MyISAM indexes

See my post on how to do this : MySQL warm procedure

Optimizing MySQL for Read-Only?

1 Answers1

MyISAM ROW FORMAT

INCREASED RAM