MySQL error: User has already more than 'max_user_connections' active connec

It’s actually quite normal to access the database with a script that gets referenced in an img tag. For example you’d use it if your host doesn’t give you access to cron jobs or if you want access restrictions on photos.

…thinking about that. if you store images in a database (which i dont), then you would actually need a new connection for each picture cause u usually show them with a php script like:

	
	$sql = "select img from images where ID = ?";
	$rs = $db->query($sql,array($img_id));
	$row = $rs->fetchRow(DB_FETCHMODE_ASSOC);
	header('Content-type: image/jpeg');
	$im = imagecreatefromstring($row['notice_img']);
	imagejpeg($im);	

and


<img src = "showdbimage.php?imgid=<?php echo $img_row['ID']; ?>"

…so if u had a gallery showing 20 pics / page u would then be looking at over 20 connections/request! …worth considering!

Ugh!

why’s that so bad?

…thanks for backing me up Icheb :wink:

Only a moron would!

I was actually refering to access restriction, not storing images in the database. You store the path to the file in the database and then use readfile() to push the image to the user if he has access.
And btw, vBulletin uses a technique similar to this (only not for access restriction) for the user avatars.

bokehman, thanks for that insightful comment.

Quote from support:

This problem should not come back.

I’m not holding my breath…

i never said we need to close connection everytime
connect

query1
query2
query3

disconnect

this is the proper way to do so , as well as i dont know what eaxct solution for this. am trying to help.
sory 4 misguide.

Yeah, it’s easy to say you meant something else when no one understands what you write in the first place.

luckily i didnt hold my breath. almost lasted a day. now its down again. and if im not loosing connectivity because of the database, then http goes down.

it is rediculous. I am so sick of shared hosts, and all their empty promises. i want to go to a dedicated server, but i cant afford it.

aaaaaarghh!!!

in case you were wondering, the host is site5.

also, just out of interest, the boast over 200 000 clients. they have 57 servers listed here: http://www.site5.com/support/uptime.php

you do the maths (also, each client can have 5 ‘real websites’ - and that’s not including resellers).

If you have a full time broadband connection just run your own.

I would, but i live in south africa, and we have the most expensive bandwidth in the world, plus quite limiting on most of our connections. unfortunately its not really an option :frowning:

…we also have a weak currency which makes things more expensive to :frowning:

ok…what can i make of this now?

all processes are up.
port monitoring says it is up
i can ping and tracert the server
but i cant access my page from a browser (just get cached content).
they say they have not suspended the site

???

Delete your cache and reload the page and see what you get.

Also doesn’t PHP automaticly close MySQL connections when the script has finished - I still recommend you use some kind of command to close the connection as well.

i have already recommend to close the connection.

MySQL settings, many concurrent users.
I run a site for a client that has over 3000 users that log in for about 5-7 hours
per day each. So, at peak times, we have to handle about 2000 concurrent users.
When configured correctly, PHP and MySQL can handle this load wonderfully on fairly
cheap Intel architecture. First off, hardware.

  1. It is better to have 2 separate servers for Apache/PHP and MySQL with the Linux of your choice.
  2. Try not to run too much else on either box; leave the resources for Apache/PHP and MySQL.

Here are the specs on each box in my config:

  1. Apache/PHP: Pentium 3, 600 MHZ, 512 megs ram.
  2. MySQL: Dual Pentium 3, 750 MHZ (1500 MHZ total), 2 gigs ram.

The reason for this configuration is that it is very database heavy;
it is a members only web site with username and password required for login, fully personalized.
It is an online school, so each student has their suite of tools for attending school,
their courses, report cards, time logging, and much more. Teachers have web based tools to create
their courses, including lessons, text to speech audio, and more.

  1. PHP coding: be sure to use persistent connections!
    Opening and closing a connection from your Apache/PHP box to your MySQL box is a very heavy load.
    By using persistent connections, a high capacity site will open connections and share them to
    exchange data rather than opening a connection on each page request, sending the data, then closing,
    and repeating that process at least once for every user click!
    Be sure to use “mysql_pconnect” instead of “mysql_connect” and also that appropriate changes are made
    in “php.ini” or overridden by using the command “ini_set”.
    You can find more documentation on doing this at the php web site.

  2. Apache set up (“httpd.conf”): I’ve changed these various settings, and played with them until they
    seem to keep the most “idle %” reported in “top”.
    MinSpareServers 10
    MaxSpareServers 20
    StartServers 70
    MaxClients 255

  3. Mysql set up (“my.cnf”). The MySQL config file, my.cnf.
    Here is what to add under the [mysqld] heading.
    The two lines, “max_connections” and “max_user_connections” are where the magic happens.
    Since your Apache/PHP box is connecting to MySQL, it appears as a single user.
    MySQL defaults to 1 max connection, with 1 max connection per user. The following lines make
    it so your Apache/PHP box can connect to your MySQL box up to the number you have set “MaxClients”
    to in the Apache config above. By using persistent connections, you can pretty much get Apache up,
    have it connect to MySQL upon start up, and just use the persistent connections to pass data between
    the two boxes rather than opening connections. Its much more efficient that way.
    set-variable = max_connections = 300
    (this must be higher than “MaxClients” set in Apache, or you won’t fully maximize use)
    set-variable = max_user_connections = 300
    set-variable = table_cache=1200
    (max number of tables in join multiplied by max_user_connections)

A few other MySQL tunings:
set-variable = max_allowed_packet=1M (sanity check to stop runaway queries)
set-variable = max_connect_errors=999999
(stop mysqld from shutting down if there are connect errors - this defaults to 1 error and mysqld stops!)

hi bitbytes. Thanks for the reply. I think the the mysql_pconnect thing is not good advice though. most resources are telling me that that is prob the cause of all my problems (even though i am not using persistant connections).

Unfortunately, all the stuff you have told me about configuring my server is not use, cause i am on a shared host and clearly dont have access to those resources.

I have re-examined my stats, and looking at them, i have prob just outgrown shared hosting. i had another site which had similar problems (which is now hosted on a VPS), so i was comparing. that site got 3000 visits a day, whereas this one only gets 3-400 visits, so i thought i had a way to go before i needed to worry about moving. BUT: what i neglected was that this site gets 20 pageviews / visit, compared to the 3 which the other site was getting. So the traffic is fairly comparable.

As i mentioned before, i cant afford a dedicated host, so been searching franticly for something. found some nice looking VPS solutions at rackforce (looks a little like a cheap knockoff of rackspace…).


Has anyone any experience with them? u reckon that could solve my problems?

Broadband can also mean 128kbit upstream, which certainly is not suited for hosting a webserver.

And as I have already told you, this is not necessary. Read my post again if you want to know why.
And did you just copy and paste that post from another source or something? Because it’s so irrelevant that it’s sad. His MySQL server says it’s refusing connections because there are too many connections, so if he had his own server he would just change that setting, now wouldn’t he?
Using persistent connections is just as counterproductive, but I’m not going to repeat my former post.

Certainly, because then you can just change the settings. However, be cautious when it comes to VPS solutions. Some hosts overload their servers with accounts, essentially crippling every account on it.

I’ve successfully hosted a webserver over a 128kbs connection and currently am using a 300kbs connection for all the links in my signature as well as many others. I am currently serving about 10,000 pages a day (65,000 hits) and no one has every complained about the speed yet.

Certainly, because then you can just change the settings. However, be cautious when it comes to VPS solutions. Some hosts overload their servers with accounts, essentially crippling every account on it.

VEs per Physical Host: 12

is that accounts per host? does 12 sounds about right? its a 2.8 Xeon…

Also 200 min gaurenteed RAM. is that enough? …how much would one be getting on a typical shared host?

Originally Posted by Icheb
Broadband can also mean 128kbit upstream, which certainly is not suited for hosting a webserver.

He thinks that he is the smartest person in this forum.

128kbit/s is suitable for email, surfing the web and online text based conferencing. It is just about usable for voice over IP, but inadequate for IP based videoconferencing.