0

I am trying to speed up some mysql (innodb) with the following characteristics.

I would like some advice about how to remove locking to improve performance. I have absolutely NO concern about non-repeatable reads or the order of writes.

If user A then B try to insert… I don’t care who writes first. If user C queries for recent records… I don’t care if C’s query misses A or B or BOTH… (as long as eventually subsequent queries will get both) If user D queries recent records, while user E,F&G are trying to increment count… I don’t care if the count column is “accurate" for "user D"… (as long as it is eventually accurate in subsequent queries)

Can someone please describe how and where to use nolock to achieve my desired behavior? I don’t want INSERT/UPDATE to slow down SELECT and I don’t care about the SEQUENCE of reads/writes wrt each other.

I don't use this table in any transactions.

My usage profile:

95% of concurrent users, are doing filtered queries on the most recent 2500 records in my “busy" table These users are adding (INSERT) ~200 records per minutes to the “busy” table Doing single-column increments (UPDATE) to 1 record at a time… ~1000 times per minute (~300 records incremented / minute)

“busy” table is not used in any transactions… no inserts/updates are ever “reverted” In subsequent requests, records may be deleted or column may be decremented.

Thanks!

Eric Oemig
  • 487
  • 6
  • 9
  • 1
    Would using an SSD solve most of these problems? Otherwise you're battling the [MVCC model](http://en.wikipedia.org/wiki/Multiversion_concurrency_control). – tadman Sep 23 '14 at 18:40
  • did you try http://stackoverflow.com/questions/917640/any-way-to-select-without-causing-locking-in-mysql ? – Devin Sep 23 '14 at 18:48
  • I thought NOLOCK was a sql server thing? MySql would use the READ UNCOMMITTED transaction isolation level instead. – Joel Coehoorn Sep 23 '14 at 21:54
  • 1
    I will let you know how "read uncommitted" turns out. thank you. – Eric Oemig Sep 25 '14 at 18:07

0 Answers0