Vbulletin Site - High mysql Load ( 5-10 )

[viruz99]

Hey Guys ,

Just moved my vbulletin site to a new vps but its loading extremely slow and with a very high mysql load ( 5-10 ) with only ~100 users on the site

Any way to fix this ?

Here are the current mysql queries.

mysql> show processlist;
mysql> +------+------------+-----------+------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------------+-----------+------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| 1654 | sitename | localhost | sitename | Query | 28 | Locked | UPDATE ws_user
SET lastactivity = 1321130755
WHERE userid = 4056 |
| 1825 | sitename | localhost | sitename | Query | 27 | Locked | SELECT DISTINCT user.email, user.languageid
FROM ws_moderator AS moderator
LEFT JOIN ws_user |
| 1848 | sitename | localhost | sitename | Query | 28 | Locked | SELECT DISTINCT user.email, user.languageid
FROM ws_moderator AS moderator
LEFT JOIN ws_user |
| 1869 | sitename | localhost | sitename | Query | 28 | Locked | SELECT DISTINCT user.email, user.languageid
FROM ws_moderator AS moderator
LEFT JOIN ws_user |
| 1878 | sitename | localhost | sitename | Query | 28 | Locked | SELECT DISTINCT user.email, user.languageid
FROM ws_moderator AS moderator
LEFT JOIN ws_user |
| 1879 | sitename | localhost | sitename | Query | 28 | Locked | UPDATE ws_user SET
posts = posts + 1,
usertitle = 'Pink Panther',
lastpost = 1321131286,
las |
| 1888 | sitename | localhost | sitename | Query | 150 | Sorting result | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1889 | sitename | localhost | sitename | Query | 28 | Locked | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1890 | sitename | localhost | sitename | Query | 149 | Sorting result | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1891 | sitename | localhost | sitename | Query | 147 | Sorting result | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1892 | sitename | localhost | sitename | Query | 142 | Sorting result | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1893 | sitename | localhost | sitename | Query | 136 | Sorting result | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1894 | sitename | localhost | sitename | Query | 28 | Sorting result | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1895 | sitename | localhost | sitename | Query | 28 | Sorting result | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1896 | sitename | localhost | sitename | Query | 28 | Sorting result | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1897 | sitename | localhost | sitename | Query | 123 | Sorting result | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1898 | sitename | localhost | sitename | Query | 28 | Locked | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1899 | sitename | localhost | sitename | Sleep | 119 | | NULL |
| 1900 | sitename | localhost | sitename | Query | 119 | Sorting result | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1901 | sitename | localhost | sitename | Query | 117 | Sorting result | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1902 | sitename | localhost | sitename | Query | 111 | Sorting result | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1903 | sitename | localhost | sitename | Query | 111 | Sorting result | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1904 | sitename | localhost | sitename | Query | 8 | Locked | SELECT
post.*, post.username AS postusername, post.ipaddress AS ip, IF(post.visible = 2, 1, 0) AS |
| 1905 | sitename | localhost | sitename | Query | 106 | Sorting result | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1906 | sitename | localhost | sitename | Query | 104 | Sorting result | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1907 | sitename | localhost | sitename | Query | 28 | Sorting result | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1908 | sitename | localhost | sitename | Query | 98 | Sorting result | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1909 | sitename | localhost | sitename | Query | 28 | Sorting result | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1910 | sitename | localhost | sitename | Query | 28 | Locked | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1911 | sitename | localhost | sitename | Query | 28 | Sorting result | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1912 | sitename | localhost | sitename | Query | 94 | Sorting result | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1913 | sitename | localhost | sitename | Query | 94 | Sorting result | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1914 | sitename | localhost | sitename | Query | 81 | Sorting result | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1915 | sitename | localhost | sitename | Query | 28 | Sorting result | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1916 | sitename | localhost | sitename | Query | 28 | Locked | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1917 | sitename | localhost | sitename | Query | 76 | Sorting result | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1918 | sitename | localhost | sitename | Query | 71 | Sorting result | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1919 | sitename | localhost | sitename | Query | 28 | Sorting result | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1920 | sitename | localhost | sitename | Query | 28 | Sorting result | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1921 | sitename | localhost | sitename | Query | 28 | Locked | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1922 | sitename | localhost | sitename | Query | 28 | Locked | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1923 | sitename | localhost | sitename | Query | 24 | Locked | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1924 | sitename | localhost | sitename | Query | 22 | Locked | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1925 | sitename | localhost | sitename | Query | 20 | Locked | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1926 | sitename | localhost | sitename | Query | 20 | Locked | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1927 | sitename | localhost | sitename | Query | 14 | Locked | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1928 | sitename | localhost | NULL | Query | 0 | NULL | show processlist |
| 1929 | sitename | localhost | sitename | Query | 7 | Locked | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
| 1930 | sitename | localhost | sitename | Sleep | 4 | | NULL |
| 1931 | sitename | localhost | sitename | Query | 4 | Locked | SELECT thread.lastposter, thread.lastpost, thread.views, thread.open, thread.forumid, thread.replyco |
+------+------------+-----------+------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
50 rows in set (0.00 sec)

viruz99 Reviewed by viruz99 on 12th Nov 2011. Vbulletin Site - High mysql Load ( 5-10 ) Hey Guys , Just moved my vbulletin site to a new vps but its loading extremely slow and with a very high mysql load ( 5-10 ) with only ~100 users on the site Any way to fix this ? Here are the current mysql queries. mysql> show processlist; mysql> +------+------------+-----------+------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+ Rating: 5

[-sky-]

set your msql to mode log

[onel0ve]

http://*******/9mCgL
and
install Sphinx Search For vBulletin 4
and optimize mysql database