Mysql makes high load !!! Please help

[Alexxi]

High i'm fed up, i don a lot of things to optimize my servers high load, but with no success...
Will the new version have other , faster and better queries, cause at night when i have a lot of visitors, my server goes mad , having high load of 12-17 !!!
Mysql config is this (my.cnf) :

[client]
port            = 3306
socket		  = /var/lib/mysql/mysql.sock

[mysqld_safe]
socket		  = /var/lib/mysql/mysql.sock
nice            = 0

[mysqld]
user            = mysql
socket		  = /var/lib/mysql/mysql.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
language        = /usr/share/mysql/english
skip-external-locking

key_buffer         = 16M
key_buffer_size         = 96M
sort_buffer_size        = 1M
read_buffer_size        = 3M
read_rnd_buffer_size    = 1M
max_allowed_packet      = 16M
thread_stack            = 128K
table_cache             = 1504
open_files_limit        = 3760
thread_cache_size       = 72
max_heap_table_size     = 256M
tmp_table_size          = 128M
query_cache_limit       = 3M
query_cache_size        = 256M
query_cache_type        = 1
low_priority_updates    = 1
join_buffer_size        = 3M
max_connections         = 300
long_query_time         = 3
log-slow-queries        = /var/log/slowq.log
skip-bdb

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer              = 16M

[CrumX]

I do have a problem with High CPU Load for view video.php. I already try removing everything which leaving only the video player and a chatango box (embeded flash java script chat).

At 100 user online the cpu usage is around 8-10%. At 200 user online its up to 27-30%. This is really really freaking high cpu load. I have 1k ++ user and the cpu load is above 100% on which force me to get a dedicated juz for this reason.

Is there anything this is my tpl file for view video

<table cellspacing="0" cellpadding="0" width="100%" align="center" border="0">
<tr valign="top">

<td width="98%">
<div class="section">
<div class="hdmaroon">
<div class="hd-l">{$video_info.vtitle} - Episode {$video_info.vepisode_num}</div>
</div>
<div style="padding: 1em; background-color: #FFFFFF">
<div style="padding-right: 10px; padding-left: 10px; padding-bottom: 2px; padding-top: 0px">
{$html_vtitle} {$video_info.vepnum}
{$VSHARE_PLAYER}
</div>
<center>{insert name=advertise adv_name='banner_top'}</center>
</div>
</div>
</td>


<td width="9"></td>

<td width="270">

<div class="section">
<div class="hdgreen">
<div class="hd-l">Related Videos</div>
</div>
<table cellspacing=0 cellpadding=0 width=298 border=0 >
<tr>
<td align="middle" width="104" style="padding-left:5px;">
{if $video_prev === ''}
[img]{$baseurl}/templates/images/no_prev.gif[/img]
&lt; PREV
{else}
<a href="{$baseurl}/view/{$related_videos[$video_prev].VID}/{$related_videos[$video_prev].seo_name}/">
[img]{$tmburl}/1_{$related_videos[$video_prev].VID}.jpg[/img]</a>
<div style="font-weight: bold; font-size: 10px; padding-top: 3px">
&lt; PREV
</div>
{/if}
</td>
<td align="center" style="padding-top:10px;">
[img]{$tmburl}/1_{$smarty.request.id}.jpg[/img]<div style="font-weight: bold; font-size: 10px; padding-top: 3px">NOW PLAYING</div>
</td>
<td align="center" style="padding-left:8px;">
{if $video_next eq ""}
[img]{$baseurl}/templates/images/no_next.gif[/img]
NEXT &gt;
{else}
[img]{$tmburl}/1_{$related_videos[$video_next].VID}.jpg[/img]
<div style="font-weight: bold; font-size: 10px; padding-top: 3px">
NEXT &gt;
</div>
{/if}
</td>
</tr>
</table>
</div>

<div class="section" style="background: #005B9A; margin-bottom: 0.9em;">
<center>
<input type=button value="Show/Hide Chat" onClick="showdiv('showhide');">
<div id="showhide" class="on">
<object width="300" height="350" id="obj_1231750498750"><param name="movie" value="http://anime-daisuki.chatango.com/group"/><param name="wmode" value="transparent"/><param name="AllowScriptAccess" VALUE="always"/><param name="AllowNetworking" VALUE="all"/><param name="AllowFullScreen" VALUE="true"/><param name="flashvars" value="cid=1231750498750&a=005B9A&b=100&c=FFFFFF&d=FFFFFF&g=333333&j=333333&k=005B9A&l=005B9A&m=005B9A&n=FFFFFF&s=1&t=0&v=0&w=0"/><embed id="emb_1231750498750" src="http://anime-daisuki.chatango.com/group" width="300" height="350" wmode="transparent" allowScriptAccess="always" allowNetworking="all" type="application/x-shockwave-flash" allowFullScreen="true" flashvars="cid=1231750498750&a=005B9A&b=100&c=FFFFFF&d=FFFFFF&g=333333&j=333333&k=005B9A&l=005B9A&m=005B9A&n=FFFFFF&s=1&t=0&v=0&w=0"></embed></object>
</div>
</center>
</div>

<div class="section">
<div class="hdorange">
<div class="hd-l">Vote For Us!</div>
</div>
<div style="padding:1em; background-color: #FFFFFF;">
<center>










</center>
</div>
</div>

<div class="section">
<div class="hdblue">
<div class="hd-l">Share Details</div>
</div>
<div style="padding:1em;">
<form id=linkForm name=linkForm>
<span style="padding-right: 2px; padding-left: 2px; padding-bottom: 2px; padding-top: 2px;">Embeddable Player:</SPAN>



<input name="video_play" value='{if $embed_type eq "0"}<iframe vspace="0" hspace="0" allowtransparency="true" scrolling="no" marginwidth="0" marginheight="0" frameborder="0" style="border:0px;" width="435" height="470" SRC="{$baseurl}/show.php?id={$video_info.VID}"></iframe>{else}
{$video_info.vtitle} - Episode {$video_info.vepisode_num}
<object width="330" height="250" type="application/x-shockwave-flash" data="{$baseurl}/player/player.swf"><param name="movie" value="{$baseurl}/player/player.swf"><param name="allowFullScreen" value="true"><param name="flashvars" value="&file={$video_info.embed_code}&height=250&image={$baseurl}/thumb/1_{$video_info.VID}.jpg&width=330&logo={$baseurl}/templates/images/playerlogo.png&skin={$baseurl}/player/snell.swf&menu=false&location={$baseurl}/player/player.swf"/></object>{/if}' size="45" onclick=javascript:document.linkForm.video_play.focus();document.linkForm.video_play.select(); readOnly>
<div style="font-size: 11px"> (Put this video on your website. Works on Friendster, eBay, Blogger, MySpace!) </div>




</form>
</div>
</div>

</td>
</tr>
</table>
</td>

Since the php file is zend encoded I cant do anything about it I hope somebody else provide me the encoded version for the php file.

[bplex]

Part of your high load problem is going to be due to the fact that vShare logs every connection that you get to mySQL. If you have a high traffic site, this can cause mySQL to have to right thousands, if not millions, of entries into the database. Unfortunately, there is no way to turn that off in the current version. However, you can periodically clear it out. By periodically clearing it out, your mySQL load will drop considerably. You can get the code to do this at viewtopic.php?f=6&t=3955&p=17817&hilit=guest_info# p17817

[Alexxi]

Hello, thanks for your reply, but i'm already having that table cleaned through cron job every 20 minutes, so the problem lies somewhere else..

[bplex]

Not necessarily. If you are having 200+ users online at any given time, you need to be clearing that table every 5 minutes, not every 20. 200 users per second x 20 minutes is nearly a quarter of a million queries. You may also want to look at doing some mySQL optimization or offload mySQL to cope with your load. Are you on a dedicated server or a VPS?

[Alexxi]

hi,
i'm on dedicated server, and wierd thing is during when i have this high visitor count, and i want to approve a video , my server load goes up to as high as 40 load avg!
WEll for now i will try deleting guest_info every 5 minutes, hope it will help!

ps.: do you know if the bug with "adding tags to database separated by spaces" will be solved in Vshare 2.7 ?

[bplex]

I'm not certain if it will or not. The "bug" is actually for tags separated by commas as well, not just those separated by spaces. The weird thing is that it isn't consistent. I've had some that work fine and others that did not work so well.

[Alexxi]

Well, high load is still a problem... and as i said before: when i have a lot of visitors, and i want to approve 1video, it tooks 1 minute to approve it and during that, load avg goes up to as high as 20-30 !

[OzzyWeb]

I'm having the same problem with 2.7 for video file view_video.php

what is the best way to fix this problem?

[hostonnet]

Server may be overloaded, optimizing mysql and apache can help, this need to be done by experianced server admin.

In vshare there is a cache feature, in admin area, settings, enable cache, this will reduce mysql usage by caching the pages.