PDA

View Full Version : Solved CPU Usage Doubled



bhanuprasad1981
23-11-09, 17:34
i bought vbet yesterday itself and i see mysql usage has increased a lot and CPU usage has been doubled for my forum , what is reason how can i control ?

vBET
23-11-09, 19:22
vBET is doing nothing for normal pages. Normal generation of your forum didn't changed.

Of course you made to the word available your forum in other languages and this is not taken from nowhere - translated pages needs additional resources, but not as many as you describe. Please fallow how changed the traffic to your forum - if suddenly you generate much more pages it is normal that more CPU and more queries are required for this. So little more CPU of course is needed when you do additional things, but just a little and big usage progress is related to bigger traffic.

Please note that at the beginning data for translation are cached. This means that next generation of cached page will be much faster (also less queries will be made, since first translation is looked in cache, then is taken from Google, but next time is taken from cache with other related translations).

How you can safe CPU and queries:

Make sure you are using last version. We had little bug in 3.2.1 which really could improve CPU usage even for not translated pages (already corrected in 3.2.2). So if you are using 3.2.1 it is really our fault - please update to 3.2.2
You can disable some translations at the begging - when cache will be filled you can add new ones from time to time.
Please check do you have set using queries by serie - this will make lot less queries to database for cached data.
Disable translation of Google maps if not used
For later it will be important for you to have appropriate Cache TTL. Bigger is better, but not more that 15 days to be fair with Google TOS
Also if you have really huge forum you will need to change your cache clearing strategy - details in parameter description
For best performance use "Always trust user" strategy
Disable option "Don't translate usernames"
Block robots for not relevant pages - like members pages, calendar and so on. Robots are able to visit every corner of your forum, but mostly forum doesn't need member pages be indexed, but still it will take your forum resources to translate those pages. So block robots for all not relevant pages in all translations. Below you will find robots.txt example for that - this is part of robots.txt used by this forum (note that URL's are changed by vBSEO, and our forum uses subdirectory in URL).




User-agent: *
sitemap: http://www.vbenterprisetranslator.com/forum/sitemap_index.xml.gz
Disallow: /forum/members/
Disallow: /forum/af/members/
Disallow: /forum/sq/members/
Disallow: /forum/ar/members/
Disallow: /forum/be/members/
Disallow: /forum/bg/members/
Disallow: /forum/ca/members/
Disallow: /forum/zh-CN/members/
Disallow: /forum/hr/members/
Disallow: /forum/cs/members/
Disallow: /forum/da/members/
Disallow: /forum/nl/members/
Disallow: /forum/en/members/
Disallow: /forum/et/members/
Disallow: /forum/tl/members/
Disallow: /forum/fi/members/
Disallow: /forum/fr/members/
Disallow: /forum/gl/members/
Disallow: /forum/de/members/
Disallow: /forum/el/members/
Disallow: /forum/iw/members/
Disallow: /forum/hi/members/
Disallow: /forum/hu/members/
Disallow: /forum/is/members/
Disallow: /forum/id/members/
Disallow: /forum/ga/members/
Disallow: /forum/it/members/
Disallow: /forum/ja/members/
Disallow: /forum/ko/members/
Disallow: /forum/lv/members/
Disallow: /forum/lt/members/
Disallow: /forum/mk/members/
Disallow: /forum/ms/members/
Disallow: /forum/mt/members/
Disallow: /forum/no/members/
Disallow: /forum/fa/members/
Disallow: /forum/pl/members/
Disallow: /forum/pt/members/
Disallow: /forum/ro/members/
Disallow: /forum/ru/members/
Disallow: /forum/sr/members/
Disallow: /forum/sk/members/
Disallow: /forum/sl/members/
Disallow: /forum/es/members/
Disallow: /forum/sw/members/
Disallow: /forum/sv/members/
Disallow: /forum/zh-TW/members/
Disallow: /forum/th/members/
Disallow: /forum/tr/members/
Disallow: /forum/uk/members/
Disallow: /forum/vi/members/
Disallow: /forum/cy/members/
Disallow: /forum/yi/members/
Disallow: /forum/calendar.php
Disallow: /forum/af/calendar.php
Disallow: /forum/sq/calendar.php
Disallow: /forum/ar/calendar.php
Disallow: /forum/be/calendar.php
Disallow: /forum/bg/calendar.php
Disallow: /forum/ca/calendar.php
Disallow: /forum/zh-CN/calendar.php
Disallow: /forum/hr/calendar.php
Disallow: /forum/cs/calendar.php
Disallow: /forum/da/calendar.php
Disallow: /forum/nl/calendar.php
Disallow: /forum/en/calendar.php
Disallow: /forum/et/calendar.php
Disallow: /forum/tl/calendar.php
Disallow: /forum/fi/calendar.php
Disallow: /forum/fr/calendar.php
Disallow: /forum/gl/calendar.php
Disallow: /forum/de/calendar.php
Disallow: /forum/el/calendar.php
Disallow: /forum/iw/calendar.php
Disallow: /forum/hi/calendar.php
Disallow: /forum/hu/calendar.php
Disallow: /forum/is/calendar.php
Disallow: /forum/id/calendar.php
Disallow: /forum/ga/calendar.php
Disallow: /forum/it/calendar.php
Disallow: /forum/ja/calendar.php
Disallow: /forum/ko/calendar.php
Disallow: /forum/lv/calendar.php
Disallow: /forum/lt/calendar.php
Disallow: /forum/mk/calendar.php
Disallow: /forum/ms/calendar.php
Disallow: /forum/mt/calendar.php
Disallow: /forum/no/calendar.php
Disallow: /forum/fa/calendar.php
Disallow: /forum/pl/calendar.php
Disallow: /forum/pt/calendar.php
Disallow: /forum/ro/calendar.php
Disallow: /forum/ru/calendar.php
Disallow: /forum/sr/calendar.php
Disallow: /forum/sk/calendar.php
Disallow: /forum/sl/calendar.php
Disallow: /forum/es/calendar.php
Disallow: /forum/sw/calendar.php
Disallow: /forum/sv/calendar.php
Disallow: /forum/zh-TW/calendar.php
Disallow: /forum/th/calendar.php
Disallow: /forum/tr/calendar.php
Disallow: /forum/uk/calendar.php
Disallow: /forum/vi/calendar.php
Disallow: /forum/cy/calendar.php
Disallow: /forum/yi/calendar.php
Disallow: /forum/search.php
Disallow: /forum/af/search.php
Disallow: /forum/sq/search.php
Disallow: /forum/ar/search.php
Disallow: /forum/be/search.php
Disallow: /forum/bg/search.php
Disallow: /forum/ca/search.php
Disallow: /forum/zh-CN/search.php
Disallow: /forum/hr/search.php
Disallow: /forum/cs/search.php
Disallow: /forum/da/search.php
Disallow: /forum/nl/search.php
Disallow: /forum/en/search.php
Disallow: /forum/et/search.php
Disallow: /forum/tl/search.php
Disallow: /forum/fi/search.php
Disallow: /forum/fr/search.php
Disallow: /forum/gl/search.php
Disallow: /forum/de/search.php
Disallow: /forum/el/search.php
Disallow: /forum/iw/search.php
Disallow: /forum/hi/search.php
Disallow: /forum/hu/search.php
Disallow: /forum/is/search.php
Disallow: /forum/id/search.php
Disallow: /forum/ga/search.php
Disallow: /forum/it/search.php
Disallow: /forum/ja/search.php
Disallow: /forum/ko/search.php
Disallow: /forum/lv/search.php
Disallow: /forum/lt/search.php
Disallow: /forum/mk/search.php
Disallow: /forum/ms/search.php
Disallow: /forum/mt/search.php
Disallow: /forum/no/search.php
Disallow: /forum/fa/search.php
Disallow: /forum/pl/search.php
Disallow: /forum/pt/search.php
Disallow: /forum/ro/search.php
Disallow: /forum/ru/search.php
Disallow: /forum/sr/search.php
Disallow: /forum/sk/search.php
Disallow: /forum/sl/search.php
Disallow: /forum/es/search.php
Disallow: /forum/sw/search.php
Disallow: /forum/sv/search.php
Disallow: /forum/zh-TW/search.php
Disallow: /forum/th/search.php
Disallow: /forum/tr/search.php
Disallow: /forum/uk/search.php
Disallow: /forum/vi/search.php
Disallow: /forum/cy/search.php
Disallow: /forum/yi/search.php

bhanuprasad1981
24-11-09, 06:22
1. i am using latest version
2. how to disable some translations ?
3. how / where i set queries type
4. i do not use google maps
5. cache is set for 15 days already
6. cache deletion settings i use is "normal deletion"
7. i am already using "Always trust user"
8. on my forum guest cannot see member page, calender etc i already disabled them on suggestion at vbseo forums

vBET
24-11-09, 16:52
2. In Flags menu
3. In Misc - if you didn't made changes then you have it set appropriately
6. After 2 weeks you will see does ti fits to your forum. If not use other one.

bhanuprasad1981
24-11-09, 18:25
strange mysql errors never saw before got nearly 30 of these in past 24 hours :-

Database error in vBulletin 3.8.2:

Invalid SQL:

SELECT thread.threadid, thread.title, thread.lastpost, thread.forumid, thread.replycount,
thread.lastposter, thread.dateline, thread.postusername, thread.postuserid, thread.iconid,
IF(views<=replycount, replycount+1, views) AS views, thread.visible, thread.open,
user.username, user.userid, user.usergroupid,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid
FROM thread AS thread
LEFT JOIN user AS user ON (user.username = thread.lastposter)
WHERE NOT ISNULL(thread.threadid) AND thread.visible = '1' AND thread.open != '10'
AND thread.forumid NOT IN(136,9,10,89,105,106,109,139,78,97,133,134,135,128,129,130,131,132,142,,8,9,10,64,67,68,69,71,72,77,78,79,80,83,84,88,89,91,92,93,94,95,97,98,99,100,102,105,106,108,109,111,112,113,119,122,123,124,130,132,133,134,135,136,137,138,139,140,141)
ORDER BY dateline DESC
LIMIT 0, 10;

MySQL Error : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '8,9,10,64,67,68,69,71,72,77,78,79,80,83,84,88,89,91,92,93,94,95,97,98,99,100,102' at line 9
Error Number : 1064
Request Date : Tuesday, November 24th 2009 @ 08:18:03 AM
Error Date : Tuesday, November 24th 2009 @ 08:18:03 AM
Script : http://www.mysite.org/search.php
Referrer :
IP Address : xx.xx.xx.xx
Username : Unregistered
Classname : vB_Database_MySQLi
MySQL Version :

vBET
24-11-09, 18:49
This is the reason in your SQL: 142,,8

It is not related with vBET - vBET doesn't modify any existing queries and uses only 3 cache tables in it's own queries.

vBET
25-11-09, 01:55
Also one more hint for performance - you can disable option "Don't translate usernames".

I will add it to the first reply.

bhanuprasad1981
28-11-09, 07:46
damm this sql errors are haunting me , upon little verification i found ip belongs to some russian spider , it is not following robots.txt nad keeps on going for serach.php :( any idea about this yandex**** spider , how i block this thing :(

vBET
28-11-09, 20:51
damm this sql errors are haunting me , upon little verification i found ip belongs to some russian spider , it is not following robots.txt nad keeps on going for serach.php :( any idea about this yandex**** spider , how i block this thing :(

Find up its IP and block it in vB options or on firewall.

AfrikaansAlbanianArabicBelarusianBulgarianCatalanChineseCroatianCzechDanishDutchEnglishEstonianFilipinoFinnishFrenchGalicianGermanGreekHaitian CreoleHebrewHindiHungarianIcelandicIndonesianIrishItalianJapaneseKoreanLatvianLithuanianMacedonianMalayMalteseNorwegianPersianPolishPortugueseRomanianRussianSerbianSlovakSlovenianSpanishSwahiliSwedishTaiwaneseThaiTurkishUkrainianVietnameseWelshYiddish
Translations by vB Enterprise Translator 4.9.99