VBET HIGH CPU/Memory usage due to mysql

[magmf]

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

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

[vBET]

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.co...rformance.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]

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

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

[vBET]

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]

my url is under-linux.org
vbet 4.2.2
vbulletin 4.0.3
vbseo 3.5 final

[vBET]

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]

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]

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]

Next release will have full file cache which will make your pages generate faster for guests - even those not translated

[bhanuprasad1981]

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