Innodb fine tune

[Nuno]

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

 

[m1ne]

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

 

[hellreturn]

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

 

[Xon]

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:

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.

 

[rdn]

2GB Digital Ocean VPS

I though Linode?

 

[Xon]

I though Linode?

There is a linode VPS for one site's DB, and a digital ocean VPS for the other site's DB.

 

[PlayStage]

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.

 

[Nuno]

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

 

[PlayStage]

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]

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?

 

[PlayStage]

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.

 

[alegeek]

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:

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.

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