One issue that can cause your website to experience performance problems is slow database queries. Many contributed and custom modules regularly create and submit SQL queries to your database, and any of these queries can affect your website's overall performance.
Examining your website for query performance issues
Checking for slow queries is a good place to start your performance investigation. Although there are many modules that create queries, one particular common source of queries comes from using the Views module.
When you're narrowing down where a problem might be, consider what kind of queries are used most heavily. There are three major types of queries you're likely to be using:
SELECTqueries pull info for display without changing it.
UPDATEqueries alter existing information.
DELETEqueries remove information from the database.
When you're investigating a query, you can look at the list of fields or tables contained in the first part of the query. Try to match these against the names of the fields in your website's nodes, users, and entities. What portion of your website displays data that uses these fields? Frequent culprits will be items like blocks, views, panels, and calendars. You can then try to use that to determine where large groups of data may be coming from, and what tables may need investigating.
If looking at the fields and tables does not allow you to pinpoint where in your website the query may be coming from, compare the number of results returned with various components of your website to see if the number of results match.
If you're unable to determine the source of the query, open a ticket with Acquia Support.
Resolving query performance issues
After you've determined that a query is all or part of a performance issue, there are several approaches you can take to remedy it:
- Use caching to reduce the number of times the query is called
- Alter the query to make it complete more quickly
- Remove the portion of your website that is generating the query
- Monitor the server
Caching is recommended across your website, on any website that gets more than a few visitors. Acquia Support frequently sees issues with websites that have uncached blocks, panels, or views. Views and Panels, particularly, do not turn on caching by default, and you must turn on caching for each specific view or panel individually.
Alter the query
Depending on how the query is generated, it may be possible to alter it using a hook provided by Drupal or the contributed module that generated the query. There are several methods you can try when altering a query:
- The Views module does a best guess on what it thinks your query should be. It can use a table that is suboptimal, or gather more information than necessary. Slow queries are frequently caused by combining two or more large tables together using a JOIN. Review the number of joins in your query, and determine if the query is pulling more information than is actually needed. If you can simplify the query by using fewer joins or different types of join, this may help reduce the problem. See this link for documentation on the JOIN syntax.
- Some tables may not have indexes on them, which greatly slows down performance. Check your tables for indexes, and consider adding them in appropriate cases. Our operations team checks for errors resulting from missing indexes and if they detect this problem, a member of the Acquia Support team will reach out to you to advise you on the next steps.
- Queries that involve
COUNT(*)to count all rows in a table can be slow if a table is very large. Acquia Support frequently sees
COUNT(*)queries in the default pagers for Views. If your website uses pagers in Views and your slow query is for the pager, we strongly recommend the Views Lite Pager module, which uses queries that are far less resource-intensive.
If you're unable to determine if your query can be altered, open a ticket with Acquia Support to get more information about the source of the problem. Acquia Support can help you determine if a quick fix is possible, or put you in touch with your account manager to learn more about how Acquia’s Professional Services team can assist you with an in-depth optimization of your use of SQL queries.
Disable or remove the query
If it's not possible to alter a query or cache it, you may need to consider temporarily disabling it to preserve your website's uptime. Acquia Support may be able to suggest what portion of your website to disable or remove, while a Professional Services engagement may be required to perform an in-depth optimization.
Monitor the server
As part of our Acquia Cloud hosting, we offer access to New Relic and Traceview, two monitoring services for dedicated hardware that can help you diagnose website problems. These services will also alert you when your website is experiencing problems. To enable either of these services, the primary contact on the account should sign in to their Acquia account at http://insight.acquia.com and visit the Add-Ons tab. To learn more about these services, visit Using TraceView and New Relic. For more about Traceview, visit Drupal Performance Profiling With Tracelytics.