3

I have a MyISAM table partitioned by range with 90 partitions representing the last 90 days. I expect this table to receive somewhere between 500 million to 1 billion records before i start rotating partitions (dropping old ones and creating new ones).

What server configuration options would you put in your .my.conf file to ensure that the fastest possible insertion into this table was possible at all times?

Any other tips would also be appreciated.

EDIT: By 'fastest possible' i mean best possible insert speed and being sustained once it contains 100's of millions of rows.

RolandoMySQLDBA
  • 16,764
Gary Willoughby
  • 102

2 Answers2

0

When ur thinking about faster insert , MYISAM is a good choice , but retrieval will be difficult because entire table will be locked . If you plan for retrieval , MYISAM will not be good option , then we need go for Innodb because it supports row level locking . If your thinking about partition , create your partition based on your business logic. Try to eliminate all related keys apart from partition index

If we go for MYISAM , you should rethink about select queries

Dilip Kumar
  • 1
-1

I guess your application logs data into the database, in one table. To get highest speedup you have to change your data (table) definition. You have to eliminate every index, auto increment, key or unique.

Better you refactor your application. Split your writes. First write into a log file, this is really fast. Second, use a second process that reads your data from log file and writes it into you database. You should split these new two application on different server.

Mirko Ebert
  • 99