Yet another mysql question...

[eluser]The New Guy[/eluser]
Hi there guys , i'm using CI on my project and everything works ok, but i have real problems with the sql queries. I'm using the AR class and on development everything went smoothly , but when i made public the website , when i have over 200 visitors at a time , the site loses the connection to database ... and databases errors are shown . Now , i'm no sql guru at all , but at least i created indexes on my tables and did some optimization of the database , but still the problem persists .
So my question , even if is not related to CI entirely , what should i do to avoid this kind of errors ?
Give me a starting point to learn sql as i should ...
Thank you in advance.

[eluser]jedd[/eluser]
Hey The New Guy (aren't you going to hate that appellation in a few months when you've cranked up a few hundred posts?)

You appear to be blaming MySQL but haven't demonstrated why you've concluded it's the problem?

Have you turned on Profiling (do you know about the CI Profiler?) What does this reveal about the time taken to perform each component. Have you looked at things like the PHP Fire Bug extension to do even more in depth analysis?

Have you monitored the OS during the problem to see if you have a lower level problem?

Apache logs (ditto)?

Have you played with PHPMySqlAdmin thing - so you can monitor the health and activity of your database server?

What results did you get when you did volume load testing in your development environment (ha! But I have to ask, just in case).

[eluser]The New Guy[/eluser]
Hey The New Guy (aren't you going to hate that appellation in a few months when you've cranked up a few hundred posts?)
-> It's just the display name , i think i can change it from user panel

You appear to be blaming MySQL but haven't demonstrated why you've concluded it's the problem?
->Because that's the part where my site hungs...telling me that it can not connect to mysql server . To many connections ...

Have you turned on Profiling (do you know about the CI Profiler?) What does this reveal about the time taken to perform each component. Have you looked at things like the PHP Fire Bug extension to do even more in depth analysis?
->using profiling :

Code:

0.0003      SELECT *
FROM (`my_sessions`)
WHERE `session_id` = 'f5a503e1c230166700794fc859a9d125'
AND `ip_address` = '86.xx.x.xx'
AND `user_agent` = 'Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.0.1'

0.0005      SELECT `member_username`, `member_description`, `member_first_image`, `member_country`, `member_city`, `member_birthday`
FROM (`my_members`)
WHERE `member_reg_time` > 1246126566
ORDER BY  RAND()
LIMIT 4

0.0002      SELECT *
FROM (`my_winners`)
WHERE `winner_mo` = '6'
AND `winner_year` = '2009'
LIMIT 1

0.0003      SELECT COUNT(*) AS `numrows`
FROM (`my_avatars`)
WHERE `avatar_cat` = 'OneCat'
AND `avatar_subcat` = 'OneSubcat'
AND `avatar_active` = 1

0.0003      SELECT *
FROM (`my_avatars`)
WHERE `avatar_cat` = 'OneCat'
AND `avatar_subcat` = 'OneSubcat'
AND `avatar_active` = 1
ORDER BY `avatar_id` DESC
LIMIT 25

Have you monitored the OS during the problem to see if you have a lower level problem?
-> Can't do this thing... shared hosting ...

Apache logs (ditto)?
->No problems in logs

Have you played with PHPMySqlAdmin thing - so you can monitor the health and activity of your database server?
->(PhpMyAdmin ?) I'm not sure at what thing i should look especially...

What results did you get when you did volume load testing in your development environment (ha! But I have to ask, just in case)..
->Almost same results , when timing the queries ...



On all my pages i have atleast 5 queries..maybe this is the problem ?(i'm not using persistent connections to DB server )

Maybe the issue is the DB server itself ?

What is the max. number of sql queries that i should have per page ?

Sorry for all these questions , but i'm a beginner

[eluser]TheFuzzy0ne[/eluser]
[quote author="The New Guy" date="1246145335"]Hey The New Guy (aren't you going to hate that appellation in a few months when you've cranked up a few hundred posts?)
-> It's just the display name , i think i can change it from user panel [/quote]

I think you should have perhaps researched that a bit first.

A good place to start, is in the SQL tab in PHPMyAdmin, or on the command line. Then you can run some queries, see how long they take, and analyse them using the EXPLAIN keyword.

Here's what I consider to be an excellent resource for learning about MySQL. - http://www.keithjbrown.co.uk/vworks/mysql/


EDIT: It's also quite possible that the MySQL server is under a lot of strain from other people on the same server running fulltext queries, and queries against unindexed fields. You should get a rough idea when you try to pull a single result out of the database. If it takes 20 seconds to retrieve a single row from a table with only 10 rows, then your server is overworked.

Do you have SSH access? If so, login, and at the CLI, try to run "uptime". You should then be able to see how the server has been running for the last several minutes.

[eluser]jedd[/eluser]

Because that's the part where my site hungs...telling me that it can not connect to mysql server . To many connections ...

In my experience, the last thing you see on a screen just before it hangs .. is the last successful thing to run before the thing that broke tried to run. IOW, the last thing on screen is usually a guide to finding out what went wrong, but should never be assumed to be the thing that did the breakage. In this case it's more likely to be a DB problem .. though your profile results would suggest that the DB is just fine. If I'm reading that right, then the performance is pretty snappy, so if it is the DB it might not be a load issue so much as throttling wrapped around that somehow, perhaps by the ISP or the architecture of your shared hosting?

Is the DB on the same box as the HTTP server?

Did you use a bucketload of memory or CPU time to service the page? {elapsed_time} and {memory_usage}

-> Can't do this thing... shared hosting ...

This is unfortunate. Do they not offer you any tools to monitor your services?

On all my pages i have atleast 5 queries..maybe this is the problem ?(i'm not using persistent connections to DB server )

Almost definitely not, especially given the results of the queries you posted above. I wouldn't be terribly worried until I was doing dozens of queries a page and those queries were excessively complicated (rather than the kinds of simple SELECTs that you appear to be doing here).

[eluser]Colin Williams[/eluser]
"Too many connections" is common on shared hosting environments. Sometimes you've got hundreds of sites sharing a database, and any one of those users can take down your database. Best bet is to upgrade your hosting. Go with something more dedicated. I like MediaTemple's GridServer with a MySQL container.

[eluser]The New Guy[/eluser]
My apologies for this late answer .
After reading all your posts , i decided to move all the staff one a dedicated server into my country.
I finished moving about 2 hours ago , and i am waiting to see if i solved something by moving the website .

Thank you again for your time , i rarely see experienced people that helps beginners like me on the forums.
Have a good day