How to improve database costs, performance and value
While databases are one of the foundational building blocks of enterprise IT, many organisations may find themselves blindsided by the difficulty of running them effectively. Poorly-optimised databases can become serious time sinks, incurring unnecessary costs and distracting IT teams from other important tasks.
Luckily for database administrators (DBAs), there are various tricks and techniques that can be employed to improve and speed up database management and create more efficient and cost-effective systems. Here are our top tips.
Automate management tasks
DBAs’ duties cover an enormous range of tasks, including everything from backup and recovery jobs to storage management and configuration – many of which are highly mundane and repetitive. This makes them ripe for automation. By automating these simple tasks, DBAs can free up their days for more rewarding, interesting and innovative activities that are a better use of their valuable time.
There are many options when it comes to automating database management tasks. Many DBAs will choose to write their own custom tools crafted to their specific needs, while others may rely on the automation tools that database management software vendors have built directly into their products. Whichever way you choose to go, automating elements of your database management can help you save both time and money, as well as reducing recovery time in the event of any failures.
Deploy containers and virtual machines
Virtualisation and multi-tenancy are nothing new in the worlds of enterprise IT and software development, but they can have big advantages for database management. If you’re running your databases on single-tenant servers, then porting them to VMs could bring big savings. For starters, this allows you to host multiple databases on the same infrastructure, which can cut down on licensing and hardware investment costs. It also offers more flexibility in porting, replicating and modifying databases, although some may find vertical scaling becomes challenging.
If you want even more portability, you may wish to consider containerising your databases. This works well as part of a DevOps environment and integrates nicely with both CI/CD pipelines and multi-cloud architectures. The lightweight nature of containers means you can run large amounts on a single server, and they also lend themselves to high levels of automation which (as we covered above) can have big benefits for cost and efficiency.
Optimise SQL queries
Not all database queries are created equal. In fact, inefficient or poorly structured SQL queries often take longer to run, and when run on live production databases they can introduce errors and cause outages for other database users. By putting in a little more legwork in fine-tuning their initial queries, database operators can speed up their operations to improve the long-term health and performance of their databases.
For example, when selecting fields as part of a query, it can be tempting to use SELECT* to quickly select all records, or using SELECT DISTINCT to identify unique results. However, this can lead to unnecessary processing if it’s being run on large databases. Instead, thinking about the results you’re looking for and structuring your query around those specific fields will cut down on the processing power required to run it. Similarly, using INNER JOINs instead of Cartesian Joins made using WHERE clauses can massively reduce the amount of work being done by the system.
Manage database growth
One of the biggest factors that affect both the performance and speed of a database is how large it is. The bigger it is, the longer it takes to search through it and deliver results from a query. Size also plays a factor in process and transaction costs for cloud-hosted databases, or in hardware upgrade cadences for on-premise systems.
The best way to ensure that your database doesn’t expand at an unsustainable rate is to set up your schema and validation rules so that it contains only data which is going to be necessary to its operation. Storing large amounts of infrequently or partially used records can increase the cost and the time it takes to run queries.
Retire old or unnecessary data
Keeping your database streamlined is a key part of making sure they remain efficient and cost-effective. However, even if you’re restricting database inputs to the bare minimum of fields that need to be there, the size of your database will inevitably swell over time. If you’re finding that your database is getting a bit unwieldy, it might be worth seeing if you can streamline it by removing old entries that no longer need to be in there.
The specifics of what records can safely be deleted and when will depend on the specific purpose of your database, but setting clear data deletion policies can help keep its size to a manageable level. Another added bonus is that this can ensure that your queries aren’t returning as many irrelevant junk results, and may even help with GDPR compliance in the case of databases containing personal information. Large tables can also be partitioned to help stop them from getting too big, and indexes should be monitored to ensure they’re still being used; any that aren’t can be removed to free up additional space.
Time your queries
Databases have many advantages, including that many of them can be used by a number of different applications and users for different things, and that many are multi-purpose. Despite this, it is necessary to think about the impact queries can have on performance for the rest of the business. If you’re running large, complex queries, for example, that need lots of processing power, it may cause other applications and queries to run slower.
Are you planning to run any particularly heavyweight queries? It may be worth considering how time-sensitive they are as if it isn’t urgent, it might be worth organising them to run outside of peak business hours when there will probably be less activity on the database. This helps to reduce the impact on the database’s efficiency and keep disruption to a minimum.
Perform regular audits
The tips we’ve mentioned above are a great place to start when improving your database. However, it is necessary to keep an eye on them and ensure they are thoroughly maintained. To keep your database in peak condition, make sure you schedule regular audits of things like input rules, data deletion policies, schema, and usage patterns. This will help ensure all of them are in line with organisational needs and that the database is serving.
It might not seem like a priority to make time for these kinds of tasks when there are so many other things to be done, but it can pay dividends in the long term if you carry out regular housekeeping of your database, where you could experience benefits like operational cost savings, faster performance, and fewer errors.
Ⓒ Future Publishing
Professional Development for IT professionals
The mission of the Irish Computer Society is to advance, promote and represent the interests of ICT professionals in Ireland. Membership of the ICS typically reduces courses by 20%. Find out more
Subscribers 0
Fans 0
Followers 0
Followers