Mysql – Should I use a storage engine other than MyISAM to optimise these tables or should I get better disks

database-tuningmyisamMySQL

There's a production database I'm working on, whose 4 largest tables contain between 4 million and 10 million rows each and about 15 fields each, with indexes on different field types (numbers, varchar and text). The total index_length is 2.2GB and the data_length is 6GB. All these tables use MyISAM and they have a read/write ratio of 66/33.

Here's the output from /etc/init.d/mysql status during idle time:

Server version          5.1.37-1ubuntu5
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/run/mysqld/mysqld.sock
...

Threads: 27  Questions: 5430994  Slow queries: 59  Opens: 298  Flush tables: 1  Open tables: 128  Queries per second avg: 182.695

The number of queries per second is at about 300 during peak time.

In the last couple of weeks, the database has become so slow that sometimes queries are locking for 2+ minutes. So, I increased key_buffer_size to 4GB (I have 8GB on the machine). The lock times have decreased. Yet, it's still unbearable during peak hours, especially when the reads/writes ratio approaches 50/50.

The server is running on a virtual environment so I/O is not great and most of the select queries do a ORDER BY <some_indexed_field> on results sets with an average of 50k rows.

I've successfully tuned this database before when it was still smaller but now I'm stuck.

Update:
Disk utilization easily reaches 100% during the day.

Best Answer

You have to use InnoDB. Here is why :

The major advantages of InnoDB over MyISAM

InnoDB caches data and index pages, whereas MyISAM only caches index pages.
InnoDB is designed for ACID-compliant transactions
InnoDB is designed for row-level locking, MyISAM uses table-level locking.
InnoDB is designed for Multiversion Concurrency Control, critical for transaction isolation
InnoDB can be tuned to engage multiple cores

MyISAM is great in a read-heavy environment. Although you can configre private caches on a per-table basis, MyISAM never caches data.

InnoDB is your best bet !!!

CAVEAT

Get enough RAM on the DB Server to cache everything in the InnoDB Buffer Pool (75% of Installed RAM).

Related Solutions

Mysql – Do you still use MyISAM or prefer Aria storage engine

It is very interesting this question would come up because a similar question was asked back in January 2011 ( When is the right time to use MariaDB instead of MySQL, and Why? ). Giving thought to that 16-month-old question and how I answered it in April 2011, here are my answers to your immediate questions:

Why still using MyISAM ?

MyISAM still has features that are unique and useful for small datasets and read slaves in HA setups

Deadlocking is impossible in low-write, heavy-read environments
- MySQL MyISAM Locking question
- Benefits of table level locking
Converting to Fixed Row Format creates bigger tables but increases query performance in terms of
- character string comparison/compression : What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?
- read/write speed : Which is faster, InnoDB or MyISAM?
Full Faith and Confidence
- InnoDB has become the de facto standard of Transactional Storage Engines for MySQL. XtraDB is being accepted and used as well among the Percona Server community. Full faith and confidence seems to rest with InnoDB over XtraDB as present. This could easily change over time as more recognize XtraDB's superior features.
- The same equally applies to MyISAM vs Aria. Full faith and confidence currently rests with MyISAM. An example of those who do not have Full faith and confidence are Drizzle users (Drizzle have abandoned MyISAM as an Optional Storage Engine and relegated it to temp table use only)

To changes storage engine from MyISAM to Maria should it be a problem? (lose index or something)

If you mysqldump the data and the schema into two separate files, you can always import data into table with either the MyISAM or Aria storage engines. Just keep around the data only mysqldump to be sure.

Mysql – What storage engine should I use for this MySQL table

When it comes to InnoDB, you should never use big primary keys. Why?

InnoDB has an internal Clustered Index called gen_clust_index. When you are doing INSERTs into an InnoDB table with just a Primary Key, you are building up the gen_clust_index. I would expect this table to bloat very quickly. Your goal should be to record the URL and index it in such a way that the primary is not large and causing potentially make for slow INSERTs. Given the size of the field, I wouldn't even create a secondary index

This table could easily be a MyISAM table. However, in a high-traffic environment, each INSERT into a MyISAM table would produce a full table lock. That's an automatic bottleneck just to record a URL. So, MyISAM is out. Making the table InnoDB allows a high-traffic environment the freedom to INSERT without locking.

I would like to suggest the following approach:

CREATE TABLE url_trail
(
    id int not null auto_increment,
    url_md5 VARCHAR(32),
    url VARCHAR(255),
    PRIMARY KEY (id),
    KEY url_md5 (url_md5),
    KEY url (url (50))
) ENGINE=InnoDB;

Using this type of layout will allow you to index the MD5 o the URL (32 btyes) rather than the 200+ character URL. There is also an optional index on the first 50 characters of the URL. You could use either index or both.

You will have to do INSERTs like this:

INSERT INTO url_trail (url,url_md5) VALUES ('www.yahoo.com',MD5('www.yahoo.com'));

You will have to do searches like this:

SELECT * FROM url_trail WHERE url_md5 = MD5('www.yahoo.com');
SELECT * FROM url_trail WHERE url = 'www.yahoo.com';

The essential thing overall is to not to have bloated primary keys.

Best Answer

Related Solutions

Mysql – Do you still use MyISAM or prefer Aria storage engine

Mysql – What storage engine should I use for this MySQL table

Related Question