Large table with geospatial data - slow queries during updates I have a large table with millions of rows (geospatial data) which is getting updated several times per day.

Selects by lat/lon are very fast (0.02-0.04 seconds) most of the time, but during updates the same queries can take as long as 5 seconds. Profiling shows them hanging either in the "sending data" or "statistics" state. Of course there is a lot of I/O going on during these mass updates which are also quite time-consuming because every row needs to be updated.

The storage engine used is InnoDB, though I had to keep buffer pool size at 128M (a larger buffer pool appears to put more strain on the server during update/cleanup operations which even results in failed/stalled connections).

Any suggestions for tuning? I wondered if maybe there is a way to specifically limit I/O write operations or prioritize Selects over Updates/Inserts (with the latter ones not being that time-critical). 

Table structure:

+-------------------+-------------+------+-----+---------+-------+
| Field             | Type        | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| vt                | datetime    | NO   | PRI | NULL    |       |
| lat               | double      | NO   | PRI | NULL    |       |
| lon               | double      | NO   | PRI | NULL    |       |
[lots of data fields]


my.cnf:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
max_connections=100
thread_cache_size=1
long_query_time = 1

open_files_limit=8192
table_cache=3000
table_open_cache=4000
table_definition_cache = 3000
query_cache_limit=256k
query_cache_size=100M
query_cache_type=1
query_cache_min_res_unit = 2k
thread_cache_size=5
read_buffer_size=1M
sort_buffer_size=2M
join_buffer_size=2M
max_heap_table_size=4G
key_buffer_size=3G
tmp_table_size=4G
innodb_file_per_table=1
You can use INSERT DELAYED to avoid blocking SELECTs:


  When a client uses INSERT DELAYED, it gets an okay from the server at
  once, and the row is queued to be inserted when the table is not in
  use by any other thread.
  
  Another major benefit of using INSERT DELAYED is that inserts from
  many clients are bundled together and written in one block. This is
  much faster than performing many separate inserts.


Unfortunately, there is no equivalent for UPDATE. You can do REPLACE DELAYED (which first deletes the existing row if one exists and then inserts a new row), but that's probably not useful to you.

A few other maybe useful optimizations: 

innodb_flush_method= O_DIRECT

innodb_flush_log_at_trx_commit= 2

Maybe consider if the geometry types (POINT) would be useful instead of your lat, lon doubles. Geometry types are indexable in InnoDB as of MariaDB 10.2.

Looking at your configuration, I think your query cache is set quite large. (Though it might make sense in your specific case, I don't know.) This can result in degraded performance due to lock contention, as the query cache is locked while it's updated.

I can see how the query cache might make sense in your use case if you're not writing to the database all that frequently. Note however that MySQL is retiring support for the query cache in MySQL 8.0. I don't know what the fate of the query cache is on the MariaDB side.