Database index performance improvement
Managing database operations is provided. The method comprises receiving an insert statement for a database and determining if the insert statement is for a batch insert operation or random insert operation. For a batch insert operation, responsive to determining a threshold number of specified leaf pages are missing from a memory buffer pool of the database, the database asynchronously pre-loads missing leaf pages from a corresponding index on disk into the memory buffer pool. For a random insert operation, responsive to determining a threshold number of specified leaf pages are missing from a memory buffer pool of the database, the database builds at least one memory cache index and inserts key values specified in the insert statement in the memory cache index. The memory cache index is merged with a corresponding index on disk.
1. A computer-implemented method for managing database operations, the method comprising:
using a number of processors to perform the steps of:
receiving an insert statement for a database;
determining the insert statement is for a batch insert operation or random insert operation;
for a batch insert operation, responsive to determining a threshold number of leaf pages specified in the insert statement are missing from a memory buffer pool of the database, asynchronously pre-loading missing leaf pages from a corresponding index on disk in the database into the memory buffer pool;
for a random insert operation, responsive to determining a threshold number of leaf pages specified in the insert statement are missing from a memory buffer pool of the database:
building at least one memory cache index;
inserting key values specified in the insert statement in the memory cache index; and
merging the memory cache index with a corresponding index on disk in the database.
2. The method of claim 1 , wherein, for a batch insert, a number of sub-tasks access a number of indexes on disk in the database and locate the missing leaf pages for pre-loading into the memory buffer pool.
3. The method of claim 2 , wherein the sub-tasks sort the missing leaf pages for pre-loading into the memory buffer pool.
4. The method of claim 1 , wherein building at least one memory cache index comprises building multiple memory cache indexes.
5. The method of claim 4 , further comprising consolidating the multiple memory cache indexes into a single consolidated memory cache index prior to merging with the corresponding index on disk.
6. The method of claim 5 , wherein consolidating the multiple memory cache indexes comprises merging leaf pages by range when overlap between leaf pages in the indexes is below a specified threshold range.
7. The method of claim 5 , wherein consolidating the multiple memory cache indexes comprises merging leaf pages individually when overlap between leaf pages in the indexes is above a specified threshold range.
8. A system for managing database operations, the system comprising:
a storage device configured to store program instructions; and
one or more processors operably connected to the storage device and configured to execute the program instructions to cause the system to:
receive an insert statement for a database;
determine the insert statement is for a batch insert operation or random insert operation;
for a batch insert operation, responsive to determining a threshold number of leaf pages specified in the insert statement are missing from a memory buffer pool of the database, asynchronously pre-load missing leaf pages from a corresponding index on disk in the database into the memory buffer pool;
for a random insert operation, responsive to determining a threshold number of leaf pages specified in the insert statement are missing from a memory buffer pool of the database:
build at least one memory cache index;
insert key values specified in the insert statement in the memory cache index; and
merge the memory cache index with a corresponding index on disk in the database.
9. The system of claim 8 , wherein, for a batch insert, a number of sub-tasks access a number of indexes on disk in the database and locate the missing leaf pages for pre-loading into the memory buffer pool.
10. The system of claim 9 , wherein the sub-tasks sort the missing leaf pages for pre-loading into the memory buffer pool.
11. The system of claim 8 , wherein building at least one memory cache index comprises building multiple memory cache indexes.
12. The system of claim 8 , further comprising consolidating the multiple memory cache indexes into a single consolidated memory cache index prior to merging with the corresponding index on disk.
13. The system of claim 12 , wherein consolidating the multiple memory cache indexes comprises merging leaf pages by range when overlap between leaf pages in the indexes is below a specified threshold range.
14. The system of claim 12 , wherein consolidating the multiple memory cache indexes comprises merging leaf pages individually when overlap between leaf pages in the indexes is above a specified threshold range.
15. A computer program product for managing database operations, the computer program product comprising:
a computer-readable storage medium having program instructions embodied thereon to perform the steps of:
receiving an insert statement for a database;
determining the insert statement is for a batch insert operation or random insert operation;
for a batch insert operation, responsive to determining a threshold number of leaf pages specified in the insert statement are missing from a memory buffer pool of the database, asynchronously pre-loading missing leaf pages from a corresponding index on disk in the database into the memory buffer pool;
for a random insert operation, responsive to determining a threshold number of leaf pages specified in the insert statement are missing from a memory buffer pool of the database:
building at least one memory cache index;
inserting key values specified in the insert statement in the memory cache index; and
merging the memory cache index with a corresponding index on disk in the database.
16. The computer program product of claim 15 , wherein, for a batch insert, a number of sub-tasks access a number of indexes on disk in the database and locate the missing leaf pages for pre-loading into the memory buffer pool.
17. The computer program product of claim 16 , wherein the sub-tasks sort the missing leaf pages for pre-loading into the memory buffer pool.
18. The computer program product of claim 15 , wherein the at least one memory cache index comprises multiple memory cache indexes, and further comprising consolidating the multiple memory cache indexes into a single consolidated memory cache index prior to merging with the corresponding index on disk.
19. The computer program product of claim 18 , wherein consolidating the multiple memory cache indexes comprises merging leaf pages by range when overlap between leaf pages in the indexes is below a specified threshold range.
20. The computer program product of claim 18 , wherein consolidating the multiple memory cache indexes comprises merging leaf pages individually when overlap between leaf pages in the indexes is above a specified threshold range.