Although I am sure this is a perennial topic in this group, I would like to start (yet another) discussion on optimization of my.cnf. I really don't understand mysql at all, and I have found even the slightest change to the my.cnf settings has a massive impact on my server performance.
I could really use some advice on settings from the group. Hopefully this threat can grow and be useful to others in the future.
My setup:
Site is planned to be used as a social network, expectation is that logged in members can add a variety of content types. Various views on panel pages display content. Some views have exposed filter criteria, others are more "static" and perhaps candidates for views caching.
Focus is on serving user generated content to logged in members, limited exposure of content to anon web surfers.
Server:
A VPS from Godaddy. Centos 6 server running Apache/mysql 5.1x, PHP, etc.
APC running, set to 384MB. Seems to be using around half of that on any given day.
2GB RAM.
Limited use for server other than the one D7 site.
D7 with over 150+ modules. Just started enabling selective Views caching.
Mysql:
I really don't have a clue what I am doing with the db. I don't know if/how Innodb should be enabled?
I have tried the tuning-primer.sh script a few times. Load on server is still very light as it is barely into beta testing. I don't know if these stats are usable as the server load grows? In any case, output of recent tuning-primer.sh is included here for your viewing enjoyment...
-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -
MySQL Version 5.1.61 x86_64
Uptime = 2 days 15 hrs 34 min 33 sec
Avg. qps = 53
Total Questions = 12312015
Threads Connected = 1
Server has been running for over 48hrs.
It should be safe to follow these recommendations
To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service
SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10.000000 sec.
You have 0 out of 12312036 that take longer than 10.000000 sec. to complete
Your long_query_time seems to be fine
BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.1/en/point-in-time-recovery.html
WORKER THREADS
Current thread_cache_size = 8
Current threads_cached = 7
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine
MAX CONNECTIONS
Current max_connections = 151
Current threads_connected = 1
Historic max_used_connections = 18
The number of used connections is 11% of the configured maximum.
Your max_connections variable seems to be fine.
No InnoDB Support Enabled!
MEMORY USAGE
Max Memory Ever Allocated : 650 M
Configured Max Per-thread Buffers : 2.70 G
Configured Max Global Buffers : 320 M
Configured Max Memory Limit : 3.02 G
Physical Memory : 2.00 G
Max memory limit exceeds 90% of physical memory
KEY BUFFER
Current MyISAM index space = 52 M
Current key_buffer_size = 256 M
Key cache miss rate is 1 : 1710
Key buffer free ratio = 74 %
Your key_buffer_size seems to be fine
QUERY CACHE
Query cache is enabled
Current query_cache_size = 64 M
Current query_cache_used = 29 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 45.79 %
Current query_cache_min_res_unit = 4 K
MySQL won't cache query results that are larger than query_cache_limit in size
SORT OPERATIONS
Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 12 M
Sort buffer seems to be fine
JOINS
Current join_buffer_size = 132.00 K
You have had 225 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.
Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.
OPEN FILES LIMIT
Current open_files_limit = 1561 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
You currently have open more than 75% of your open_files_limit
You should set a higher value for open_files_limit in my.cnf
TABLE CACHE
Current table_open_cache = 700 tables
Current table_definition_cache = 512 tables
You have a total of 808 tables
You have 700 open tables.
Current table_cache hit rate is 6%
, while 100% of your table cache is in use
You should probably increase your table_cache
You should probably increase your table_definition_cache value.
TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 16 M
Of 17728 temp tables, 22% were created on disk
Created disk tmp tables ratio seems fine
TABLE SCANS
Current read_buffer_size = 4 M
Current table scan ratio = 13 : 1
read_buffer_size seems to be fine
TABLE LOCKING
Current Lock Wait ratio = 1 : 86
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.
#
And finally, the my.cnf file I am currently using:
Example MySQL config file for large systems.
#
This is for a large system with memory = 512M where the system runs mainly
MySQL.
#
MySQL programs look for option files in a set of
locations which depend on the deployment platform.
You can copy this option file to one of those
locations. For information about these locations, see:
http://dev.mysql.com/doc/mysql/en/option-files.html
#
In this file, you can use all long options that a program supports.
If you want to know which options a program supports, run the program
with the "--help" option.
The following options will be passed to all MySQL clients
[client]
password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock
Here follows entries for some specific programs
The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer_size = 256M
max_allowed_packet = 16M
table_open_cache = 700
table_definition_cache = 512
sort_buffer_size = 2M
read_buffer_size = 4M
read_rnd_buffer_size = 12M
myisam_sort_buffer_size = 128M
thread_cache_size = 8
query_cache_size= 64M
Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
Don't listen on a TCP/IP port at all. This can be a security enhancement,
if all processes that need to connect to mysqld run on the same host.
All interaction with mysqld must be made via Unix sockets or named pipes.
Note that using this option without enabling named pipes on Windows
(via the "enable-named-pipe" option) will render mysqld useless!
skip-networking
Replication Master Server (default)
binary logging is required for replication
log-bin=mysql-bin
binary logging format - mixed recommended
binlog_format=mixed
required unique id between 1 and 2^32 - 1
defaults to 1 if master-host is not set
but will not function as a master if omitted
server-id = 1
Replication Slave (comment out master section to use this)
#
To configure this host as a replication slave, you can choose between
two methods :
#
1) Use the CHANGE MASTER TO command (fully described in our manual) -
the syntax is:
#
CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,
MASTER_USER=, MASTER_PASSWORD= ;
#
where you replace , , by quoted strings and
by the master's port number (3306 by default).
#
Example:
#
CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
MASTER_USER='joe', MASTER_PASSWORD='secret';
#
OR
#
2) Set the variables below. However, in case you choose this method, then
start replication for the first time (even unsuccessfully, for example
if you mistyped the password in master-password and the slave fails to
connect), the slave will create a master.info file, and any later
change in this file to the variables' values below will be ignored and
overridden by the content of the master.info file, unless you shutdown
the slave server, delete master.info and restart the slaver server.
For that reason, you may want to leave the lines below untouched
(commented) and instead use CHANGE MASTER TO (see above)
#
required unique id between 2 and 2^32 - 1
(and different from the master)
defaults to 2 if master-host is set
but will not function as a slave if omitted
server-id = 2
#
The replication master for this slave - required
master-host =
#
The username the slave will use for authentication when connecting
to the master - required
master-user =
#
The password the slave will authenticate with when connecting to
the master - required
master-password =
#
The port the master is listening on.
optional - defaults to 3306
master-port =
#
binary logging - not required for slaves, but recommended
log-bin=mysql-bin
Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql
You can set .._buffer_pool_size up to 50 - 80 %
of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M
Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
Remove the next comment character if you are not familiar with SQL
safe-updates
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
Thanks in advance!
Comments
If you want to know how to
If you want to know how to configure my.cnf with good default values I recommend:
https://tools.percona.com/
Once you have the server up and running you'll need a way to determine where your performance bottlenecks are. A good tool for this is New Relic:
http://newrelic.com/
Or using the performance data that Devel module can show at the bottom of the page:
http://drupal.org/project/devel
Or profiling with XHProf
http://drupal.org/project/XHProf
http://techportal.inviqa.com/2009/12/01/profiling-with-xhprof/
If you find that MySQL is indeed a bottleneck (don't just assume!), then one tool at your disposal is the MySQL query log:
http://www.techiegyan.com/2009/06/24/setting-up-slow-query-log-mysql/
which can be analyzed with a tool like:
http://www.percona.com/doc/percona-toolkit/2.1/pt-query-digest.html
which is included in:
http://www.percona.com/software/percona-toolkit
Once you've found a problem query you should use MySQL EXPLAIN to figure out why it's slow:
http://www.lornajane.net/posts/2011/explaining-mysqls-explain
Once you've done the easy stuff (like adding indexes to the table), the easiest way to fix a slow query is to not run it. Setup caching at various levels (Page caching, block caching, views caching) to ensure that it rarely gets run.
In my experience database tuning scripts (like tuning-primer.sh that you tried) have limited usefulness. While they can sometimes offer good suggestions to increase certain buffers, they can't know when its too much. At some point increasing a particular buffer will degrade performance because it adds time to setup the larger buffer; or degrade scalability as MySQL claims more and more RAM that is rarely being used. Always compare performance/scalability before and after using one of these scripts to ensure that you've actually made things better.
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
Thanks for the update dalin
The big challenge I am noticing in my case is memory usage. It doesn't take much load for my server to chew through all 2 GB of RAM, despite really only running the D7 site. It seems like much of this memory is being used by mysql.
There is an email server, and that is only to support the site admin.
I'm just starting to apply views caches where possible. I have several views with exposed filters and I don't think any of those are candidates for views caching?
Thanks again for the links.
I am reading this at the moment:
http://www.mysqlperformanceblog.com/2012/03/21/troubleshooting-mysql-mem...
If MySQL is chewing up your
If MySQL is chewing up your RAM, make sure to adjust my.cnf so that the max memory for MySQL is less, i.e. less table/query caching and smaller index. It will make your queries slower, but you should avoid hitting the database where you can, anyways.
The scripts already gives a hint:
"Max memory limit exceeds 90% of physical memory"
This will make your server SWAP to a crawl. (It also says you do not have support for InnoDB, while D7 uses InnoDB by default).
Thanks Martijin
I was hesitant to lower the various memory values for things such as "key_buffer_size = 256M", but I did note a massive improvement in free -m when I lowered this value from a previous 512M setting.
I will go through the various settings and try lower values, as you say the trade-off may be slower queries, but at least the server may stop having heart attacks. Is that a fair summary of your comment?
I don't quite know how I ended up with myisam instead of innodb tables in the db. I also don't understand how/if the tables can be changed to Innodb at this point?
Thanks again, great input, hopefully will be helpful to others as well!
Disclaimer: I am no export on
Disclaimer: I am no export on MySQL, or even server administration. I just picked up a few things along the way, so what I am about to tell you may or may not be correct :-)
MySQL keeps track of various variables, such as number of queries, read indexes, etc. The tuner script will use these variables to determine whether your settings are sane, or could use some tweaking. For example, if these variables show your reading a lot of tables from disk, the tuner script will tell you to increase the table caching. Queries differ on every setup and for each application, therefor tuning is a necessary step on each server.
Obviously MySQL would be fastest if all tables can be kept in memory, but that would require lots of RAM. Therefor it will always be a tradeoff between speed and RAM usage. MySQL will also gladly use all the RAM you tell it to use, even if this means reverting to virtual memory (swap). This is slow, and should be prevented at any time. Swap is only there for excessive memory usage, at peak points when RAM is full. Therefor, my.cnf should be tuned to use a maximum amount of memory, i.e. your 2G, minus the RAM Apache/PHP (and other processes) will need. Currently, you told MySQL it could use over 3G of memory, while only 2G is available, meaning you will have at least 1G of swap usage. Tell MySQL to take a max of 750MB-1G and you will begin to see more response.
Oh and about the InnoDB
Oh and about the InnoDB versus MyISAM: D7 uses InnoDB by default, because this format has some advantages over MyISAM (per-row locking, transations, etc.). I think InnoDB uses more memory though, and my.cnf may look different when using InnoDB than when using MyISAM. I always ask my hosting party for InnoDB and they tune the basic settings, and I tune the settings after the site is live for a few days. Then it's something I check up on regularly, say every month or so.
Converting to InnoDB is fairly easy, you could use PHPMyAdmin for it, but I would not do it on a live database. And always backup before :-) I don't think D7 uses any actual InnoDB specific settings, so just changing the table format should be sufficient. But do a Google search before you actually do it ;-)
Thanks Martijin, think you've hit nail on head
Expertise is much appreciated, takes a group to figure this stuff out!
I noticed the 3GB entry in the tuner script as well, but I don't quite understand how my.cnf is allowing mysqld to try to use 3GB of RAM?
Do you think it is another setting in another file that I am missing?
From tuner script...
MEMORY USAGE
Max Memory Ever Allocated : 650 M
Configured Max Per-thread Buffers : 2.70 G
Configured Max Global Buffers : 320 M
Configured Max Memory Limit : 3.02 G
Physical Memory : 2.00 G
Max memory limit exceeds 90% of physical memory
A commonly used way of
A commonly used way of calculating is: key_buffer_size + (read_buffer_size + sort_buffer_size) * max_connections = K bytes of memory
Decreasing either will decrease the total memory usage. I would first decrease the key_buffer_size, half it, and work your way from there.
Useful Link to sample my.cnf
I went through the sample my.cnf on this site and found it helpful:
http://nakuls77.wordpress.com/2008/09/14/standard-mysql-mycnf-configurat...
My current my.cnf file follows. I noted a dramatic drop in memory usage, but again, server is very lightly loaded in beta test stage. Not sure about performance or scaling. Also, going to look into converting tables to Innodb.
Changed sections only displayed below:
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=3000
max_user_connections=600
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
key_buffer=128M # 64M for 1GB, 128M for 2GB, 256 for 4GB
join_buffer_size=2M # 1M for 1GB, 2M for 2GB, 4Mfor 4GB
max_allowed_packet=32M
table_cache=1024
sort_buffer_size=2M # 1M for 1GB, 2M for 2GB, 4M for 4GB
read_buffer_size=2M # 1M for 1GB, 2M for 2GB, 4M for 4GB
read_rnd_buffer_size=1536K # 768K for 1GB, 1536K for 2GB, 3072K for 4GB
max_connect_errors=10
thread_concurrency=4
myisam_sort_buffer_size=128M # 32M for 1GB, 64M for 2GB, 128 for 4GB
skip-locking
server-id=1
#
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
Remove the next comment character if you are not familiar with SQL
safe-updates
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 32M
write_buffer = 32M
[mysqlhotcopy]
interactive-timeout
Another link
Might be worth taking a look at as well:
http://2bits.com/articles/mysql-my-cnf-configuration-for-a-large-drupal-...
Current my.cnf file
Thanks for all the input. Below is my updated my.cnf file. No comparison, memory usage has dropped from 2GB+ to less than 600MB for the Server, and that is 100MB or so allocated to webmin.
Still lots of tuning needed, but crisis is over. I will update as I make performance related changes to my.cnf. Additional comments most welcome.
Please note that this my.cnf is for a db running myisam NOT innodb. Innodb, as Martijn pointed out, is default for D7. When I change the db tables to Innodb I will post my updated my.cnf.
my.cnf file:
#
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
query_cache_limit=8M
query_cache_size=32M
query_cache_type=1
max_connections=250
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
key_buffer=64M
join_buffer_size=1M
max_allowed_packet=16M
table_cache=1024
sort_buffer_size=2M # 1M for 1GB, 2M for 2GB, 4M for 4GB
read_buffer_size=2M # 1M for 1GB, 2M for 2GB, 4M for 4GB
read_rnd_buffer_size=1536K # 768K for 1GB, 1536K for 2GB, 3072K for 4GB
max_connect_errors=10
thread_concurrency=4
myisam_sort_buffer_size=64M # 32M for 1GB, 64M for 2GB, 128 for 4GB
skip-locking
skip-innodb
server-id=1
thread_concurrency = 8
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
Remove the next comment character if you are not familiar with SQL
safe-updates
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 32M
write_buffer = 32M
[mysqlhotcopy]
interactive-timeout
#
How To Get Values?
I like the brief calculation Martijn Houtman posted in http://groups.drupal.org/node/286233#comment-895543 but I am a bit confused, as I cannot find values for sort_buffer_size or read_buffer_size either in my my.cnf file, nor in the output of SHOW STATUS;
If I do not specify these values in the configuration file, how do I know what they are set to? How can I adjust these values when I don't know what they are to begin with?
Thanks.
You might want to read up on
You might want to read up on http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html
Some defaults are 'compiled in', the rest is set in the config file.
How Useful Is The Query Cache?
I'm also curious how useful MySQL's Query Cache is to most Drupal sites.
I was surprised to run through Percona's config tool at https://tools.percona.com/ and find that it suggested
query_cache_type = 0;
I had come across the following calculation somewhere in my MySQL configuration journey, used to determine if your Query Cache is in fact useful or not:
To see how well your QC is performing, SHOW GLOBAL STATUS LIKE 'Qc%'; then compute the read hit rate: Qcache_hits / Qcache_inserts If it is over, say, 5, the QC might be worth keeping.
Running that calculation against the values I just pulled from a production MySQL server which has been active for over two weeks I got a value > 14. This would seem to indicate my Query Cache is helping.
Thoughts?
The query cache may be useful
The query cache may be useful in Drupal:
- if you don't have many authenticated users. ((almost) each time an authenticated user visits a new page, their 'accessed' time is updated in the user table. Lots of reading and writing to this table causes the query cache to be invalidated. The users table is joined to for many queries in Drupal thus there's a lot of cached queries to invalidate/rebuild).
- if you are using the DB to store the cache (and not APC, or Memcache, etc.) AND events that cause a cache clear don't happen frequently (new comments, new nodes, etc.).
- if your database doesn't get a lot of load (as described in the stackexchange post, this is a single pathway that all requests to the DB must pass through regardless of how many CPUs your machine has).
In short it might help, it might not. Test before and after you make the change.
Percona is generally aiming at the high-traffic market, so in those cases the query cache is almost always the wrong choice.
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
query_cache_type = 0;
I thought this answer might be of interest to the query_cache_type = 0; consideration:
http://dba.stackexchange.com/questions/33214/query-cache-type-0-vs-query...
Restart vs Reload vs SET Statement
When we issue a "service mysql restart" on a GNU box, the entire server is shut down and spun-up all fresh and clean.
An alternative would seem to be "service mysql reload" which, I believe, simply re-reads the config file and makes the appropriate changes to the variables in the live running server.
Then of course we can connect using a MySQL client and issue a "SET" statement to change a single variable in the running server.
Clearly shutting down and restarting, as in Case 1 above, causes all of the RAM to be freed and various MySQL caches need to be built back up again over time before we can really test whether there was a change in performance.
If I am engaging in configuration testing, does it make sense to issue
service mysql reloadcommands or even just make individual SET statements and compare the before-and-after?In any case, what is a good method for testing different configuration values? How much time or how many Drupal page loads should be done before comparing the observed changes after modifying the config?
Very Generic Drupal Suggestions?
I have a MySQL server (dedicated) which is being used by a single Apache/PHP server (dedicated) for serving MANY Drupal sites from a multisite install.
Some sites are more for anonymous users (few content updates) and some are more for authenticated users (frequent content updates).
Are there any basic changes I should be making to the out-of-the-box my.cnf which are specifically known to be helpful to serving Drupal sites? Is there anything really obviously "wrong" in the defaults mysqld has with no overrides from my.cnf in relation to Drupal?
To this end, I don't care about things like TCP port or UNIX socket file, storage locations, or client options. I realize D7 recommends the use of InnoDB as the storage mechanism. That kind of info. Really really generic Drupal improvements over the MySQL defaults?
Does this question even make sense?
Even the most active sites bias read over write
There are differences between Drupal sites, but I would (have) argued that there are more similarities than differences. Any given site will run into issues like clearing the cache in a semi-regular way.
I would like to see a well documented, my.conf file for Drupal which highlights some best practices for the 90% of Drupal 7 sites out there.
There are going to be differences for Drupal 6, allocated RAM, solid state drives, cloud servers...... But let's start fleshing this out as a community so that some of this is better documented.
I do think having this in something like a wiki page would be better. Possibly even something like a Google doc so that comments can be added to specific lines of the config.
EDIT: I've created a wiki here - http://groups.drupal.org/node/289613
--
OpenConcept | Twitter @mgifford | Drupal Security Guide
Excellent idea, but it's
Excellent idea, but it's really hard to get a "one size fits all" or even 90% configuration file out there.
I would suggest getting 2-3 to go from; basic VPS, dedicated server with Apache and MySQL and dedicated MySQL only server. That would give the 3 main installations a good starting point to tune from.
HollyIT - Grab the Netbeans Drupal Development Tool at GitHub.
WSOD on Status Report
I am getting a blank screen when trying to run the Status report. Not sure the cause, but I suspect it is something that can be solved by adjusting my.cnf. This file is incredibly important to a successful Drupal deployment.
here are my current settings. Any ideas welcome.
My php.ini memory limits is: memory_limit = 512M
Here is the my.cnf as of today:
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
query_cache_type=0
max_connections=250
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
key_buffer=64M
join_buffer_size=1M
max_allowed_packet=16M
table_cache=1024
sort_buffer_size=2M # 1M for 1GB, 2M for 2GB, 4M for 4GB
read_buffer_size=2M # 1M for 1GB, 2M for 2GB, 4M for 4GB
read_rnd_buffer_size=1536K # 768K for 1GB, 1536K for 2GB, 3072K for 4GB
max_connect_errors=10
thread_concurrency=4
myisam_sort_buffer_size=64M # 32M for 1GB, 64M for 2GB, 128 for 4GB
skip-locking
skip-innodb
server-id=1
thread_concurrency = 8
[mysqldump]
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 32M
write_buffer = 32M
[mysqlhotcopy]
interactive-timeout
Are you sure MySQL is your bottleneck?
Is it?
I am questioning that. On most servers I setup so far, I always left the default MySQL configuration and maybe tweaked very little with MySQL Tuner, but in generally it always was fine.
Are you sure that MySQL really is your bottleneck? I personally would say your memory is your bottleneck and I would either invest the additional money for 4 GB RAM (especially for a professional site) or change hosts, where you can get more memory for less money.
If you calculate all the hours you put into tuning MySQL you might as well afford the additional memory cost easily ...
Lets calculate that through for a performance optimized site on 4 GB:
Apache: 1.5-2 GB
MySQL: 1 GB
Caching: 0.75 GB
* APC: 128 MB
* Memcache: 256 MB
* Varnish: 368 MB
With your PHP: memory_limit = 512M this means you can accomodate a maximum of "4" (!) clients concurrently if all those processes use the 512 MB. Lets assume you have an average of 128 MB per Drupal page load and an average time of 1s per page load, then this would be 12-16 clients per second MAXIMUM.
Now you are assuming that 12-16 Drupal page loads create such a load on MySQL that you need to totally tweak this and you have not yet even switched to InnoDB (Make DB backup, read: http://highervisibilitywebsites.com/blog/convert-your-mysql-database-myi...).
I am again questioning that, because while Drupal has quite some DB load as soon as you put caches to memcache you don't have any problematic queries left - UNLESS you have slow queries and you should check for 2s instead of 10 ...
And even without Memcache, the DB has never been for me a bottleneck.
In your setup you can give 1.3 GB your RAM to Apache and 512 MB to MySQL and you you can still only accomodate around 10 clients per second.
Again: MySQL is probably not your problem and you are trying to optimize something to death that will have very little effect on your setup - besides that it had been scaled too high (and such led to memory overusage).
My recommendation:
And even better:
(apc.php, memcache.php, mysql tuner, varnishstat)
I hope that helps: Have fun!
Few things that made my
Few things that made my Drupal fly
****Me and Drupal :)****
Clickbank IPN - Sell online or create a membership site with the largest affiliate network!
Review Critical - One of my sites
PHP Memory limit is too high
Do you really need "memory_limit = 512M"? If so then some weird script (or heavy view) is eating your RAM and I suggest you find that script. If not then lower it down to... Lets say 128MB?
Because one should always try to avoid going to DB in the first place have you considered using of the caches Fabianx mentioned? I mean instead of trying to optimize your my.cnf?
You wrote: "Focus is on serving user generated content to logged in members, limited exposure of content to anon web surfers." So my wild guess would be that using Memcached configured right would solve your problem.
Tipi
-TIP Solutions
Memory Limit Lowered to 128M
Thanks for the ideas folks, much appreciated!
(Fabinax):
I don't really understand the advantages of Memcache vs APC. I am currently running APC and I assumed it was an "either or" situation?
Also, I am planning to switch the db to innodb, but I am not really sure this is an improvement?
Finally, more memory seems like a great idea, cost is an issue with my VPS hosting company, GoDaddy. They really overcharge for server RAM. Probably should consider just deploying my own hardware and throwing as much RAM at the server as it can handle.
(Tipit):
I meant to lower the memory limit back to 128M, maybe even lower. I had a strange problem with Localization updates and I thought "throwing more memory" at the problem might have helped. It didn't, AND I forgot to lower the value back down.
I'm not sure there is a direct linkage between this setting and the number of concurrent users unless each is running a process that in fact consumes that amount of memory?
Memory Limits and Caches
The purpose of the PHP memory limit is to ensure that no one process may destroy your web server's RAM usage... That being said, you should be aware of how much RAM your server processes use and adjust your web server concurrent connections and child processes accordingly. For instance if you are using Apache and you set MaxClients too high with a PHP memory_limit too high, you are setting up your server to swap on the regular.
On *nix systems you can get Apache's current per-process RAM usage as with:
ps -C apache2 -o rss=(replace "apache2" with your process name, such as "httpd" as needed) Then average those values and decide how many of that average your server can sustain. But recognize as well that any of them MAY increase up to PHP's memory_limit.That being said, we have sites which require a memory_limit of 512M just to get the Modules list to display, which seems very ugly to me. Any help on that would be appreciated. :)
As for caching, it is my understanding that memcache can be deployed to replace any/all of Drupal's "cache_" DB tables, and can offer great speedup for common DB queries in this way. APC, on the other hand, is a PHP Cache (it caches PHP code). So they may not be as redundant as they appear...
Any corrections on this appreciated. I'm still learning, too! :)
Running into same Memory Limit to Display Modules issue
I have run into the same Memory_Limit = 512M to display modules/avoid WSOD on admin pages as well from time to time. Upping then decreasing this value does not seem like an optimal solution!
I really need to put some time into Memcache vs/with APC. I am also uncertain what the future holds for APC with PHP 5.5.
I have been experimenting with Views caching recently. It is a great idea in principal, but so many of my views benefit from dynamic user entered content updates that it is hard to find a time balance.
Thanks again nerdcore, as we are both in the same town, please let me buy you a coffee one of these days!
I have run into the same
This sounds like either you have > 200 modules and/or or you have not allocated enough memory to APC. Grab a copy of apc.php to check. Alternatively just use Drush rather than the UI.
Probably not worth thinking about. By the time we start running 5.5 in production it will all be sorted out.
Yeah it's too bad that only that very basic cache plugin is included with Views Core. But there are several other options. You might want to look into
http://drupal.org/project/views_content_cache
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
Aquia running a webinar tomorrow on mysql
Just an FYI for the group:
Aquia is running a free webinar tomorrow:
Topic: How to Scale MySQL in Support of Drupal
Meeting Time Info: Wednesday April, 3 2013 - 1:00PM EDT (18:00 UK)
http://www.acquia.com/resources/acquia-tv/conference/how-to-scale-mysql-...