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
Comments
Slow query log and/or
Slow query log and/or newrelic can be pretty helpful. devel's query timer is just sampling the pages you visit - ideally you want to sample all page requests and see if there are any trends.
knaddison blog | Morris Animal Foundation
+1
+1 for NewRelic. I have had situations in the past where I was sure it was the database dragging down performance, only to enable NewRelic to find out that it's really PHP and rendering time. Even if you do the demo/free account, it can really help zoom in on the trouble spots. Just make sure not to leave the Drupal Modules function on long-term (newrelic.framework.drupal.modules = 0 to disable). I found the overhead to be too much for my liking, but I leave the rest of it on in prod permanently.
You may want to reduce
You may want to reduce long-query-time to 0 temporarily (or at least 1) to get a good amount of data in the slow query log. Then use Percona Toolkit:
http://www.percona.com/software/percona-toolkit/
specifically Query Digest:
http://www.percona.com/doc/percona-toolkit/2.0/pt-query-digest.html
and Query advisor:
http://www.percona.com/doc/percona-toolkit/2.0/pt-query-advisor.html
and Visual explain:
http://www.percona.com/doc/percona-toolkit/2.0/pt-visual-explain.html
Along with good-ole
EXPLAIN SELECT ...
http://dev.mysql.com/doc/refman/5.1/en/using-explain.html
To analyze the queries.
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
Help learning about MySQL Indexes
I have a number of resources that can teach you a lot about MySQL indexes.
Improving performance with better indexes presentation -- http://effectivemysql.com/presentation/improving-performance-with-better...
Explaining the MySQL Explain presentation -- http://effectivemysql.com/presentation/explaining-the-mysql-explain/
There are probably videos of these around as I have given each presentation a few times.
If you want to read about everything you need to know, then the book "Optimizing SQL Statements" was written exactly to address this single topic.
See http://effectivemysql.com/book/optimizing-sql-statements/
Regards
Ronald
DBTuner
DB Tuner only shows CCK fields that are used as a filter in views. So if no CCK fields show up, odds are you are not filtering your views based off a CCK field.
this generates a decent config
Starts you with a solid base so you can test Dalin's tips against production.
http://tools.percona.com/
Good stuff guys, im starting
Good stuff guys, im starting to dig into this as well for a game server we are setting up that will get quite a lot of writes and plenty of reads, but mainly though the services api endpoint returning json to our game client.
Are their any really NON DBA type of explanation of when to use indexes, is this mainly on content types with quires that are being made that joins in them?
for example, we have game assets table that has the stored assets a use has bought and then we have an inventory table with what you might have in inventory vs on self.. this gets hit quite a bit as a select when the user starts up the game, and then hit when her or she changes something in inventory, buys or sells something..
I know im bringing this down a level in techno terms but what things should we look out for, we mainly use phpmyadmin to look at the db.
We always do a quick peek at the main db, and look at the records , size and overhead columns.. but it would be nice to know where to dig deeper.
We have also enabled slow query logs and im reading up on Explain..
anyhow any pointers would be great as to when to use an index..
cheers thanks for all the good info.
Here's the best non-technical
Here's the best non-technical tutorial on indexes that I've seen:
http://www.alistapart.com/articles/indexing-the-web-its-not-just-googles...
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
This are all great resources.
This are all great resources. Thanks to everyone who answered (or will answer)
I'd ask more questions, but all of this is going to take a while to digest :)
Premium Drupal Commercial themes
Performance issues.
Best solution, is enable slow query log in my.cnf, having long query time = 5.
You will get all slow queries which need to be optimised.
One more resource
Hello,
Thank you for sharing info about great articles and books, added to bookmarks.
I found one more blog about it. It's quite new but promising:
http://www.dbasquare.com/2012/04/04/how-important-a-primary-key-can-be-f...
http://www.dbasquare.com/2012/04/06/optimizing-mysql-performance-with-ac...
Regards
If you site has a lot of inserts disable query cache
Hi.
Perconna mysql is at least in my tests 20-40% faster that oracle mysql server so I think that you could see some gain on just doing that change.
If your site has a lot of inserts or at least one insert per minute it is better to disable query cache completely since it gets invalidated per each insert so if you have a big query cache you will create to much I/O unnecessary using the useless query_cache(useful in very rare cases).
Comments base on your my.cnf.
PePe.