Hi folk,
I’m looking for some advice as the problem I am about to describe is not easily repeatable in a test environment.
We’re still investigating the specifics, but believe our web application fell victim to an overactive web crawler or some other event that caused a burst of very high activity.
The net result was our Oracle DB server which provides spatial data storage for our Geoserver install was overcome. Our database people had the following to say about the matter:
“The locking was not locking on table data but rather oracle’s own locks (latches) oracle uses chained lists to keep track of who has what and when you request something it has to put a latch on it - these are very simple locks on resources and there are no queues in place, so if you need to get something it will try and get the latch and if it fails then it will try again, and again and again, until it gets it, hence the CPU’s are spinning.
This form of ‘contention’ can be very common and can be caused by high logical IO (where you are potentially requesting a lot of data from the buffer cache). Which is why the saying reduce logical IO and that will take care of the physical IO comes from! Poorly tuned SQL can cause this problem. “
Tthere’s nothing (as far as I know – correct me if I am wrong) that I can do personally to tune the SQL requests made by GeoServer to published Oracle tables.
Is there any general advice anyone can give to avoid such problems in the future?
Mark Hammond
Senior Web Software Developer
British Trust for Ornithology, The Nunnery, Thetford, Norfolk, IP24 2PU, UK
Faulting SQL
SELECT CATEGORY, BOUNDARY AS BOUNDARY
FROM BTOWATCH.BT_MAP_10KM_CNT_SP_VIEW
WHERE ( SPECIES_CODE = :1
AND ( ( SDO_FILTER (BOUNDARY,
:2,
‘mask=anyinteract querytype=WINDOW’) =
‘TRUE’
OR SDO_FILTER (BOUNDARY,
:3,
‘mask=anyinteract querytype=WINDOW’) =
‘TRUE’)
AND ( CATEGORY = :4
OR CATEGORY = :5
OR CATEGORY = :6
OR CATEGORY = :7
OR CATEGORY = :8
OR CATEGORY = :9)));
/* Formatted on 13/12/2013 17:33:49 (QP5 v5.256.13226.35538) */
SELECT diminfo
FROM ALL_SDO_GEOM_METADATA
WHERE OWNER = :1 AND TABLE_NAME = :2 AND COLUMN_NAME = :3;
· Build Information
- Version 2.1-SNAPSHOT
- Subversion Revision 16029
- Build Date 15-Jun-2011 05:07
- GeoTools Version 2.7-SNAPSHOT (rev 37435)
Data directory
/usr/local/geoserver
Locks
0
Connections
6
Memory Usage
4 GB
JVM Version
Oracle Corporation: 1.7.0_25 (Java HotSpot™ 64-Bit Server VM)
Available Fonts
GeoServer can access 144 different fonts.
Native JAI
false
Native JAI ImageIO
false
JAI Maximum Memory
4 GB
JAI Memory Usage
0 KB
JAI Memory Threshold
75.0
Number of JAI Tile Threads
60
JAI Tile Thread Priority
5
ThreadPoolExecutor Core Pool Size
5
ThreadPoolExecutor Max Pool Size
10
ThreadPoolExecutor Keep Alive Time (ms)
30000
Update Sequence
669
Oracle 11g Release 2 running on Centos 6