Re: MySql crashes
Posted by:
Rick James
Date: March 21, 2016 05:12PM
40M vs 200M -- I may have been mixing up the 1GB vs 512MB RAM. Swapping is deadly if there is no Swap; it is very costly for performance if you do have swapping.
The article's warning against swapping is probably referring to the fact that SSDs do wear out -- there is a limited number of writes that can be performed on each sector. (This does not apply to reads, nor to spinning drives.) However, unless your provider is using old SSDs, I would not worry too much. Also, if they have "enterprise grade" SSDs, the firmware will do "wear leveling". This is where the blocks are moved around on the SSD rather than pounding on one spot until it dies. The "swap file" would, if there is a lot of swapping, be pounded into failure if it weren't for wear leveling.
So, I want you to minimize the swapping, both for performance and to avoid any issues with SSDs. But if swapping happens _occasionally_, that should be OK.
The article discusses swappiness: "With values close to zero, the kernel will not swap data to the disk unless absolutely necessary." -- So setting "swappiness" to 1 may be the best thing to do for a MySQL system, especially a dedicated server like you have.
Back to the buffer_pool_size. I do not know exactly what value is best for your system. Instead, I am advising
* As high as possible, but
* Low enough so that mysqld never (or almost never) swaps.
For servers bigger than 4GB, "70% of available ram" works well. But on a 1GB VM, I don't know how much of that 1GB is "available" and I worry that other, less flexible, memory usages will eat into any simple formula like that.