Назад | Перейти на главную страницу

MySQL - переход на сервер с более низкой производительностью, насколько я могу уменьшить размер?

Я управляю сайтом уже несколько лет, трафик которого на самом деле не растет, и я хочу сэкономить на хостинге, но сохранить его для лояльных пользователей сайта и API.

В базе данных есть одна таблица с почти 4 миллионами строк и на двойном сервере Xeon 5320 объемом 4 ГБ.

Когда я проверяю статистику сервера на этом сервере с помощью ps -aux, Я получаю отдачу от MySQL, работающего примерно на 11%, так что никакой серьезной нагрузки. Основной запрос к MySQL выполняется примерно за 0,45 секунды.

Я зашел на linode.com, чтобы посмотреть, какую производительность я могу получить от одной из их крошечных коробок, и их 360 МБ RAM XEN VPS вернет тот же запрос за 20 секунд.

Явно недостаточно хорошо.

Я посмотрел на переменные MySQL, и они обе очень похожи (я включил вывод переменных show ниже, если кому-то интересно).

Есть ли хороший способ решить, какой размер сервера нужен, в зависимости от того, откуда я?

Может ли RAM иметь значение при большом размере таблицы?

Есть ли способ выяснить, какой объем оперативной памяти был бы идеальным?

Вот вывод переменных шоу (хотя я не уверен, что это важно).

+---------------------------------+------------------------------------------------------------+
| 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)

По какой-то причине эта таблица правильно форматируется при предварительном просмотре, но, видимо, не при просмотре вопроса. Надеюсь, это все равно не нужно.

Это будет в значительной степени зависеть от размера базы данных (размер на диске дает очень приблизительное, но обычно достаточно хорошее представление) от того, сколько памяти вам нужно, чтобы иметь возможность хранить все это в памяти.

Тип таблицы таблиц базы данных также очень помогает. MyISAM (который до последней версии MySQL был типом таблицы по умолчанию) не хранит фактические строки базы данных в памяти (они могут быть, но это зависит от ОС). Итак, если бы у вас было, скажем, 200 МБ файлов .MYD, вам может потребоваться всего несколько сотен МБ для их хранения в памяти - если они были преобразованы в INNODB, и были небольшие накладные расходы из других разделов сервера db и других частей веб сервер.

Тщательное определение размера использования памяти сервером, а также отключение функций, которые вам не нужны, например двоичного журнала и механизма DBD, позволят вам делать больше с меньшими потерями памяти. Как обычно, если вы можете хранить всю базу данных в памяти и с меньшим количеством операций записи, это может быть быстрее, чем раньше, на большом ящике. необходимость касаться диска для содержимого базы данных будет намного медленнее на маленькой машине.

Существуют различные сценарии, которые помогут вам настроить объем памяти, который использует Mysql.

Память может быть здесь самой большой проблемой. Я бы проверил используемую в настоящее время память и убедился, что у вас есть место для роста в этом отделе.

Как только вы обнаружите, что в настоящее время используется память, вы можете попробовать увеличить ее до того же количества на новом сервере и протестировать этот запрос.

Коста

Если возможно, создайте виртуальную машину для репликации того, что у вас есть. Убедившись, что вы приближаетесь к тому же уровню производительности, выключите машину и уменьшите ее ресурсы. Запустите его снова и посмотрите, что у вас есть. Повторяйте это до тех пор, пока не дойдете до точки, где, по вашему мнению, производительность будет настолько низкой, насколько вы готовы принять. Это должно дать вам представление о том, что вам понадобится на VPS.

Я бы посоветовал, прежде чем идти дальше, изучить оптимизацию сервера MySQL, таблицы и запросов, если вы еще этого не сделали. Вполне возможно, что при тщательной оптимизации вы получите адекватную производительность на машине со значительно более низкими характеристиками.