0
  1. What MySQL settings improve text searches?
  2. Which resources (memory, cpus, disk: ssd) to add first to increase performance?

Setup

  • Amazon RDS
    • type: db.t2.medium (2 CPU; 4G mem)
    • MySQL 5.6.27
  • Application: Redmine 3.3.0
  • Response time with simple but very generic search (for example: 'the') with no other load: ~15 sec.
  • Not using InnoDB FULLTEXT Indexes
  • Info on Redmine DB: [--] Database: redmine [--] +-- TABLE: 81 [--] +-- COLL : 3 (utf8_general_ci, utf8_unicode_ci, latin1_swedish_ci) [--] +-- ROWS : 5114804 [--] +-- DATA : 1.3G(81.79%) [--] +-- INDEX: 288.5M(18.21%) [--] +-- TOTAL: 1.5G [--] +-- ENGIN : 1 (InnoDB)

MySQL Settings

binlog_cache_size: 32768 binlog_format: MIXED bulk_insert_buffer_size: 64000000 default_storage_engine: InnoDB explicit_defaults_for_timestamp: 1 gtid-mode: OFF innodb_buffer_pool_size: {DBInstanceClassMemory*3/4} innodb_file_per_table: 1 innodb_flush_method: O_DIRECT innodb_ft_cache_size: 80000000 innodb_log_buffer_size: 8388608 innodb_log_file_size: 134217728 key_buffer_size: 16777216 local_infile: 1 log_output: TABLE log_slave_updates: 1 master-info-repository: TABLE max_binlog_size: 134217728 max_connections: {DBInstanceClassMemory/12582880} max_heap_table_size: 512000000 performance_schema: 0 query_cache_limit: 512000 query_cache_size: 512000000 query_cache_type: 1 read_buffer_size: 262144 read_rnd_buffer_size: 524288 relay_log_info_repository: TABLE relay_log_recovery: 1 sync_binlog: 1 table_open_cache_instances: 16 thread_stack: 262144 tmp_table_size: 512000000

0

query_cache_size: 512000000 plus query_cache_type: 1 is really bad for performance.

If the QC is on, then every modification to a table will cause a purge of all rows for that table in the QC. The bigger the QC is, the longer it takes. I recommend only 50M for the size. Or turn the QC off. All

"the" is a poor test case since it is (assuming English) so very common. Pick some word that occurs only a few times. Or, maybe a word that occurs once, a word that occurs about 10 times, and a word that occurs 100 times.

max_heap_table_size: 512000000 and tmp_table_size: 512000000 are also bad because they are 12% of RAM; lower to 1% of RAM. In complex queries, those limits come into play. If there are several complex queries running at once, you could blow out RAM. Swapping is really bad for performance.

If you can't permanently change those settings, complain to the provider. If they won't listen, then change providers. That's how bad I think the settings are. No change to "memory, cpus, disk: ssd" can fix the query_cache_size problem.

  • thank you very much for taking the time to answer my question. The performance improved after applying your recommendations! – Sander van Burken Apr 3 '17 at 6:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.