2

I have a very good server (great CPU, huge RAM, NVMe) with a huge InnoDB buffer pool that's not full yet. The website is very write-intensive, but it runs fast enough (SELECT queries well optimized, good indexes where possible, etc).

However, since I've reduced long_query_time to just 1, I've been finding some queries which I'm not sure how I can improve.

  • INSERT statements that should simply insert the row without any problem;
  • REPLACE statements that should simply be based on the primary key to replace the row if needed;
  • UPDATE statements that use the primary key in the WHERE clause, and just updates a simple field.

This is the kind of insights I see logged in the Slow Query table:

Query Time = 00:00:01.842812
Lock Time = 00:00:00.000081
Rows Examined = 0

Query Time = 00:00:01.658600
Lock Time = 00:00:00.000150
Rows Examined = 0

Query Time = 00:00:01.825054
Lock Time = 00:00:00.000170
Rows Examined = 1

Query Time = 00:00:02.241583
Lock Time = 00:00:00.000084
Rows Examined = 1

This seems to just be some occurrences during the day, and not all queries of the same type.

My first suspicion would be that the tables might be locked at that point, but the "Lock Time" above is obviously negligible.

All my tables are InnoDB, and:

innodb_flush_log_at_trx_commit  = 2
innodb_doublewrite              = 1
innodb_flush_method             = O_DIRECT
innodb_buffer_pool_size         = 95G
innodb_log_file_size            = 24G
open_files_limit                = 10000

Replication is disabled. No table partitions. MariaDB 10.8.

13
  • 1
    It is possible, for example, that some huge query eliminates the table from the cache. And the server needs re-read the table for the query execution which needs in some time. Does the available memory is enough for to fit all your databases into the cache?
    – Akina
    Nov 17, 2022 at 9:17
  • @Akina - thank you for your reply. What cache exactly are you referring to? Query Cache is disabled, I'm not aware of any other. The buffer pool is not full yet (74.03% used), so there is still room there to fit more data. The server has a lot of memory free still (36GB free).
    – Nuno
    Nov 17, 2022 at 9:20
  • To Akina's point, do you have more Memory allocated to the Buffer Pool than the total size of all databases on your server?...just because it's not full now doesn't mean it wasn't at some previous point. Things get flushed from it for different reasons, so the fullness can fluctuate.
    – J.D.
    Nov 17, 2022 at 13:37
  • @J.D. - thank you. It's been constantly around the same % (haven't seen it full since I increased to 95GB months ago), even though the database is much larger than that, but a lot of the data is not used daily (or it's archives, etc).
    – Nuno
    Nov 17, 2022 at 14:42
  • Are all your tables ENGINE=InnoDB?
    – Rick James
    Nov 17, 2022 at 17:25

1 Answer 1

0

In most situations, REPLACE can and should be replaced by INSERT ... ON DUPLICATE KEY UPDATE.

REPLACE is a combo of a DELETE and an INSERT. If an auto_increment id is being used, it always burns a value, leading the ids rising rapidly.

Where practical, "batch" INSERTs. 100 rows can be inserted in a single batch 10 times as fast as one-row-at-a-time.

Poor indexing can lead to unnecessary table scans, which take time and block another activity. Please provide sample REPLACEs and UPDATEs, plus the relevant SHOW CREATE TABLEs.

Combine queries where it makes sense. For example, don't do a SELECT to get the PK, just to make the UPDATE faster.

1 second for a query tells me one of these: (*) inadequate INDEX or (*) complex query, or (*) some other query is blocking things. (The "lock time" is not relevant.)

How big are the tables? A table scan of a million rows would take seconds or maybe minutes, even fully cached.

1
  • Hi Rick. I appreciate your time and reply. However, I'm not sure if any of the above explains the situation. If you see, "Rows Examined = 0 or 1", so I doubt it's an inadequate Index or Full Table Scan happening. :) .. and regarding REPLACE, sometimes I do it intentionally. As I said, these seem to be happening randomly. All of these queries run instantly, most of the time.
    – Nuno
    Nov 17, 2022 at 20:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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