Howdy, Stranger!

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

Sign In Register

Quick Links


Categories

In this Discussion

› › Vps for two sites with intensive mysql use
New on LowEndTalk? Please Register and read our Community Rules.

All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.

Vps for two sites with intensive mysql use

shilenoshileno Member
in General

I have two sites using mysql enough, now I have them on a vps with cpanel installed (with varnish cache) in VPSDIME.

The databases are on the external mysql server that offers vpsdime.

Even so the vps was restarted yesterday for CPU usage (load of 2 for more than two hours)

Can you recommend some service to host these sites? Ideally it with cpanel.

Comments

  • blackblack Member

    Abusivecores or go with a dedicated server.

    Thanked by 1Brad
  • udkudk Member
    edited June 2014

    How many queries per second are you doing? Is there any room for optimization?

    Might want to consider a dedicated server (with SSDs) as I can't imagine too many VPS providers will be keen on the high disk IO/CPU usage. Post your budget and see what offers you get.

  • shilenoshileno Member

    @udk said:
    How many queries per second are you doing?

    As I can get that information?

    My ideal budget is US20 monthly

  • udkudk Member

    Try https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

  • shilenoshileno Member

    @udk said:
    Try https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

    works if the databases are on an external server?

  • raindog308raindog308 Administrator, Veteran

    shileno said: works if the databases are on an external server?

    Yes - it's just connecting and running SQL queries.

  • shilenoshileno Member
    edited June 2014

    @udk said:
    Try https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

    -------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
[--] Data in MyISAM tables: 922M (Tables: 271)
[--] Data in InnoDB tables: 506M (Tables: 801)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 2M (Tables: 4)
[!!] Total fragmented tables: 35

-------- Security Recommendations  -------------------------------------------
[!!] User '@localhost' has no password set.
[!!] User '@sh2.xxxxxxxxx.com' has no password set.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 11h 49m 26s (7M q [32.992 qps], 303K conn, TX: 167B, RX: 2B)
[--] Reads / Writes: 78% / 22%
[--] Total buffers: 652.0M global + 50.9M per thread (500 max threads)
[!!] Maximum possible memory usage: 25.5G (424% of installed RAM)
[OK] Slow queries: 0% (0/7M)
[OK] Highest usage of available connections: 7% (38/500)
[OK] Key buffer size / total MyISAM indexes: 8.0M/550.8M
[OK] Key buffer hit rate: 100.0% (71M cached / 19K reads)
[OK] Query cache efficiency: 71.4% (3M cached / 5M selects)
[!!] Query cache prunes per day: 156262
[OK] Sorts requiring temporary tables: 1% (3K temp sorts / 201K sorts)
[!!] Joins performed without indexes: 65082
[!!] Temporary tables created on disk: 37% (53K on disk / 142K total)
[OK] Thread cache hit rate: 99% (208 created / 303K connections)
[OK] Table cache hit rate: 59% (668 open / 1K opened)
[OK] Open file limit used: 5% (341/5K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
[OK] InnoDB buffer pool / data size: 512.0M/506.7M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (> 100M)
    join_buffer_size (> 50.0M, or always use indexes with joins)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
  • udkudk Member

    See that long list of recommendations? Start there ;)

    Then you can either look at better caching or moving server.

  • msg7086msg7086 Member

    @udk said:
    See that long list of recommendations? Start there ;)

    Then you can either look at better caching or moving server.

    If I understand correctly OP is using an offshore mysql server that is not under his control.

    @OP:

    If you have lots of request/sec, do consider a higher budget and get something better. I have a website with 2M PV/day, 200 req/sec on peak hours and that's way beyond low end area. Think twice if you are under similar load.

  • tommytommy Member

    udk said: Try https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

    short url :

    wget mysqltuner.pl
    Thanked by 1marrco
  • serverianserverian Member
    edited June 2014

    Your VPS's load is not related to mysql queries since they are running on an offloaded server. The thing that makes high load is CPU usage on your VPS. You need to use a caching plugin like varnish to reduce the load.

    Edit: Ops, didn't notice that you already use varnish. Varnish's ram usage allowance may need tweaks.

  • wychwych Member

    Optimise your caches or it may be time to upgrade...

  • MaouniqueMaounique Host Rep, Veteran
    edited June 2014

    If you are using a high ram VPS and your db is not huge, you may gain a lot by loading mysql to ram. There are a few ways, from caching, to a ramdisk you periodically sync, I would suggest the first option since it is easier to tune and less dangerous for newbies. Just run free -m and if you have a lot of free mem, start loading things to ram!
    http://www.mysqlperformanceblog.com/2011/04/04/mysql-caching-methods-and-tips/
    You may also look at indexes, bad indexing can cause a lot of cpu usage.

  • raindog308raindog308 Administrator, Veteran

    Nice link @Maounique - this one is a bit older...in fact so old I wonder if it's still relevant, though the params haven't changed.

    http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/

  • dnwkdnwk Member

    Do a MySQL load balancing with percona xtradb cluster

  • shilenoshileno Member

    @serverian said:
    Your VPS's load is not related to mysql queries since they are running on an offloaded server. The thing that makes high load is CPU usage on your VPS. You need to use a caching plugin like varnish to reduce the load.

    Edit: Ops, didn't notice that you already use varnish. Varnish's ram usage allowance may need tweaks.

    I just installed litespeed + varnish. low CPU load immediately, I hope to continue like this.

  • MaouniqueMaounique Host Rep, Veteran

    The effect should be mostly from varnish.

  • shilenoshileno Member

    @Maounique said:
    The effect should be mostly from varnish.

    My first setup was: Apache + varnish and had problems with the cpu load

    Changing the settings to: litespeed + varnish the improvement was quite

    Note: in both cases use the varnish plugin of unixy.net

  • MaouniqueMaounique Host Rep, Veteran

    Then Apache was not configured correctly, in general, the cpu gain from apache to litespeed is minimal, I would say at most 20%, probably 10 or below. That is, if they are both tuned tot he max for the app at hand.

  • bookstackbookstack Member

    Not an expert in php tuning, but generally php-fpm yield better performance compared to mod_php.

Sign In or Register to comment.