1
I have a PHP5.6 application running Symfony 2.x, backed by MySQL 5.1.73. We are currently experiencing latency in our data tier during peak load. During normal operation we see about 2k queries per second and performance is fine, the app server responds in <200ms, 45ms of that is MySQL time but at peak load we might see 6k queries per second for around 5-10 minutes 2 twice a day, each weekday. At this time we can see app server responses reach 8,550ms 8,420ms of that is MySQL time.
Server Spec: RAM 64GB Intel(R) Xeon(R) CPU E5-2640 v4 @ 2.40GHz
We are setup as a multi-tenant system where each tenant has their own database schema circa 3000 schemas on the database server.
Slow query logging shows that selects on the same table in each schema are slow but this is also the table with the highest throughput. This table can be up to 70MB with around 500,000 rows for our busier tenants.
I'd like to change the following configs (some gradually over the course of a few days/weeks) to improve performance:
thread_cache_size: 64 -> 128 -> 256 -> 512 -> 1000
Our threads created value can reach 3000 at peak load, from what i've read creating threads is an expensive process and i'm hopping this will reduce that. i'd like to increase the cache gradually to observe the impact. Is there a particular strategy for setting this value when dealing with a busy PHP application?
innodb_thread_concurrency: 8 -> 0
From what i've read setting no limit on this is the best option unless the db server is showing resource contention(which it doesn't seem to be).
innodb_buffer_pool_size: 16384M -> 45000M
key_buffer: 32187M -> 500M
Around a year ago we completed the migration from MyISAM to InnoDB but we still haven't adjusted the innodb_buffer_pool_size and key_buffer to reflect this.
In the last couple of weeks we have increased the table_open_cache value from 1k -> 4k -> 8k as we could see more tables being opened than was available in table_open_cache.
This has had a positive impact but not enough to handle our load spikes.
Are any of these configs above or below a reasonable value? I understand that it may be hard to answer when you have limited knowledge of the dataset but please let me know and I'll provide what I can.
I've kept any value which might be of importance below:
mysql> SHOW GLOBAL VARIABLES;
+-----------------------------------------+-------------------------------------------------------------------------------------------+
| Variable_name | Value |
+-----------------------------------------+-------------------------------------------------------------------------------------------+
| innodb_adaptive_hash_index | ON |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_size | 17179869184 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | ON |
| innodb_flush_log_at_trx_commit | 2 |
| innodb_flush_method | O_DIRECT |
| innodb_lock_wait_timeout | 50 |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 524288000 |
| innodb_max_dirty_pages_pct | 90 |
| innodb_open_files | 131072 |
| innodb_rollback_on_timeout | OFF |
| innodb_stats_on_metadata | ON |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| join_buffer_size | 1048576 |
| max_connect_errors | 100 |
| max_connections | 10000 |
| max_join_size | 18446744073709551615 |
| max_length_for_sort_data | 1024 |
| max_long_data_size | 134217728 |
| max_prepared_stmt_count | 131072 |
| max_tmp_tables | 32 |
| query_cache_limit | 2097152 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1406140416 |
| query_cache_type | ON |
| read_buffer_size | 262144 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 | |
| sort_buffer_size | 262144 | |
| table_definition_cache | 315000 |
| table_lock_wait_timeout | 50 |
| table_open_cache | 8000 | |
| thread_cache_size | 64 |
| thread_handling | one-thread-per-connection |
| thread_stack | 262144 | |
| timed_mutexes | OFF | |
| tmp_table_size | 67108864 | |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
Waterbuck
Posted 2019-05-16T14:27:13.873
Reputation: 11