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

[eluser]Hockeychap[/eluser]
Have had a quick look at the model. Couple of things jump out:

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

Code:
LEFT JOIN meet_athletes AS Meet_Athlete ".
                    "ON Meet_Event.id = Meet_Athlete.meet_event_id ".

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:

Code:
LEFT JOIN meet_athletes AS Meet_Athlete ".
                    "ON Meet_Event.Event_id = Meet_Athlete.meet_event_id ".

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
#12

[eluser]dmorin[/eluser]
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:

Code:
ini_set("memory_limit","64M");

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-gui...hmark.html
#13

[eluser]dmorin[/eluser]
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:

Code:
Loading Time Base Classes - 1.9890
Controller Execution Time ( Meets / Events ) - 0.0627
Total Execution Time - 2.0519

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.
#14

[eluser]Jay Logan[/eluser]
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:

Code:
$autoload['libraries'] = array('database', 'sessions', 'auth', 'flash',
    'js', 'validate', 'form');

$autoload['helper'] = array('url', 'form', 'app');

$autoload['plugin'] = array();


$autoload['config'] = array();

And what could be causing this error to appear?

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

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.
#15

[eluser]Hockeychap[/eluser]
Hmm interesting all-round.

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

Code:
Loading Time Base Classes      0.0389
Controller Execution Time ( Results / Event )      9.5107
Total Execution Time      9.5498

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 ?
#16

[eluser]dmorin[/eluser]
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:

Code:
mysqladmin -u db_username -p processlist

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.
#17

[eluser]Jay Logan[/eluser]
Yes, I have phpMyAdmin under my cPanel.
#18

[eluser]Jay Logan[/eluser]
Sorry for my ignorance but where would I execute that command? I do have root access to my server and I run cPanel/WHM on it. I'm using a Mac.
#19

[eluser]dmorin[/eluser]
if you have root access, do that, whether ssh or some kind of console, and just run the program from the terminal. Make sense?
#20

[eluser]Hockeychap[/eluser]
If you have phpmyadmin then you can see the full stats of the database (this is the sort of equivalent of the command line mysqladmin step. If your cPanel looks like mine then :

From the CPanel page for the domain -> phpmyadmin
When that comes up -> "show mysql runtime information".

In the very first table it should have a couple of useful numbers namely,

max. concurrent connections 5 --- ---
Failed attempts 0 0.00 0.00%
Aborted 0 0.00 0.00%

These will tell you the maximum numbers of users and how many aborts happened.

Once you've got those, click on the variables tab and look for "max connections". What value is in there ?

Unfortunately, I am going to be out for the evening in about 10 mins, so will catch up on this thread in the morning. Good luck.

Cheers,
Justin




Theme © iAndrew 2016 - Forum software by © MyBB