November 2018 Summaries

SingleStoreDB Self-Managed 6.7 is a new release focused on usability, compatibility, and performance. The free tier for SingleStore now offers full functionality to support real-world use cases, with improved ease of use, faster queries, and new tools for monitoring and management. A new customer portal and open developer forum are also introduced, allowing users to access community support and freely access SingleStore documentation. The new release includes an upgraded customer portal, a new monitoring UI called SingleStore Studio, which is delivered as software as a service (SaaS) and will be updated regularly. The studio offers tremendous benefits, including stateless tools, additional features for management tools like Ansible, Chef, and Puppet, and the ability to work easily with management tools. The new release also includes an upgraded customer portal and a new developer forum to interact with SingleStore and fellow developers. With the free tier proving very popular, organizations are using it for new projects, with plans to convert instances to an enterprise license as each project moves into production.

Scaling distributed systems is challenging due to various factors such as the need for dynamic resource allocation and maintenance of service level agreements. Databases are particularly hard to scale because they involve multiple aspects, including CPU, RAM, storage, and SQL objects. The growth in data ingestion per unit time, concurrent queries, working set, and number of SQL objects require different scaling strategies. Most legacy databases only support a single-box model, which limits scalability and makes it difficult to handle unexpected growth without downtime. In contrast, scale-out relational databases like SingleStore can easily scale by adding new nodes to a cluster, allowing for online operations with no downtime.

Epigen Technology relies heavily on SingleStore as part of its core toolkit. Facial recognition is a challenging topic in AI, and Epigen has developed a framework that combines AWS S3, SingleStore, machine learning, AI, and visualization to improve accuracy and scalability. The system uses a scalable architecture, incorporating GPUs for vectorization and comparison, and utilizes SingleStore's Pipelines feature for rapid data ingest and processing. With SingleStore, Epigen aims to achieve higher accuracy, compare against other databases, and use full-motion video, which can help with applications such as shortening lines at airports and recognizing missing children. The technology is developed as a collaborative effort between Epigen and SingleStore, with the goal of creating a reference architecture for cognitive computing that goes beyond traditional machine learning and AI tools.

The discussion revolves around the limitations of traditional Hadoop-based data lake architectures, which struggle to provide flexible access to data for operational machine learning (ML) and artificial intelligence (AI) applications. SingleStore, a fast, fully scalable relational database that supports ANSI SQL, is introduced as a potential solution to simplify data processing architecture and speed decision-making. It offers the best of both worlds: scalability on industry-standard servers like Hadoop, and full relational capabilities with transaction support and standard SQL interface. This enables businesses to run ML against all their data, including old and new data, in one process, providing instant access to the newest information as it comes in. SingleStore can replace or work alongside traditional data lake architectures, offering a simpler and faster system for ingesting and querying data.

SingleStore's DNA is focused on performance, which was demonstrated by shattering the trillion-rows-per-second scan barrier for a single SQL query on industry-standard Intel servers in March 2023. The company has broadened its vectorized, SIMD query execution technology to star join group-by aggregate queries, resulting in ludicrously good performance improvements. This enhancement enables developers to use SingleStore to get stunning performance at concurrency levels they couldn't have dreamed of before, with a database that supports mixed workloads and runs ANSI SQL. The new algorithm uses proprietary, patent-pending methods to operate directly on compressed columnstore data formats, achieving significant speedups in star join queries, up to 101 times faster than without the new capability. This improvement is particularly beneficial for applications using star or snowflake schemas with integer surrogate keys. SingleStoreDB Self-Managed 6.7 also introduces various other performance improvements, including just-in-time compilation of queries, IN-list performance enhancements, resource governor upgrades, columnstore filter speedups, intra-cluster network compression, fast sampled rowstore scans, optimizer and statistics updates, new information_schema views for tuning, query plan warnings, and statistics views.

The SingleStore company has introduced a new toolset for managing, deploying, troubleshooting, and monitoring their database platform. The new tools are designed to be easily integrated with existing configuration and deployment tools, providing better compatibility and ease of use. The toolset includes three command-line tools (memsql-deploy, memsql-admin, and memsql-report) that can be used together, as well as a user-friendly interface called SingleStore Studio for monitoring and troubleshooting queries. The new tools take advantage of role-based access control, allowing users to manage capabilities at a user level, and are designed to iterate faster with separate releases from the SingleStore engine. The toolset is compatible with popular deployment technologies such as AWS CloudFormation and will be expanded to other tools in the near future.

Today, SingleStoreDB Self-Managed 6.7 was announced, offering a free tier for everyone to use databases with up to 128GB of RAM usage, and no limit on database size on disk, including solid state drive (SSD). The free tier is full featured and includes all enterprise capabilities, such as high availability and security. Customers can learn, develop and test, and deploy into production using the free tier, but may reach a point where they need more support or resources. This new offering supports robust applications, real-time geospatial analytics, trading applications, image recognition, and operating in production with community support. To upgrade, users can seamlessly move to a time-limited Enterprise trial license before buying a full Enterprise subscription.

Areeba, a financial services company, has adopted SingleStore to improve its fraud detection, anti-money laundering (AML), and product recommendations. Areeba previously used Hadoop for unstructured data and MariaDB as a relational database but now leverages SingleStore to analyze large amounts of data in real-time. By combining transactional and analytical processing, SingleStore enables Areeba to detect illicit transactions and provide personalized recommendations to customers. The company is also exploring the use of blockchain and cloud technologies to further enhance its services. With SingleStore, Areeba aims to move beyond traditional solutions and create better outcomes for its customers.

The SingleStoreDB Self-Managed 6.7 release includes an important performance improvement in adaptive data compression that optimally responds to network conditions. This feature, developed by a summer intern at SingleStore, utilizes the aggregator's CPUs to compress packets while waiting on the network, making better use of otherwise unused resources. The approach identifies when to invest time compressing buffers versus actually sending packets, and it has been found to significantly improve performance in clusters with weak networks or many leaves. By reducing network congestion, the feature allows data to be loaded at a faster rate than the network speed alone would permit. The improvement is estimated to achieve a 2.7x speedup over uncompressed data loading, bounded by the compression ratio of 3x.

** SingleStore has a long tradition of sharing its performance optimization efforts with customers, and this blog post continues that tradition by detailing the development of Visual Explain, a tool that helps customers optimize their database schema for maximum performance and discover bottlenecks in the SingleStore engine. The author, who interned at SingleStore last summer, worked on the team developing SingleStore Studio, which includes Visual Explain, and shares insights into the design process, including the use of existing tools like Pev's Postgres EXPLAIN Visualizer as inspiration. The tool was developed to address the complexity of database query execution plans, providing a graphical interface for humans to understand and interact with the output of EXPLAIN or PROFILE commands. The author discusses the challenges of designing an aesthetically pleasing layout algorithm for trees, drawing on research from papers like Reingold and Tilford's Tidier Drawings of Trees and Walker's tree-drawing algorithm. The final implementation uses a strategy similar to Walker's, but with some modifications to accommodate the specific needs of Visual Explain, including the use of heuristics to determine the amount of space between subtrees.

Wag!, a dog-walking service, has adopted SingleStore to handle its rapid growth by processing large amounts of real-time data. The company's database was reaching its maximum computing capacity due to the high number of writes and delays on database replicas, causing lag and preventing it from scaling read queries. By migrating to SingleStore, Wag! reduced its average CPU utilization to the high teens and is now able to handle spikes in traffic smoothly. With SingleStore's support, scalability, and performance capabilities, including pipelines, rowstore, columnstore, and geospatial data type features, Wag! aims to further optimize its operations and explore new features such as Pipelines to Stored Procedures and a publicly accessible database of taxi data for student lab work.

SingleStoreDB Self-Managed 6.7 is now available, offering faster query performance, improved usability with robust monitoring, simplified deployment tooling, and a new free tier that anyone can use. The new release provides a free production license for up to 128GB of RAM capacity, making maximum performance available to everyone. It also introduces dramatic improvements in query performance, leveraging query vectorization and SIMD technology, which makes Star Joins faster by up to 100x. Additionally, SingleStore Studio simplifies tuning and deployment with new monitoring and management tooling, improved loading and backups, and a new developer forum for tips and troubleshooting. The free tier offers all the capabilities found in the Enterprise licensing tier of the database, providing tremendous value to customers.