Optimization techniques for SQL+ML queries: A performance analysis of real-time feature computation in OpenMLDB

Abstract: In this study, we optimize SQL+ML queries on top of OpenMLDB, an open-source database that seamlessly integrates offline and online feature computations. The work used feature-rich synthetic dataset experiments in Docker, which acted like production environments that processed 100 to 500 records per batch and 6 to 12 requests per batch in parallel. Efforts have been concentrated in the areas of better query plans, cached execution plans, parallel processing, and resource management. The experimental results show that OpenMLDB can support approximately 12,500 QPS with less than 1 ms latency, outperforming SparkSQL and ClickHouse by a factor of 23 and PostgreSQL and MySQL by 3.57 times. This study assessed the impact of optimization and showed that query plan optimization accounted for 35% of the performance gains, caching for 25%, and parallel processing for 20%. These results illustrate OpenMLDB's capability for time-sensitive ML use cases, such as fraud detection, personalized recommendation, and time series forecasting. The system's modular optimization framework, which combines batch and stream processing without interference, contributes to its significant performance gain over traditional database systems, particularly in applications that require real-time feature computation and serving. This study contributes to the understanding and design of high-performance SQL+ML systems and highlights the need for specialized SQL optimization for ML workloads.