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.