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,364
  • 3
  • 47
  • 80
Gary Willoughby
  • 102
  • 2
  • 10
  • 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 '13 at 15:06
  • Only select is necessary (but that is not the problem) and the storage engine is mentioned in the question. ta. – Gary Willoughby Jul 21 '13 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](http://serverfault.com/questions/350458/how-do-you-do-load-testing-and-capacity-planning-for-databases). – Ladadadada Jul 21 '13 at 16:12
  • Is this a data warehousing use case? – HTTP500 Jul 23 '13 at 22:21
  • No, just a collection of logs access via a thin client. – Gary Willoughby Jul 23 '13 at 22:31
  • @Ladadadada [Concurrent inserts](http://dev.mysql.com/doc/refman/5.5/en/concurrent-inserts.html) may help here (but note: “The results of a concurrent INSERT may not be visible immediately”). See also [this question](http://dba.stackexchange.com/q/20745/4594). – Sergey Vlasov Jul 24 '13 at 14:47
  • I already answered this question in the DBA StackExchange : http://dba.stackexchange.com/a/46947/877 – RolandoMySQLDBA Jul 26 '13 at 14:52
  • Please do not cross-post: http://dba.stackexchange.com/questions/46934 – Chris S Jul 26 '13 at 15:13

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
  • 4