1

I have a MySQL 8.0 cluster database and so far I'm using it to test a couple of things, so I only write from time to time. Problem is that I have realised that there are write operations all the time. Even if my apps are not writing to it.

This is a screenshot of the dashboard.

Just to mention, I have another cluster in Digital Ocean and this issue is not happening there.

Any help will be appreciated example of writing operations

UPDATE (2021-03-09) Executing query as Vérace suggested: query

As I see that time_zone_transition is the one growing the most

Improve this question
1
  • Hi again - I was reviewing the queues and your question came up (voted not to close! :-) ) - but I remarked (which I previously hadn't) that these writes that you have noticed aren't occurring on a (presumably?) similar cluster. This strikes me as very puzzling to say the least as I would expect that the "housekeeping" to which I refer in my answer to occur on both - are there any differences in architecture, HW, Digitial Ocean subscription for your two clusters? I presume that you're running Linux (same OS/version)? Your cluster s/ware (which is?) should be the same?
    – Vérace
    Mar 7, 2021 at 17:00

1 Answer 1

Reset to default
0

For a university course in Data Visualisation, I used the Prometheus/Grafana stack (great s/ware) to present images of various database metrics while running various HammerDB benchmarks (also an excellent tool).

As part of this, to provide a baseline, I looked at my (single) server "at rest" and noticed (as you have) that there was activity - albeit a very small amount (~ 10 writes/second) - on an "inactive" server.

Basically, this is "house-keeping" functionality which happens even if there is no ongoing external DDL or DML.

There is an interesting article here and here (more recent and probably more accurate) showing the impact of the number of idle connections on the NOTPM (No. of Transactions Per Minute) results from the DBT2 benchmark tool.

As you can see from the results displayed in the second article, there is a small (but not non-existent) effect from just having idle connections.

You say that you are running a cluster - I would imagine that there is a communications overhead there with nodes pinging each other even in the absence of any serious workload (or even any workload at all).

You don't mention what cluster (NDB? Galera?), but I'm sure that a delve into the code (way above my pay grade... :-) ), a search, or a question on your cluster's architecture/guru list would be able to provide more detail.

If you're interested and have the time and the inclination to test what's going on, you may like to leave your "inactive" cluster running for a while (say over a weekend?) and run (some variant of) this query before and after you begin the test (adapted from here):

SELECT 
     table_schema AS the_db, 
     table_name AS the_table, 
     round(((data_length + index_length) / 1024 / 1024), 2) `size_in_mb` 
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC;

You might like to include the information_schema (and the mysql schema) as well since some/much/all of this activity that you are seeing might be occurring there.

This would presumably be dependent on the architecture of your cluster. Inserting the results of these queries into tables of their own should render any comparisons which you may wish to do relatively trivial.

Also, bear in mind that there may not be a whole lot to see, as these writes may simply be UPDATEs of a single or just a few records?

p.s. welcome to the forum and +1 for an interesting question!

Improve this answer
7
  • Wow! Such an interesting and detailed answer! Thanks a lot for your time. I’ll run that query you mention. I was worried because they are write operations and even if the amount is insignificant, every day the space left is less. So 3 days ago was 1,221GB and now is 1,224GB without using it 🤔
    – Juan Wilde
    Mar 7, 2021 at 15:57
  • Done! Upvote can't be seen because of my reputation, but it is there :)
    – Juan Wilde
    Mar 7, 2021 at 16:06
  • @JuanWilde - thanks - sometimes votes take a while to come through - though, even with very low rep., you should be able to upvote and mark as correct your own questions! Anyway, we'll see - just glad that my answer was of use to you! :-)
    – Vérace
    Mar 7, 2021 at 16:08
  • You could add a list of the tables that are increasing in size to your question to make it more complete - and maybe help to tease out exactly what "housekeeping" might be taking place during periods of "inactivity"!
    – Vérace
    Mar 7, 2021 at 17:07
  • You mean with the query you have provided?
    – Juan Wilde
    Mar 8, 2021 at 19:16

Your Answer

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

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