Hall of Fame

[TigerClaw]

Hello,

I already checked the other threads

http://www.phpvibe.com/forum/troubleshooting-issues/videos-showing-per-category/msg14281/#msg14281

about this but they are 1 year old and the code looks very different from the one I have.
Could you please point out how to disable everywhere the infinite scrolling? The queries are killing my server and I have been forced to use a workaround with the query timeout.

I managed to disable it for channels but I still have to disable it for the homepage and for Recent, Most Viewed, Most Liked pages.

I'm using the last release before 4.0.

Thank you

[TigerClaw]

It seems the Recent, Most Viewed, Most Liked pages linked from the sidebar are not influenced by neither home.php or channel.php

[PHPVibe A.]

Home doesn't use infinite scroll in 4.0
That code still works fine for channels.
For videos you need to count that query in the video block.

[TigerClaw]

Could you please tell me in which file I can find the video block?

Thanks

by the way this is the query
"SELECT id ,title from vibe_videos where id > xxxxxx order by id asc;"

# Thread_id: 245963  Schema: xxxx_vibe  QC_hit: No
# Query_time: 14.507697  Lock_time: 0.000027  Rows_sent: 97690  Rows_examined: 202701

SELECT id ,title from vibe_videos where id > xxxx order by id asc;

The server has 64 Gb of RAM, RAID 6 of Sata 7200 Rpm. It has plenty of resources to manage the DB. My.cnf can't be optimized more.

The problem is some of these queries pile up and reach the max_connections limit blocking out everybody else. I'm using now a workaround limiting the query time but I want to get rid of them completely.

We already discussed about this query here:

http://www.phpvibe.com/forum/troubleshooting-issues/no-database-indexes/msg17696/#msg17696

Does it change in 4.0?

[PHPVibe A.]

From what I can make of that query, it's the query from the function "Guess next"
which pulls the next video in a playlist. Will check it asap.

[TigerClaw]

Ok thank you. By the way I'm desperate for performance so if you can just tell me which code i should remove completely so users can't use this function would be enough for now

[PHPVibe A.]

I've added all those indexes to sqlupdater.php and orginal setup, you can run it once and check.

[PHPVibe A.]

If you remove the guess next...it won't jump to next song in the playlist...let me check it more.

[TigerClaw]

Sorry if I'm asking again but it is a production site with a lot of customizations so I have to be sure 

Can I apply sqlupdater.php to the last revision before 4.0?

Anyway you should be right about the playlist. I'm having issues to replicate the error since I cut the waiting time on MySQL but I see it is the only part of the website that still lags a lot.

That query needs to be cleaned up. Once the users start to make a lot of playlist in bigger sites (>100k videos) the load on the server is way too high, doesn't matter which server it is.

Thank you for your help Alexander

[PHPVibe A.]

Yes, it won't double fields/indexes, may prins some warnings, but it should be safe.
Let me know if that does it for the playlist query.

[TigerClaw]

Got this error when trying to update my test installation which is using the last revision of the 3.x branch:

Homepage table updated...
Users table updated...Search indexes updated...
Performance indexes updated...
ezSQL (v2.17) Debug..

Last Error -- [BLOB/TEXT column 'date' used in key specification without a key length]

Query [21] -- [alter  table vibe_likes add unique uid_uni (uid, vid)]

Query Result..

No Results

Anything to be worried about? Thank you

P.S. Sorry about posting in the 4.0 thread. I thought it would have been appropriate considering I'm using the 4.0 sqlupdate. My bad.

[PHPVibe A.]

That wasn't for you Was for all the cloudfare and 'how to' stuff which was posted, which has no connection to that thread.

That's my mistake, i've doubled the row, you can remove the first one for date index:

Code Select Expand

$db->query("alter  table ".DB_PREFIX."videos add index dates_idx (date)");

But you should not worry about this, it's 100% a harmless warning.

[TigerClaw]

I updated the production site and I got the following error.

Could you please confirm that I would see all errors in the output? The site is really busy and have a very strict wait_timeout (3 seconds). So I was worried the queries will not be executed correctly.

Thanks

Code Select Expand

Homepage table updated...
Users table updated...Search indexes updated...
Performance indexes updated...
ezSQL (v2.17) Debug..

Last Error -- [BLOB/TEXT column 'description' used in key specification without a key length]

Query [21] -- [alter  table vibe_likes add unique uid_uni (uid, vid)]

Query Result..

No Results

[PHPVibe A.]

I've put a note on that on the first post about it It's harmless, check your search, if any problems search won't work.

[TigerClaw]

After a few days of testing I can see the situation is a bit better but the query for the playlist should be rewritten. On sites were the users don't use playlist the mysql PID uses only 50% of 1 core. On the others it spikes to 800% even after the sqlupdate. All servers are identical.

[PHPVibe A.]

Did you patch it with the playlist fix? Or it's the old playlist system?

[TigerClaw]

I create the indexes with the slqupdate.php. I did not upgrade to 4.0. Did you change the query in the new version?

[PHPVibe A.]

Yes, because it wasn't selecting good the videos.