MySQL

Slow Queries In Drupal Can Often Be Cured By Indexes

Recently, we were reviewing the performance of a large site that has a significant portion of its traffic from logged in users. The site was suffering from a high load average during peak times.

We enabled slow query logging on the site for a entire week, using the following in my.cnf:

Contents: 

Tags: 

High server load because of repeating queries too many times

One of the suboptimal techniques that developers often use, is a query that retrieves the entire content of a table, without any conditions or filters.

For example:

SELECT * FROM table_name ORDER BY column_name;

This is acceptable if there are not too many rows in the table, and there is only one call per page view to that function.

However, things start to get out of control when developers do not take into account the frequency of these calls.

Here is an example to illustrate the problem:

Contents: 

Tags: 

Reduce your server's resource usage by moving MySQL temporary directory to tmpfs

For sites that have lots of slow queries, disk access is often the bottleneck. For these slow queries, MySQL writes temporary tables to disk, populates them with intermediate results, then query them again for the final result.

We all know that the disk is the slowest part in a computer, because it is limited by being mechanical, rather than electronic. One way of mitigating this is to tell MySQL to use memory rather than disk for temporary tables.

Contents: 

Tags: 

Presentation: HUGE! A Drupal site with 381 modules, 174GB MySQL database, and 200 million row tables

Here are the slides from my Drupal Camp Toronto 2012 session.

The site is ScienceScape.org, a repository of scientific research going back to the 19th century, down to the latest biotechnology and cancer release.

Update: You can watch a video of the presentation on Vimeo.

Contents: 

Tags: 

Bypassing Drupal core Node Access table joins to avoid outage caused by slow queries

A client site was facing intermittent outages, specially at peak hours.

We investigated the issue over a few days, and narrowed down the cause to certain slow queries, described in more details below.

They had tagadelic on every page, displaying a tag cloud, and from that, crawlers hit every term to paths like taxonomy/term/1581.

Slow queries causing server outage

Contents: 

Tags: 

Subscribe to RSS - MySQL