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.

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

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

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.

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?

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.

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…

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.

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:

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.

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?

Have had a quick look at the model.  Couple of things jump out:

1. Think there’s a typo in the code.

In the left hand side of the join condiion you have a field called Meet_Event.id.  In all the other cases, the field is linked on Meet_Event.Event_Id.  So can you check that your table Meet_Events actually has a field called ID. My guess is that the line should read:

2. A six table chain to get a couple of counts is probably a little excessive. In version 3.x of Mysql there was about a 3 table limit before performance pitched.  You’re going to be near to a performance limit (will have to try and simulate this at home tomorrow).

NB. I will caveat that with the fact that the design of the tables seems to be correct from a dba viewpoint.

3. As I think I said earlier, check the tables for indicies on the keys that you are joining on.  If I’ve read your sql properly, can you check that have an index on each of the following:

Meet_Athletes.Meet_Event_ID
Events.Event_ID
Athletes.Athlete_ID
Roster.Athlete_ID
Coaches.Coach_ID

4. If the indicies don’t help, then I would start looking into using sub-queries on the section of the query dealing with the athletes and their coaches.

5. If after all of this, you;re queries are still running unacceptably slowly, then it will be worth fracturing your query into two or three parts and loading them into a number of php arrays (as array key lookups in PHP is very fast). I would only do this as a final step.

As a general comment, bar the number of joins, this is not a complex query and should be well withing MYSQLs capability, even on a shared hosting platform. 

If you need any help with checking the tables or want me to have a look at the actual query in action, post a note back and I’ll explain how to extract and zip up the data so that I can load it onto a spare server I manange.

Hope this helps,

Cheers
Justin

I can’t even get the page at the link above “Click the graphic above…” to load.  Maybe your memory limit is too low or maybe it’s just taking too long.  Try adding the following to the method that isn’t loading:

The page you linked to above is interesting.  It’s loading the CI base quickly and all of the queries are executing quickly, but your page is still taking > 1 second to complete so something very inefficient is going on.  If you look at the benchmark page in the CI Documentation, you’ll see how to add benchmarks to your php.  Doing that around anything you suspect may be slow will show you how long it’s taking to execute.

http://ellislab.com/codeigniter/user-guide/libraries/benchmark.html

Finally got that page to load.  The queries were really fast, including the super join one, but they’re probably being cached in the MySQL query cache so that doesn’t mean they aren’t part of the problem.  However, the profiler also recorded this:

Notice how long it takes to load the base classes here?  That’s very strange.  What are you auto-loading and do any of your auto-loaded libraries have intense processes that are called from the constructor?  Loading the base classes should be very quick.  For example, on my dev site, it’s 0.0040 seconds.

So could it be a server issue? I have a dedicated server running cPanel / WHM. Do I need to clear some MySQL query cache? For my auto-loaded libraries, this is what I have:

And what could be causing this error to appear?

I think what I am going to try next is upload the site onto this HostGator account I use for another site. Maybe it’s just this dedicated server.

Hmm interesting all-round.

Picked a results page and in this case the loading time was small and the db time high ?!?

Wonder if the constructor on your site is creating a db connection and the database is flat out. If the connection fails, then the rest of the process will fail.

Do you have access to either phpmyadmin or a mysql client on your hosting platform? I have a feeling that the setting in my.ini governing maximum numbers of concurrent connections for your site may not be high enough ?

The MySQL query cache shouldn’t be an issue.  The previous post is on to it.

The Fatal error is caused when your site looses a connection to the database server so essentially, your query fails and when you try to figure out how many rows were returned, it fails.  This can be caused by many things.  If you have access to your database server, try running this:

Replacing db_username with a user in the db that has admin/root permissions.  If you can run that, let us know how many open connections there are.