2 analysis
source | link

(Although the primary Question is about GIS, the the secondary question is about speeding up "find nearest".)

For the equivalent of LIMIT 50, the following algorithm will probably touch less than 200 rows (and calculate Great Circle distances for only those). (Better than 79,901?)

http://mysql.rjweb.org/doc.php/latlng (code included)

Analysis of VARIABLES/STATUS

It seems like you have not run much yet. Hence, there is not much much to say here:

Observations:

  • Version: 8.0.12
  • 16 GB of RAM
  • Uptime = 05:49:58; some GLOBAL STATUS values may not be meaningful yet.
  • Are you sure this was a SHOW GLOBAL STATUS ?
  • You are not running on Windows.
  • Running 64-bit version
  • You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:

key_buffer_size = 50M
long_query_time = 2

Turn on the slowlog so you can identify slow queries.

Details and other observations:

( (key_buffer_size - 1.2 * Key_blocks_used * 1024) / _ram ) = (1024M - 1.2 * 16 * 1024) / 16384M = 6.2% -- Percent of RAM wasted in key_buffer. -- Decrease key_buffer_size.

( Key_blocks_used * 1024 / key_buffer_size ) = 16 * 1024 / 1024M = 0.00% -- Percent of key_buffer used. High-water-mark. -- Lower key_buffer_size to avoid unnecessary memory usage.

( table_open_cache ) = 4,000 -- Number of table descriptors to cache -- Several hundred is usually good.

( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 443,594 / 786432 = 56.4% -- Pct of buffer_pool currently not in use -- innodb_buffer_pool_size is bigger than necessary?

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 87,040 / (20998 / 3600) / 2 / 512M = 1.4e-5 -- Ratio -- (see minutes)

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 20,998 / 60 * 512M / 87040 = 2.16e+6 -- 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. (Cannot change in AWS.)

( 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.

( join_buffer_size / _ram ) = 262,144 / 16384M = 0.00% -- 0-N per thread. May speed up JOINs (better to fix queries/indexes) (all engines) Used for index scan, range index scan, full table scan, each full JOIN, etc. -- If large, decrease join_buffer_size to avoid memory pressure. Suggest less than 1% of RAM. If small, increase to 0.01% of RAM to improve some queries.

( query_prealloc_size / _ram ) = 8,192 / 16384M = 0.00% -- For parsing. Pct of RAM

( query_alloc_block_size / _ram ) = 8,192 / 16384M = 0.00% -- For parsing. Pct of RAM

( net_buffer_length / max_allowed_packet ) = 16,384 / 64M = 0.02%

( (Com_show_create_table + Com_show_fields) / Questions ) = (7 + 7) / 698 = 2.0% -- Naughty framework -- spending a lot of effort rediscovering the schema. -- Complain to the 3rd party vendor.

( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (0 + 20 + 0 + 0) / 0 = INF -- Statements per Commit (assuming all InnoDB) -- Low: Might help to group queries together in transactions; High: long transactions strain various things.

( Select_scan / Com_select ) = 96 / 355 = 27.0% -- % of selects doing full table scan. (May be fooled by Stored Routines.) -- Add indexes / optimize queries

( expire_logs_days ) = 0 -- How soon to automatically purge binlog (after this many days) -- Too large (or zero) = consumes disk space; too small = need to respond quickly to network/machine crash. (Not relevant if log_bin = OFF)

( slave_pending_jobs_size_max / max_allowed_packet ) = 128M / 64M = 2 -- For parallel slave threads -- slave_pending_jobs_size_max must not be less than max_allowed_packet

( 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

( back_log / max_connections ) = 151 / 151 = 100.0%

( Threads_created / Connections ) = 4 / 214 = 1.9% -- Rapidity of process creation -- Increase thread_cache_size (non-Windows)

Abnormally large:

Com_create_db = 0.17 /HR
Com_drop_db = 0.34 /HR
Com_show_profiles = 1.9 /HR
Innodb_buffer_pool_pages_flushed / max(Questions, Queries) = 0.365
Innodb_buffer_pool_pages_free = 443,594
Select_range / Com_select = 33.2%
Ssl_session_cache_size = 128
innodb_purge_threads = 4
innodb_undo_tablespaces = 2
max_error_count = 1,024
max_length_for_sort_data = 4,096
optimizer_trace_max_mem_size = 1.05e+6
slave_pending_jobs_size_max = 128MB

Abnormal strings:

Ssl_session_cache_mode = SERVER
default_authentication_plugin = caching_sha2_password
event_scheduler = ON
explicit_defaults_for_timestamp = ON
ft_boolean_syntax = + -><()~*:&
have_query_cache = NO
have_ssl = YES
have_symlink = DISABLED
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
innodb_fast_shutdown = 1
innodb_undo_log_truncate = ON
log_syslog = ON
master_info_repository = TABLE
optimizer_trace = enabled=off,one_line=off
optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on
relay_log_info_repository = TABLE
slave_rows_search_algorithms = INDEX_SCAN,HASH_SCAN
ssl_ca = ca.pem
ssl_cert = server-cert.pem
ssl_key = server-key.pem

For the equivalent of LIMIT 50, the following algorithm will probably touch less than 200 rows (and calculate Great Circle distances for only those). (Better than 79,901?)

http://mysql.rjweb.org/doc.php/latlng (code included)

(Although the primary Question is about GIS, the the secondary question is about speeding up "find nearest".)

For the equivalent of LIMIT 50, the following algorithm will probably touch less than 200 rows (and calculate Great Circle distances for only those). (Better than 79,901?)

http://mysql.rjweb.org/doc.php/latlng (code included)

Analysis of VARIABLES/STATUS

It seems like you have not run much yet. Hence, there is not much much to say here:

Observations:

  • Version: 8.0.12
  • 16 GB of RAM
  • Uptime = 05:49:58; some GLOBAL STATUS values may not be meaningful yet.
  • Are you sure this was a SHOW GLOBAL STATUS ?
  • You are not running on Windows.
  • Running 64-bit version
  • You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:

key_buffer_size = 50M
long_query_time = 2

Turn on the slowlog so you can identify slow queries.

Details and other observations:

( (key_buffer_size - 1.2 * Key_blocks_used * 1024) / _ram ) = (1024M - 1.2 * 16 * 1024) / 16384M = 6.2% -- Percent of RAM wasted in key_buffer. -- Decrease key_buffer_size.

( Key_blocks_used * 1024 / key_buffer_size ) = 16 * 1024 / 1024M = 0.00% -- Percent of key_buffer used. High-water-mark. -- Lower key_buffer_size to avoid unnecessary memory usage.

( table_open_cache ) = 4,000 -- Number of table descriptors to cache -- Several hundred is usually good.

( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 443,594 / 786432 = 56.4% -- Pct of buffer_pool currently not in use -- innodb_buffer_pool_size is bigger than necessary?

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 87,040 / (20998 / 3600) / 2 / 512M = 1.4e-5 -- Ratio -- (see minutes)

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 20,998 / 60 * 512M / 87040 = 2.16e+6 -- 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. (Cannot change in AWS.)

( 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.

( join_buffer_size / _ram ) = 262,144 / 16384M = 0.00% -- 0-N per thread. May speed up JOINs (better to fix queries/indexes) (all engines) Used for index scan, range index scan, full table scan, each full JOIN, etc. -- If large, decrease join_buffer_size to avoid memory pressure. Suggest less than 1% of RAM. If small, increase to 0.01% of RAM to improve some queries.

( query_prealloc_size / _ram ) = 8,192 / 16384M = 0.00% -- For parsing. Pct of RAM

( query_alloc_block_size / _ram ) = 8,192 / 16384M = 0.00% -- For parsing. Pct of RAM

( net_buffer_length / max_allowed_packet ) = 16,384 / 64M = 0.02%

( (Com_show_create_table + Com_show_fields) / Questions ) = (7 + 7) / 698 = 2.0% -- Naughty framework -- spending a lot of effort rediscovering the schema. -- Complain to the 3rd party vendor.

( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (0 + 20 + 0 + 0) / 0 = INF -- Statements per Commit (assuming all InnoDB) -- Low: Might help to group queries together in transactions; High: long transactions strain various things.

( Select_scan / Com_select ) = 96 / 355 = 27.0% -- % of selects doing full table scan. (May be fooled by Stored Routines.) -- Add indexes / optimize queries

( expire_logs_days ) = 0 -- How soon to automatically purge binlog (after this many days) -- Too large (or zero) = consumes disk space; too small = need to respond quickly to network/machine crash. (Not relevant if log_bin = OFF)

( slave_pending_jobs_size_max / max_allowed_packet ) = 128M / 64M = 2 -- For parallel slave threads -- slave_pending_jobs_size_max must not be less than max_allowed_packet

( 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

( back_log / max_connections ) = 151 / 151 = 100.0%

( Threads_created / Connections ) = 4 / 214 = 1.9% -- Rapidity of process creation -- Increase thread_cache_size (non-Windows)

Abnormally large:

Com_create_db = 0.17 /HR
Com_drop_db = 0.34 /HR
Com_show_profiles = 1.9 /HR
Innodb_buffer_pool_pages_flushed / max(Questions, Queries) = 0.365
Innodb_buffer_pool_pages_free = 443,594
Select_range / Com_select = 33.2%
Ssl_session_cache_size = 128
innodb_purge_threads = 4
innodb_undo_tablespaces = 2
max_error_count = 1,024
max_length_for_sort_data = 4,096
optimizer_trace_max_mem_size = 1.05e+6
slave_pending_jobs_size_max = 128MB

Abnormal strings:

Ssl_session_cache_mode = SERVER
default_authentication_plugin = caching_sha2_password
event_scheduler = ON
explicit_defaults_for_timestamp = ON
ft_boolean_syntax = + -><()~*:&
have_query_cache = NO
have_ssl = YES
have_symlink = DISABLED
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
innodb_fast_shutdown = 1
innodb_undo_log_truncate = ON
log_syslog = ON
master_info_repository = TABLE
optimizer_trace = enabled=off,one_line=off
optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on
relay_log_info_repository = TABLE
slave_rows_search_algorithms = INDEX_SCAN,HASH_SCAN
ssl_ca = ca.pem
ssl_cert = server-cert.pem
ssl_key = server-key.pem
1
source | link

Side issues

Use ascii for hex values such as hashes.

Pack hex hashes into binary:

hash_id BINARY(16)
HEX(hash_id)   -- when reading
hash_id = UNHEX(...) -- when writing

Get rid of the AUTO_INCREMENT id and just use hash_id.

Better would be to get rid of the hash -- if the data grows too large to fit in RAM, it will become I/O-bound.

Faster algorithm

For the equivalent of LIMIT 50, the following algorithm will probably touch less than 200 rows (and calculate Great Circle distances for only those). (Better than 79,901?)

http://mysql.rjweb.org/doc.php/latlng (code included)