Riot Games was established in 2006 by a couple of entrepreneurial gamers who believe player-focused game development can result in awesome games. They released their debut title, League of Legends, to critical and player acclaim in 2009. It has over 32 million plays every month.
The Estuate technical audit was undertaken to evaluate the architecture and configuration of the Vertica analytics platform and provide guidance on technical best practices. Riot Games was out of compliance with best practices and Estuate provided advice in many areas. Non-compliant audit points were addressed in detail and the necessary recommendations were provided.
Based on the audit check best recommendations and suggestions were provided for following areas
Sort order had high cardinality columns on projection fact tables
Having high cardinality column in projection sort order significantly impacts query performance and disk space utilization.
Ran DBD comprehensive design by providing logical schema, sample data and sample queries as input. Redesigned projections were deployed which improved the performance
Dimension table projections should to be replicated
If dimension tables are segmented then it might create a network join which might consume more bandwidth
Dimension tables were replicated which created local joins. Made sure that there is no data flow between the nodes
It can significantly impact query performance
Partition strategy was followed which made sure it doesn’t exceed ROS limits.
Impacts workloads on cluster and SQL queries
Provided recommendations which were implemented, which resulted in SQL tuning and managing workload history
Workload segmentation through Resource pools
Only one custom resource pool was defined
Multiple resource pools were defined and tuned which yielded optimal performance
Some of the OS parameters were not set as per Vertica recommendations. This can have negative impact on Vertica cluster
OS configuration parameters were set like max number of file handles, max threads or processes per user, max file size etc.
Data was not denormalized for the fact tables. This can create joins between the projections which can slow down the runtime queries
Denormalized for fact tables which increased the query throughput
Database statistics was not collected for around 88% projections
It causes optimizer to make poor choices resulting in reduce query performance
Database statistics was gathered and made sure the database is up to date.
Disable CPU hyper threading
Hyper threading are extremely compute intensive and it cause more workloads during data retrieval process
Hyper threading was disabled and made sure that Vertica is not overloaded for query retrieval