MySQL InnoDB: Index Locks - Advice Needed

I have an application that is sending function calls to my mySQL database. Once sent, the function does what it needs (which includes single line selects, INSERT and UPDATE by index).

Right now I am able to do about a million mySQL calls a day. These mySQL function calls are broken down into my app's actual "calls" which can be anywhere from 1 to 20 different mySQL function calls. My app is getting about 120,000 "app function calls" a day.

I have 12 gigs of RAM, so I boosted my InnoDB in-memory RAM from 2 gigs to 8 gigs and that did not appear to increase. Next I figured, if 1 thread is getting that much, adding threads would increase it. So I did 开发者_运维问答this, and now I am seeing tons and tons of Index Locks errors.

I know that MySQL says to simply resend the request, which works, but I am not seeing much increase in speed. I am basically maxing at 180,000 "app function calls" on my end regardless of threads, when single threaded was about 120,000. This is because once I have about 2-3 threads, its constant thread locking errors.

Now each of my tables has tons of indexes for search efficiency, since I have millions of rows. However, I plan to replicate this database so a new instance is doing most of the searches on a SLAVE, while the main data is maintained on my MASTER (which is all the updates / inserts). Because of this I was thinking maybe removing all indexes on the MASTER besides the primary key and unique keys would lower this? Then I could add the indexes on the SLAVE. However I am not even sure if this will work using mySQL's base REPLICATION feature if the indexes are different, since this is my first attempt at using it.

Any suggestion on what I should do to make this more effective. Right now, MySQL is processing about 1/20th of what I need it to in a given day.

I appreciate any feedback.

You need to profile your queries, to make sure that they're as efficient as possible. Use the built-in execution plan description process (EXPLAIN, or the Execution Plan visualizer in the MySQL Management Console) to help you figure out where your queries are bogging down.

In terms of hardware solutions, if you're not completely executing in-memory, consider upgrading your disk to an SSD; the throughput they provide can be very significant (as in, 2 order of magnitude more IOPS than rotating rust).

What MySQL version are you running? Recent versions of MySQL improved InnoDB concurrency a lot. You may also want to try the Percona MySQL builds.

Can you also give some more details about the structure of your table and how exactly your queries look like? When you say the INSERT and UPDATE are by index, do you mean the primary key, or a secondary index?

1 - Maybe you should try to partition your tables. This will decrease the size of parts of index which needed to be locked.

2 - http://www.youtube.com/watch?v=Oon06s9dXrY

3 - Try to separate your threads by different work scenarios. One thread for one type of operations (for example order registration), one thread for other type of operations (for authorisation check). This can significantly decrease your concurrency level.

4 - add cache level to your application if possible