From Eigenpedia

Jump to: navigation, search

This page explains how to change the size of the buffer pool LucidDB allocates from available RAM, and why you might need to do that. For background on the buffer pool, read LucidDbMemoryManagement.


What is the Default Buffer Pool Size?

For standard LucidDB packaging, it's 5000 pages of 32KB each, which works out to a little under 160MB (using the common definition of KB and MB).

How Do I Change the Buffer Pool Size?

The SQL command to change the buffer pool size is as follows, and it takes effect immediately (allocating or freeing the specified number of 32K pages of memory from the OS as needed):

alter system set "cachePagesInit" = 10000;

However, if you are increasing the buffer pool size above its default for the very first time, it takes a little more work. The reason is that there is a separate system parameter, cachePagesMax, which imposes an upper bound:

0: jdbc:luciddb:> alter system set "cachePagesInit"=50000;
Error: Invalid parameter setting.  Setting must be between 1 and 'cachePagesMax'. (state=,code=0)

In that case, use the following procedure:

1. Raise the ceiling:

alter system set "cachePagesMax" = 10000;

2. Restart LucidDB

3. Change cachePagesInit:

alter system set "cachePagesInit" = 10000;

There's no need to restart LucidDB a second time.

How Do I Convert From Bytes to Pages?

This requires a bit of arithmetic (a system procedure taking a string like '512MB' as parameter would be nicer, but doesn't exist yet).

Suppose you would like a 512MB buffer pool size. That's 512*1048576 bytes = 536870912 bytes. Since each pages is 32KB=32768 bytes, you want 536870912/32768=16384 pages. If you're good with powers of two, you can avoid the big numbers (512MB=2^29; 32KB=2^15; 29-15=14; 2^14=16384).

How High Can I Go?

There are a number of hard limits you should be aware of:

  • On a 32-bit operating system, LucidDB is limited by the size of the virtual address space of the process it runs in. This varies by operating system, but will always be less than 4GB, possibly much less. On a 64-bit operating system, the corresponding limit is so high as to be irrelevant.
    • 32-bit Linux limits each process to a 3 GB address space.
    • 32-bit Windows limits each process to a 2 GB address space, though some versions provide a /3GB switch in boot.ini to switch to a 3 GB address space.
    • In any event, part of the address space is consumed by application code.
  • Regardless of operating system, LucidDB is limited by the available physical RAM. LucidDB intentionally locks buffer pool pages into memory to discourage abuse of virtual memory, so you can't allocate more RAM than you actually have.

The error message from exceeding a limit varies according to the operating system and type of limit. In general, it's a good idea to be cautious when trying to set a higher limit (go up by increments). Prior to version 0.7.1, you may end up with a database image which can't even be successfully started, so make a backup first. More recently, LucidDB will fall back to a relatively small default number of maximum cache pages (currently 1024) in the event that either cachePagesMax or cachePagesInit is too large. You should verify that your changes are in effect by examining the performance counters system view.

Besides hard limits, there are also other considerations to take into account:

  • Every byte locked up in the buffer pool is unavailable to LucidDB's Java VM (both in terms of available address space and physical RAM). If the JVM can't allocate enough space for its heap, performance will suffer due to excessive garbage collection; eventually Java out-of-memory errors may also occur, causing SQL execution to fail. Even if the JVM heap has space left, a huge buffer pool may prevent the JVM from managing native memory effectively, leading to performance problems or even an abrupt end to the process. For example, Sun's JVM allocates memory from the process heap (in excess of its configured maximum Java heap size) for the purposes of dynamic compilation. If these allocations fail, the JVM will terminate abruptly.
  • Besides LucidDB's own JVM, other processes running on the machine will be affected if LucidDB's buffer pool eats up too much of the available RAM. This mailing list post gives an example where it was no longer possible to start sqllineClient on the same machine to fix the problem!

How Low Can I Go?

The hard limit is 1 page, but you should never set the buffer pool anywhere near that low; the system is likely to lock up since it needs to keep a few pages in reserve at all times for critical transaction-processing purposes. As with size increases, make a backup first.

Setting the buffer pool size too low will generally lead to poor performance, since many of LucidDB's algorithms perform better when given more scratch memory from the buffer pool. A bigger buffer pool also allows more stored data to be kept in cache.

Beyond a certain point, statement execution will not even be able to start if not enough scratch memory can be allocated from the buffer pool. The minimum amount of memory needed per statement is in general proportional to the complexity of the statement; in particular, INSERT and MERGE statement complexity is proportional to the number of columns and indexes defined on the target table. When you see the error below (Cache scratch memory exhausted), the remedy is to either increase the buffer pool size, or decrease the degree of concurrency.

0: jdbc:luciddb:> create schema s;
0: jdbc:luciddb:> create table s.t(i int, j int, k int, l int, m int, n int);
0: jdbc:luciddb:> alter system set "cachePagesInit"=10;
0: jdbc:luciddb:> insert into s.t values (1,2,3,4,5,6);
Error: Cache scratch memory exhausted (state=,code=0)

Could I Get Some Hard Numbers, Please?

By this point, you're probably thinking this page is waving its hands a lot. And you're right. But it's for a reason...tuning memory settings for a DBMS is very much an application-specific and system-specific activity for which there are only rules of thumb. Beyond that, there is no substitute for iterative tuning based on empirical results from benchmarking a specific configuration.

LucidDB does provide performance counters such as cache hits; for that, see the SYS_ROOT.DBA_PERFORMANCE_COUNTERS system view, and LucidDbMonitor. For more information, see LucidDbPerformanceTuning.

What Are My Current Settings?

In case you forget what changes you've made to a system, or wish to verify that changes are working correctly:

0: jdbc:luciddb:> select * from sys_root.dba_system_parameters where param_name like 'cachePages%';
| cachePagesMax   | 5000         |
| cachePagesInit  | 2500         |
Personal tools