Bertrams Books - Consultancy - Improving customer service through database optimisation
Bertrams is a large wholesaler in the books supply chain throughout the UK with a wide range of customers both here in the UK and overseas.
With orders increasingly being placed online, Bertrams needed a robust back end database to support this growing route to market.
Bertrams Books uses DB2 with Net Search Extender (NSE) as the host for their Group Bibliographic / Multimedia Database, and was experiencing some issues surrounding the performance, scalability, resilience and backup for this critical DB2 database.
When customers searched online for book titles the search time was taking far too long which, in todays fast paced on demand environment simply was not good enough and customers were clicking off the site before completing their search.
Triton were commissioned to review the current DB2 set-up and provide recommendations that would improve the response times. The objective was to get the best performance possible from Bertrams existing software licenses by using the best server configuration possible.
Triton's DB2 Health Check aims to address these challenges by providing a comprehensive, structured review of Bertram's DB2 environment, delivering detailed recommendations on how they can best be addressed.
After completion of the DB2 health check, Triton proposed a phased approach as a way forward for Bertrams to obtain better response times for the search queries and a more stable, scalable, and robust solution:
The first phase of the project was for Triton to implement the key recommendations highlighted in the DB2 health check. This would improve performance of both the update and search processes. The key recommendations included:
- Making sure no index update processes run during the day.
- Improving the performance of the index update process by updating the memory related parameters in the cteixcfg.ini configuration file.
- Tuning DB2 registry variables and configuration parameters to minimise I/O.
- Tuning DB2 bufferpools to reduce synchronous I/O.
Triton recommended that the second phase of the project should involve Bertrams moving to 64-bit so that DB2 would better leverage the vast amount of available memory on the database server. This would then reduce I/O and improve the query response times. This meant that Bertrams would need to migrate from DB2 V8 to DB2 V9.5 since all previous versions of NSE only support 32-bit. It will also require dropping the 32-bit text indexes and re-creating the 64-bit versions. Triton provided Bertrams with consultancy for the database and NSE migration
The third phase would be to use the data partitioning feature (DPF) functionality of DB2. This will then enable NSE to leverage multiple CPUs if each CPU is dedicated to a partition. Using DPF will significantly enhance both the index update and search processes. Since data will be spread across partitions each of these processes can be done in parallel over smaller chunks of data and on dedicated CPUs.
Once the first two phases of the project had been completed Bertrams instantly saw a massive improvement in their search response times. The times shot down to at most 1 or 2 seconds allowing Bertrams to provide their customers with a far superior and efficient experience.
As their business grows Bertrams, working with Triton, have a clear strategy for increasing search capacity and further reducing response times, using the scalable, multi-processor, partitioning and replication features of DB2 9.5.