Posted by Robert Half Technology on Friday, June 26, 2015 - 09:48 | Follow me
Is speed holding back your app’s performance? A fast web SQL database is critical to making web applications run smoothly. These five tips can help you to improve the performance of your web SQL database.
1. Invest in the right hardware
Skimping on essential hardware is an example of being penny-wise and pound-foolish.
This is especially true for CPUs, as some web SQL database systems are licensed per-core. It is therefore cheaper to upgrade to faster CPUs than add more CPU cores and the licenses for them. Working with your database administrator and system administrator will help you research potential hardware bottlenecks.
Here are some common signs that you need to upgrade your hardware for a faster web SQL database:
- Queries frequently locking on disk access are a sign your disk system is lagging.
- Long disk wait times measured in the operating system indicate a slow disk system.
- A CPU often running at high percentages indicates an overworked CPU. You need to upgrade to a faster CPU or add more CPUs.
- Frequent use of the swap file showing strain on memory capacity.
- The higher the ratio of physical disk reads to logical disk reads, the more likely it is that you need more memory. This is because databases cache data in memory, and if the database frequently accesses this information from disk, it does not have enough memory for caching.
- Analyze for network connectivity issues that may be causing the web servers and the web SQL database to have communication problems. The goal is to have high throughput and low latency.
2. Check for frequent queries
Monitor the health of your web SQL database system throughout the day by logging which queries are being run, and how often. Does it appear appropriate for the application’s actual usage? Excessive queries may indicate a need to be caching at the application level.
For instance, if you commonly retrieve the URL for the user’s picture, it would make sense to assign that task to a high-speed session cache such as Memcached on the application server instead of querying the database for it. These queries may not be bad queries on their own, but they can equate to the proverbial death by a thousand cuts.
3. Look for expensive queries
Some performance problems may be caused by queries that are slow or simply too demanding of the system. Finding your top offenders by CPU usage will show you queries that may need to be optimized. If they are already optimized, try to find a way to run them less frequently — for example, asynchronously from a user’s request or another novel method that can minimize the impact on the user experience.
Another way to corral your resource-gobbling queries is to look for queries that perform the most physical reads from disk. These types of queries are hampering your system by demanding too much from the disks and may need to be optimized.
4. Examine execution plans
Once you have identified negative queries, you should check their execution plans. The execution plans will show you precisely why your queries are running slowly. The execution plans may show that you need to make some key modifications, such as adding an index or query hints.
5. Look into your locks
An invaluable tool for evaluating slow database performance is your list of database locks. Examining the reasons behind queries locking will give you important insight into the operation of your database.
You may discover that you need to add CPU power or improve the disk system in your server. Some locks will indicate that you need to make adjustments to server settings. Other locks will indicate your application needs to be changed, such as allowing reads from dirty records on uncommitted transactions, in some situations.
Speeding up your web SQL database is an exercise in research, careful measurement and experimentation to find the right balance for your system. With patience, you’ll find the right balance and give your users the great experience they deserve.
What tricks do you have for making a web SQL database faster? Let us know in the comments below.