MySQL for Drupal optimization

Главные вкладки

Аватар пользователя potss potss 25 марта 2012 в 13:13

What can I do to optimize a MySQL database for a Drupal website to make it run faster?
Enable Query Caching, Use InnoDB Tables, & Double Check Expected Table Indexes
How to speed up your specific site will depend heavily on the nature of your site and the types of queries you are sending to MySQL. In general, the Drupal schema should include the commands to index the fields that would be most helpful. Your best bet for speeding things up on the MySQL side would be to try enabling MySQL query caching. This should increase speed somewhat. The speed increase you see will depend in part on the types of queries that you usually handle.

Additionally, make sure that you are using InnoDB tables instead of MyISAM tables. You may also want to take a look at your MySQL tables just to make sure that your tables are indexed as expected.

Additional options for tuning MySQL for Drupal is available(http://drupal.org/node/51263 Also, check out Tools, tips, and links on optimizing mysql:
http://drupal.org/node/85768.

==========
Adding Query Caching (http://nadeausoftware.com/articles/2007/03/speed_drupal_web_site_enablin...)

MySQL’s query cache is disabled by default. Enable it to speed up page load times by 5-15%. Use the MySQL Administrator application, available as a free download from MySQL.org.

1. Start MySQL Administrator.
2. Log in as your database server’s administrator (the database root account).
3. Windows:
1. Enable the cache:
1. Click on Startup Variables in the left-hand list.
2. Select the Performance tab at the top of the window.
3. Check the Cache Size checkbox to enable the setting.
4. Enter a cache size value, such as 32 Mbytes.
5. Click the Apply changes button to save the settings.
2. Restart the database server:
1. Click on Service Control in the left-hand list.
2. Click the Stop Service button to stop the server.
3. Click the Start Service button to start the server again.
4. Mac:
1. Enable the cache:
1. Click on the Options icon at the top of the window.
2. Select the Performance section from the pull-down menu at the top of the window.
3. Click on the pencil icon beside Cache Size to enable the setting.
4. Enter a cache size value, such as 32 Mbytes.
5. Click the Save button to save the settings.
2. Restart the database server:
1. Click the Service button at the top of the window.
2. Click the Stop server button to stop the server.
3. Click the Start server button to start the server again.

=============

Sources: http://nadeausoftware.com/articles/2007/03/speed_drupal_web_site_enablin...

http://drupal.org/node/85768

1. You can analyze your MySQL performance with the MySQL Performance Tuning Primer Script.

# cd /usr/local/src/
# wget http://day32.com/MySQL/tuning-primer.sh
# chmod u+x tuning-primer.sh
# ./tuning-primer.sh

It will ask for your database root user name and password and print out a list of recommendations. Was shocked to learned that on my VPS the cache was not even enabled - very helpful to know!

2. Next open your my.cnf file in pico or some kind of proper code/text editor:

Depending on the memory resources you have available you'll want to paste in something like these examples (adjust up or down depending on how your system differs, of course):

For a setup with 500mb of RAM your my.cnf file may look like this:

[mysqld]
max_connections = 150
max_user_connections = 150
key_buffer = 36M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 3M
table_cache = 1024
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 1800
connect_timeout = 10
max_allowed_packet = 1M
max_connect_errors = 1000
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
tmp_table_size = 16M

For a system with 256mb of ram it may look like this:

[mysqld]
max_connections = 75
max_user_connections = 75
key_buffer = 16M
myisam_sort_buffer_size = 32M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 1000
connect_timeout = 10

max_allowed_packet = 1M
max_connect_errors = 1000
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
tmp_table_size = 16M

Please note that every server configuration is going to differ and simply pasting these in may cause unexpected results.

3. Save your my.cnf file and restart mySQL. This can be done via your control panel or the command line (on some unixes: service mysqld restart otherwise /etc/rc.d/init.d/mysqld restart or /etc/init.d/mysqld restart)

Your new settings are now active and you can run the script from above again and see the difference in your results. After some experimenting I've found that it is useful to look at the script results right after making a change just to see if your modifications were recognized by the system and get the early returns from whether things were improved or not -- but, to get a truly accurate reading from the script you should check back in 24-48 hours after rebooting mysql (this is actually noted at the top of the script itself, but it doesn't really explain why) depending on your site traffic. Also, I've found that the way I've got Drupal set up it is particularly demanding in the tmp_table_size and table_cache areas (e.g., you may want to bump up the number for both of these areas in the settings above)

If you'd like to read up on more about mySQL tuning I suggest taking a look at these resources:
http://drupal.org/node/51263
http://day32.com/MySQL/
------------------------------------------------------------------------------------------------
MySQL InnoDB: performance gains as well as some pitfalls

In the vast majority of cases, web sites will be setup with the MyISAM engine for all the Drupal tables.
MyISAM Engine

MyISAM is the default engine on most MySQL installations, and is oriented towards read heavy workload. MyISAM started life for datawarehousing and analytics, where transactions are rarely needed. Its has no support for things that relational databases take for granted nowadays, such as lack of referential integrity, row level locking, ...etc.
InnoDB

For transactional workload, MySQL relies so far on InnoDB from InnoBase. InnoDB has all the features of a "real" relational database engine, including row level locking, referential integrity, ...etc..
Drupal and locking

Drupal, by default, caters for MyISAM, since this is how it started, and this is what most people have on their hosting machines. So, Drupal does table level locking, as per this code in database.mysql.inc:

<?phpfunction db_next_id($name) {
  $name = db_prefix_tables($name);
  db_query('LOCK TABLES {sequences} WRITE');
  $id = db_result(db_query("SELECT id FROM {sequences} WHERE name = '%s'", $name)) + 1;
  db_query("REPLACE INTO {sequences} VALUES ('%s', %d)", $name, $id);
  db_query('UNLOCK TABLES');

  return $id;
}?>

This code is used every time we assign a new id for an object, for example, a user, a node, a comment, a category term.

There is also this function:

<?phpfunction db_lock_table($table) {
  db_query('LOCK TABLES {'. db_escape_table($table) .'} WRITE');
} ?>

And this one to go with it:

<?phpfunction db_unlock_tables() {
  db_query('UNLOCK TABLES');
} ?>

These last functions are called in Drupal core (5.x) in the following functions:

<?php./includes/bootstrap.inc:387:  db_lock_table('variable');
./includes/cache.inc:94:  db_lock_table($table);
./modules/block/block.module:190:  db_lock_table('blocks');?>

The first one in bootstrap.inc is when setting a variable, which is usually not a frequent operation. The second one in cache.inc is in cache_set(), which can be a frequent operation for the filter cache, and page cache, and can cause contention.

There has been some work on eliminating locking in Drupal, and it should be available in Drupal 6.x.

How InnoDB can help with locking

InnoDB can help with locking because its locks are on the row level, not on the table level.

So, if a busy site has lots of people adding comments, and browsing, the node and comment modules call check_markup(), which in turn calls cache_get() and cache_set().

On a large site, I commented out the filter cache code altogether so as to eliminate the contention for that table.

Similarly, if the page cache is turned on by the site administrator, there could be contention on the table if there are lots of adds on it.

Auto Increment and Locking in InnoDB

Note that InnoDB still locks the entire table when there is and auto increment column. This is to avoid two inserts at the same time giving duplicate values for the auto increment column.

So, watch out for that caveat too.

InnoDB Pitfalls

However, all is not rosy with InnoDB. Because of its transactional nature, it has bottlenecks of its own. On MyISAM, doing a query that does SELECT COUNT(*) FROM {some_table}, is very fast, since MyISAM keeps the information in the index.

On InnoDB, this info is not stored in an index, and even the index and the data are kept in the same file. So, doing the same query on a table can incur a significant performance penalty.

To check what overhead this has, I wrote a simple test benchmark code. I duplicated a client node table that has 20,243 rows from MyISAM to InnoDB.

On a quiescent AMD 64 machine with MySQL server 5.0.24, doing a SELECT COUNT(*) FROM node takes 0.835 milliseconds on MyISAM, while on InnoDB it takes 12.292 milliseconds!

Running the query a second time causes comparable numbers, with MyISAM taking 0.331 ms, and InnoDB taking 0.488 ms. However, this is due to the query cache having the results in there.

In practice, this affects Drupal in some blocks, like the "popular content" block for example. A client had to turn that block off to save some 1200 milliseconds per page load after they converted to InnoDB.
-----------------------------------------------------------------------------------------
MySQL my.cnf configuration for a large Drupal site

For a large web site with hundreds of thousands of page views per day, it is important to increase certain parameters, such as the query cache, join buffers, key buffer, ..etc.

The following my.cnf file is from an Ubuntu server (Debian derived) with dual CPUs, and 2 GB of RAM. It assumes that you are using MyISAM tables only and not InnoDB.

Depending on what modules you have, you may need to adjust some of those parameters, but this is definitely a good start.

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
!>
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid


socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
language        = /usr/share/mysql/english
skip-external-locking
old_passwords   = 1
bind-address            = 127.0.0.1

key_buffer              = 16M
key_buffer_size         = 32M
max_allowed_packet      = 16M
thread_stack            = 128K
thread_cache_size       = 64
query_cache_limit       = 8M
query_cache_size        = 64M
query_cache_type        = 1
join_buffer_size        = 512K

max_connections         = 150
log_slow_queries        = /var/log/mysql/mysql-slow.log
skip-bdb
skip-innodb

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[geshifilter-mysql]&#10;#no-auto-rehash # faster start of mysql but no tab completition&#10;&#10;[isamchk]&#10;key_buffer              = 16M

<strong>Slow MySQL queries on a multi-server setup: use compression</strong> A few months ago, we saw something strange at a client. They were facing slow cache queries, such as the following ones. 2507.25 1 cache_get SELECT data, created, headers, expire FROM cache_menu WHERE cid = '1:en' 1303.68 1 cache_get SELECT data, created, headers, expire FROM cache WHERE cid = 'content_type_info' They are running MySQL on a separate box from the one that has PHP running. Running the same SQL locally on the MySQL box did not show the same slow performance as running it from the PHP box. Upon closer investigation, it was found to be that they had the link between the boxes set to 10MBps only, instead of the full 1000MBs that it can do. Once the servers were configured for the proper speed, performance became much better. What is interesting is that there is a MySQL option to get around such a problem on the software level. MySQL provides a flag for the mysql_connect() function that would compress the data sent. See MySQL Performance Blog: Large result sets vs. compression protocol. This has also been reported more than 3 years ago for Drupal in issue #11891. So, I created a patch for Drupal 7 (applies to Drupal 6 RC1 as well) that you can download and apply. Ideally, this would be a settings.php option that turned on for large sites that are on more than one box. <strong>My.cnf</strong> #&#10;# The MySQL database server configuration file.&#10;#&#10;# You can copy this to one of:&#10;# - &quot;/etc/mysql/my.cnf&quot; to set global options,&#10;# - &quot;~/.my.cnf&quot; to set user-specific options.&#10;# &#10;# One can use all long options that the program supports.&#10;# Run program with --help to get a list of available options and with&#10;# --print-defaults to see which it would actually understand and use.&#10;#&#10;# For explanations see&#10;# http://dev.mysql.com/doc/mysql/en/server-system-variables.html&#10;&#10;# This will be passed to all mysql clients&#10;# It has been reported that passwords should be enclosed with ticks/quotes&#10;# escpecially if they contain &quot;#&quot; chars...&#10;# Remember to edit /etc/mysql/debian.cnf when changing the socket location.&#10;[client]&#10;port                = 3306&#10;socket               = /var/run/mysqld/mysqld.sock&#10;&#10;# Here is entries for some specific programs&#10;# The following values assume you have at least 32M ram&#10;&#10;# This was formally known as [safe_mysqld]. Both versions are currently parsed.&#10;[mysqld_safe]&#10;socket           = /var/run/mysqld/mysqld.sock&#10;nice          = 0&#10;&#10;[mysqld]&#10;#&#10;# * Basic Settings&#10;#&#10;user               = mysql&#10;pid-file    = /var/run/mysqld/mysqld.pid&#10;socket         = /var/run/mysqld/mysqld.sock&#10;port          = 3306&#10;basedir              = /usr&#10;datadir              = /var/lib/mysql&#10;tmpdir             = /tmp&#10;language  = /usr/share/mysql/english&#10;# lc-message-dir is unknown to MySQL 5.1&#10;#lc-messages-dir       = /usr/share/mysql&#10;skip-external-locking&#10;old_passwords = 1&#10;&#10;#&#10;# Instead of skip-networking the default is now to listen only on&#10;# localhost which is more compatible and is not less secure.&#10;bind-address           = 127.0.0.1&#10;#&#10;# * Fine Tuning&#10;#&#10;key_buffer          = 64M&#10;key_buffer_size     = 32M&#10;max_allowed_packet  = 16M&#10;thread_stack              = 192K&#10;thread_cache_size   = 286&#10;&#10;# This replaces the startup script and checks MyISAM tables if needed&#10;# the first time they are touched&#10;myisam-recover         = BACKUP&#10;max_connections        = 150&#10;max_user_connections   = 150&#10;table_cache            = 1024&#10;#thread_concurrency     = 10&#10;#&#10;# * Query Cache Configuration&#10;#&#10;query_cache_limit            = 8M&#10;query_cache_size        = 64M&#10;query_cache_type        = 1&#10;myisam_sort_buffer_size = 128M&#10;#&#10;# * Logging and Replication&#10;#&#10;# Both location gets rotated by the cronjob.&#10;# Be aware that this log type is a performance killer.&#10;# As of 5.1 you can enable the log at runtime!&#10;#general_log_file        = /var/log/mysql/mysql.log&#10;#general_log             = 1&#10;#&#10;# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.&#10;#&#10;# Here you can see queries with especially long duration&#10;log_slow_queries   = /var/log/mysql/mysql-slow.log&#10;#long_query_time = 2&#10;#log-queries-not-using-indexes&#10;#&#10;# The following can be used as easy to replay backup logs or for replication.&#10;# note: if you are setting up a replication slave, see README.Debian about&#10;#       other settings you may need to change.&#10;#server-id            = 1&#10;#log_bin                        = /var/log/mysql/mysql-bin.log&#10;expire_logs_days     = 10&#10;max_binlog_size         = 100M&#10;#binlog_do_db               = include_database_name&#10;#binlog_ignore_db   = include_database_name&#10;#&#10;# * InnoDB&#10;#&#10;# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.&#10;# Read the manual for more InnoDB related options. There are many!&#10;#&#10;# * Security Features&#10;#&#10;# Read the manual, too, if you want chroot!&#10;# chroot = /var/lib/mysql/&#10;#&#10;# For generating SSL certificates I recommend the OpenSSL GUI &quot;tinyca&quot;.&#10;#&#10;# ssl-ca=/etc/mysql/cacert.pem&#10;# ssl-cert=/etc/mysql/server-cert.pem&#10;# ssl-key=/etc/mysql/server-key.pem&#10;join_buffer_size = 4M&#10;read_buffer_size = 4M&#10;sort_buffer_size = 4M&#10;interactive_timeout = 25&#10;wait_timeout = 2000&#10;connect_timeout = 10&#10;max_connect_errors = 1000&#10;tmp_table_size = 16M&#10;[mysqldump]&#10;quick&#10;quote-names&#10;max_allowed_packet   = 16M&#10;&#10;[mysql]&#10;#no-auto-rehash      # faster start of mysql but no tab completition&#10;&#10;[isamchk]&#10;key_buffer               = 16M&#10;&#10;#&#10;# * IMPORTANT: Additional settings that can override those from this file!&#10;#   The files must end with &#039;.cnf&#039;, otherwise they&#039;ll be ignored.&#10;#&#10;!includedir /etc/mysql/conf.d/&#10;[/geshifilter-mysql]

Комментарии

Аватар пользователя potss potss 25 марта 2012 в 14:15

ну если найду ешё что-то, что мне нужно будет для себя, то скопирую.
мне здесь удобнее работать и хранит инфу кому не нравиться может ни читать.