In-Disk vs. In-Memory Databases:
In general, today’s databases are divided into two categories, depending on where the data is stored: disk-based databases and main memory databases. Disk-based databases are more common, whereas in-memory databases are usually used for high performance solutions.
This categorization doesn’t mean however that in-disk databases do not use the memory, or that in-memory ones don’t use the disk. Naturally, for the sake of durability, when writing the data in a memory based database, it must also be written to disk (although not necessary for all data and index files, but at least some form of redo logs is required). In-disk databases can usually cache the disk data in-memory, depending on the available memory. This cached data can later be used to serve some queries faster – from memory rather than going to disk.
With this in mind, we could argue that in-disk vs. in-memory categorization is less about the physical location of the data, but rather more about where in the database architecture is the slowest link for serving queries.
In-Network Cloud Database:
Xeround’s Cloud database for MySQL apps is an in-memory, distributed database, where several replicas of the data reside on several machines. Because of the distribution element and replication, we could categorize it as an In-Network database.
And indeed, the gating factor where it comes to performance is the network.
In a regular DB, the application would connect to the database via the network, and then data is retrieved locally (either from memory or disk) and sent back to the application. (Sometimes, the client can save the network hop when collocating the DB and the application). With Xeround there are extra hops from the frontend to the backend – at least one in a simple read and sometimes more than one depending on the operation type.
Benefits and Considerations When Working with an In-Network Database:
Xeround’s distributed architecture allows us to scale beyond the boundaries of a single machine by harnessing the capacity of main memory and CPU of several machines. This both lifts the key limitation on in-memory databases (the available RAM of any single machine) and enables MySQL high availability by replicating the data to more than a single location.
This however implies that the lower boundary of how fast Xeround serves a query is set to four network hops: client à frontend à backend à frontend à client; whereas in a non-distributed database you could get a response to a query in two network hops (client à DB à client). In that case, if the database is served from memory, then the operation will most likely be quicker (primary key search on cached data), but if data is not cached and a disk seek is needed it will probably be slower compared to Xeround’s performance.
Xeround’s solution balances our distributed architecture – which enables elasticity and high-availability – with an in-memory database for improved performance.
The key thing you should be aware of when connecting your app to Xeround has to do with optimizing complex queries. As one of our customers put it: “You don’t give me any passes on inefficient queries but you also don’t ever refuse to serve them.” We do accept any queries you have, no matter how inefficient, but it could just take you longer. Wrong indexing will cause more network hops and will increase the response time (whereas, in traditional DBs this can sometime be handled by caching.)
Xeround is 100% MySQL compatible so that you do not need to change your code to use a cloud database, and we always strive to make the move to the cloud as plug-and-play and as simple as possible. This, however, doesn’t negate the need to optimize your SQL queries :) as is the best practice no matter what DB solution you use.
Some best practices to keep in mind:
While these may seem obvious, it’s always good to make sure that you follow these best practices:
- Be sure that you’re using indexes and avoid full-table scans.
- If you’re noticing a query takes longer than you would have expected, use EXPLAIN statement to make sure there are no full-table scans and create indexes as needed.
- BTREE and HASH indexing: the default type of indexing is BTREE. However, equality operations with high cardinality are served faster with HASH indexing.
- It is better to use “ORDER BY” by columns that are BTREE indexed.
- It is better not to store large BLOBs – the larger the record the longer the response time.
If you need any help or have specific questions regarding your indexes or common queries types- you can always email our support at support@xeround.com
You may also be interested in:
RSS
Email
