Posted by hypertext200 on July 9, 2009 at 4:28am
Hi
I have MySQL server running in as local, but I see a server load is very high and queuing all processes, I have about 100,000 rows in node table and taxonomy + 200,000 users. When less than 30 online registered users can handle server well, but when it become over 30 server load is increasing as a hell. I hope having dedicated MySQL server will solve this, is it effect to me? having dedicated MySQL server?.
I'm using APC with cache router.
Comments
Slow queries
Have you tried turning on the MySQL slow query log? If the database is indeed your bottle neck (highly probable), then getting new hardware is usually the last option you choose to fix it.
Generally you increase DB performance in this order:
But for a more detailed method of determining how to solve a MySQL problem see this decision chart:
http://rackerhacker.com/2009/02/04/mysql-performance-flow-chart/
--
Dave Hansen-Lange
Web Developer
Advomatic LLC
East Asia Office
Hong Kong
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
Yes do find out what queries
Yes do find out what queries are slow. We just put up a large sized site, in terms of the size of each page, and searching was killing mysql. Examine watchdog and see how many users are searching. Try disabling search and see if load gets better. Also, the more search terms the longer and more intensive the search. We ended up going with apachesolr, which we run on the same box, and the site handles very well now.
how about RAM usage? if you
how about RAM usage?
if you have free RAM, memcached can be configured to cache session handling - as you describe (more that 30 active users = 30 active sessions for authenticated users).
There's a few dozen places
There's a few dozen places that you should look before you investigate memcache for managing session data. Very few sites out there are optimized to the level where moving sessions from the DB to memcache will make a noticeable difference. Especially to a site that only has ~ 30 sessions for authenticated users. Your problem is most definitely somewhere else.
--
Dave Hansen-Lange
Web Developer
Advomatic LLC
East Asia Office
Hong Kong
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
Based on provided
Based on provided information, this was my suggestion to lower mysql load.
Here is the support request
Here is the support request i put at the MySQL site,
http://forums.mysql.com/read.php?24,270712,270712#msg-270712
This generated by Devel module, purpose is showing image thumbnails, about 20 thumb per page.
You can see fully details about tables and the EXPLAIN it, I know using filesort and temptable killing the MySQL, those SQL are generated by the Views and I optimized those by adding index to the value at the votingapi_cache table, it increases some sort of performance, since this querying about 50,000 rows and when 30-40 users accessing it, I hope MySQL will kill,
I have similar four views, I hope when users access to see images and then load will be high.
@:dalin@drupal.org
I tried optimize the queries mainly by indexing, but for me wasnt effect, how can I optimize MySQL settings?.
Senior Drupal Developer at DrupalConnect
views provide api, you can
views provide api, you can use this API to get view data and cache it before show. In this case instead view you will need to show cached value.
Question is how often you have real updates for this views and how big delay you can have for data showed by views.
Another question, do you views use are common for all users or use user id some where?
query 1, provided by this link http://forums.mysql.com/read.php?24,270712,270712#msg-270712 is broken. Repost it.
I'm using cache router with
I'm using cache router with APC, I think it will cache all views, I saw some cache table in the database too, if it is not caching, how can I cache it, view images is the main function of the sites, so I just needed was add some effective sorting to the images, but its going to kill my database.
As you said what I want to do is cache all views?. Will cache router help me with APC installed, I already have APC installed.
Senior Drupal Developer at DrupalConnect
yes, you use - but with this
yes, you use - but with this you still have problems with performance.
And I suggest you don't see for 'mysql' optimization and see for logic optimization. If view don't have real time updates or updates can be delayed there are no reasons to generate view each time for each user.
And as solution I suggest you plain content cache.
As php page you can use next example instead real view:
<?php
if (!$view_content = cache_get($view_name, 'cache')) {
$view = views_get_view($view_name);
$view_content = $view->render();
cache_set($view_name, $view_content, 'cache', time() + 3600);
}
print $view_content;
?>
as result you will have cached generated by views functionality content for one hour - this mean that you will keep all power provide by views. And you will not need to generate view every time for every user, content will be regenerated only once in hour!
Caching the query is good,
Caching the query is good, but only as a temporary solution. I'm sure your expecting your site to grow, both in visitors and in data size. That means that query will take longer and longer to process.
But there is a problem - contention.
What happens if your data grows to where that query starts taking 30, 60, 90 seconds etc. As the execution time increases, so does the chance of a visitior landing on that page when the cache is expired (and as the query time increases so does the chance of multiple people triggering a re-query) . Murphy's law also takes effect here. A regular user is sitting there with a white screen since that query is executing. The reflex reaction from a typical user is to hit refresh a couple of times before moving on. Now you got even more instances of that query running. Now your creating one nasty bottleneck.
A better solution is to cache the results, but only update it during a cron run. That way only one request (the cron request) is triggering the monster query. All regular users are always served a cached copy of the result then.
Of course the best solution is to try to come up with a more optimized query. Try rewriting it. Sometimes with joins MySQL isn't that smart on picking the right index, so you can always add index hinting into the query. If that doesn't work then try breaking the query into a couple separate queries.
HollyIT - Grab the Netbeans Drupal Development Tool at GitHub.
and why you think that my
and why you think that my solution will update cache(render view) more than once at hour? and you can increase/decrease cache time as you want - hourly, daily, monthly.
to use cron hook you need to create custom module, I suggest code which can be used directly as php code in node source. Can you suggest something more simply? please provide example.
As I said optimization goal is "site performance" and query optimization isn't "best solutions", it is only part(one step) of site optimization. Change site logic affects MySQL queries indirectly, in my example dramatically lower query count run by view.
It's very simple. Let's say
It's very simple. Let's say this query takes 5 seconds to perform:
User a visits site at 9:00:00. The cache is bad. Now that query is executed. The same expire time is still in the cache since it hasn't been reset.
User b visits site at 9:00:03. The cache is bad. While visitor A's request is causing the query to re-execute, there is no way for this new request to know that. Now visitor b is causing the query to execute again.
Now imagine that query takes a little longer, or the site is really busy. Once that cache is expired every request is going to call the query up until one of the requests finally gets through and can set a new cache with a new expiration time.
Another route would be to create a variable to act as a semaphore for the query. Then you could put that into a node with php code. Basically set a variable for view_cache_run (or something like that) with the current time. Then once the query is done running reset that variable to 0. Then you check to see how long that value has been set. If its been set longer than 5 minutes or so, reset it to the current time and run the query. That would work unless the query ever gets over 5 minutes. In that case there are much bigger problems.
HollyIT - Grab the Netbeans Drupal Development Tool at GitHub.
you are right, I forgot that
you are right, I forgot that content generation will take time.
Right, and doing it from a
Right, and doing it from a cron means that you can overwrite the old cache with the new, and there's no significant gap in availability (but only if you avoid letting the cache expire before the cron has updated results, so watch your cache settings).
Ken Winters
www.coalmarch.com
Ken Winters
@Alexandr Kosarev, Where
@Alexandr Kosarev,
Where should I put your code? In views module?
Senior Drupal Developer at DrupalConnect
you need to have "PHP
you need to have "PHP filter" module and php input form enabled
as notice intoxination it will be better if cache will be updated by cron with "lock".
if you have custom module add to hook_cron next code:
<?phpfunction custom_cron() {
$view_name = 'CHANGE_TO_YOUR_VIEW_NAME';
if (variable_get('cache_'.$view_name, 0) < (time() + 3000) && !vaiable_get('generation_lock_'.$view_name, 0)) {
variable_set('generation_lock_'.$view_name, 1);
$view = views_get_view($view_name);
$view_content = $view->render();
cache_set($view_name, $view_content, 'cache', CACHE_PERMANENT);
variable_set('cache_'.$view_name, time() + 3600);
variable_set('generation_lock_'.$view_name, 0);
}
}
?>
to show generated cache create node with input form php and add:
<?phpprint cache_get($view_name, 'cache');
?>
What the views in build
What the views in build cache do? is it only for anonymous users?. I'm using APC with cache router will that effect to save cacahe?
Senior Drupal Developer at DrupalConnect
I found that error on the
I found that error on the node table saying, you cannot re declare the index to the status, it has declared as INDEX status and INDEX title,status will that be a bottleneck?
And currently I'm runnig on a local mysql server and if I move to a new server will that help me to solve the problem?
Senior Drupal Developer at DrupalConnect
heshanmw it appears to me
heshanmw it appears to me that you are clearly in over your head. I would suggest hiring a Drupal shop with experience in Drupal performance tuning.
--
Dave Hansen-Lange
Web Developer
Advomatic LLC
East Asia Office
Hong Kong
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
+1
+1