RocketModule - MySQL http://rocket.local/categories/mysql en Convert your MySQL database from MyISAM to InnoDB, and get ready for Drupal 7 at the same time http://rocket.local/blog/convert-your-mysql-database-myisam-innodb-and-get-ready-drupal-7-same-time <div class="field field-name-body field-type-text-with-summary field-label-hidden"><div class="field-items"><div class="field-item even" property="content:encoded"><p><img src="/files/innodb-trans.png" alt="" style="float:left;margin:5px 15px 3px 0;" />If you haven't already heard, <a href="http://drupal.org/node/301362">Drupal 7 will default to using the InnoDB storage engine instead of MyISAM</a> for MySQL (though a MyISAM database will continue to work just fine in Drupal 7). This is fairly substantial change within Drupal core, and as the thread in the issue queue I linked to shows, there were a lot of questions and apprehension about it. However...</p> <p>...we are going to just skip over a lot of that apprehension and get down to point of this article - there's no good reason not to hop right into using InnoDB today on your Drupal 5 or Drupal 6 site. The rewards are; a possibly significant improvement in performance, a definite improvement in scalability (most highly trafficked Drupal sites have been using InnoDB for some time now because of this), and you'll start getting used to working with what will be more and more common in your Drupal-life, InnoDB.</p> <p><strong>My experience</strong><br /> I came to the conclusion about how great InnoDB is after researching the experiences of others, and after converting a large Pressflow-driven Drupal 5 site from InnoDB vs MyISAM. This change resulted in a 14% increased throughput during load tests performed in JMeter. That's a very substantial increase, and while everyone's mileage will vary based on their own site, server, and any number of variables it's clear enough to me that there's nothing to be afraid of as far as InnoDB goes (quite the contrary).</p> <p><strong>Converting your database to InnoDB</strong><br /> Before you go any further <strong>backup your database</strong> before doing any steps below. If you 'splode your database for any reason, you'll need it.</p> <p>Here are the steps:</p> <blockquote><p> 1. <em>Shutdown MySQL</em></p> <p>2. <em>Move/copy/change the name of ib_logfile0 and ib_logfile1 files</em>. (find where MySQL exists on your system - locations can vary greatly). MySQL will recreate these files when you start it up again. Not anytime you change the innodb_log_file_size parameter you will need to shutdown MySQL, move these files, and start up MySQL again.</p> <p>3. <em>Tune it up a bit</em><br /> Based on a lot of searching around and benchmarking with JMeter I arrived at the setting below for running on my Macbook Pro. See the links at the end of this post for articles which can help you determine what to adjust these numbers to for other machines (ones with more RAM/CPU, for instance. The production server for this particular site ended up with 5000M setting for innodb_buffer_pool_size. So settings will, and should, vary greatly just depending).</p></blockquote></div></div></div><div class="field field-name-taxonomy-vocabulary-1 field-type-taxonomy-term-reference field-label-above"><div class="field-label">Categories:&nbsp;</div><div class="field-items"><div class="field-item even"><a href="/categories/drupal" typeof="skos:Concept" property="rdfs:label skos:prefLabel">Drupal</a></div><div class="field-item odd"><a href="/categories/mysql" typeof="skos:Concept" property="rdfs:label skos:prefLabel">MySQL</a></div><div class="field-item even"><a href="/categories/performance" typeof="skos:Concept" property="rdfs:label skos:prefLabel">performance</a></div><div class="field-item odd"><a href="/categories/innodb" typeof="skos:Concept" property="rdfs:label skos:prefLabel">InnoDB</a></div></div></div> Mon, 18 Jan 2010 18:28:18 +0000 Caleb Gilbert 94 at http://rocket.local http://rocket.local/blog/convert-your-mysql-database-myisam-innodb-and-get-ready-drupal-7-same-time#comments Provisioning and install script for a speedy Drupal workflow http://rocket.local/blog/provisioning-and-install-script-speedy-drupal-workflow <div class="field field-name-body field-type-text-with-summary field-label-hidden"><div class="field-items"><div class="field-item even" property="content:encoded"><p>I made this script and the <a href="/database-backup-dump-and-svn-commit-script-drupal-workflow">database backup, dump, and SVN commit script</a> because I was determined to spend as little time as possible doing sysadmin while setting up dev and staging sites, so that I could spend as much time as possible developing (e.g., the fun stuff). With one command the script can:</p> <ul><li>'svn up' a version controlled database, and upload it to your database</li> <li>Run queries against database to set preferred site defaults</li> <li>'svn up' site docroot</li> <li>Copy over fresh "files" directory from another site (e.g., production). Note, not a good option if you have your "files" directory version controlled.</li> <li>Set owner:group file permissions on all site files</li> </ul><p><strong>How to</strong><br /> The script should go in non-public, secure directory, which is somewhere below the site doc root. I haven't tried running it with permissions less than sudo, though it may be possible. Invoke by doing:</p> <p><code>sh path/to/provision_and_install.sh</code></p> <p><strong>Download</strong><br /><a href="/downloads/provision_and_install.sh.zip">provision_and_install.sh.zip</a></p> </div></div></div><div class="field field-name-taxonomy-vocabulary-1 field-type-taxonomy-term-reference field-label-above"><div class="field-label">Categories:&nbsp;</div><div class="field-items"><div class="field-item even"><a href="/categories/drupal" typeof="skos:Concept" property="rdfs:label skos:prefLabel">Drupal</a></div><div class="field-item odd"><a href="/categories/mysql" typeof="skos:Concept" property="rdfs:label skos:prefLabel">MySQL</a></div><div class="field-item even"><a href="/categories/development" typeof="skos:Concept" property="rdfs:label skos:prefLabel">development</a></div><div class="field-item odd"><a href="/categories/sysadmin" typeof="skos:Concept" property="rdfs:label skos:prefLabel">sysadmin</a></div><div class="field-item even"><a href="/categories/svn" typeof="skos:Concept" property="rdfs:label skos:prefLabel">svn</a></div></div></div> Sat, 28 Mar 2009 17:33:42 +0000 Caleb Gilbert 88 at http://rocket.local http://rocket.local/blog/provisioning-and-install-script-speedy-drupal-workflow#comments Database backup, dump, and SVN commit script for Drupal workflow http://rocket.local/blog/database-backup-dump-and-svn-commit-script-drupal-workflow <div class="field field-name-body field-type-text-with-summary field-label-hidden"><div class="field-items"><div class="field-item even" property="content:encoded"><p>This script is useful for keeping a database within reach of an 'svn up' anywhere it needs to be deployed (e.g., dev and staging sites). In addition to creating a database dump, it also svn commits the database. I use it in conjuction with this <a href="/provisioning-and-install-script-speedy-drupal-workflow">provisioning / install script</a>, but it can be used on its own.</p> <p><strong>How to</strong><br /> The script should go in non-public, secure directory, which is somewhere below the site doc root. I haven't tried running it with permissions less than sudo, though it may be possible. Invoke by doing:</p> <p><code>sh path/to/dump_and_checkin.sh</code></p> <p>It has logging and verbose output for confirmation of it's operations on the command line.</p> <p>Future possiblities includes doing something <a href="http://www.petersblog.org/node/959">like this</a> to cut down on the size of the diffs committed, but since haven't gotten a chance to test that in a prodcution environment, am sticking with with plain-and-simple in order to guarantee integrity of the SQL file.</p> <p><strong>Download</strong><br /><a href="/downloads/dump_commit.sh.zip">dump_commit.sh.zip</a></p> <p><a href="/downloads/dump_commit_w_perms.sh.zip">dump_commit_w_perms.sh.zip</a> (dump and commit, plus update file permissions, a feature not related to backup - simply for maintenance reasons. I use this one myself, but use the other script if this feature is not wanted or needed)</p> </div></div></div><div class="field field-name-taxonomy-vocabulary-1 field-type-taxonomy-term-reference field-label-above"><div class="field-label">Categories:&nbsp;</div><div class="field-items"><div class="field-item even"><a href="/categories/drupal" typeof="skos:Concept" property="rdfs:label skos:prefLabel">Drupal</a></div><div class="field-item odd"><a href="/categories/mysql" typeof="skos:Concept" property="rdfs:label skos:prefLabel">MySQL</a></div><div class="field-item even"><a href="/categories/sysadmin" typeof="skos:Concept" property="rdfs:label skos:prefLabel">sysadmin</a></div><div class="field-item odd"><a href="/categories/svn" typeof="skos:Concept" property="rdfs:label skos:prefLabel">svn</a></div></div></div> Sat, 28 Mar 2009 17:28:46 +0000 Caleb Gilbert 87 at http://rocket.local http://rocket.local/blog/database-backup-dump-and-svn-commit-script-drupal-workflow#comments Speeding up Drupal Forums http://rocket.local/blog/speeding-drupal-forums <div class="field field-name-body field-type-text-with-summary field-label-hidden"><div class="field-items"><div class="field-item even" property="content:encoded"><p>The Drupal forum.module has become, well, somewhat infamous for less than awesome scalability. Recently I had a chance to see this firsthand, and track down a solution for managing the long page load times for a <a href="http://wallstreetoasis.com">client</a> who has a highly trafficked forum. This was not a case of a site that was un-tuned - actually this particular site had a lot of good work and performance enhancements already done to it, including block caching and even some modifications to the forum module that were allowing to work better than it would have without them. But still 5-6 second page load times on /forum persisted.</p> <p>As this was my first time working on the site, I began by reviewing all of the main configuration files for Apache, MySQL, and PHP, since they are the foundation for everything else. After making some adjustment there, I headed over to the Drupal admin interface and check /admin/setting/performance/ and made sure all was happy there as well. Finally, I went to the block admin page and double-checked all of the blockcache settings, which as it turns out were set a bit to aggressively, resulting in slow form submission times (every time anyone submitted anything a gazillion blocks were being re-cached whether they need to be or not).</p> <p>With the foundation of the site now looking good and everything, except the forum pages flying along (and the tracker, but that's a story for a later article) - it was just down to forum.module.</p> <p>Here are the steps that led to cutting the page load time in half from what they were:</p> <p>1. Disable the forum.module, rename it to something like "forum.module.orig", make a copy and then rename the copy "forum.module".</p> <p>2. Download the <a href="http://drupal.org/project/advcache">advcache module</a> and apply the forum patch includes to your new copy of the forum.module.</p> <p>3. If want the cleanest solution and are comfortable with coding/debugging at all, instead of just copying the forum module and working on it directly (and thus having a hacked Drupal core file around all the time) - name the copied file something completely different than forum.module and edit all of the hook/function calls with in it with new name and place it where you keep the rest of your contributed modules.</p> <p>4. (Note: this step is an option only if all your forums are public)<br /> Open the module and remove all references to db_rewrite_sql. This will keep Drupal from doing a lot of expensive and uneccesary queries in order to check access rights. (<a href="http://2bits.com/articles/how-drupals-nodeaccess-table-can-negatively-impact-site-performance.html">thanks Khalid</a>)</p></div></div></div><div class="field field-name-taxonomy-vocabulary-1 field-type-taxonomy-term-reference field-label-above"><div class="field-label">Categories:&nbsp;</div><div class="field-items"><div class="field-item even"><a href="/categories/drupal" typeof="skos:Concept" property="rdfs:label skos:prefLabel">Drupal</a></div><div class="field-item odd"><a href="/categories/mysql" typeof="skos:Concept" property="rdfs:label skos:prefLabel">MySQL</a></div><div class="field-item even"><a href="/categories/performance" typeof="skos:Concept" property="rdfs:label skos:prefLabel">performance</a></div><div class="field-item odd"><a href="/categories/tuning" typeof="skos:Concept" property="rdfs:label skos:prefLabel">tuning</a></div><div class="field-item even"><a href="/categories/forums" typeof="skos:Concept" property="rdfs:label skos:prefLabel">forums</a></div></div></div> Thu, 28 Feb 2008 18:41:41 +0000 Caleb Gilbert 71 at http://rocket.local http://rocket.local/blog/speeding-drupal-forums#comments Prepare your Drupal site to be Slashdotted, Dugg, and Farked - Part II http://rocket.local/blog/prepare-your-drupal-site-be-slashdotted-dugg-and-farked-part-ii <div class="field field-name-body field-type-text-with-summary field-label-hidden"><div class="field-items"><div class="field-item even" property="content:encoded"><p>It's been a couple weeks since we posted <a href="http://highervisibilitywebsites.com/prepare-your-drupal-site-to-be-slashdotted-dugg-and-farked">part one</a> of our look at optimizing a Drupal site to withstand large amounts of traffic, <br />and since that time it happened again - a site we host, got "Farked" (an inbound link from Fark.com) even bigger than it did last time. In the 8 short hours since the link to the client's site went up, and as I write this - the site has received 27,000 + unique viewers. When I logged in there to the site there actually were 1850 users online at the same time.</p> <p>We just about fell out of our chair when we saw that...</p> <p>...after all this is a site is that's on a shared server - not a dedicated one. And <em>those</em> kind of numbers would even give some dedicated servers a thorough workout. In the meantime, it was operation 911. Forget the long term issue of finding a larger server space for this site which clearly is outgrowing it's enviroment - that could be handled <em>afterwards</em>. Right now, we had to get the server and site back pronto. </p> <p>So quickly we posted a "We'll be back soon" message to the deluged site so that the rest of the sites on the server would work again, and then set out to get Farked site back up. Now 27,000 visitors in 8 hours would mean over 75,000 in 24. There's no shared hosting enviroment we know of that can handle a dynamic, php-driven, website with kind of load...so what to do...what to do.</p> <p>Well, in short order we made an html file out of the page which Fark.com linked to and put a 301 redirect in the site .htaccess file so that anyone visiting that page would get redirected to the much lighter html file and thereby bypass Drupal and all of the bootstrapping and database overhead. The rest of the pages on the site would function just as they always do, of course. What this did was allow us to take the site live again, serve wayyyy more people than should be possible for on a $20 a month hosting plan, and keep everyone else's site happy and screaming along. </p> <p>Two other things we did since the time we wrote the first article helped a lot too:</p> <p>condensed the number of, and compressed, the css files for the site. Simply put, we cut the number of files requested on each page visit by 6 by aggregating all the css into one file. That's a lot of requests when you multiply it by thousands. </p> <p>Disabled the statistics.module. This is a no-brainer. Apache is logging eveything already and there are more robust tools to process and interpret the logs with than what comes with Drupal (no offense Drupal) - so this is a LOT of overhead that the site/server doesn't need to deal with. Besides the input/output processesing, it also speeds up the database to not have such a larger access log file hangin around.</p> <p>So there you go - how to make lemonade out of more hits than you should be able to handle. </p></div></div></div><div class="field field-name-taxonomy-vocabulary-1 field-type-taxonomy-term-reference field-label-above"><div class="field-label">Categories:&nbsp;</div><div class="field-items"><div class="field-item even"><a href="/categories/drupal" typeof="skos:Concept" property="rdfs:label skos:prefLabel">Drupal</a></div><div class="field-item odd"><a href="/categories/mysql" typeof="skos:Concept" property="rdfs:label skos:prefLabel">MySQL</a></div><div class="field-item even"><a href="/categories/lamp" typeof="skos:Concept" property="rdfs:label skos:prefLabel">LAMP</a></div><div class="field-item odd"><a href="/categories/apache" typeof="skos:Concept" property="rdfs:label skos:prefLabel">Apache</a></div></div></div> Thu, 15 Feb 2007 01:22:55 +0000 Caleb Gilbert 54 at http://rocket.local http://rocket.local/blog/prepare-your-drupal-site-be-slashdotted-dugg-and-farked-part-ii#comments Import raw content (nodes, users) into Drupal http://rocket.local/blog/import-raw-content-nodes-users-drupal <div class="field field-name-body field-type-text-with-summary field-label-hidden"><div class="field-items"><div class="field-item even" property="content:encoded"><p><em>(This article is made specifically for showing how to get raw content into Drupal, but the methods below can likely be adapted for other use cases [we also imported a list of users using these techniques for instance].)</em></p> <p>Importing raw content into Drupal, can be, well let's just say it's not always easy. In two years of experience with Drupal we've imported a MoveableType site, a Blogspot site, content from one Drupal site to the other, and now raw data from a csv file (raw meaning that it did not come from some other CMS/website), and we've used everything from custom scripts, several different Drupal modules along the way including import-exportapi.module, userimport.module, nodeimport.module, WordPresstoDrupal.module.With the exception of the WordPresstoDrupal.module we've been mostly disappointed with the results of our experiments with the Drupal based import options. That said, this may not be the best method to start off with if you can find a ready-made solution that works for you, since it is somewhat time consuming.</p> <p>Besides being a somewhat complex thing to begin with, the issues around importing content into a Drupal installation are exacerbated by the wide array of variables any point and click solution has to contend with. It's impossible for any module developer to be able to keep up with the number of different/custom Drupal content types for even one version of Drupal, let alone for all new versions of Drupal.</p> <p>In short, for the serious site-operator/Drupal developer getting raw content imported to Drupal is always going to be a problem because one size is never going to fit all (the 'bad' side of such a customizable platform). This is important to understand in order to fully appreciate the workaround below, which is not particularly easy or fast, but is 99.9% reliable from a data integriy standpoint. </p> <p>"How's that," you say? Well, because the following method does not actually "import" anything at all, at least not in the traditional sense that most people think of when adding new content into a database. With this method you'll create complete Drupal nodes with SQL, which you will then simply place it in your existing database. Nothing is automatically calculated, scripted, or extrapolated - which is exactly why this method is not easy or particularly fast, but also why it is precise:</p> <p><strong>In brief:</strong><br />Create two separate sql import files. One for the "node" table of your database, and another for the "node_revisions" table. The last step will be to update the "sequences" table to let the system 'know' about the new content.</p> <p>(these "node" and "node_revisions" tables are used only for certain module based content types not for cck nodes - though you could adapt these instructions to create new cck nodes by figuring out what tables you need to populate and using the techniques below)</p> <p><strong>What you need:</strong></p></div></div></div><div class="field field-name-taxonomy-vocabulary-1 field-type-taxonomy-term-reference field-label-above"><div class="field-label">Categories:&nbsp;</div><div class="field-items"><div class="field-item even"><a href="/categories/drupal" typeof="skos:Concept" property="rdfs:label skos:prefLabel">Drupal</a></div><div class="field-item odd"><a href="/categories/mysql" typeof="skos:Concept" property="rdfs:label skos:prefLabel">MySQL</a></div></div></div> Mon, 29 Jan 2007 23:49:11 +0000 Caleb Gilbert 45 at http://rocket.local http://rocket.local/blog/import-raw-content-nodes-users-drupal#comments Mysql tuning. Tools, tips, and links on optimizing mysql for Drupal http://rocket.local/blog/mysql-tuning-tools-tips-and-links-optimizing-mysql-drupal <div class="field field-name-body field-type-text-with-summary field-label-hidden"><div class="field-items"><div class="field-item even" property="content:encoded"><p>Here are some basic, but high impact ways to optimize MySQL for Drupal (there are much more sophisticated and expensive ways to speed up your database of course):</p> <p>Am not sure if these tips do any good for someone on a shared hosting plan or not (do shared plans have access to a my.cnf file?). Also, I can only confirm these setting for MySQL 4.0.2 thru the latest 4.0.x version, but I think it would work for 5.x (maybe someone can confirm this and leave a comment...).</p> <p>Actually, it will work for below 4.0.2 I think as long as you add <code>set-variable =</code> before each line (see this page for more on <a href="http://dev.mysql.com/doc/refman/4.1/en/program-variables.html" rel="nofollow">set-variable</a>) </p> <p>1. Get <a href="http://www.day32.com/MySQL/tuning-primer.sh">this script</a>, upload it, unzip it, and install it in your /etc folder (at the root of your server, not your Drupal install, right). Then run it from the command line by entering <code>sh /path-to-file/tuning-primer.sh</code></p> <p>The script will run and what you'll be left with is an output with some info and suggestions about your MySQL settings. Was shocked to learned that on my VPS the cache was not even enabled - very helpful to know!</p> <p>2. Next open your my.conf file in pico or some kind of proper code/text editor:</p> <p>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):</p> <p>For a setup with 500mb or ram paste this in your my.cnf file:</p> <div class="codeblock"><code>[mysqld]<br />max_connections = 800<br />max_user_connections = 800<br />key_buffer = 36M<br />myisam_sort_buffer_size = 64M<br />join_buffer_size = 2M<br />read_buffer_size = 2M<br />sort_buffer_size = 3M<br />table_cache = 1024<br />thread_cache_size = 286<br />interactive_timeout = 25<br />wait_timeout = 1800<br />connect_timeout = 10<br />max_allowed_packet = 1M<br />max_connect_errors = 999999<br />query_cache_limit = 1M<br />query_cache_size = 16M<br />query_cache_type = 1<br />tmp_table_size = 16M</code></div> <p>For a system with 256mb of ram:</p> <div class="codeblock"><code>[mysqld]<br />max_connections=500<br />max_user_connections = 500<br />key_buffer = 16M<br />myisam_sort_buffer_size = 32M<br />join_buffer_size = 1M<br />read_buffer_size = 1M<br />sort_buffer_size = 2M<br />table_cache = 1024<br />thread_cache_size = 286<br />interactive_timeout = 25<br />wait_timeout = 1000<br />connect_timeout = 10<br />max_allowed_packet = 1M<br />max_connect_errors = 999999<br />query_cache_limit = 1M<br />query_cache_size = 16M<br />query_cache_type = 1<br />tmp_table_size = 16M</code></div> <p>3. Save your my.cnf file and restart mysql. This can be done via WHM or the command line (not sure what that command is - sorry)</p></div></div></div><div class="field field-name-taxonomy-vocabulary-1 field-type-taxonomy-term-reference field-label-above"><div class="field-label">Categories:&nbsp;</div><div class="field-items"><div class="field-item even"><a href="/categories/mysql" typeof="skos:Concept" property="rdfs:label skos:prefLabel">MySQL</a></div><div class="field-item odd"><a href="/categories/lamp" typeof="skos:Concept" property="rdfs:label skos:prefLabel">LAMP</a></div><div class="field-item even"><a href="/categories/drupal-tuning" typeof="skos:Concept" property="rdfs:label skos:prefLabel">Drupal tuning</a></div></div></div> Sun, 21 Jan 2007 21:13:21 +0000 Caleb Gilbert 36 at http://rocket.local http://rocket.local/blog/mysql-tuning-tools-tips-and-links-optimizing-mysql-drupal#comments