MySQL has tens of thousands of users leveraging it for complex technical challenges, but single-box database systems still reach their limits when pushed. In this blog post, we summarize three of the most common errors users see on MySQL and some tips and tricks on how to approach them, as well as what to look for when seeking a more scalable alternative. Feeling like you’ve maxed out MySQL? Let’s dive in and learn more.
Error #1: Too Many Connections
- When building applications, you may find that as you adopt consumers you are starting to hit limits with the amount of connections allowed. The default in MySQL is 151 connections, but you should be careful to increase this too high as MySQL uses one thread per connection and having too many active threads may hurt your performance. Each thread needs memory, and memory is expensive.
- Many users have tried to address this challenge by adopting flavors such as AWS RDS MySQL still face issues with increase loads, particularly with table/row level locking increasing the number of client connections
- Here at SingleStore, many users leverage our cloud DBaaS to handle highly concurrent workloads. Its MySQL wire protocol compatibility makes it easy to migrate your workloads. Connections are also extremely efficient for SingleStore, as the engine is smart when mapping connections to threads. If connections are idle, we immediately allocate threads to other work instead of hoarding them. This allows SingleStore to scale up to handling more connections than threads in most cases.
- SingleStore’s default value for max_connections is 100,000
- By comparison, AWS RDS hard limit is 16,000.
- Google Cloud SQL for MySQL limit is 4,000
- Azure Database for MySQL limit is 20,000
Error #2: Out of Memory
- Running out of memory in MySQL could mean a few different things. The easiest place to start is to make sure tables have not exceeded the allocated memory (especially for temporary tables). This can also often be caused by highly concurrent workloads over large queries that require lots of memory (GROUP BY, for instance).
- The InnoDB engine requires careful tuning when MySQL becomes resource constrained
- A common approach after exhausting all options of a single box VM’s memory has been something like MariaDB’s columnstore architecture. However, columnstore architectures are typically built largely for analytical purposes and are not so great at handling real-time ingest or transactional queries.
- SingleStore’s Universal Storage allows users to get the TCO benefits of a columnstore database, with the performance and usability of memory-constrained rowstores like fast seeks and updates.
- Universal Storage allows applications to run operational transactions on data that cannot be stored in RAM at an affordable cost. Hash indexes and UPSERT ability on columnstore tables give users the ability to leverage disk for OLTP, HTAP and analytics all in one place
Error #3: The Table is Full
- MySQL users often come across this issue when they run out of memory or disk space
- Memory-related concerns typically arise because users have not allocated enough memory to handle both query processing as well as data storage.
- Disk-related issues are a bit easier to resolve given the lower TCO, however adding more disk may ultimately lead to a disproportionate amount of storage as it relates to the rest of the resources on the single MySQL VM. The InnoDB engine helps by compressing data by around 50%.
- SingleStore’s Universal Storage (i.e., columnstore) offers 75-90% compression, helping dramatically reduce the amount of disk required to hold your data. However, simply compressing data is not enough. For columnstore to provide best in class performance, the engine must leverage seekable encodings and vectorized execution. SingleStore does both.
- SingleStore offers a distributed, scalable alternative to MySQL. Universal Storage helps thousands of users support highly concurrent workloads with high compression rates.
- SingleStore offers many more unique differentiators such as Pipelines to rapidly ingest data from Kafka, S3, GCS, etc. -- with just a single SQL query!
- Try SingleStoreDB Cloud today with the help of one of our cloud engineers. Click here to access your \$500 in free credits!