MySQL stored procedures - execution time varies significantly from run to run

MySQL & MariaDB Other MySQL® Questions
1

Was hoping someone in the forums has experienced this SP issue to help me with a resolution. I have several dozen SP’s that run to process data (both reads and writes) and the run times vary greatly - from 45 secs to 80 secs. Same data can be processed and the run times still vary. I just want consistency - either fast or slow (preferably fast).

I’m running MySQL CE 5.5.24 and have tuned most queries in the SP’s to use indexes. All tables are innodb. But now I’m thinking I need additional tuning on the memory settings and cache settings. All of my temp tables in the SP’s are memory based, which should speed up data access.

I’m looking to increase my buffer pool size from 150M to 2G and up the stored_program_cache from the default 256 to 768 to make sure all SP’s are in cache (there are several hundred SP’s in the db). Am I on the right path to resolving this issue? All suggestions and db wisdom are welcome.

Thanks.

2

Buffer pool is one of significant reason for variation in execution because it could be required data pages are not in BP and need to fetch from disk and that’s where it takes times. Along with that, variation in server load could be another factor.