1

We have fairly large MySQL database (about 35GB total) and topping about 900qps. Performance is not a big issue for now, but the project is growing constantly and I'd rather start thinking about optimisation beforehand.

I recently learned about InnoDB/NDB cluster solutions, where you can add at least 3 servers for database fault tolerance, but i was wondering, will it improve overall performance as there are multiple servers involved in handling the traffic?

The project basically is public advertisement platform, and has only a few central tables that receive most traffic - users, ads and some more. I cannot point a single weak point, there's lots of different things that can be optimized with infrastructure (for example there is internal instant messaging between users - I plan to test how it will perform with MongoDB, I think messages suit this database architecture well)

Here is the query statistics for database server:

query statistics

Deniss Kozlovs
  • 191
  • 1
  • 9
  • What is the coding style? I see lots of stmt_prepare/execute/close, yet very few call_procedure. Is everything in a few giant stored procs? – Rick James Jan 08 '20 at 16:39
  • Would the UPTIME for the statistics listed in the question have been about 51 days? If there a reason stmt close is lagging about 7 million behind stmt prepare? Frequently these numbers are much closer together to ensure resources are released as soon as possible. – Wilson Hauck Jan 13 '20 at 16:24
  • 1
    @RickJames There are procedures and triggers, but not much. Isn't it because select is usually goes with prepare and these statistics just show them separate. – Deniss Kozlovs Jan 15 '20 at 11:28
  • 1
    @WilsonHauck Yes, about that. Current server uptime is 56 days. I'm not exactly sure why there is a difference. Could it mean that those queries were not properly executed? That would mean lots of bugs, but I havent noticed anything unusual. – Deniss Kozlovs Jan 15 '20 at 11:32
  • Please post from SSH root login to your MySQL instance the TEXT result of SELECT @@UPTIME; Thanks – Wilson Hauck Jan 15 '20 at 13:36
  • Instead: `SHOW GLOBAL STATUS LIKE 'Uptime';` – Rick James Jan 15 '20 at 16:45
  • 1
    @RickJames `SHOW GLOBAL STATUS LIKE 'Uptime'` returns 4973651 – Deniss Kozlovs Jan 16 '20 at 11:43
  • @RickJames Thanks for the correction to determine UPTIME. – Wilson Hauck Jan 17 '20 at 01:10
  • @DenissKozlovs Could you take the time to post to pastebin.com A) SHOW GLOBAL STATUS; and B) SHOW GLOBAL VARIABLES; for workload analysis? And share the pastebin.com links with us, please. – Wilson Hauck Jan 17 '20 at 01:11
  • 1
    @WilsonHauck Here you go: https://pastebin.com/b1dVhghB https://pastebin.com/b1bP6sYV – Deniss Kozlovs Jan 23 '20 at 08:58
  • @DenissKozlovs Thanks for the data. A few questions for you, A) GB on server, B) cores/CPU, C) data storage on HDD, SSD or NVME? D) have you tried www.jetdirect.com FREE version to identify TOP Queries, Tables low impact when polling is 10 seconds, much helpful information and charts of last 2 hours. Analysis will be completed after you post A), B), C). – Wilson Hauck Jan 23 '20 at 15:44
  • 1
    @WilsonHauck A) /var/lib/mysql is mounted as external storage with 70GB, 48G used. B) CPU is Intel(R) Xeon(R) CPU E5-2697 v2 @ 2.70GHz, 8 cores, MemTotal: 16265780 kB C) not sure completely, but i lean towards SSD; D) no, i havent tried. Here is the results of MysqlTuner script: https://pastebin.com/v0H7kDYG – Deniss Kozlovs Jan 24 '20 at 21:41

2 Answers2

2

There are tradeoffs. Multiple writable servers in a cluster still need to send all the writes to all the other machines. Reads benefit from clustering and/or Slaves. Writes benefit only slightly from clustering. (Sharding is the real solution for write scaling.)

Even adding two or more Slaves onto a single Master helps writes some. This is because the reads are now spread across at least the two Slaves, thereby competing less with the writes.

When growing fast:

  • If the disk becomes more than half full, watch out. If you need to ALTER a large table, it may need enough space for a full copy. And you don't want to run out of disk space.
  • Dumps and ALTERs take longer and longer.
  • Watch the writes (see above)
  • Usually there are slow queries even in the best-tuned systems. They will raise their ugly heads as you grow.
  • At some point (perhaps before 35G, perhaps long after 35G), the amount of RAM (think "innodb_buffer_pool_size" and NDB's equivalent) will become a serious concern. Often that can be postponed by avoiding table scans and other 'optimizations'.
  • Watch out for long-running transactions. Even 1 second may have an impact on your 900 qps.
  • If you have "spikes", they likely portend worse things to come.

If you are currently using InnoDB, you will find that there are several structural changes to move to NDB. The Transaction model is quite different ("eventual consistency"). Depending on the type of app, the could be a problem, or even a "brick wall".

Within just MySQL/MariaDB, there is "InnoDB Cluster" (MySQL 8.0) and Galera Cluster (PXC, MariaDB). They are possibly equally "good".

All of the above need at least 3 machines, preferably spread across at least 3 data centers. (Yes, datacenters can go down.)

Perhaps I can say more if you provide more details on your app. Even SHOW CREATE TABLE for your largest table may provide a lot of insight.

If you like, follow the instructions here ; I may be able to help you scale some more before changing topology or hardware.

More

Since nearly all queries are SELECTs, any replication or clustering topology can help you scale. Any number of Slaves can provide any amount of scaling. Those slaves can hang off a single Master (or InnoDB Cluster) or the 3 nodes of a Galera Cluster. NDB works differently, but can also be arbitrarily scaled for reading.

So the simple answer to your question is "yes". As for which solution, the is nothing (yet) to say which solution would be better for you. Perhaps the best that can be said is "Pick a solution and run with it."

Rick James
  • 2,058
  • 5
  • 11
0

A review of the VARIABLES and GLOBAL STATUS:

Observations:

* Version: 10.3.15-MariaDB
* 16 GB of RAM
* Uptime = 64d 10:48:05
* You are not running on Windows.
* Running 64-bit version
* You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:

  • Is this a standalone InnoDB database, not clustered, not a Slave, etc?

  • Find out whether you have HDD or SSD; then see a few items in the Details, below.

  • You are using the "Query cache", but it is not being very efficient, possibly slowing down the system overall. Recommend either turning it off or using DEMAND together with carefully picking which SELECTs to have SQL_CACHE in.

  • No COMMITs? Are you using autocommit=ON and ever using BEGIN? Please describe the typical DML queries; we may have suggestions on using transactions differently to decrease I/O.

  • Consider changing from REPLACE to INSERT ... ON DUPLICATE KEY UPDATE.

  • Your question was about what system to use. Please note that network bandwidth may be a big issue for you (Bytes_sent = 7666357 /sec); hence addressing number and verbosity of queries may be useful (and system-agnostic).

  • Why so many SHOW STATUS calls?

  • Lots of full-table scans for DELETE. Let's discuss them and possible ways to improve them, especially if they are on big tables. ( http://mysql.rjweb.org/doc.php/deletebig )

Details and other observations:

( Table_open_cache_misses ) = 14,420,381 / 5568485 = 2.6 /sec -- May need to increase table_open_cache (now 2048)

( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,024 * 4 = 4,096 -- Amount of work for page cleaners every second. -- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixable by lowering lru_scan_depth: Consider 1000 / innodb_page_cleaners (now 4). Also check for swapping.

( innodb_page_cleaners / innodb_buffer_pool_instances ) = 4 / 6 = 0.667 -- innodb_page_cleaners -- Recommend setting innodb_page_cleaners (now 4) to innodb_buffer_pool_instances (now 6)

( innodb_lru_scan_depth ) = 1,024 -- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth

( innodb_doublewrite ) = innodb_doublewrite = OFF -- Extra I/O, but extra safety in crash. -- OFF is OK for FusionIO, Galera, Slaves, ZFS.

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 182,569,362,432 / (5568485 / 3600) / 2 / 2048M = 0.0275 -- Ratio -- (see minutes)

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 5,568,485 / 60 * 2048M / 182569362432 = 1,091 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf. -- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size (now 2147483648). (Cannot change in AWS.)

( innodb_flush_method ) = innodb_flush_method = fsync -- How InnoDB should ask the OS to write blocks. Suggest O_DIRECT or O_ALL_DIRECT (Percona) to avoid double buffering. (At least for Unix.) See chrischandler for caveat about O_ALL_DIRECT

( Innodb_row_lock_waits ) = 917,931 / 5568485 = 0.16 /sec -- How often there is a delay in getting a row lock. -- May be caused by complex queries that could be optimized.

( innodb_flush_neighbors ) = 1 -- A minor optimization when writing blocks to disk. -- Use 0 for SSD drives; 1 for HDD.

( innodb_io_capacity ) = 200 -- I/O ops per second capable on disk . 100 for slow drives; 200 for spinning drives; 1000-2000 for SSDs; multiply by RAID factor.

( sync_binlog ) = 0 -- Use 1 for added security, at some cost of I/O =1 may lead to lots of "query end"; =0 may lead to "binlog at impossible position" and lose transactions in a crash, but is faster.

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks. -- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( character_set_server ) = character_set_server = latin1 -- Charset problems may be helped by setting character_set_server (now latin1) to utf8mb4. That is the future default.

( local_infile ) = local_infile = ON -- local_infile (now ON) = ON is a potential security issue

( query_cache_size ) = 128M -- Size of QC -- Too small = not of much use. Too large = too much overhead. Recommend either 0 or no more than 50M.

( Qcache_hits / Qcache_inserts ) = 1,259,699,944 / 2684144053 = 0.469 -- Hit to insert ratio -- high is good -- Consider turning off the query cache.

( Qcache_hits / (Qcache_hits + Com_select) ) = 1,259,699,944 / (1259699944 + 3986160638) = 24.0% -- Hit ratio -- SELECTs that used QC -- Consider turning off the query cache.

( Qcache_inserts - Qcache_queries_in_cache ) = (2684144053 - 46843) / 5568485 = 482 /sec -- Invalidations/sec.

( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (128M - 59914960) / 46843 / 16384 = 0.0968 -- query_alloc_block_size vs formula -- Adjust query_alloc_block_size (now 16384)

( Select_scan ) = 6,048,081 / 5568485 = 1.1 /sec -- full table scans -- Add indexes / optimize queries (unless they are tiny tables)

( Com_stmt_prepare - Com_stmt_close ) = 4,138,804,898 - 4129522738 = 9.28e+6 -- How many prepared statements have not been closed. -- CLOSE prepared statements

( Com_replace ) = 28,182,079 / 5568485 = 5.1 /sec -- Consider changing to INSERT ... ON DUPLICATE KEY UPDATE.

( binlog_format ) = binlog_format = MIXED -- STATEMENT/ROW/MIXED. -- ROW is preferred by 5.7 (10.3)

( slow_query_log ) = slow_query_log = OFF -- Whether to log slow queries. (5.1.12)

( long_query_time ) = 10 -- Cutoff (Seconds) for defining a "slow" query. -- Suggest 2

( max_connect_errors ) = 999,999,999 = 1.0e+9 -- A small protection against hackers. -- Perhaps no more than 200.

( Connections ) = 206,910,348 / 5568485 = 37 /sec -- Connections -- Use pooling?

Abnormally small:

Com_show_tables = 0
Created_tmp_files = 0.12 /HR
Innodb_dblwr_pages_written = 0
Qcache_total_blocks * query_cache_min_res_unit / Qcache_queries_in_cache = 5,166
eq_range_index_dive_limit = 0
innodb_ft_min_token_size = 2
innodb_spin_wait_delay = 4
lock_wait_timeout = 86,400
query_cache_min_res_unit = 2,048

Abnormally large:

Access_denied_errors = 93,135
Acl_table_grants = 10
Bytes_sent = 7666357 /sec
Com_create_trigger = 0.0026 /HR
Com_create_user = 0.0013 /HR
Com_replace_select = 0.086 /HR
Com_reset = 1 /HR
Com_show_open_tables = 0.02 /HR
Com_show_status = 0.18 /sec
Com_stmt_close = 741 /sec
Com_stmt_execute = 743 /sec
Com_stmt_prepare = 743 /sec
Delete_scan = 43 /HR
Executed_triggers = 1.5 /sec
Feature_fulltext = 0.62 /sec
Handler_read_last = 0.83 /sec
Handler_read_next = 357845 /sec
Handler_read_prev = 27369 /sec
Innodb_buffer_pool_pages_misc * 16384 / innodb_buffer_pool_size = 16.2%
Innodb_row_lock_time_max = 61,943
Prepared_stmt_count = 3
Qcache_free_blocks = 24,238
Qcache_hits = 226 /sec
Qcache_inserts = 482 /sec
Qcache_total_blocks = 118,160
Select_range = 53 /sec
Sort_range = 47 /sec
Tc_log_page_size = 4,096
innodb_open_files = 10,000
max_relay_log_size = 1024MB
performance_schema_max_stage_classes = 160

Abnormal strings:

aria_recover_options = BACKUP,QUICK
ft_min_word_len = 2
innodb_fast_shutdown = 1
innodb_use_atomic_writes = ON
log_slow_admin_statements = ON
myisam_stats_method = NULLS_UNEQUAL
old_alter_table = DEFAULT
plugin_maturity = gamma
Rick James
  • 2,058
  • 5
  • 11