/    Sign up×
Community /Pin to ProfileBookmark

Database Options

Copy linkTweet thisAlerts:
Jun 25.2022

I’m struggling with a shared hosting server and the database limit.

It seems 150 is the max number of connections I can get as

`‘SHOW VARIABLES LIKE ‘max_connections’;`

Returns 150.

`SHOW STATUS LIKE ‘max_used_connections’;`

Returns, 149, 150, 148, fails etc so always close to my 150 limit.

My DB is 25MB and 300,000 rows and growing.

Is there any other free and useful database you use on most shared web hosting servers?

to post a comment

6 Comments(s) ↴

Copy linkTweet thisAlerts:
@Steve_R_JonesmoderatorJun 25.2022 — The 150 is probably the Default value.... Are you getting any errors like-> “too many connections“
Copy linkTweet thisAlerts:
@NogDogJun 25.2022 — There are a whole lot of variables there I'd try to figure out before assuming the actual problem is the max connections. (Taken in a vacuum, 150 seems like plenty unless your site traffic has grown to the point where you should be able to afford a non-free option. ;) )

  • * Are you actually seeing response time lags (or errors), and can you directly attribute those lags (or errors) to waiting for a DB connection?

  • * How are you connecting within your code? If using persistent connections, maybe don't do that so as to avoid creating unused zombie connections if anything unexpected happens in your app.

  • * And probably a host of other questions I might ask if I knew more about your specific environment, what the app does database-wise, etc., etc. :)
    • Copy linkTweet thisAlerts:
    @kiwisauthorJun 25.2022 — @NogDog#1644913

    My connection type is from this thread

    https://forum.webdeveloper.com/d/398036-connection-error/21

    I have about a dozen classes which all have 10-40 methods in them. Most have DB queries and one if the DB class itself. I include all classes at the top of my page and set the DB instance and variable there.

    ``<i>
    </i>require_once '/OOP/database.php';
    foreach (glob("/OOP/*.php") as $object){
    include_once $object;
    }
    $database = new database();<i>
    </i>    `</CODE>

    Some of my queries are complex. Multi table joins, upwards of 6 tables in extreme cases, MIN,MAX,COUNT and SUM included. Queries can take 0.5-2 seconds to run but run fine. Each table has indexes for joins.

    I'm not sure if the issue is here or other users on my shared host?


    No issues today and when I run this I get 20

    <C>    SHOW STATUS WHERE ''variable_name' = 'Threads_connected';`

    I don't ever kill $database, should I?
    Copy linkTweet thisAlerts:
    @NogDogJun 25.2022 — > @kiwis80#1644917 I don't ever kill $database, should I?

    No, it should be released once the script finishes (or possibly earlier if the run-time compiler determines that at some stage in the script nothing will ever reference $database again.)

    > @kiwis80#1644917 Queries can take 0.5-2 seconds to run

    2 seconds is a long time these days for a DB query. There is probably some tuning that could be done on those, either the query itself, or the way the relevant tables have been indexed and such. It might be time to run EXPLAIN ANALYZE [your query] and find out where the slowest parts are (e.g. is it doing full table scans instead of using an index in those places?).
    Copy linkTweet thisAlerts:
    @SempervivumJun 25.2022 — Two thoughts from my side:

    1.

    In your other thread you wrote:
    >when on my normal page I create an instance of my first class I pass in my database, that passes it over to any new classes created within it.

    I need to do a lot of rework to move my current stuff over to this.

    and in this thread:
    >I have about a dozen classes which all have 10-40 methods in them. Most have DB queries and one if the DB class itself.

    I assume that it was a complex rework to modify the code from the old procedure (multiple connections) to the new one (one connection only and handing over to other objects). Are you sure that you modified all locations in your code and transferred them to the new procedure? Did you test this?

    2.

    You wrote that the modified code ran fine for a long time and the issue occurred a short time ago. I wonder if the reason might be that the number of visitors increased significantly (which would be fine on the other hand)?
    Copy linkTweet thisAlerts:
    @kiwisauthorJun 27.2022 — @Sempervivum#1644919

    yes, it took sometime to rework it. Now when I declare a new class I pass in my database. For example


    $newCarObject = $newCar($database);
    ×

    Success!

    Help @kiwis spread the word by sharing this article on Twitter...

    Tweet This
    Sign in
    Forgot password?
    Sign in with TwitchSign in with GithubCreate Account
    about: ({
    version: 0.1.9 — BETA 4.26,
    whats_new: community page,
    up_next: more Davinci•003 tasks,
    coming_soon: events calendar,
    social: @webDeveloperHQ
    });

    legal: ({
    terms: of use,
    privacy: policy
    });
    changelog: (
    version: 0.1.9,
    notes: added community page

    version: 0.1.8,
    notes: added Davinci•003

    version: 0.1.7,
    notes: upvote answers to bounties

    version: 0.1.6,
    notes: article editor refresh
    )...
    recent_tips: (
    tipper: @Yussuf4331,
    tipped: article
    amount: 1000 SATS,

    tipper: @darkwebsites540,
    tipped: article
    amount: 10 SATS,

    tipper: @Samric24,
    tipped: article
    amount: 1000 SATS,
    )...