SQLAnywhere 12 -- memory leak !?

[Former Member]

hi all, I have a client whose data I am attempting to port from 9.0, 32bit to 12.0 64 bit, EBF 3873. The database is about 54gb in size and I allocate 24 gb RAM to the dbengine cache, running over tcpip. Most of the time it runs OK however, at times the engine becomes un-responsive as well as the client application, and the engine starts eating up about 8mb of ram about every second and "reading" the disk at about the same speed, 7-8mb/sec. (The IO that the db is on, for testing purposes, has a speed of 60-80mb/sec.)

I was trying to trace this database's events/queries to another trace db (placed on an independent SSD) however when the aforementioned issue happens, the tracing also gets foo-barred. I have not seen this issue w/ the 32bit 9.0 database. How can I trace/identify what is going on?

thanks a big bunch -- Cos

[Breck_Carter · Accepted solution]

If you are running on a modern Windows OS, click on the Task Manager - Performance tab - Resource Monitor... button to see some more details of what is going on in the world of file and disk I/O. I am not sure, but it is conceivable that some of the extra I/O might be caused by the Windows System Restore process. Back in 2009 I didn't think SQL Anywhere was affected but I'm not so sure any more... if memory serves, I have seen the Resource Manager reporting heavy disk I/O against SQL Anywhere minutes after the database was shut down, but I can't remember the specifics.

You might also be interested in Foxhound...

[johnsmirnios]

How are you monitoring the memory size? If it is with the Task Manager, it is showing you the 'working set' of the process and is not a very useful metric. If the working set is increasing at the same rate as IO, it would just suggest that the server is likely reading & caching database pages -- which is normal and appropriate.

Does the unresponsive period occur near a checkpoint?

Also, you might be able to monitor what is going on using request-level logging: http://dcx.sybase.com/index.html#1201/en/dbusage/performance-s-5753386.html*d5e7938

[Former Member]

hi John, thanks a big bunch for the fast response. Yes, I am looking at the Task Manager working set and I believe you're right, increasing the working set memory at the same rate as the IO would indicate a scan of a table, but why so slow, 7-8mb when the IO has a much faster throughput? (no, I don't believe it's occurring near a checkpoint)

-- regardless, I will attempt the request-level logging and report back -- thx again for the feedback. Cos

[johnsmirnios]

It might not be doing just a sequential scan: random IO (for index lookups, etc) can be much slower. For example, if every IO requires a 12ms seek you would only get about 83 IOs/sec. If the IOs are 4KB per read, that's just 333KB/sec.

[Former Member]

hi John, thanks to the request-level logging idea, I was able to pinpoint closer and need to do further analysis before I post my findings -- sometime tomorrow.

thx again and will follow up

[Former Member]

hi John, running the engine with -zr all -zo -- to include all request logging and building both the satmp_request_time and satmp_request_profile tables yielded the following outliers (everything else seems to be normal:

1. select getdate() as serverdate from dummy -- called once, total_ms=23,544 -- really, 23 seconds for a getdate()
   
2. a couple of tables taking between 1,400 ms to 8,500ms roughly -- execution of those statements only once. -- also no biggie, but in reality, these queries take a fraction of a second.

what baffles me is that once the 'working set' memory starts increasing, the IO also correspondingly is consistent at around 8-10mb/sec (the db, for testing, is on a USB3 connected disk, just so I can see things running in slow-motion), the tracing DOES NOT OUTPUT any more info in the tracing file but this memory growing process lasts for about 4-6 minutes after which it subsides, this process occurring long after the last statement in the trace file is recorded.

what could this be indicative of?

I'm tempted to try to replicate this under sybase 9.0 (our 32 bit original installation, although I doubt I will see this kind of effect and also comparing drastically different environments) and perhaps also sqlanywhere 16 as well.

any thoughts? thx much, Cosmin

[Former Member]

hello folks, been looking more extensively at both the full tracing data as well as the request-level logging traces and for some reason, I cannot find any INSERTS, UPDATES nor DELETES in these traces, even though I know my app generates them. Am I missing something elementary here? I only see SELECT statements being generated through both of these tracing methods.

thx much for any thoughts, Cos