Search through our group's posts (events, discussions, jobs, etc.) and their comments here. Results are displayed in order of post date. Use the pager below to browse results.
Database Scalability
This group is an attempt to get a serious discussion started around database scalability with Drupal sites.
Right now, Drupal mostly relies on direct connections with mysql to generate Web pages. There is an internal caching mechanism which can help alleviate load, but which introduces some problems of its own (like the user login problem, where users have to reload a page after logging into the system). Typically, mysql optimizations and hardware concerns become a primary area of focus after Drupal has reached its caching limits. While there are not that many massively trafficed Drupal sites around (massive means greater than 100k hits an hour), they are coming, and it would be great to have some defined paths people could follow to build up their sites.
Read more
Tuning your application stack
We were doing OG2List performance tuning today. We cam up with a series of content, group, and user generating scripts:
http://cvs.drupal.org/viewcvs/drupal/contributions/modules/devel/generate
We starting monitoring what processes were CPU bound. We discovered MySQL was bound, which was great news! First we changed the my.conf to my-large.cnf to have a better performance. Then we ran the mysql process viewer so we could see the running mysql queries and their effect on MySQL. We identified the most resource intensive queries and then used MySQL explain.
We found that select distinct was causing a hidden Group By in addition to a Where. The net result was that we were creating temp table which is almost the worst case, excluding file creation in MySQL. We looked at the query and quickly added three indicies using alter table add index(column name). Presto! MySQL tuning meant it was no longer the bottle neck.
Read more
Fast private file transfers for Drupal
For those looking to have Drupal serve private files efficiently, I've posted a simple core patch that adds X-Sendfile support:
http://drupal.org/node/74472
Performance issues in the queue
Three weeks to go before the code freeze. Here are some issues to focus on:
- Split up the cache table: http://drupal.org/node/72290
- File based caching: http://drupal.org/node/45414
- Seems the above can be turned into a module because of this patch: http://drupal.org/node/67675
- Robots.txt patch: http://drupal.org/node/75916
- Set cached pages to a reasonable date: http://drupal.org/node/65017
- Better caching (cacheable blocks): http://drupal.org/node/72617
- Don't select uid=0 when starting sessions: http://drupal.org/node/76931
- Let admins set watchdog logging level (save
MySQL monitoring tools
Hi,
Here's a couple of tools that may help in monitoring MySQL performace:
- mysqlresources script - Measuring resources for a MySQL server on Linux (looking at /proc)
- innotop script - MySQL and InnoDB monitor (requires InnoDB)
Anyone using similar scripts to monitor high performance mysql servers?
Cheers
Read more
Block Cache Module for 4.7
Just want to let you high performancers know about the Block Cache module that I just committed. Could be handy tool to have in the arsenal.
I realize that Killes did a bunch of work in this direction as a core patch, but this is a module-based approach for 4.7 that could even be backported to 4.6.
Please post issues/patches if you've got ideas.
-jeff
Read moreAn Introduction to MySQL Performance Tuning
I thought I would share this with everyone as I think it's relevant to the group, even though it is sort of an advertisement.
An Introduction to MySQL Performance Tuning
Thursday October 19, 2006
Are you experiencing current performance bottlenecks? Designing a new application and want to know how best to structure your schema and index strategy for optimal performance? Interested in how to transform your SQL into faster, more efficient queries? Then this free web presentation is for you! You will get expert insight and learn best practices to help you identify those areas of database and application design that will give you the greatest benefits for performance.
Read moreApache server configurations for high availability, load balancing
Currently Drupal.org has multiple WebServers that I believe are synchronized to load balance and provide a degree of high availability.
I believe this is done by rsyncing the file directories served by the web. I know there have been criticism of this design on the infrastructure list. I am curious what configurations people are recommending for Apache servers. I am assuming DBs are on separate servers. I am assuming different web servers are used for static pages or cached pages, and for dynamic pages.
Are people using Rsync, NFS mounts, S3?
What has worked best for you and why does it meet your needs?
Read moreThe most expensive core query
According to my devel module logs and lots of watching the query lists, the query that costs us the most in all of Drupal core is this one:
SELECT DISTINCT b.* FROM blocks b LEFT JOIN blocks_roles r ON b.module = r.module AND b.delta = r.delta WHERE b.theme = S AND b.status = D
Let the brainstorming begin; what can we do to improve it? Are there enough indexes? Can we avoid the DISTINCT? Are there other options?
Read moreBuy more hardware or patch to Master (Write), Slave (Read), Slave (back-up)
Drupal suffers from database scalability issues due to the fact that reads and writes to the database are executed on the same server(s). In custom applications, database reads and writes may be separated to different database servers.
If you are going to make the move from one database server to two database servers, should you buy more hardware, or configure each database to be optimized for the kind of load (write, read, back-up) it has?
For example, MyISAM does table locks during writes which causes delays for reads from the same tables. One option is to use InnoDB tables for high volume write tables. This should reduce the waits.
Read more