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.

8
  • Do you have any other requirements such as being able to SELECT, UPDATE and DELETE from these tables? Have you already decided on the storage engine and if so, which one did you choose?
    – Ladadadada
    Jul 21, 2013 at 15:06
  • Only select is necessary (but that is not the problem) and the storage engine is mentioned in the question. ta. Jul 21, 2013 at 15:33
  • Not sure how I missed that. With MyISAM, whenever a SELECT is running, an INSERT will have to wait until that is finished before it can start. Indexes will be critical in ensuring that your SELECTs finish quickly. The query cache may also help depending on the read/write ratio. All of these have a negative performance impact on INSERTs and would be turned off if you did not need SELECTs. You might want to spend some time with this question.
    – Ladadadada
    Jul 21, 2013 at 16:12
  • Is this a data warehousing use case?
    – HTTP500
    Jul 23, 2013 at 22:21
  • No, just a collection of logs access via a thin client. Jul 23, 2013 at 22:31

2 Answers 2

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

1
  • MySQL 5.6.6 implements partition lock pruning, which eliminates unneeded locks in many cases. Jul 26, 2013 at 17:44
-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.

Not the answer you're looking for? Browse other questions tagged .