PDA

View Full Version : Solved VBET HIGH CPU/Memory usage due to mysql



magmf
04-06-10, 13:00
Guys,

I really need have vbet more optimized for my website.

VBet is getting me a lot of new users I really liked the tool BUT is using too many resources.


My cpu load before VBET was bellow 2 and now is more than 4 all time

now for example
08:32:18 up 11 days, 2:27, 1 user, load average: 7.45, 8.24, 8.26

attached few tickets from before and after vbet enabled in my website


I want to share you that i jumped from 70.000 pages indexed on google to more than 500.000

my rank at alexa jumped also
Under-linux.org - Site Info from Alexa (http://www.alexa.com/siteinfo/under-linux.org)

and i'm receiving more visits everyday but I still need a better optimization on how vbet handle with queries.

take a look on my top



top - 08:53:46 up 11 days, 2:49, 1 user, load average: 10.77, 9.81, 9.06
Tasks: 423 total, 5 running, 418 sleeping, 0 stopped, 0 zombie
Cpu(s): 15.7%us, 2.4%sy, 0.0%ni, 66.6%id, 9.8%wa, 0.1%hi, 5.4%si, 0.0%st
Mem: 12330536k total, 11619688k used, 710848k free, 24064k buffers
Swap: 995988k total, 994972k used, 1016k free, 2917568k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
29384 mysql 20 0 5722m 2.5g 4552 S 99 20.9 3661:46 mysqld



as you can see mysql at top because of vbet


mysql show processlist;



mysql> show processlist;
+********-+************+****************-+************+********-+****--+************--+****************************************************************************************************--+
| Id | User | Host | db | Command | Time | State | Info |
+********-+************+****************-+************+********-+****--+************--+****************************************************************************************************--+
| 3102552 | underlinux | localhost:49801 | underlinux | Query | 16 | Sending data | SELECT cache.originaltext as originaltext, cache.translated as translated FROM vbvbenterprisetransla |
| 3102570 | underlinux | localhost:49843 | underlinux | Query | 12 | Sending data | SELECT cache.originaltext as originaltext, cache.translated as translated FROM vbvbenterprisetransla |
| 3102597 | underlinux | localhost:49901 | underlinux | Query | 9 | Sending data | SELECT cache.originaltext as originaltext, cache.translated as translated FROM vbvbenterprisetransla |
| 3102600 | underlinux | localhost:49907 | underlinux | Query | 9 | Sending data | SELECT cache.originaltext as originaltext, cache.translated as translated FROM vbvbenterprisetransla |
| 3102617 | underlinux | localhost:49944 | underlinux | Query | 6 | Sending data | SELECT cache.originaltext as originaltext, cache.translated as translated FROM vbvbenterprisetransla |
| 3102620 | underlinux | localhost:49953 | underlinux | Query | 6 | Sending data | SELECT cache.originaltext as originaltext, cache.translated as translated FROM vbvbenterprisetransla |
| 3102632 | underlinux | localhost:49986 | underlinux | Query | 3 | Sending data | SELECT cache.originaltext as originaltext, cache.translated as translated FROM vbvbenterprisetransla |
| 3102633 | underlinux | localhost:49988 | underlinux | Query | 0 | statistics | SELECT cache.originaltext as originaltext, cache.translated as translated FROM vbvbenterprisetransla |
| 3102636 | underlinux | localhost:49994 | underlinux | Sleep | 1 | | NULL |
| 3102639 | underlinux | localhost:50000 | underlinux | Query | 0 | statistics | SELECT cache.originaltext as originaltext, cache.translated as translated FROM vbvbenterprisetransla |
| 3102641 | underlinux | localhost:50005 | underlinux | Query | 0 | Sending data | SELECT cache.originaltext as originaltext, cache.translated as translated FROM vbvbenterprisetransla |
| 3102648 | underlinux | localhost:50021 | underlinux | Sleep | 1 | | NULL |
| 3102649 | underlinux | localhost:50022 | underlinux | Query | 0 | statistics | SELECT cache.originaltext as originaltext, cache.translated as translated FROM vbvbenterprisetransla |
| 3102651 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 3102652 | underlinux | localhost:50027 | underlinux | Query | 0 | statistics | SELECT translated FROM vbvbenterprisetranslator_cache_medium_vi WHERE originaltext='Cadastrar+200+cl |
| 3102660 | underlinux | localhost:50044 | underlinux | Query | 0 | statistics | SELECT translated FROM vbvbenterprisetranslator_cache_medium_fa WHERE originaltext='BackTrack+4+%28F |
| 3102661 | underlinux | localhost:50047 | underlinux | Query | 0 | statistics | SELECT cache.originaltext as originaltext, cache.translated as translated FROM vbvbenterprisetransla |
| 3102663 | underlinux | localhost:50051 | underlinux | Sleep | 1 | | NULL |
| 3102664 | underlinux | localhost:50052 | underlinux | Query | 1 | end | UPDATE vbuser SET
profilevisits = profilevisits + 1
WHERE userid = 61484 |
| 3102666 | underlinux | localhost:50056 | underlinux | Query | 0 | update | REPLACE INTO vbdatastore
(title, data, unserialize)
VALUES
('cron', '1275652500', 0) |
+********-+************+****************-+************+********-+****--+************--+****************************************************************************************************--+
20 rows in set (0.16 sec)

mysql>




mysql and cpu usage before and after vbet

http://under-linux.org/wiki/images/1/14/Mysql-vbet.png

http://under-linux.org/wiki/images/f/f0/Vbetmysql2.png

http://under-linux.org/wiki/images/6/65/Vbet-cpuusage.png

vBET
04-06-10, 14:34
Hello.

As you wrote vBET gave you lot of new traffic. So comparing CPU and mem usage before and after vBET, is really comparing CPU and mem usage before and after having bigger traffic...
So this comparison is not appropriate. There is no possibility to have same resource usage with bigger traffic. Also please note that vBET practically takes no resources for normal pages (only generates flags) so all your additional resource usage comes from additional traffic - traffic to translated pages. More traffic = more resource needed

Please note that vBET queries are optimized, all are using indexes and take all data in few queries. Please check your slow queries log to determine what really takes most of your MySQL time. Also - you can always rebuild indexes in case if something crashed in your MySQL DB.

Please tell what is the size of your cache tables? Also please tell which version of vBET are you using and give your forum URL.

Please check our performance hints: http://www.vbenterprisetranslator.com/forum/vbet4-general-discussions/408-vbet-performance.html

Of course we are improving vBET continuously. In next major version we will support file cache :) So performance improvements will happen :) Still please note that your way comparison is not appropriate and do not show how much more resources takes vBET - it shows how much more resources your forum takes with your bigger traffic.

magmf
04-06-10, 20:49
vBET,

file cache or mysql non cached queries use a lot of I/O making device load be HIGH.

My suggestion to you is use Memcache to cache instead o file and mysql or connect vbet with vboptimise that already have support for many types of caches

example of usage vboptimise with vbet will be something like that


if ($cache = vb_optimise::$cache->get('unique language key'))
{
***//use cache
}
else
{
***// get from elsewhere
***vb_optimise::$cache->set('unique language key');
}



why you dont try talk with Deceptor from DragonByte Tech Forums - DragonByte Technologies Home (http://www.dragonbyte-tech.com/)

my website is saving more than 10 million queries a day with vboptimise and if you work with him to better support vbet everyone can be happy

take a look
vB Optimise - Resource Statistics (http://www.dragonbyte-tech.com/vboptimisesites.php)

vBET
04-06-10, 21:29
Thanks for your advice :) I put your last post in TODO list.

Please answer given questions - what is your forum URL and used vBET version. We want to check do you have appropriate installation - previous vBET versions used double redirects which is not used anymore, in case of vBSEO it requires appropriate integration.

magmf
04-06-10, 21:38
my url is under-linux.org
vbet 4.2.2
vbulletin 4.0.3
vbseo 3.5 final

vBET
04-06-10, 21:49
OK - you do not have double redirects and last vBET version. At this moment I can only advise you to use our performance hints (link in first reply) - especially block irrelevant content in robots.txt - like member pages, calendar, and some others (note that those pages will disappear from indexes, but it is good for your SEO, because those are mostly considered as content duplication).

Also - if your server will hot handle all 52 languages - please consider to disable some of those. At this moment I understand that it handles it, you just would be more happy if with less resources ;)

We will also work on supporting not only file cache, but also existing cache libraries :) Thanks again for your hint.

magmf
04-06-10, 21:58
VBET,

I'm just worried with the future. For now my site can handle but since vbet is working I can get a lot of new users and this can make my server goes down :(

also i'm already blocking irrelevant content in my robots.txt

vBET
05-06-10, 20:27
Hawing lot of new users will make you think about scalability whenever you have vBET or not. We are really glad that vBET is giving you lot of new users and possible advertisement incomes :) As I already wrote we will improve caching system :)

vBET
10-06-10, 22:45
Next release will have full file cache which will make your pages generate faster for guests - even those not translated :)

bhanuprasad1981
14-06-10, 18:12
Next release will have full file cache which will make your pages generate faster for guests - even those not translated :)

hope we get this soon :)

vBET
14-06-10, 23:11
This month for sure :) Full file cache for guests is working fine on our forums, just want to add some other new functionalities :)

bhanuprasad1981
20-06-10, 17:03
any updates :)

vBET
21-06-10, 14:50
Today we will have release for vBET3.x - for vBET4.x in first week of next month :)

bhanuprasad1981
21-06-10, 18:53
ohhh no 15 days more :(

vBET
22-06-10, 22:32
We have more to do in case of vB4 (support Friendly URLs + more kinds of translated links for VBSEO). Also this time will give us feedback about this new functionality (i.e. we already got idea from one user to allow to disable it for normal pages). So it will take some more time, but at the end you will get solution which is better tested and with additional features :)

AfrikaansAlbanianArabicBelarusianBulgarianCatalanChineseCroatianCzechDanishDutchEnglishEstonianFilipinoFinnishFrenchGalicianGermanGreekHaitian CreoleHebrewHindiHungarianIcelandicIndonesianIrishItalianJapaneseKoreanLatvianLithuanianMacedonianMalayMalteseNorwegianPersianPolishPortugueseRomanianRussianSerbianSlovakSlovenianSpanishSwahiliSwedishTaiwaneseThaiTurkishUkrainianVietnameseWelshYiddish
Translated to other languages supported by vB Enterprise Translator 4.9.2