indexes

mherchel's picture

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

Read more
mikeytown2's picture

Tuning MySQL - Adding indexes, lots of them: Shotgun Strategy

This discussion got me thinking...
http://groups.drupal.org/node/56438#comment-160418

So I decided to create some code that will auto add indexes to your CCK fields where there are none. Works for me (using MySQL), may not work for you. Also at the bottom is code to add indexes elsewhere in Drupal.
<?php
$ret = array();
// Add indexes to CCK fields
$result = db_query("SHOW TABLES LIKE 'content_%'");
while ($table = db_result($result)) {
if ($table == 'content_group') {
db_add_index($ret, 'content_group', 'weight', array('weight'));

Read more
Peter-gdo's picture

Using .htaccess Options Indexes versus WebFM versus an index.php for Direction Listings

I've run into the problem of letting authenicated users access /sites/default/files/folder1/folder2 and see a full directory listing. Typically I solve this with htaccess (Options Indexes, IndexOptions +ScanHTMLTitles -IconsAreLinks FancyIndexing and other values). However, I see that Drupal's webroot/.htaccess does not play nicely.

Read more
Subscribe with RSS Syndicate content