Welcome Guest, Not a member yet? Register   Sign In
Site Is So Slow!
#1

[eluser]Jay Logan[/eluser]
I just recently added a new feature to my CI powered site. Ever since, the site runs so slow. And unfortunately, the new feature was a pretty drastic change so I can't just roll back to a previous version of the site (without losing a lot of data). Not only is the site slow, but a lot of my users are seeing this error:

Fatal error: Call to a member function num_rows() on a non-object in /home/ptentry/public_html/application/libraries/Sessions.php on line 46

The site is heavily database driven and my best guess is that it is causing stress on my server. I have a dedicated server running cPanel. I'm at the point now where I'd pay for someone to get things running right.

I guess my question is what could have caused this to happen?

All the new feature did was basically change my DB structure, and make it so the data uploaded from a CSV is compared to data I already have in my database. If the data is found, nothing happens. If the data is not found, it inserts into a database. And some new routes were added.

Would DB cache help? Is active record slowing things down? Any input would be appreciated.
#2

[eluser]Hockeychap[/eluser]
Just a couple of quick thoughts on the mysql front:

1. How is the comparison being done for the existing data ?. It may be worth looking at the insert ..on duplicate key update syntax as a quicker way of doing things as the update statement will not actually do any updates in the data is the same.

2. Temporarily turn on the slow , error and possibly query log files to see what's causing you pain. (it's worth checking the slow_queries varible to see if you have any slow queries first).

3. Check the indicies that you use on the tables. People commonly miss off secondary indicies. The query log is a good place to start to see what's actually happening (although it can get quite large quite quickly fast).

I'd recommend cloning your database to a local machine if possible as you can then run through the app, turn on the log files etc without impacting your production setup.

If nothing comes up there it's also worth looking in slightly more detail at the db set to make sure it's reasonably well tuned for the number of connections that you are running.

Hope this helps
#3

[eluser]Krzemo[/eluser]
I'd start with it this way:
1) check cpu and memory usage by process (I assume that bandwidth is not the case here)
2) if its DB, I'd start loggin queries and their execution times (if it hasn't been done before, there should be lots of place for improvements like indexes, tables analyzing, rewriting queries, etc.. When I was optimizing DB for a first time I almost couldnt belive how much faster a query can get when you dig into it.)
3) if mem usage is not around 100% and you have significantly more selects then anything else and most of them are repetable - cache will help for sure.
4) add few more CPUs and memory - its often cheaper then rewriting application Wink
#4

[eluser]Jay Logan[/eluser]
Thank you both for the tips. I will get someone to work on all that for me because I don't know much about the server side of web sites. I'm a noob dedicated server owner. And my programmer is MIA so if anyone is interested in checking this out for me, PM me and I can take care of you via PayPal.
#5

[eluser]Jay Logan[/eluser]
OK so 1 page that appears to run particularly slow is a page that shows all the events for a meet and counts how many athletes are participating in that event. Some meets have 40 events or more so I would imagine that kind of query could tax my server a bit. Is there any obvious bad practice with the code I use below for my model?


Code:
function get_events_extended($meet_id, $coach_id)
    {
        
        $result = $this->db->query(
            "SELECT Meet_Event.*, Event.*, COUNT(Meet_Athlete.id) AS Total, ".
                "COUNT(Coach.id) AS Coach ".
                "FROM meet_events AS Meet_Event ".
                "LEFT JOIN events AS Event ".
                    "ON Meet_Event.event_id = Event.id ".
                "LEFT JOIN meet_athletes AS Meet_Athlete ".
                    "ON Meet_Event.id = Meet_Athlete.meet_event_id ".
                "LEFT JOIN athletes AS Athlete ".
                    "ON Meet_Athlete.athlete_id = Athlete.id ".
                "LEFT JOIN roster AS Roster ".
                    "ON Roster.athlete_id = Athlete.id ".
                "Left Join coaches AS Coach ".
                    "ON Roster.coach_id = Coach.id AND Coach.id = ? ".
                "WHERE Meet_Event.meet_id = ? ".
                "GROUP BY Meet_Event.id ".
                "ORDER BY Event.type DESC, Event.name ASC, Meet_Event.gender ASC",
            array(
                $coach_id,
                $meet_id
            )
        );
        return $this->clean_result($result);
    }
#6

[eluser]jalalski[/eluser]
Try removing the GROUP BY clause. I have a vague memory of that being problematic in some versions of MySQL, having GROUP BY combined with ORDER BY.
If it makes a difference, then you can find a way to rewrite the query.
#7

[eluser]Krzemo[/eluser]
Group by seems obligatory as you are counting records there...

1)
I dont see a point in all those joins which you are not using at all in output.
I can see that you are getting data only from
meet_events
events
meet_athletes
coaches

wile having 6 tables joned and not making any conditions on those unused...

2)
Im not sure how it works in MySQL but in Oracle using statment like Meet_Event.* instead of listing all columns literaly like Meet_Event.id, Meet_Event.name etc also gives some overheads (this migh be as small as one ms, but when multiplied by 10 000 page loads...)

3)
I would run tuned query and get its explain plan to see what else we can do about tables underneath it.

Thats for the begining...
#8

[eluser]Hockeychap[/eluser]
Oops - no don't take out the group by statement - you'll need it for the count(). Although there is a bug in the the group by / order by scenario when there is an union of multiple tables.

I'll have a look at the query in detail on the train home tonight, but first thought is to make sure you have indicies on all the join columns.
#9

[eluser]dmorin[/eluser]
As others have mentioned, unless your pretty familiar with MySQL, this is going to be difficult to work on. The first thing I would recommend doing is on your development machine, preferably with the production data, enable the profiler by adding the following somewhere so it will be included on every page:

Code:
$this->output->enable_profiler(TRUE);

For example, if you're autoloading any of your own libraries, put that in the constructor of the library and it will be included on every page. Then, browse the site and look at the bottom of the page to see the profile for that page. This will tell you how long SQL queries are taking and point out any other issues. Post back with that data and we can go from there, most likely with optimizing queries.
#10

[eluser]Jay Logan[/eluser]
I have enabled the profiler on my development server. You can see the output here: http://www.caals.com/4/meet/815398826

The slowest page I find is a link on the page above. Click the link above "Click the graphic above to register your athletes for this meet."

How do the numbers look?




Theme © iAndrew 2016 - Forum software by © MyBB