I've a credit card applicatoin that's delivering function calls to my mySQL database. Once sent, the function does what it really needs (including single line chooses, Place increase by index).
At this time I can do in regards to a million mySQL calls each day. These mySQL function calls are divided into my app's actual "calls" which may be between 1 to twenty different mySQL function calls. My application gets about 120,000 "application function calls" each day.
I've 12 gigs of ram, so increased my InnoDB in memory ram from 2GIG to 8GIG which didn't seem to increase. Next I believed, if 1 thread gets much, adding threads would increase it. And So I did this, and now i'm seeing boat loads of Index Locks errors.
I understand that mySQL states to merely resend the request, which works, but I'm not seeing much rise in speed. I'm essentially maxing at 180,000 "application function calls" on my small finish no matter threads, when single threaded involved 120,000. The reason being after i have a couple of-3 threads, its constant thread securing errors.
Now all of my tables has a lot of index's for search efficiency, since i have have countless rows. However, I intend to replicate this database so a brand new instance does the majority of the searches on the SLAVE, as the primary information is maintained on my small MASTER (that is all of the updates / card inserts). Due to this I believed maybe getting rid of all index's around the MASTER aside from the primary key and unqiue secrets would lower this? I Quickly could add the index's around the SLAVE. However I'm not even confident that this works using mySQL's base REPLICATION feature when the index's will vary, as this is my first attempt for utilizing it.
Any suggestion on which I ought to do in order to get this to more efficient. At this time mySQL is processing about 1/20th of the items I want it to inside a given day.
I appreciate any feedback
You have to profile your queries, to make certain that they are as efficient as you possibly can. Make use of the built-in execution plan description process (EXPLAIN, or even the Execution Plan visualizer within the MySQL Management Console) that will help you determine where your queries are bogging lower.
When it comes to hardware solutions, if you are not completely performing in-memory, consider improving your disk for an SSD the throughput they offer can be quite significant (as with, 2 order of magnitude more IOPS than rotating rust).
What MySQL version are you currently running? Recent versions of MySQL enhanced InnoDB concurrency a great deal. You may even would like to try the Percona MySQL develops.
Are you able to also give more particulars concerning the structure of the table and just how exactly your queries seem like? Whenever you the Place increase are by index, would you mean the main key, or perhaps a secondary index?
1 - Maybe gradually alter partition your tables. This can decrease how big areas of index which must be locked.
3 - Attempt to separate your threads by different work situations. One thread for one sort of procedures (for instance order registration), one thread for different kind of procedures (for authorisation check). This could considerably lower your concurrency level.
4 - add cache level for your application if at all possible