Hi all, looking for some help!
Basically I run a community site which has been up and growing for about 2 years now. Lately my site has been getting slower and slower, and frequently has periods lately where it doesn't respond at all, but then 1 minute later will be fine. I have upgraded my VPS numerous times and that has helped hold off the issue, but it seems my last resort is to upgrade to a fully dedicated server, but I wanted to see if my results were typical before going that far as that type of solution is quite a chunk of change.
Here are my server stats:
http://www.hostforweb.com/vps.html
HFWVPS-3
Guaranteed RAM 768+ MB
Guaranteed CPU 626.6+ Mhz
And here are my drupal stats:
1926 users
6370 nodes
120446 comments
7970 nodevote (used on blog nodes only)
139742 votingapi_vote
101194 votingapi_cache
3205 term_data
8328 term_node
Modules enabled:
actions, adsense, archive, basicevent, blog, captcha, comment, contact, event, feedback, freelinking, menu, nodevote, page, path, ping, poll, print, profile, quotes, search, story, taxonomy, throttle, tinymce, tracker, upload, urlfilter, views, views_rss, views_theme_wizard, views_ui, vote_up_down, voting_actions, votingapi
The site's biggest factors are comments.. we get tons a day.. but beyond that is the vote_up_down of comments.. as the stability seems somewhat sporadic i was wondering if table locks might be causing the issue, but i really don't know.. i just know that when i get about 30 users logged in (50~ anonymous) that the site starts lagging..
From the little bit that I have talked to my host they stated that my server was hogging memory resources. Right now I am guaranteed 768MB, and a fully dedicated server could get me 2GB, but I am wondering if perhaps some optimization could help just as much - especially in that my VPS is totally stock and I have done absolutely no confiuration of the mysql or apache settings.
(for the record, my taxonomy has been totally user driven and is full of a ton of garbage terms that have never been cleaned up)
Please help out with some tips if you have them, and please let me know if I can provide any more detail. Thank you!
Comments
Are you upgrading to 5.0?
Do you have a schedule for upgrading to 5.0? Tuning your site for 4.7 might not be smart if you are immediately upgrading to 5.0.
Do you have MySQL on the same machine or different? You could have bottlenecks in MySQL due to memory and disk speed, spend all your time tuning the Web server, and find there is no difference.
Do you have a PHP accelerator? You can cut chunks of time and memory from most pages with an accelerator?
Are you using CGI or the PHP module? The module is faster.
There are a few other things you can do when you have a dedicated system. You can move settings from .htaccess to http.conf to remove the overhead of reading .htaccess for every page. Some VPSs give you the freedom to access and configure everything while others are locked into a common configuration.
When you tune everything else, then make sure cron is running the jobs that clean up the tables.
You can switch modules off and on one at a time to see what is chewing up memory.
You can split administration code out from the regular code to cut your overheads. I post the occasional example against modules and hope to see the change in a future Drupal.
In phpmyadmin, select a table then optimise the table. You can optimise many small tables in one hit.
petermoulding.com/web_architect
petermoulding.com/web_architect
i Intend to upgrade to 5.0
i Intend to upgrade to 5.0 eventually, but not anytime soon. mostly because i have not yet looked into upgrading my custom template to 5.0, and i have a few hacks here and there that would require some testing.. that and i am getting married in 30 days, so i have no time for any serious focus until afterwards.. so i would say i will be on 4.7 for at least another 3-4 months.
everything is on the same machine, and from what i can tell mysql is indeed the bottleneck.. i forgot to mention that when my site crashes this is the error it always throws:
Can't create a new thread (errno 12); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug.
my server is using the 'zend' php optimizer.
I am not familiar with the CGI or PHP module, what exactly were you referring to?
I am currently using the .htaccess file, but I can look into moving to the http.conf file, although I am totally unfamiliar ;)
Cron has not been running for about 2 months now.. namely because my search index had over 2 million records, and frequently the cron would fail, and keep re-trying all night long and causing the server to crash.. can you tell me what exactly the cron clears? i thought it was only used to update search indexes with new records..
I am currently using all the modules I have enabled, but I do intend to drop nodevote for vote_up_down, which will get rid of that.. but that won't be able to be done until i finish some conversion routines i am working on.. my main page is a view which is done to add date headers.. i have wondered if this view was somehow much more stressing than if i weren't using a view..
any suggestions on what tables to optimize? i was under the impression that you only needed to optimize a table if it had an overhead listed in phpmyadmin, is this an invalid assumption?
thanks for the response!
.htaccess versus httpd.conf
.htaccess contains settings that override the settings in Apache's httpd.conf file. The .conf file is read once when Apache starts up and the .htaccess is read once for every page read. If you move your settings from .htaccess and delete .htaccess, that saves Apache one read per page. The saving is tiny and it adds up on some sites.
If you do not know about httpd.conf then put this change way down low on your list of things to do. Get married, have kids and then, when you have a 14 year old computer geek in the family, set him or her the task of learning about httpd.conf.
petermoulding.com/web_architect
petermoulding.com/web_architect
CGI versus module
When you find your httpd.conf file, look for LoadModule php5_module. If you have that set, PHP loads as a native module in Apache and does some things just once. CGI is the alternative and loads a fresh copy of PHP for every page, which means PHP has to repeat a lot of work for every page.
Some ISPs set up CGI as the default because it is easier. I do not know what the various Linux and Unix distributions set as default.
petermoulding.com/web_architect
petermoulding.com/web_architect
i'll check this out tonight.
i'll check this out tonight.
Optimising tables
Running optimise for an optimised table does nothing which means you can run optimise for all tables by checking all tables then selecting optimise. Analyse is another thing you can run easily and quickly. Optimise is for tables with lots of deletes and and analyse is for tables that grow rapidly. Run both about once per month on very busy sites.
petermoulding.com/web_architect
petermoulding.com/web_architect
Some modules have old SQL
Some modules have SQL that works for small numbers of rows and needs improvement for larger numbers of rows. You see the occasional performance issue raised against modules. Check your individual modules for updates or patches to improve SQL.
Some common problems:
A table is index by an id but a common search uses another field. The solution is to add an extra index. Some of the modules I use had this problem and I posted improvements against the modules. You use a different set of modules and need to check them for performance updates.
A table search generates a scan due to an index that is too short. A long text field is indexed for the first 10 characters but the search needs to read 20 characters to make a match. The latest MySQL uses more intelligent indexes on text fields which means you can simply index the whole field.
SQL uses the least selective selection first. MySQL can rearrange some SQL to improve performance but not all SQL. You can use the Show command to see how SQL uses tables and indexes. If you are using a module that was never tuned for size then you might have to tune it yourself. Check for performance issues against each module.
petermoulding.com/web_architect
petermoulding.com/web_architect
I don't think i have updated
I don't think i have updated any of my modules in about 6-7 months.. ill go through them..
also, could you add anything more to the need for cron to run? like i said i havent had this on in about 2 months, is it necessary, or would it not running cause performance issues possibly?
thanks!
Cron cleanup
On my system I remember only one big cleanup and that is watchdog dropping off old table entries. I have not looked through all the modules and I am using different modules to you. Some of the archive modules move items to archive through cron.
If you can do anything to reduce table size, especially tables with lots of indexes, then you gain an advantage because you reduce index size and the indexes are heavily cached.
There are ways to display what happens in MySQL and in order of importance, you get the following problems:
whereclause.like '%chick%'. Try to limit the search by first selecting something specific.where country = 'CA' and name like '%chick%'The solution to problem 2 is to add an index and the index will eat into your memory. Problem 3 eats memory because MySQL caches whole rows instead of index entries.
petermoulding.com/web_architect
petermoulding.com/web_architect
InnoDB?
You mean MyISAM locking is too broad, not InnoDB. InnoDB is at the row level, so it is as granular as can be. InnoDB is better in certain locking situations, since MyISAM locks the entire table when it writes to it.
InnoDB is slow for some things that are fast in MyISAM, such as SELECT COUNT() queries. InnoDB has to physically go through the data and count the rows.
--
Drupal development and customization: 2bits.com
Personal: Baheyeldin.com
--
Drupal performance tuning and optimization, hosting, development, and consulting: 2bits.com, Inc. and Twitter at: @2bits
Personal blog: Ba
Yeah, back to front.
I was thinking of the example where you write code for Myisam then use it with InnoDB. You are getting the MyISAM table locking on a database that does not need it. When you change from Myisam to InnoDB, check your SQL is optimal for InnoDB. Myisam oriented SQL can be too broad for InnoDB.
Myisam is faster for some activity which means you change only some tables to InnoDB. I do not know of a good description of the limitations in InnoDB but, as a rule, keep the insert tables, like the logging tables, on Myisam.
The Falcon table engine seems to avoid the problems of both approaches by maintaining updates in memory separate from the read activity. A lock locks only against other update streams and there appears to be one lock for a transaction, not for a row. If there is a big transaction mixed in with lots of small transactions, the small transactions should be able to complete without waiting on the big transaction. Perhaps someone should start a thread on testing Falcon against Drupal for the update intensive parts of ecommerce, forums and comments.
Back at InnoDB, InnoDB seems to start faster than Oracle but not scale up as far as Oracle. There are fewer people with practical experience of scaling InnoDB and, on some projects, InnoDB slows down in ways that are not obvious. If we put your comments on InnoDB along with the Myisam equivalent in a post about testing Falcon, we will have a list of everything to test in Falcon when Falcon hits beta.
We should also note the release of MySQL when we talk about Myisam because Mysql changed the way it performs string index operations sometime around 4.1 or 5.0. I know it made an improvement to some of my projects. Some of the Drupal 4.7 SQL is oriented to the old version of Myisam. I think Drupal 5.0 should have set PHP 5.0 and MySQL 5.0 as a minimum so we can use all the improvements in MySQL and the MySQL-PHP interface. Perhaps Drupal 6.0 will do that.
petermoulding.com/web_architect
petermoulding.com/web_architect
InnoDB and Falcon
I agree with you that applications written for MyISAM will suffer if converted as is to InnoDB. This includes Drupal itself as well.
I actually wrote something about InnoDB's performance benefits as well as pitfalls, including slowing down things. The popular content block was the offender in one case I was involved in.
There is another issue: InnoDB is now owned by Oracle now.
As for Falcon, it is still to early to say. Some published benchmarks are not encouraging, showing it to be slower than either InnoDB or MyISAM, but they are still Alpha, and this will be their bread and butter in the future, so they need to get it right.
--
Drupal development and customization: 2bits.com
Personal: Baheyeldin.com
--
Drupal performance tuning and optimization, hosting, development, and consulting: 2bits.com, Inc. and Twitter at: @2bits
Personal blog: Ba
MySQL Optimization
The first thing you should check is your MySQL server settings. That will probably be the biggest drain on the server (has been in our case). Check these pages:
http://dev.mysql.com/books/hpmysql-excerpts/ch06.html
http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html
http://drupal.org/node/85768
Also, change line 52 or includes/database.mysql.inc to use mysql_pconnect() instead of mysql_connect().
Be sure to enable caching and set the minimum life to 5 or 10 minutes (only affects anonymous users). Also, disable any modules you don't use and throttle others like the search module and any blocks that perform intensive queries.
Sean Robertson
webolutionary@webolutionary.com
Sean Robertson | @seanr1978 on twitter
seanr@webolutionary.com
I had looked into MySQL
I had looked into MySQL optimization about a month ago or so.. at the time i could not for the life of me find where my configuration file was for MySQL on my VPS.. I guess I need to contact my host about that.. is the file always named my.cnf?
i will give the pconnect() a try as well.
my cache is set to 5 minutes.. i have used throttle in the past, however i was under the impression that throttle only affected anonymous users, whereas my main issue appears to be with logged in users who aren't receiving cached pages.. i will go back and take a look over throttleing again, however my issues are pretty steady, not just when hit hard, because i almost always have 30 users logged in now, so throttleing would just remove my functionality 50% of the time a user was on the site..
i will start to look over the sql tweaking stuff, if you have any suggestions where i might find the .cnf file please let me know ;)
Try to upgrade to 5.0 and
Try to upgrade to 5.0 and turn cache on ?
------------------------------------------------------
Be Smart, Think Free, Choose OpenSource.
Find the culprits first
Before you do anything to try to fix this, you have to find the culprit(s) first.
Number of visitors should not be an issue. A site I helped recently get 44 users + 1200 visitors. But this is not the only factor.
To check if you have locks when things are slow, use SHOW PROCESSLIST in mysql and see if you have locking going on. Or install mtop and use it to the same effect.
To see if you are thrashing (swap hell), use top or vmstat to see if you are exceeding memory. If you are, then reduce the number of apache processes to fit in whatever memory you have when the load peaks.
Memory can also be affected if you have many page components that have url aliases. An array is built dynamically, and if you have too many, the PHP processing (and memory) will quickly become untenable.
Install the devel module, and see if there are queries that take too much time, or is the slowness in the non query time, hence in the PHP code itself.
On busy sites, I have seen the menu table corruption (many duplicate items with no parents) cause slowness.
Some other slow things can be certain blocks that are database intensive (popular content for example). Some of the SELECT DISTINCT(...) queries may be slow if the tables are big. Disable such blocks.
Install APC or eAccelerator. They do wonders for CPU utilization, so MySQL can get more room to breath.
Also, check if you can disable the filter cache (code hacks required). It can be a drag. Also the page cache may be more of a drag too (yes, contrary to what you may think), so disable it and see what happens.
To check if PHP is CGI or Apache module, create a file called phpinfo.php with the line:
<?php phpinfo() ?>in it, and then visit it. It should say right there. Never use PHP in CGI mode on a busy site.Not having cron running is a recipe for disaster. You have to get it going, indexing things bit by bit, as well as clean things off.
Again, before you do any of the above use the tools I listed to know WHERE the problem is, before wasting time and effort to see where the bottleneck(s) is/are. Don't upgrade memory until you know as well.
--
Drupal development and customization: 2bits.com
Personal: Baheyeldin.com
--
Drupal performance tuning and optimization, hosting, development, and consulting: 2bits.com, Inc. and Twitter at: @2bits
Personal blog: Ba
Thanks to everyone who has
Thanks to everyone who has chimed in on this thread. I started re-examining the goals of my site and realized that perhaps getting rid of comments was something I was ready to do (they frequently get out of hand or are overly profane).. I did some local testing with the devel module and found that the number of queries per page load were 1/3rd of what they are now with commenting (and voting related stuff) turned off.. so, for now i am throttleing these features when the site is busy, hopefully this will have an immediate affect.
also, thanks to seanr above I checked out the tuning-primer.sh script (http://drupal.org/node/85768).. this showed me that my my.cnf was only 2 lines, and that no cacheing was being done, etc.. after a few quick changes i now have on slow query logging, proper cache settings, etc. - i am hoping this will have major effects.
one thing remains in this script which is of concern.. it lists the following:
Current Lock Wait ratio = 1 : 371
This ratio appears to vary from 1:150 to 1:380 right now (very slow downtime right now).. it is my guess that this lock ratio will get very bad when we are busy, and i intend to run this script tomorrow to find out.. i was wondering if anyone else was familiar to this ratio, and if these values were poor? From what i could find on the web it appeared that most people would have a 1 : 1000+ ratio.. perhaps this is a sign that table locking is indeed a major issue for my site?
thanks again everyone, if I figure out more on this lock/wait ratio i will be sure to post again!
httpd.conf
I am running drupal 4.7 on apache2 w/ clean urls..
i tried copying my .htaccess to httpd.conf and then clearing out .htaccess and it does not work...
am I missing something?