Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

high sql load on shared node
New on LowEndTalk? Please Register and read our Community Rules.

high sql load on shared node

romanzromanz Member
edited August 2020 in General

Can PHP script monitor its own MySQL load ?

My acct. was suspended because of high sql load, mostly from crawlers. I counted 13k lines in access log during 24hrs before suspension, each access has SQL query in it.

I have disabled bot access now and looking to decrease load, but wondering, is this such unreasonable number for shared acct sold as high traffic account (highest tier they sell) ? I have no information about SQL load, but have timed entire script execution and it takes in average 0.1sec from top of PHP to the last line including SQL access and output.

Comments

  • @romanz said:
    Can PHP script monitor its own MySQL load ?

    My acct. was suspended because of high sql load, mostly from crawlers. I counted 13k lines in access log during 24hrs before suspension, each access has SQL query in it.

    I have disabled bot access now and looking to decrease load, but wondering, is this such unreasonable number for shared acct sold as high traffic account (highest tier they sell) ? I have no information about SQL load, but have timed entire script execution and it takes in average 0.1sec from top of PHP to the last line including SQL access and output.

    Move to VPS, you will get better environment..

    A simple uptime dashboard using UptimeRobot API https://upy.duo.ovh
    Currently using VPS from BuyVM, Gullo's, Hetzner, HostHatch, HostSailor, HostSolutions, InceptionHosting, LetBox, MaxKVM, MrVM, VirMach.

  • JasonMJasonM Member
    edited August 2020

    @romanz said: I counted 13k lines in access log during 24hrs

    which web host? You can DM me if you find not appropriate to revel their name in public.

  • romanzromanz Member
    edited August 2020

    @JasonM said:
    which web host? You can DM me if you find not appropriate to revel their name in public.

    Jason, they are popular LET provider. No names.

    @chocolateshirt said:
    Move to VPS, you will get better environment..

    I had several VPS before but VPS providers are also quick to suspend instead of throttle. Since I dont need high speed I/O and already have good connection, my thought is to get Raspberry 4 2GB, with 64GB V30 card and about $5/yr worth of electricity I break even with my current shared hosting in 3yrs. But the fact that nobody will arbitrarily suspend me for violating their undisclosed rules and leave me with no access would be priceless.

    Anyway, right now I am looking for way to read my own MySQL load and self-throttle. The higher SQL load, the longer delay, something like:

    if ( $mySqLoad > 0.1 ) sleep ( $mySqLoad );

  • @romanz said:

    @JasonM said:
    which web host? You can DM me if you find not appropriate to revel their name in public.

    Jason, they are popular LET provider. No names.

    @chocolateshirt said:
    Move to VPS, you will get better environment..

    I had several VPS before but VPS providers are also quick to suspend instead of throttle. Since I dont need high speed I/O and already have good connection, my thought is to get Raspberry 4 2GB, with 64GB V30 card and about $5/yr worth of electricity I break even with my current shared hosting in 3yrs. But the fact that nobody will arbitrarily suspend me for violating their undisclosed rules and leave me with no access would be priceless.

    Anyway, right now I am looking for way to read my own MySQL load and self-throttle. The higher SQL load, the longer delay, something like:

    if ( $mySqLoad > 0.1 ) sleep ( $mySqLoad );

    you are not happy with vps also then dedicated resources is the solution.
    cheap vps are almost shared hosting but un managed

    move to Dedicated Server so your cpu usuage is dedicated for your mysql.

    Thanked by 1romanz
  • jarjar Provider
    edited August 2020

    Shared hosting is best for websites that can at least do static page caching. A good rule to follow is don’t use shared if every page load requires a MySQL query. Follow that and you’ll safely put your sites on the most functional solutions.

    An empty host might let things go for a long time, but eventually if they do well they’ll crack down.

    Thanked by 1romanz

    Founder @ MXroute

  • When you say "load" what does that mean - disk, CPU? I'm guessing it is suboptimally configured and you could easily reduce the load.

  • romanzromanz Member
    edited August 2020

    @tetech said:
    When you say "load" what does that mean - disk, CPU? I'm guessing it is suboptimally configured and you could easily reduce the load.

    I am not sure, tech support was not very helpful when I inquired for details, he said "I am not familiar with the cause of the abuse I am simply going by the suspension message my manager left on the service." and then closed ticket.

    @mhosting_in said:
    you are not happy with vps also then dedicated resources is the solution.
    cheap vps are almost shared hosting but un managed

    move to Dedicated Server so your cpu usuage is dedicated for your mysql.

    This is very low (human) traffic site, and cannot justify cost of dedi server.

    LunaNode looks good, they do not suspend VPS, but have a mechanism to let it burst for a while then throttle, which is in my opinion brilliant and customer friendly. Anyone knows other providers (shared or VPS) that throttle instead suspend ?

  • Why not hetzner? They don't throttle at all...

  • @romanz said:

    @tetech said:
    When you say "load" what does that mean - disk, CPU? I'm guessing it is suboptimally configured and you could easily reduce the load.

    I am not sure, tech support was not very helpful when I inquired for details, he said "I am not familiar with the cause of the abuse I am simply going by the suspension message my manager left on the service." and then closed ticket.

    I would say the first thing is to understand what your own stuff is doing. If VPS providers are also throttling and it is a "very low traffic human site", then something is not set up right, whether that's excessive logging, missing table indexes, running out of RAM and hitting swap, etc. What is getting hit (CPU/disk/...) should be investigated, VPS gives you some more ability to do that. Start with tracking iops and CPU use.

  • seriesnseriesn Member, Top Provider
    edited August 2020

    @romanz said: I am not sure, tech support was not very helpful when I inquired for details, he said "I am not familiar with the cause of the abuse I am simply going by the suspension message my manager left on the service." and then closed ticket.

    EH What? Like seriously what? There is no way someone closed the ticket just by saying that! At the very least, they should have flagged it for the person who suspended the service.

    You deserve basic explanation. I can imagine they would say "dude, you are on your own. This is the problem, now how you fix is your problem" at the very least.

    This made 0 sense and if a host did that to you, you should move away from them.

    Source: Me.

    Now comes the other aspect,

    Take shared hosting as a big apartment

    • Now think of one, with many many room mates, where you are basically sleeping on bunk beds with many many other users. It will take one snoring room mate to make your life hard. That's your typical "ultra budget" hosting.

    • So you chose to upgrade and get away from ultra budget to slightly premium. Now you have your own room. You might still have a snoring neighbor. But, you won't hear it all the time as their will be walls in between you guys to lower the impact and you have some form of isolation. Because, well, your budget allowed that.

    • One day, you found your self a nice Significant other. Now she calls you all the time, wants to come over, you need some privacy. So, you move into your own Apartment. In hosting world, that would be when your website start getting busy and requires better isolation and dedicated resources. Apartment is a VPS.

    • You guys got married, start having kids. Now you became the neighbor who is very noisy. But you don't want to hear the complain. You also need more space for the growing family. So you move into your own house. Just like when your website gets big, very busy, you will need to upgrade to Dedicated server.

    The saga continues. Now remember, the person who snores, never hears themselves snoring, and thinks that the neighbor is making shit up. I don't want to type anymore :).

    If you need a fast VPS, join the family. We don't rent too many apartments in one building.

  • romanzromanz Member
    edited August 2020

    At this point, the website I host is very small and does not need 99.9% reliability, so I have decided to keep my stuff at arms length.

    Have plenty computing power and storage under desk in home office, running 24/7, UPS backed up including network switch, router and cable modem. My PC is running VirtualBox with few containers and they get tunneled to public VPS, which I only need for static IP.

    The amount of maintenance work in the remote or local VPS is the same and forwarding VPS is much easier to rebuild than fully setup service container.

  • This probably wont function on shared hosting, as the load is shared, I wrote this bit of code about 10 years ago for a similar reason (obvs not using it now)

    function load_balance($maxload=700)
    {
        $load = sys_getloadavg();
        while (($load[0] * 100) > $maxload) {
          echo "High load - {$load[0]} $loadavg, sleeping...<br />";
            ob_flush();
            flush();
            sleep(1);
            $load = sys_getloadavg();
        }
    }
    
  • @romanz said:
    At this point, the website I host is very small and does not need 99.9% reliability, so I have decided to keep my stuff at arms length.

    Have plenty computing power and storage under desk in home office, running 24/7, UPS backed up including network switch, router and cable modem. My PC is running VirtualBox with few containers and they get tunneled to public VPS, which I only need for static IP.

    The amount of admin work in the VPS is the same if running under my desk or in remote datacenter and forwarding VPS is much easier to rebuild than fully setup service container.

    That will work if you do not pay the electricity bill, last time I checked (cpu was way more un-efficient but my GPU was the killer staying in constant 57c even idle ) everything is idling around 100 wats , this mean over 2 KWh per day or if you get €0.15 (cheap) per kw it is still 9 euros per month which can get you good small VPS here ...

    Thanked by 1romanz

    Future-Proof Yourself! Buy! Buy!

  • romanzromanz Member
    edited August 2020

    @coolice said:

    @romanz said:
    At this point, the website I host is very small and does not need 99.9% reliability, so I have decided to keep my stuff at arms length.
    ...

    That will work if you do not pay the electricity bill, last time I checked (cpu was way more un-efficient but my GPU was the killer staying in constant 57c even idle ) everything is idling around 100 wats , this mean over 2 KWh per day or if you get €0.15 (cheap) per kw it is still 9 euros per month which can get you good small VPS here ...

    Thank you, I am glad you brought it up, I have considered this and have records of all my PCs consumption.

    My office PC has integrated graphic (Ryzen 5 2400G). I measured at the wall: 14W off, 30W idling + 2x 1W monitors in standby, 80W including monitors when fully on playing 60fps Crysis 2 or Master Chief Collection.

    Cannot do anything with 14W unless I change power supply for lighter one, this one is 650W 80+, but it is overkill for the current system setup, 300W PS or even 250W one would be sufficient and more efficient at these loads. Also planning to switch to Ryzen 7 4800G when it becomes available in retail, which is 7nm technology (as opposed to my 14nm Ryzen 5) and that should make it more energy efficient. Currently they are only offered to OEMs but there is talk they should be available for retail in Q3. Looking to upgrade not for the energy efficiency but for extra cores, I do like to have bunch of things running on the 2nd virtual desktop.

    For now, I have 32W idle (that includes background containers). Which is approx 23kWh per month = $3~4 in hydro. Keep in mind this machine was on 24/7 before so I was paying that idling energy cost regardless, installing few service containers in the background did not add to running costs. Mind you, my electricity comes from water (Niagara Falls), so it is 100% renewable and environmentally friendly.

    If you want to take low energy consumption to another level, use laptop. The PS on laptops are much more efficient at idling and mobile CPUs are good at scaling down power intake and also do a good job on short temporary boosts. My daughter's Flex 5 2-in-1 with Ryzen 5 4500U is faster than my 2400G desktop. It is 7nm tech and has 6 cores. Don't have its consumption measurements but it is a wee thing with wee 53Wh battery and she said it lasts 6+ hrs on Netflix, at least half of it is screen so that gives you <5W idling consumption. Stuff like watching video or hosting a website in the background is basically just idling for these CPUs, one could easily host hundreds websites off that laptop, and network would become bottleneck long before CPU.

Sign In or Register to comment.