database optimization

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
edgelink's picture

PHP / Magento Software Developer | EdgeLink

Employment type: 
Full time
Telecommute: 
Not allowed

We are seeking a Mid-Senior Level Software Engineer (PHP, Magento) to join a creative team working on client's e-commerce sites. You will be working as part of a team to build new PHP applications as well as maintaining and updating currently built sites. This is a fast paced environment where the focus is on customer delivery and product development. We are passionate about the work as well as writing good code. You must be a self-starter and able to work without supervision but still be able to collaborate with others when necessary.

Read more
mojopages's picture

BACK END DEVELOPER | MojoPages

Employment type: 
Full time
Telecommute: 
Allowed

Do you have the desire to join an exciting, fast paced startup that is rapidly growing into one of the most highly trafficked websites today? Ever scaled a website to the Alexa top 100? Ever worked with GWT, Ajax, Spring, Tomcat, Solr?

If you answered YES to these questions, we may have the perfect job for you....

Our organization is called MojoPages
Mojopages is a venture funded consumer facing internet company that competes in the $15 billion dollar local search market.

Read more
navaneeth'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?.

aisforaaron's picture

Drupal Setup Expert | National Audubon Society

Employment type: 
Contract
Telecommute: 
Not allowed

I'm looking for a Drupal consultant to come on-site (Edison, NJ) and review our current setup before we go into production. We are really looking for advice on security, server/database setup, SEO, database optimization and module tweaks/recommendations that may speed up our installation. We are using an IIS multisite setup with Domain Access and Taxonomy Access, with very few tweaks to installed modules. No custom modules yet.

Subscribe with RSS Syndicate content