database optimization

We encourage users to post events happening in the community to the community events group on https://www.drupal.org.
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
navaneeth_r's picture

DB Impact

Hi,
On analyzing the process list in our heavy traffic site, we found numerous "lock/Unlock {cache} table " sleep queries.
Is it neccessary to lock cache tables whenever we update/insert cache.
In drupal 6 version of cache_set() the db_lock_table()/ db_unlock_table() has been removed.
Can we do this changes in drupal5?.
Will this affect sites default cache mechanism and performance?.

Read more
Subscribe with RSS Syndicate content