Mysql – Optimizing MySQL for Read-Only

hardwareinnodbmyisamMySQLoptimization

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?

Best Answer

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

Related Solutions

Mysql – How to shard 14 gb of data for archival database

Any full featured database will be able to handle this with ease. My preference would be for Postgres, however SQL server, Oracle and MySQL are all valid options.

When designing the tables you need to consider how you will read the data so that you can optimize read performance. There are a couple of things to look at.

1> Appropriate indexes for the queries you are going to run.
2> The use of Partitioning (read the appropriate manuals). You would use partitioning in this case perhaps by having a partition per state assuming the queries that need to perform affect only one state's data at a time.
3> Disk and network. These can often be the cause of slow reads so make sure that they are as fast as you can afford. Consider if you need to have a RAID type replication or can live without this. i.e.: do you need 100% up-time [RAID] or can you afford to have some downtime [single disk and backup] if there is a disk failure.
4> If you have a choice of storage methods with the chosen DB, choose the most appropriate t the task at hand. Again refer to the DB's documentation for advice.
5> Maintenance plan. Ensure you regularly rebuild fragmented indexes and tables, this will help prevent slowing performance over time.

When it comes to RAM, get as much as you can fit into the server you have.

Mysql – What’s the optimal server configuration for fastest inserting in a MySql database in this scenario

There are two major things you can change. One involves the table and one involves a config setting.

TABLE CHANGE

You can increase SELECT and INSERT speed by changing the MyISAM table's row format to FIXED. In order to do do, you would run

ALTER TABLE mydb.mytable ROW_FORMAT=Fixed;

There is a drawback to this: This could potentially increase the table size 80-100%

I have discussed changing MyISAM row format before

May 10, 2011 : What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?
Aug 12, 2011 : Which DBMS is good for super-fast reads and a simple data structure?
Jul 06, 2012 : MySQL table with 100,000 records queried often
Sep 26, 2012 : Choosing MyISAM over InnoDB for these project requirements; and long term options

CONFIG SETTING

MyISAM supports a special protocol called a concurrent insert. It works by appending new rows to a MyISAM as long as there are no gaps as a result of DELETEs.

Here is some good news: you can disable the gap check for concurrent insert

[mysqld]
concurrent_insert = 2

According to the MySQL Documentation on concurrent_insert, here is what happens for value 2:

Enables concurrent inserts for all MyISAM tables, even those that have holes. For a table with a hole, new rows are inserted at the end of the table if it is in use by another thread. Otherwise, MySQL acquires a normal write lock and inserts the row into the hole.

If the table is being heavily read, new INSERTs just get appended. When you are the only DB connection with a file handle to the table, then a normal gap check is done.

See the MySQL Documentation on Concurrent Inserts

EPILOGUE

These two changes combined may produce surprising performance results. Why? Using ROW_FORMAT=Dynamic would trigger a search for a gap large enough to hold a new row. If the table has ROW_FORMAT=Fixed, all gaps are the same size. A new row insertion would be as quick as possible because the first gap row searched is used immediately for the insertion.