Default database configurations are written to start anywhere, including machines with far more memory than a small VPS. Left alone, they either waste memory you need or fail to use memory you have, and on a 1 GB server the first one is what kills the database.
Measure before changing anything
Find out whether the database is actually your problem. On most sites it is not.
Check whether the server is swapping, whether the database process is the largest memory consumer, and whether slow pages correlate with database time at all.
A site that is slow because it has no caching is not fixed by database settings, and tuning it first means spending an afternoon to gain nothing. Speeding up a website in the right order walks through where the database sits.
The setting that matters most
innodb_buffer_pool_size is the memory the database uses to hold data and indexes. Almost everything else is noise by comparison.
The common advice is 70–80% of system memory. That is for a dedicated database server. On a VPS also running a web server and PHP, it is a recipe for the kernel killing something.
A workable starting point on a shared-purpose VPS: 25–30% of total memory, or the size of your actual data if that is smaller. A 200 MB database does not benefit from a 2 GB buffer pool.
Find your data size:
SELECT ROUND(SUM(data_length + index_length)/1024/1024) AS mb FROM information_schema.tables;
Connections cost memory
max_connections is not free. Each allowed connection reserves buffers, so a high value on a small server is memory committed to connections you will never have.
A typical website uses far fewer simultaneous connections than people assume, often under twenty. Setting 500 on a 1 GB VPS is how a database that was fine becomes the process the kernel chooses to kill.
Watch the actual peak:
SHOW STATUS LIKE 'Max_used_connections';
Set the limit somewhat above that figure, not far above it.
Give the server somewhere to fall back to
Before tuning memory upward, make sure swap exists. Without it, exceeding memory means the kernel kills the largest process (which is the database) and the symptom is a site that works, fails with a connection error, and works again after a restart.
With swap, the server gets slower and stays up, which is the trade you want while you find the right numbers. Configuring swap and memory explains creating it.
Find the slow queries before touching more settings
One badly written query does more damage than any configuration value, and no amount of tuning hides it.
Enable the slow query log briefly:
slow_query_log = 1 long_query_time = 1 slow_query_log_file = /var/log/mysql/slow.log
Leave it on for a day of real traffic, then read it. What usually appears is a handful of queries, repeated constantly, all missing the same index.
Turn it off afterwards. It writes on every slow query and can fill a disk on a busy server. Managing logs goes into that failure.
Indexes beat configuration
A query scanning an entire table takes the same time whatever your buffer pool is set to, until the table no longer fits in memory: at which point it becomes dramatically worse.
Run a slow query with EXPLAIN in front of it. A row count near the size of the table means no useful index is being used, and adding one usually turns seconds into milliseconds.
On an application you did not write, this is often a plugin's query, and the fix is either an index or removing the plugin.
Change one thing, and keep a way back
Put your changes in a separate file under the configuration directory rather than editing the main one. Distribution updates replace the main file; your file survives, and reverting is deleting it.
Change one value, restart, and watch for a day. Several changes at once means an improvement you cannot attribute and a regression you cannot isolate.
And check the service actually restarted rather than that the command returned. A database that failed to start after a configuration change takes the site down, and the error is in the log rather than on your screen.
What tuning will not fix
Worth being direct, because this is where the time goes.
It will not fix an uncached site. Every uncached page load queries the database; caching removes the query entirely, which no setting can match.
It will not fix an application that is slow for its own reasons: a plugin doing work on every request, a page assembling itself from dozens of queries.
And past a point it stops helping at all: once the working set fits in memory and the queries have indexes, further tuning changes very little. If you have measured a ceiling and it has not moved across several changes, the limit is elsewhere, usually the application, and continuing to tune is effort spent on the wrong layer.
A reasonable configuration for a small VPS
Buffer pool at 25–30% of memory or the size of your data. Connections a little above your observed peak. Swap present and swappiness low. Slow query log used briefly, then off.
That is most of the available gain. Beyond it, the returns are small and the risk of a configuration nobody understands is not. Managing databases on a VPS deals with the operational side.
Watch the cache hit rate rather than the size
A memory setting is judged by whether it is working, and there is a figure for that.
mysql -e "SHOW STATUS LIKE 'Innodb_buffer_pool_read%'" mysql -e "SHOW STATUS LIKE 'Innodb_buffer_pool_pages_free'"
Compare the reads served from memory against those requiring disk. A high proportion from disk means the pool is smaller than the working set, and raising it will help.
Free pages consistently above zero means the opposite: the pool is larger than the data needs, and the memory would be better left to the rest of the machine.
Give the change time before judging it
A restarted database starts with an empty cache and is slower than it was, which looks like the change made things worse.
mysql -e "SHOW STATUS LIKE 'Uptime'" uptime
Wait until the cache has filled with real traffic before measuring, which on a busy site is minutes and on a quiet one can be hours.
Compare against the same period on a previous day rather than against the hour before the restart. A comparison across different traffic levels tells you about the traffic rather than about the setting.