Sybase Cache Management and Query Performance
Posted on 2011-02-24
I have a Sybase ASE database - 15GB RAM 6 COREs of IA64, running Red Hat Linux 2.4 (64Bit) Kernel, RAID 1 disk 2GB Fibre Channel Disks (bit old now), Single database approx 30GB, with 10GB of Shared Memory most of that data is not usually access - and should be in a DataMart. I am activey archiving data - reducing to 2 years of history - this will reduce the table sizes by 2/3rds. System is used to create Orders and do basic account management, Names / Address and Order Inquiries.
I have an order table with 3mil rows and order lines with 4mil rows and a few other related tables similar row counts used for inquiry purposes. I have allocated these tables (1GB and 500MB) their own significant caches and separate ones for their indexes (500MB).
Now the issue: we see a small number of queries that are _very_slow - 20 - 30 seconds accessing data that should be in cache. If I re-run the query (we have a system to trap slow SQL statements) directly the query is _always_ very fast - have checked for Deadlocks - none.
I assumed it was a cache issue, hence allocated a significant cache for the order related tables, The queries all use indexes - no table scans.
Just wondered if any one has had a similar experience. Trying to get some consistent performance. Could be a DISK IO issue, could be data is rolling out of cache - going to use the Relaxed LRU mode for the caches.