Vertica resource failure errors in Data Aggregator logs
Article ID: 144763
Updated On:
Products
CA Infrastructure Management
CA Performance Management - Usage and Administration
DX NetOps
Issue/Introduction
The following errors from the Data Aggregator (DA) karaf.log file found in (default path) /opt/IMDataAggregator/apache-karaf-2.4.3/data/log.
The first will be a RIB Query Exception from Vertica that states:
That is normally followed by messages such as:
Caused by: com.vertica.support.exceptions.TransientException: [Vertica][VJDBC](3587) ERROR: Insufficient resources to execute plan on pool scheduled_short_rib_processing [Timedout waiting for resource request: Request exceeds limits: Queries Exceeded: Requested = 1, Free = 0 (Limit = 4, Used = 4); Memory(KB) Exceeded: Requested = 1870392, Free = 441947 (Limit = 14214979, Used = 13773032) (queueing threshold)]
The first will be a RIB Query Exception from Vertica that states:
[Vertica][VJDBC](3326) ERROR: Execution time exceeded run time cap of 00:05; nested exception is java.sql.SQLTransientException:
[Vertica][VJDBC](3326) ERROR: Execution time exceeded run time cap of 00:05
org.springframework.dao.DataAccessResourceFailureException: PreparedStatementCallback; SQL [];
[Vertica][VJDBC](3326) ERROR: Execution time exceeded run time cap of 00:05; nested exception is java.sql.SQLTransientException:
[Vertica][VJDBC](3326) ERROR: Execution time exceeded run time cap of 00:05
That is normally followed by messages such as:
Caused by: com.vertica.support.exceptions.TransientException: [Vertica][VJDBC](3587) ERROR: Insufficient resources to execute plan on pool scheduled_short_rib_processing [Timedout waiting for resource request: Request exceeds limits: Queries Exceeded: Requested = 1, Free = 0 (Limit = 4, Used = 4); Memory(KB) Exceeded: Requested = 1870392, Free = 441947 (Limit = 14214979, Used = 13773032) (queueing threshold)]
... 63 more
Environment
All supported Performance Management releases
Cause
We've seen in-house where the speed of the disks can lead to stability issues with Vertica. When I/O is low, the following can be done to help minimize Vertica crashes and some resource related errors during normal product operation.
The concept is dirty pages. Linux uses memory to cache file writes. It uses a Page Cache to store these writes before writing them to disk. Each page is called a dirty page (because it hasn't been written yet).
There are settings for when it will write this data to disk and when the cache is too full and needs to be flushed to disk. If the max is reached, all new writes are halted until the cache is flushed.
Depending on the size of the cache and speed of the disks, this can take some time. This is where we believe the issue is that is causing Vertica to crash.
If Linux is too busy dumping dirty pages to disk, and it takes more than 2 minutes, Vertica could crash.
There is one Linux setting called: vm.dirty_ratio. It is the ratio of Page Cache in memory compared to overall memory. When this ratio is reached, Linux halts all new write requests and flushes the Page Cache to disk. OOTB, this value is 20 (20% of overall memory).
There is another Linux setting called: vm.dirty_background_ratio. It is the ratio of the Page Cache at which it will start writing the data to disk. OOTB, this value is 10 (10% of overall memory).
The concept is dirty pages. Linux uses memory to cache file writes. It uses a Page Cache to store these writes before writing them to disk. Each page is called a dirty page (because it hasn't been written yet).
There are settings for when it will write this data to disk and when the cache is too full and needs to be flushed to disk. If the max is reached, all new writes are halted until the cache is flushed.
Depending on the size of the cache and speed of the disks, this can take some time. This is where we believe the issue is that is causing Vertica to crash.
If Linux is too busy dumping dirty pages to disk, and it takes more than 2 minutes, Vertica could crash.
There is one Linux setting called: vm.dirty_ratio. It is the ratio of Page Cache in memory compared to overall memory. When this ratio is reached, Linux halts all new write requests and flushes the Page Cache to disk. OOTB, this value is 20 (20% of overall memory).
There is another Linux setting called: vm.dirty_background_ratio. It is the ratio of the Page Cache at which it will start writing the data to disk. OOTB, this value is 10 (10% of overall memory).
Resolution
Optimal setting is for the Page Cache to be dumped from full to 0 in 30 seconds as to not hold up pending writes for more than 30 seconds.
To determine the best value for these settings the vioperf script needs to be run against the DR DB data disks. From that data:
This means dirty_ratio would be:
Rounding up since we're looking at 30 secs we'd use dirty_ratio = 2 and dirty_background_ratio = 1.
Example for 2 mins:
Here dirty_ratio would be:
Rounding down since we want to remain under 2 mins max we'd use dirty_ratio = 5 and dirty_background_ratio = 2.
To change a setting immediately run:
To make the setting permanent, add the name=value to /etc/sysctl.conf. This is what we'd recommend here, editing the /etc/sysctl.conf file on all nodes in the cluster. In the above example calculations, we'd add the following lines to the /etc/sysctl.conf file on each node:
NOTE: Adjust your comments and values as needed to fit the given environment.
After making the change permanent via /etc/sysctl.conf edits, run "sysctl -p" on each node to refresh with the system with new configuration.
To determine the best value for these settings the vioperf script needs to be run against the DR DB data disks. From that data:
- Take the speed per core value.
- Multiply it by 30 (seconds) and it gives us the size of the file cache maximum.
- Figure out the percent of memory by doing:
- (file cache size in MB/size of overall memory in MB)*100=dirty_ratio value
- 40 MB/s * 30 = 1200 MB (aka 1.2GB)
This means dirty_ratio would be:
- ( 1200 / 92000 ) * 100 = 1.3
Rounding up since we're looking at 30 secs we'd use dirty_ratio = 2 and dirty_background_ratio = 1.
Example for 2 mins:
- 40 MB/s * 120 = 4800 MB (aka 4.8 GB)
Here dirty_ratio would be:
- ( 4800/ 92000 ) * 100 = 5.2
Rounding down since we want to remain under 2 mins max we'd use dirty_ratio = 5 and dirty_background_ratio = 2.
To change a setting immediately run:
- sysctl -w name=value
To make the setting permanent, add the name=value to /etc/sysctl.conf. This is what we'd recommend here, editing the /etc/sysctl.conf file on all nodes in the cluster. In the above example calculations, we'd add the following lines to the /etc/sysctl.conf file on each node:
# Set dirty_ratio to 2 overriding default value of 20
dirty_ratio = 2
# Set dirty_background_ratio to 1 overriding default value of 10
dirty_background_ratio = 1
NOTE: Adjust your comments and values as needed to fit the given environment.
After making the change permanent via /etc/sysctl.conf edits, run "sysctl -p" on each node to refresh with the system with new configuration.
Additional Information
This may not fully resolve the appearance of the errors without improving I/O for the data and catalog disks used by the Data Repository.
Feedback
Yes
No
Powered by
