0

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 nearly 4 million row table, and on a 4 GB dual Xeon 5320 server.

When I check server statistics 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 360 MB 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               | + -><()~*:""&|                                             |
| ft_max_word_len                 | 84                                                         |
| ft_min_word_len                 | 4                                                          |
| ft_query_expansion_limit        | 20                                                         |
| ft_stopword_file                | (built-in)                                                 |
| group_concat_max_len            | 1024                                                       |
| have_archive                    | NO                                                         |
| have_bdb                        | YES                                                        |
| have_blackhole_engine           | NO                                                         |
| have_compress                   | YES                                                        |
| have_crypt                      | YES                                                        |
| have_csv                        | NO                                                         |
| have_dynamic_loading            | YES                                                        |
| have_example_engine             | NO                                                         |
| have_federated_engine           | NO                                                         |
| have_geometry                   | YES                                                        |
| have_innodb                     | YES                                                        |
| have_isam                       | NO                                                         |
| have_merge_engine               | YES                                                        |
| have_ndbcluster                 | NO                                                         |
| have_openssl                    | DISABLED                                                   |
| have_ssl                        | DISABLED                                                   |
| have_query_cache                | YES                                                        |
| have_raid                       | NO                                                         |
| have_rtree_keys                 | YES                                                        |
| have_symlink                    | YES                                                        |
| hostname                        | li130-17                                                   |
| init_connect                    |                                                            |
| init_file                       |                                                            |
| init_slave                      |                                                            |
| innodb_additional_mem_pool_size | 1048576                                                    |
| innodb_autoextend_increment     | 8                                                          |
| innodb_buffer_pool_awe_mem_mb   | 0                                                          |
| innodb_buffer_pool_size         | 8388608                                                    |
| innodb_checksums                | ON                                                         |
| innodb_commit_concurrency       | 0                                                          |
| innodb_concurrency_tickets      | 500                                                        |
| innodb_data_file_path           | ibdata1:10M:autoextend                                     |
| innodb_data_home_dir            |                                                            |
| innodb_adaptive_hash_index      | ON                                                         |
| innodb_doublewrite              | ON                                                         |
| innodb_fast_shutdown            | 1                                                          |
| innodb_file_io_threads          | 4                                                          |
| innodb_file_per_table           | OFF                                                        |
| innodb_flush_log_at_trx_commit  | 1                                                          |
| innodb_flush_method             |                                                            |
| innodb_force_recovery           | 0                                                          |
| innodb_lock_wait_timeout        | 50                                                         |
| innodb_locks_unsafe_for_binlog  | OFF                                                        |
| innodb_log_arch_dir             |                                                            |
| innodb_log_archive              | OFF                                                        |
| innodb_log_buffer_size          | 1048576                                                    |
| innodb_log_file_size            | 5242880                                                    |
| innodb_log_files_in_group       | 2                                                          |
| innodb_log_group_home_dir       | ./                                                         |
| innodb_max_dirty_pages_pct      | 90                                                         |
| innodb_max_purge_lag            | 0                                                          |
| innodb_mirrored_log_groups      | 1                                                          |
| innodb_open_files               | 300                                                        |
| innodb_rollback_on_timeout      | OFF                                                        |
| innodb_support_xa               | ON                                                         |
| innodb_sync_spin_loops          | 20                                                         |
| innodb_table_locks              | ON                                                         |
| innodb_thread_concurrency       | 8                                                          |
| innodb_thread_sleep_delay       | 10000                                                      |
| interactive_timeout             | 28800                                                      |
| join_buffer_size                | 131072                                                     |
| key_buffer_size                 | 8384512                                                    |
| key_cache_age_threshold         | 300                                                        |
| key_cache_block_size            | 1024                                                       |
| key_cache_division_limit        | 100                                                        |
| language                        | /usr/share/mysql/english/                                  |
| large_files_support             | ON                                                         |
| large_page_size                 | 0                                                          |
| large_pages                     | OFF                                                        |
| lc_time_names                   | en_US                                                      |
| license                         | GPL                                                        |
| local_infile                    | ON                                                         |
| locked_in_memory                | OFF                                                        |
| log                             | OFF                                                        |
| log_bin                         | OFF                                                        |
| log_bin_trust_function_creators | OFF                                                        |
| log_error                       |                                                            |
| log_queries_not_using_indexes   | OFF                                                        |
| log_slave_updates               | OFF                                                        |
| log_slow_queries                | OFF                                                        |
| log_warnings                    | 1                                                          |
| long_query_time                 | 10                                                         |
| low_priority_updates            | OFF                                                        |
| lower_case_file_system          | OFF                                                        |
| lower_case_table_names          | 0                                                          |
| max_allowed_packet              | 1048576                                                    |
| max_binlog_cache_size           | 4294963200                                                 |
| max_binlog_size                 | 1073741824                                                 |
| max_connect_errors              | 10                                                         |
| max_connections                 | 100                                                        |
| max_delayed_threads             | 20                                                         |
| max_error_count                 | 64                                                         |
| max_heap_table_size             | 16777216                                                   |
| max_insert_delayed_threads      | 20                                                         |
| max_join_size                   | 4294967295                                                 |
| max_length_for_sort_data        | 1024                                                       |
| max_prepared_stmt_count         | 16382                                                      |
| max_relay_log_size              | 0                                                          |
| max_seeks_for_key               | 4294967295                                                 |
| max_sort_length                 | 1024                                                       |
| max_sp_recursion_depth          | 0                                                          |
| max_tmp_tables                  | 32                                                         |
| max_user_connections            | 0                                                          |
| max_write_lock_count            | 4294967295                                                 |
| multi_range_count               | 256                                                        |
| myisam_data_pointer_size        | 6                                                          |
| myisam_max_sort_file_size       | 2146435072                                                 |
| myisam_recover_options          | OFF                                                        |
| myisam_repair_threads           | 1                                                          |
| myisam_sort_buffer_size         | 8388608                                                    |
| myisam_stats_method             | nulls_unequal                                              |
| net_buffer_length               | 16384                                                      |
| net_read_timeout                | 30                                                         |
| net_retry_count                 | 10                                                         |
| net_write_timeout               | 60                                                         |
| new                             | OFF                                                        |
| old_passwords                   | ON                                                         |
| open_files_limit                | 1024                                                       |
| optimizer_prune_level           | 1                                                          |
| optimizer_search_depth          | 62                                                         |
| pid_file                        | /var/run/mysqld/mysqld.pid                                 |
| plugin_dir                      |                                                            |
| port                            | 3306                                                       |
| preload_buffer_size             | 32768                                                      |
| profiling                       | OFF                                                        |
| profiling_history_size          | 15                                                         |
| protocol_version                | 10                                                         |
| query_alloc_block_size          | 8192                                                       |
| query_cache_limit               | 1048576                                                    |
| query_cache_min_res_unit        | 4096                                                       |
| query_cache_size                | 0                                                          |
| query_cache_type                | ON                                                         |
| query_cache_wlock_invalidate    | OFF                                                        |
| query_prealloc_size             | 8192                                                       |
| range_alloc_block_size          | 4096                                                       |
| read_buffer_size                | 131072                                                     |
| read_only                       | OFF                                                        |
| read_rnd_buffer_size            | 262144                                                     |
| relay_log                       |                                                            |
| relay_log_index                 |                                                            |
| relay_log_info_file             | relay-log.info                                             |
| relay_log_purge                 | ON                                                         |
| relay_log_space_limit           | 0                                                          |
| rpl_recovery_rank               | 0                                                          |
| secure_auth                     | OFF                                                        |
| secure_file_priv                |                                                            |
| server_id                       | 0                                                          |
| skip_external_locking           | ON                                                         |
| skip_networking                 | OFF                                                        |
| skip_show_database              | OFF                                                        |
| slave_compressed_protocol       | OFF                                                        |
| slave_load_tmpdir               | /tmp/                                                      |
| slave_net_timeout               | 3600                                                       |
| slave_skip_errors               | OFF                                                        |
| slave_transaction_retries       | 10                                                         |
| slow_launch_time                | 2                                                          |
| socket                          | /var/lib/mysql/mysql.sock                                  |
| sort_buffer_size                | 2097144                                                    |
| sql_big_selects                 | ON                                                         |
| sql_mode                        |                                                            |
| sql_notes                       | ON                                                         |
| sql_warnings                    | OFF                                                        |
| ssl_ca                          |                                                            |
| ssl_capath                      |                                                            |
| ssl_cert                        |                                                            |
| ssl_cipher                      |                                                            |
| ssl_key                         |                                                            |
| storage_engine                  | MyISAM                                                     |
| sync_binlog                     | 0                                                          |
| sync_frm                        | ON                                                         |
| system_time_zone                | UTC                                                        |
| table_cache                     | 64                                                         |
| table_lock_wait_timeout         | 50                                                         |
| table_type                      | MyISAM                                                     |
| thread_cache_size               | 0                                                          |
| thread_stack                    | 196608                                                     |
| time_format                     | %H:%i:%s                                                   |
| time_zone                       | SYSTEM                                                     |
| timed_mutexes                   | OFF                                                        |
| tmp_table_size                  | 33554432                                                   |
| tmpdir                          | /tmp/                                                      |
| transaction_alloc_block_size    | 8192                                                       |
| transaction_prealloc_size       | 4096                                                       |
| tx_isolation                    | REPEATABLE-READ                                            |
| updatable_views_with_limit      | YES                                                        |
| version                         | 5.0.77                                                     |
| version_bdb                     | Sleepycat Software: Berkeley DB 4.1.24: (January 29, 2009) |
| version_comment                 | Source distribution                                        |
| version_compile_machine         | i686                                                       |
| version_compile_os              | redhat-linux-gnu                                           |
| wait_timeout                    | 28800                                                      |
+---------------------------------+------------------------------------------------------------+
239 rows in set (0.00 sec)

For some reason, that table formats properly in the preview, but apparently not when viewing the question. Hopefully it isn't needed anyway.

1

4 Answers 4

2

Memory could be the biggest issue here. I would check the memory usage currently used and make sure you have some room for growth in that department.

Once you find out memory consumed currently you can try upping to the same amount on the new server and test that query.

kosta

2
  • I'm not sure I completely understand coderwhiz. How do I check the memory consumed. If you mean the mem usage for mysql in ps -aux response, it is only at 7.5%. Is there a way to get memory usage while running a query?
    – pedalpete
    May 3, 2010 at 21:04
  • Not sure if you figured this out but its not only RAM that could be hurting this. CPU and Disk is also a big factor for queries and seeing that your on a smaller VM, I wonder if the Disk IO or CPU is much less performing then your prod server.
    – coderwhiz
    Mar 29, 2011 at 21:11
2

It will depend to a large extent the size of the database, (the size on disk will give a very rough, but usually good-enough idea) of how much memory you need to be able to keep it all in memory.

The table type of the database tables also helps out a lot. MyISAM (which, until the latest version of MySQL, was the default table type) does not keep the actual database rows in memory (they may be, but that is down to the OS). So, if you had say 200MB of .MYD files, you might need only a few hundred MB to store them in memory - if they were converted to INNODB, and there was little overhead from other sections of the db server and other parts of the webserver.

Carefully sizing the memory use of the server, as well as turning off functionality that you don't need - like the binary log and DBD engine will let you do more, with less memory wasted. As usual, if you can keep the entire database in memory, and with fewer writes, it could be faster than before on a bigger box. having to touch the disk for database contents will be far slower on a small machine.

There are various scripts that will help you to tune the amount of memory Mysql uses as well.

1

If possible, create a virtual machine to replicate what you currently have. After ensuring that you are getting somewhere near the same level of performance shut the machine down and reduce its resources. Start it up again and see what you have. Repeat this until you get to the point where you reckon the performance is about as low as you're prepared to accept. That should give you an idea of what you're going to need on the VPS.

1
  • Thanks John, Since I've got the current machine being tested on linode, I can scale up until I get the performance I'm looking for. From the sounds of things, there isn't a straight forward formula for deciding these things
    – pedalpete
    May 4, 2010 at 2:17
0

I would suggest that before you go any further you look into optimising the MySQL server, the table and the queries, if you haven't already done that. It may well be that with thorough optimisation you may get adequate performance on a considerably lower spec machine.

1
  • I've already optimized the database considerably (at least to the best of my ability) to the point where a 4 table query across +1 million rows is returned in less than 1 second on the bigger machine.
    – pedalpete
    May 4, 2010 at 0:40

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .