Splat against a scalability wall

[trophaeum]

So yea... my site went splat earlier... bigtime bug on a windshield splat... I'm running the latest SVN and was importing data and hit 130k links and it is now so slow and useless that its just plain funny. I have found a range of issues with Pligg that can quite easily cause and fix this however ideally I want SVN access so as I don't have to keep my own diff's in order each time I update.

What is involved in getting SVN access? Who do i talk to etc. Should I get SVN access, mostly what I will be looking into is scalability issues with Pligg and maybe at a later date i'll look into cleaning some things up and helping with other random things however I don't really have the time for those currently.

PM or email me asking for a username and password should it be that simple.

[ChuckRoast]

trophaeum

While I'm not doubting, nor challenging your skill set. Can you provide a bit more details other than "It went splat'?

How were you importing the 130,000+ posts? RSS import?
Did you set each post to allocate a random number of votes?
Can you provide a link to this "Splat"? Screen shots, Error details etc?

I'm sure you can understand. We do have many people that "chime in" here that have only touched upon the source code and really haven't the slightest clue how it works.

Even digg doesn't get 130,000+ posts pumped into their system in an entire day, and they are running a system slightly more advanced than our little single server code.

Please enlighten us a bit?

[trophaeum]

OK, here's an initial patch that did INSANE performance improvements on the frontpage.

if anyone can make

select link_id from pligg_links use index (PRIMARY) where link_id>=(select floor(max(link_id)*rand()) from pligg_links) order by link_id limit 1

work at a decent speed i'm all ears, i hate double querying but this just runs like a dog compared to the 2 queries, aparently the subquery is running multiple times. i'm over trying to debug it.

ALTER TABLE `pligg_links` ADD INDEX ( `link_title_url` )
ALTER TABLE `pligg_links` ADD INDEX ( `link_status` )
ALTER TABLE `pligg_votes` ADD INDEX ( `vote_type` )

3 new indexes

Index: modules/random_story/random_story_main.php
===================================================================
--- modules/random_story/random_story_main.php  (revision 1176)
+++ modules/random_story/random_story_main.php  (working copy)
@@ -3,14 +3,15 @@
 function random_story_getdata(){
        global $view, $db, $current_user, $main_smarty;

-       $cols = $db->get_col('select link_id from ' . table_links . ' where `link_status` = "published" order by link_id desc limit 200;');
-       //echo count($cols);
-       if($cols){
-               $randstory = rand(1, count($cols));
-               $randstoryurl = getmyurl("story", $cols[$randstory]);
-               $main_smarty->assign('random_story_randstoryurl', $randstoryurl);
-       }
+       $published = $db->get_var('select count(*) from '.table_links.' where link_status="published"' );
+
+       if ( ! $published ) {
+               return;
+       }
+       $linkid = $db->get_var('select link_id from ' . table_links . ' where `link_status` = "published" limit 1 offset '.mt_rand(0,$published));
+       $randstoryurl = getmyurl("story", $linkid);
+       $main_smarty->assign('random_story_randstoryurl', $randstoryurl);
 }


-?>
\ No newline at end of file
+?>
Index: libs/search.php
===================================================================
--- libs/search.php     (revision 1176)
+++ libs/search.php     (working copy)
@@ -115,11 +115,11 @@

                if($this->searchTerm == ""){
                        // like when on the index or upcoming pages.
-                       $this->sql = "SELECT DISTINCT link_id $from_where $search_clause $this->orderBy LIMIT $this->offset,$this->pagesize";
+                       $this->sql = "SELECT link_id $from_where $search_clause $this->orderBy LIMIT $this->offset,$this->pagesize";
                }else{
                        $this->sql = "SELECT link_id, link_date, link_published_date $from_where $search_clause ";
                }
-               $this->countsql = "SELECT count(*) $from_where $search_clause $this->orderBy";
+               $this->countsql = "SELECT count(*) $from_where $search_clause";

                return;
        }
@@ -367,4 +367,4 @@
        }
 }

-?>
\ No newline at end of file
+?>

patch some slow code

sometimes queries went from 4+seconds to .0something so this makes an incredible difference. more hunting to go, i'm sure theres more i can pull out of pligg!

[trophaeum]

I am sucking results from an api, not the rss module, 1 vote per entry, nothing more, tags are all there.

By splat i mean so server intensive i basically can't use it in its current state. i'm profiling sql queries as i speak to find what the main causes of this are however mysql is making a few stupid index selections now so i'm having to look into this a bit deeper.

Some table stats from the links table
Rows 137,546
Row length ø 661
Row size ø 1,113 B

Space usage Type Usage
Data 88,920 KiB
Index 60,544 KiB
Total 149,464 KiB

I hate spent some time to optimize mysql to handle this a bit better but i'v currently purposely disabled the mysql cache to see what queries are going to hit it HARD when the cache invalidates.

[trophaeum]

ok, scrap the index's from above, mysql makes a LOT of stupid index selections with them applied. currently only apply this extra index.

ALTER TABLE `pligg_links` ADD INDEX ( `link_title_url` )

specifically it speeds up finding new friendly url names for entries when they are posted.

[savant]

SourceForge.net: Subversion

For SVN access

[trophaeum]

I was after commit access, not just read, already talking to people on IRC to get things rolling tho. Should be some interesting performance patches to SVN soon enough

[trophaeum]

PS: Thanks to the mysql query optimizer being dumb as a post, im having to add ignore index hints randomly through the entire codebase as i type! 'fun' tasks...

[trophaeum]

sending the first patchset to ash as i type. watch the svn commits if ur interested, its upto ash to read through it, understand it and commit now

[savant]

nice nice