CODEWITHSUNDEEP
mysqlsqlprimary-key
Or Weinberger
Or Weinberger

Reputation: 7472

PRIMARY set as VARCHAR

I have a table where the primary key is set to VARCHAR(14).

When I get a high volume of visitors, the server's I/O rate goes crazy and when using iotop I can see the mysql is the problem.

As for usage pattern, for every visit the script will select a single line by the varchar key. There are no joins or any other complex queries.

Is this crazy I/O rate caused by the VARCHAR key? Should I change it to INT(11)?

Thanks,

EDIT: this image might help: https://i.sstatic.net/vOVUB.png

Upvotes: 2

Views: 149

Answers (4)

bobwienholt
bobwienholt

Reputation: 17610

Are you doing something like this:

SELECT *
FROM table
WHERE varchar_key = 467487644;

If so, mysql will attempt to convert the value of varchar_key in every row to an integer before comparing. It will do a full table scan every time.

Changing to:

SELECT *
FROM table
WHERE varchar_key = '467487644';

Will prevent that from happening and the primary key will be used properly.

You should check the EXPLAIN output, if you are just looking up one row in one table by the primary key, the explain out put should have "PRIMARY" in the "key" column, and "rows" should be 1. Otherwise, something is not right.

Upvotes: 0

Maxim Krizhanovsky
Maxim Krizhanovsky

Reputation: 26699

In InnoDB, the data in the table is ordered on the disk in the same order as the primary key (it's clustered on the primary key). Since varchar keys are not sequential, this means that your inserts are not sequential as well, which causes random-write, which is heavy I/O operation. So first check if the high I/O is not caused by writes, not reads. Although reads can cause high I/O as well - if the IDs are unique character strings, it's hard for MySQL to properly load the right subset of IDs in the buffer pool (as it always load a whole page and not single record), and if the buffer pool is small, it have to load a new page in the buffer pool virtually for every request, which means it have to flush the dirty pages, etc., which actually causes both read and write load to the I/O subsystem. So, if you can't change the type of the primary key, check your innodb_buffer_pool_size.

If you need further assistance, please post the innodb_buffer_pool_size value, as well as the exact structure of the table, it's size in GB / number of records, and queries used with the table.

Upvotes: 1

MikeyKennethR
MikeyKennethR

Reputation: 608

Keep your key as the logical key - then use a INT(?) as physical key. The physical key should be randomly spread, hence the risk for deadlocks will decrease. Designing a database should include these classical analysis prior to the grand opening - once you are up and running, this becomes a stressfull problem. As mkk suggests - the index can be stressed, but data in clusters is the real problem. indexes tends to be in memory. I always use timestamps reversed when doing physical keys, when I/O per/sec is high enough - in order to spread them in the cluster.

Upvotes: 0

Michael Berkowski
Michael Berkowski

Reputation: 270637

Even if you changed the PK column to be an auto-increment INT, assuming your VARCHAR column contains character data you would still need to access its index to select your row, would you not? There would not be a benefit to creating an INT PK column.

On the other hand, if your PK column contains integer data in a VARCHAR column you should absolutely change it to INT

Upvotes: 0

Related Questions