Does this Hostgator Technician Know What He's Talking About?

[Fluoresce]

I present videos on my site. The videos are hosted by Youtube. I embed them on my site using Youtube's embed codes, which I store in a database.

 

This morning I received lots of "Too many connections" errors, which is weird because my site barely receives any traffic. I contacted Hostgator, my hosting company, and was told the following by a junior technician. Please tell me if he knows what he's talking about.

 

He said that shared accounts such as mine are limited to 25 simultaneous MySQL connections. Therefore, only 25 visitors at any one time can watch a video on my site.

 

Eh?

 

I thought that the MySQL connection was open for only a split second, just enough time for the embed code to be selected. Does the connection remain open for as long as the video is playing?

 

Another question ...

 

Because my site barely receives any traffic, it's weird that I should be getting "Too many connections" errors. My site's either being attacked or there's something wrong with my code.

 

What are the best practices for preventing "Too many connections" errors? For example, should I always use mysql_close() after I make a selection?

 

How is it that much more popular sites don't run into this problem? Are they all using dedicated hosting?

 

On any single page, the maximum number of selections/updates I make is approximately three.

Edited October 27, 2014 by Fluoresce

[ignace]

It is possible that Google, Yahoo, Bing and the likes are crawling your website. They are known to open multiple connections to crawl your website faster, but it is possible that by this they are actually hitting the limit. If you have Google Analytics installed on your website, it should be possible to detect who is at your website doing what at the time this occurs.

 

In the case of crawlers you can tell them (atleast the legal ones) to only use 1 connection, not multiple.

[mac_gyver]

you are correct, each database connection is only open for a fraction of a second. by the time the browser renders the page and someone actually starts playing a video, the instance of your script that output that page has long since ended, closing any database connection that was open on that page request. 25 concurrent connections is fairly low. large sites handle this problem by not using shared web hosting.

 

your page could be partially at fault if it is opening more than one database connection per each invocation of your script or of opening/closing a connection for each database operation on the page.

 

you would need to look at the web server access log to see if there really are 25+ concurrent requests to your page and where they are coming from.

[Fluoresce]

Thank you for the replies.

 

 

your page could be partially at fault if it is opening more than one database connection per each invocation of your script or of opening/closing a connection for each database operation on the page.

 

 

Yes, it seems that five of my pages were making a connection and then including a file at the bottom of the page that made a second connection. I've now revised my pages so that they only ever make a single connection, which is closed as soon as it's no longer required.

 

Question

 

If you don't use mysql_close(), when exactly is a connection closed? Is it at the end of the script (i.e. when it reaches "?>")?

 

If so, then if you have two snippets of PHP code on a page, the first of which makes a connection, you can't use the same connection in the second snippet of PHP. Is that correct?

 

 

In the case of crawlers you can tell them (at least the legal ones) to only use 1 connection, not multiple.

 

 

Is that what you guys do? Is it a good practice? Wouldn't it inhibit the number of pages that get indexed and thus your SEO campaign?

[mac_gyver]

if you don't close the connection in your code, php closes it when php finishes processing the page. when php finishes running the code on a page, either normally (reached the end of the page or an exit/die/return statement) or abnormally (a fatal error occurs) it runs a 'clean up' routine that destroys all the resources used by the code on the page, which would close the database connection.
 
inline html, that's outside of any php tags, is still actually php code. there's an inline html php token it gets converted to (T_INLINE_HTML), which simply outputs the inline html when that token is interpreted when the php code runs on the page.

[Psycho]

If you don't use mysql_close(), when exactly is a connection closed? Is it at the end of the script (i.e. when it reaches "?>")?

 

If so, then if you have two snippets of PHP code on a page, the first of which makes a connection, you can't use the same connection in the second snippet of PHP. Is that correct?

 

As mac_gyver stated, it is closed automatically when it is done executing all the pages needed for the request. It is not when it encounters a ?>. You can reuse that request across any of the pages used on that request.

 

Another thought to your original problem. Are you using any AJAX on your pages? If so, there could be multiple requests made to the server while a user is watching a video.

[ignace]

Also if your pages always generate the same darn page, then cache it and avoid the DB connection completely.

 

You create a unique cache key for each page and check if it exists, then stop and echo it, or continue and create the cache. Something like this:

 

<?php

$cacheKey = 'video_' . intval($_GET['id']);

if (file_exists('cache/' . $cacheKey)) {
  include('cache/' . $cacheKey);
  exit;
}

// query DB

$html = include('some/view.html');
file_put_contents('cache/' . $cacheKey, $html);

echo $html;

[Fluoresce]

Are you using any AJAX on your pages?

 

 

No, I don't use AJAX. Thanks for the input, though.

 

you would need to look at the web server access log to see if there really are 25+ concurrent requests to your page and where they are coming from.

 

Thanks for that.

 

Do you mean the raw access logs?

 

If so, I just checked and there doesn't seem to be any simultaneous requests at the times when the "Too many connections" errors occur. Weird.

 

I had more "Too many connections" errors this morning, so they're starting to bother me.

 

Each page now only uses a single connection, and the connection's closed ASAP, so I'm not sure what the problem is.

[Fluoresce]

Also if your pages always generate the same darn page, then cache it and avoid the DB connection completely.

 

You create a unique cache key for each page and check if it exists, then stop and echo it, or continue and create the cache. Something like this:

 

<?php

$cacheKey = 'video_' . intval($_GET['id']);

if (file_exists('cache/' . $cacheKey)) {
  include('cache/' . $cacheKey);
  exit;
}

// query DB

$html = include('some/view.html');
file_put_contents('cache/' . $cacheKey, $html);

echo $html;

 

Thanks for this. I shall look into it. This is new to me.