This post's content
In many cases, developers, DBAs and data analysts struggle with bad application performance and are feeling quite frustrated when their SQL queries are extremely slow, which can cause the entire database to perform poorly.
Luckily, there is a solution to this problem! In this article, we will briefly cover a few ways you can use to improve the overall database performance. In many cases, you’ll need to use one or more of these paths to resolve database performance issues.
Optimize Queries
In most cases, performance issues are caused by poor SQL queries performance. When trying to optimize those queries, you’ll run into many dilemmas, such as whether to use IN or EXISTS, whether to write a subquery or a join. While you can pay good dime on consulting services, you can also speed up SQL queries using query optimizers such as EverSQL Query Optimizer, which will both speed up the query and explain the recommendations, so you could learn for the future.
Essentially, EverSQL is one of the most effective online SQL query optimizers currently available; it is capable of optimizing not just MySQL, but MariaDB and PerconaDB queries as well - and you can try it out entirely for free!
Create optimal indexes
Indexing, when done properly, can help to optimize your query execution duration and increase overall database performance. Indexes accomplish this by implementing a data structure that helps to keep things organized and makes locating information easier; basically, indexing speeds up the data retrieval process and makes it more efficient, thereby saving you (and your system) time and effort.
Get a stronger CPU
The better your CPU, the faster and more efficient your database will be. Therefore, if your database underperforms, you should consider upgrading to a higher class CPU unit; the more powerful your CPU is, the less strain it will be under when tasked with multiple applications and requests. Also, when assessing CPU performance it’s important to keep track of all aspects of CPU performance, including CPU ready times (which can tell you about the times your system attempted to use the CPU but couldn’t because all of the CPU’s resources were too busy or otherwise occupied).
Allocate more memory
Similar to how having a CPU that’s not powerful enough can impact the efficiency of a database, so too can lack of memory. After all, when there is not enough memory available in the database to perform the work that is being asked of, database performance is understandably going to take a hit. Basically, having more memory available will help to boost the system’s efficiency and overall performance. A good way to check if you need more memory is to look at how many page faults your system has; if the number of faults is high (in the thousands, for example) it means that your hosts are running low on (or potentially entirely out of) available memory space. Therefore, when trying to improve database performance it’s important to both look at how much memory you have total as well as page faults (to determine if you need additional memory to improve efficiency).
In addition, you can consider increasing the amount of memory used by MySQL. Our recommendation is allow it to allocate 70% of the total memory (assuming the database is the only application on that server). You can modify the amount of memory allocated to the database using the innodb_buffer_pool_size key in MySQL’s configuration file, my.cnf.
Data defragmentation
If you’re having trouble with a slow database, another possible solution is data defragmentation. When many records are written to the database and time goes by, the records are fragmented in MySQL’s internal data files and on the disk itself. The defragmentation of the disk will allow grouping the relevant data together, so I/O related operations will run faster, which will directly impact on overall query and database performance. Also, on a somewhat related note it’s also important to have enough disk space in general when running a database; if you’re looking to truly optimize database performance, make sure to utilize disk defragmentation while also keeping plenty of free disk space available for your database.
Disk Types
Fetching the results of even a single query can require millions of i/o operations from the disk, depending on the amount of data the query needs to access for processing, and depending on the amount of data returned from the query. Therefore, the type of disks in your server can greatly impact the performance of your SQL queries. Working with SSD disks can significantly improve your overall database performance, and specifically your SQL query performance.
Database version
Another major factor in database performance is the version of MySQL you're currently deploying. Staying up to date with the latest version of your database can have significant impact on overall database performance. It's possible that one query may perform better in older versions of MySQL than in new ones, but when looking at overall performance, new versions tend to perform better.
Credits: Simon Mudd
Summary
Ultimately, whether you choose to utilize one or more of these methods, you can rest assured that there are plenty of options for improving your database performance. Test them one by one to see which one will have the largest impact on your database.
Interesting article.
I would say that CPU speed may not make as much difference as i/o speed, though of course this depends heavily on the queries you are generating. Normally i/o is more important to moving to SSDs makes a huge difference if you're using Hard disks. Not everyone is using SSDs though if you've moved already this does help performance a lot.
If you use SSDs then the innodb buffer pool size becomes less of a concern. Again it's all a matter of balancing the different settings.
Another thing that can make a difference is simply the version of MySQL you are using. Newer versions tend to be better overall though it has been highlighted that single query performance may be better on old versions. In any case staying up to date is good, you tend to have better monitoring capabilities with the newer versions of MySQL and more options to make things work better.
As with a lot of these things: database tuning depends on usage so you need to at least investigate where you're seeing bottlenecks and once you've identified them move to remove or reduce them.
Regards,
Simon