1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Innodb fine tune

Discussion in 'Server Configuration and Hosting' started by Nuno, Jul 25, 2015.

  1. Nuno

    Nuno Active Member

    Hi,

    I run a small community with a database size of 800MB.
    I run a 2Gb kvm vps and when I use innodb with 1gb pool size the server start to swap.

    For now I convert to myisam so I can gradually move to innodb.
    Things are running ok for now.

    My concern is related to the balance between the amount of ram my vps needs and the pool size.

    Do I need more ram to run a xf with a 800mb database?

    Thanks
     
  2. Solidus

    Solidus Well-Known Member

    There's no headroom at all to increase buffer pool size?
     
  3. hellreturn

    hellreturn Active Member

    If you have extra available sure you can assign and if not, nop.
     
  4. Xon

    Xon Well-Known Member

    I'm running a site with an ~10gb database on a 2GB Digital Ocean VPS (it uses SSDs), and big trick is tuning the IOP limits.

    I run with a innodb buffer pool of 768M, and the following IO tuning:
    Code:
    innodb_flush_method=O_DIRECT
    innodb_thread_concurrency=0
    innodb_read_io_threads=64
    innodb_write_io_threads=64
    innodb_io_capacity=3000
    #innodb_flush_neighbor_pages = none # MariaDB 5.5
    innodb_flush_neighbors=0  # MariaDB 10.x
    
    Increasing the innodb buffer pool size is not always the best solution.
     
    Marcus and RoldanLT like this.
  5. RoldanLT

    RoldanLT Well-Known Member

    I though Linode? :)
     
  6. Xon

    Xon Well-Known Member

    There is a linode VPS for one site's DB, and a digital ocean VPS for the other site's DB.
     
    RoldanLT likes this.
  7. Set3sh

    Set3sh Active Member

    Hello,

    Should your stored data is InnoDB format then disable query cache because the buffer pool size takes care of storing your data. That is why you should give the innodb_buffer_pool_size a big enough value to reflect your actual InnoDB data + 128MB

    But I would recommend you to study all other applications resources usage and try to optimize. This way MySQL is going to have more resources.


    Kind regards,
    George.
     
    Last edited: Jul 28, 2015
    Nuno likes this.
  8. Nuno

    Nuno Active Member

    Hello,

    With InnoDB data + 128 I get 1GB and theres only 1GB more left to nginx/php/caches.
    Since I'm swapping when i set the buffer size to 1GB should I get a bigger VPS or is there anything else I can do to optimize the 2GB I have?

    Thanks
     
  9. Set3sh

    Set3sh Active Member

    Hello,

    If you are fine with the cpu power you already have then add more ram to your server.
    Additional 2GB of ram should be enough to accomodate mysql, nginx/php/caches, mail server, os etc.


    Kind regards,
    George.
     
    Nuno likes this.
  10. Nuno

    Nuno Active Member

    Hello,

    I'm running a vps at vultr.
    I'm going to fine tune the server first before deciding for an upgrade.

    Edit: any advice for innodb settings beside the one given by @Xon?
     
  11. Set3sh

    Set3sh Active Member

    Hello,

    Do not make the mistake thinking you are better off using swap memory.
    Swap memory is not ram, it's basically hard drive space.

    I cannot provide anymore MySQL optimization specific parameters for you to modify because it requires a more detailed server investigation.

    Taking certain values from others suggestions is not a guarantee of providing good performance for you or anyone else too.
    Each server environment is different and requires close study and specific optimization.

    The main points when optimizing an SQL Server are:
    > how much data is it stored
    > what are the most used operations: Reads or writes
    > bottlenecks
    > table locking or row level locking (MyISAM vs InnoDB)
    > caching or not

    Like I said: There is no such thing as a "copy-paste and forget about it" MySQL configuration.
    If MySQL needs resources you have no other choice to provide it or suffer performance degradation.


    Kind regards,
    George.
     
    Last edited: Jul 28, 2015
  12. alegeek

    alegeek Member

    I just wanted to say thanks for sharing this, I've had these settings running on a 2GB VPS and noticed a difference in performance :)
     
    Xon likes this.

Share This Page