mysql - moving to a lower performance server, how small can I go?
- by pedalpete
I've been running a site for a few years now which really isn't growing in traffic, and I want to save some money on hosting, but keep it going for the loyal users of the site and api.
The database has one a nearly 4 million row table, and on a 4gb dual xeon 5320 server.
When I check server stats on this server with ps -aux, i get returns of mysql running at about 11% capacity, so no serious load.
The main query against mysql runs in about 0.45 seconds.
I popped over to linode.com to see what kind of performance I could get out of one of their tiny boxes, and their 360mb ram XEN vps returns the same query in 20 seconds.
Clearly not good enough.
I've looked at the mysql variables, and they are both very similar (I've included the show variables output below, if anybody is interested).
Is there a good way to decide on what size server is needed based on what I'm coming from?
Is it RAM that is likely making the difference with the large table size?
Is there a way for me to figure out how much ram would be ideal??
Here's the output of the show variables (though I'm not sure it is important).
+---------------------------------+------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /usr/ |
| bdb_cache_size | 8384512 |
| bdb_home | /var/lib/mysql/ |
| bdb_log_buffer_size | 262144 |
| bdb_logdir | |
| bdb_max_lock | 10000 |
| bdb_shared_data | OFF |
| bdb_tmpdir | /tmp/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| completion_type | 0 |
| concurrent_insert | 1 |
| connect_timeout | 10 |
| datadir | /var/lib/mysql/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| div_precision_increment | 4 |
| keep_files_on_create | OFF |
| engine_condition_pushdown | OFF |
| expire_logs_days | 0 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -
For some reason, that table formats properly in the preview, but apparently not when viewing the question. Hopefully it isn't needed anyway.
