Hi all …
Using MySQL 5.7.5-m15, I have a stored procedure that is pretty simple in its idea …
- Uses a cursor to read from a table
- Concatenates column values to produce an XML formatted line per row
- Concatenates XML formatted rows into a MEDIUMTEXT variable (appending)
- When done, write MEDIUMTEXT variable into an OS file
Typically, the MEDIUMTEXT variable will grow up to 11-15 MB and may contain up to 50k XML formatted rows. Entire process may repeat to produce multiple files in a single run.
It takes a an average of 5 minutes to produce a single file. I’m noticing performance degrades as the MEDIUMTEXT variables grows and almost halts as we get over 30k rows. Once it is reinitialized, performance is restored.
I know there is not enough data to make a conclusive judgment. But for the experienced, it may trigger something 
I have updated the buffer variables (sort/join/temp), max_allowed_packet to no avail. I’m installing OS and DB monitoring tools trying to isolate the source of this apparent slowness.
I’m new to MySQL and have been burning the midnight oil reading multiple books about MySQL performance management. I came across few ideas …
- Cursors can be slow because they iterate over rows. Couldn’t find alternatives to accomplish same job.
- Cursors use temporary tables & I have seen some recommendations to have temp table created in RAMFS. Not sure how much improvement to expect.
- I don’t believe there are are tunable variables to control thread memory allocated for stored procedure. I believe, it is unlimited.
I suspect the server may be swapping during the run of this job. However, I will be studying the vmstats/mpstat/iostat later today.
What else am I missing?
Thanks