This group is dedicated to solutions and approaches for high traffic, high performing Drupal sites. As such, it will deal with a lot of information around the rest of a typical Drupal "stack" -- the operating system, web server, database, and PHP tweaks that combine to support the Drupal application.
This is not a support channel or a place to post bug reports.
If you think you have found a bug, use the issue queue for the appropriate project.
If you need support, see http://drupal.org/support for options.
Optimizing MySQL (indexes) for non-developers
Environment
I’m in the process of setting up a CentOS VPS running a moderately used D6 site (approximately 1000 visits/day). I’ve installed the entire stack including Apache, MySQL 5.5, and PHP 5.3. The VPS has a 3.6Ghz dedicated CPU, and 2.2GB Memory (provided through VPS.net)
I’ve installed Memcache and APC, which seem to be working fine. I’ve tuned MySQL using mysqltuner.pl and tuning-primer.sh. My current my.cnf is posted below. All tables are InnoDB.
Problem
My current issue is that when logged in, various pages tend to be very slow (occasionally taking over 10 seconds to generate). I have devel’s query log functionality enabled and it does not seem to be one specific query that causes the problem- it seems to be random queries that sometimes take several seconds. This especially seems to happen with admin pages.
Indexes?
After doing a bit of research, I believe I need to index various database tables. This site has 22 content types and 73 fields defined. My questions is how do I know what tables to index and what fields to index? I've looked into the DBtuner module, but that only shows core fields (as opposed to the CCK fields).
Does anyone have any tips or suggestions?
Below is my current my.cnf
[mysqld]
#added MH 1-17-12
#comment this out after troubleshooting
log-queries-not-using-indexes
## General
datadir = /var/lib/mysql
tmpdir = /var/lib/mysqltmp
socket = /var/lib/mysql/mysql.sock
skip-name-resolve
sql-mode = NO_ENGINE_SUBSTITUTION
#event-scheduler = 1
## Cache
thread-cache-size = 16
table-open-cache = 4096
table-definition-cache = 2048
query-cache-size = 96M
query-cache-limit = 5M
## Per-thread Buffers
sort-buffer-size = 1M
read-buffer-size = 1M
read-rnd-buffer-size = 1M
join-buffer-size = 2M
## Temp Tables
tmp-table-size = 256M
max-heap-table-size = 256M
## Networking
back-log = 100
max-connections = 25
max-connect-errors = 10000
max-allowed-packet = 16M
interactive-timeout = 3600
wait-timeout = 600
bind-address = 127.0.0.1 #added mh 1-24-12 for phpmyadmin
### Storage Engines
#default-storage-engine = InnoDB
innodb = FORCE
## MyISAM
key-buffer-size = 64M
myisam-sort-buffer-size = 128M
## InnoDB
innodb-log-file-size = 100M
innodb-log-buffer-size = 4M
innodb-file-per-table = 1
#innodb-open-files = 300
innodb_flush_log_at_trx_commit = 2
innodb_additional_mem_pool_size = 20M
# InnoDB caches. Increase if needed (defaults to 8M, should use as much as is available). MH 1-25-12
innodb_buffer_pool_size = 1024M
innodb_flush_method = O_DIRECT
## Replication
server-id = 1
#log-bin = /var/lib/mysqllogs/bin-log
#relay-log = /var/lib/mysqllogs/relay-log
relay-log-space-limit = 16G
expire-logs-days = 7
#read-only = 1
#sync-binlog = 1
#log-slave-updates = 1
#binlog-format = STATEMENT
#auto-increment-offset = 1
#auto-increment-increment = 2
## Logging
log-output = FILE
slow-query-log = 1
slow-query-log-file = /var/lib/mysqllogs/slow-log
log-slow-slave-statements
long-query-time = 2
[mysqld_safe]
log-error = /var/log/mysqld.log
open-files-limit = 65535
[mysql]
no-auto-rehash
High Performance Drupal 7 Authenticated User Benchmarks
Hi All,
I just thought you might be interested to see some performance benchmarks that I've produced recently whilst doing some work porting the Authcache module to D7.
I think the results are "pretty encouraging". What do you think :) ?
summary: ~1000 fold increase in authenticated user performance, 5 fold increase for anonymous users
1200 requests per second @ 2.5ms per request on a very modest single server.
|Benchmark|Requests|Concurrency|Total
Time Taken/s|Requests/s|Avg
Time Per
Request/ms|Time Per
Request
(across all)/ms|Longest
Request/ms|Completed
Confused LEMP vs LAMP same vps hardware LEMP is slower?
My Nginx setup, granted very basic start to it, no real caching set up yet... LAMP has really no special additions to it kinda a out of the box install.
Drupal sets ups are clones on both systems.
command for testing on both ab -c10 -n1000 http://localhost/
nginx result
Server Software: nginx/1.0.9
Server Hostname: localhost
Server Port: 80
Document Path: /
Document Length: 3255 bytes
Concurrency Level: 10
Time taken for tests: 78.190 seconds
Complete requests: 1000
Failed requests: 0
Measuring Memory and CPU usage during a benchmark
This is not exclusively "Drupal related", but it's still useful if you want to properly find bottlenecks during a benchmark.
I want to do a test with Ab (Apache Benchmark) and I would like to monitor the memory and CPU usage during that test. What do you use for that? top command line tool?
[ANN] MongoDrop: A simple distribution that uses MongoDB
High Performance Drupal meetup at Filter Digital in Culver City on February 7, 2012
| Video: The Magic of NewRelic, by Robert J. Brown: http://blip.tv/ladrupal/episode/5944185 Video: Drupal and MongoDB, by Ashok Modi: http://blip.tv/ladrupal/episode/5944184 |
The High Performance Drupal meetup now meets on the 1st Tuesday of the month. We're meeting again on February 7, 2012 and will be at Filter Digital, a digital solutions agency in Culver City located just two blocks from Media Temple, a familiar venue of Drupal meetups on the Westside.
LA Drupal meetups follow a basic format and start off with Drupal news and announcements, local job announcements, raffle prizes and full-length presentations. As always, we'll also have community Q&A, during which time anyone in attendance can ask questions, divine answers from the crowd and give brief, 5-10 minute-long presentations known as lightning talks.
We'll finish off the night with more networking and a Drupal After Dark at 9:30pm.
For any changes to our agenda, stay tuned to this meetup announcement or click the Sign up button below (or both!) to be notified when the agenda has been updated.
About High Performance Drupal
What is the High Performance Drupal meetup about? The "high performance" part of the name is a catch-all to describe Drupal scalability, performance and benchmarking but we also discuss hosting, infrastructure, deployment, monitoring and highly repeatable processes. Also, this meetup has been held at coffeeshops in the past and the addition of caffeine makes our meetings feel as if they go faster and faster as the night goes on.
This is an LA Drupal meetup that focuses exclusively on advanced topics and there will not be a Users Helping Users portion to the evening.
Thank you to our sponsors!
Light food and refreshments will be served throughout the evening courtesy of our food, drink and venue sponsor, Filter Digital, a digital solutions agency.
Our client list spans global brands and Fortune 100 companies to startups and boutique firms. Let us help you create a digital solution that fits your needs!
Our projector for the evening is being provided by Exaltation of Larks, a Drupal strategy, consulting, development and training company with a team of Drupal experts in Los Angeles.
We like doing hard things on the web and excel at creating and maintaining exceptional Drupal sites with unexceptional budgets.
Read more
Amazon DynamoDB
Interesting announcement from Amazon today: managed NoSQL via their DynamoDB: http://aws.amazon.com/dynamodb
More details:
http://allthingsdistributed.com/2012/01/amazon-dynamodb.html
The interesting part of this is the data being stored on SSD, low latency, and the ability to pre-provision capacity and usage. My initial thought about this as it applies to Drupal would be along the lines of the mongodb project. IE, a Drupal module and API to enable certain tables to be stored and queried from a DynamoDB database.
Read moreDrupal + High Concurrent Users and Node.js or Twisted?
We are in the process of using drupal with our new game server, this server will read and write a lot of the users game data, We have chosen to go with the basic pressflow and minimal modules enabled, some custom ones that work with the services api (its how we communicate with the client and server with JSON).
Someone mentioned to me that and im still trying to figure out the formula of well with a pure drupal site optimized you can only have so many concurrent users hitting the db before apache/php bottle neck comes in.
Read more
Memcache configuration options, using multiple memcache servers
I'm interested in finding out how people are configuring Drupal sites to make the best use of multiple memcache servers and distinct machines.
Topologies I've seen so far include:
- replicating the caches to multiple memcache servers
- storing separate caches on each memcache server.
I would also be interested in your experience or thoughts on using moxi with Drupal.
Some relevant discussions I've found so far include
Read moreRunning out of tricks on how to optimize my Drupal site
Hello,
I would like to seek your advice on what else that I need to do in order to speed up Drupal site. Its really slow for logged in users . The only thing that is good now is Boost , which also has some problems on its own that i worked around.
My site is on a dedicated server on HostGator. Here's what I have so far:
Installed Boost - running ok for anonymous users. Problems with the login block being visible after logging out. I solved this (almost) by ajaxifying the login block (i just wrote my custom code)
Read more