Halfgaar

@RickJames The idInstallation. Installations don't need to know much about each other. And, the Vitess frontends parse, deconstructs and reconstruct queries and results in case you do need to select across shards.

@RickJames It (also) seems that hitting this table too concurrently gets progressively slower. I have some tricks up my sleeve to fix that. I may post an answer to this question if those work. We're also resuming our Vitess project, for sharding. Thanks for helping.

@RickJames it's hundreds of readings arriving from 200k installations, roughly. Once per minute for each 'installation', hence 2000 times per second a batch of 100. It's mostly written. Reading is an order of magnitude less. And the alarms, ignore that for now. It's dealt with by another stage that has less of an impact. -- I've been fiddling with any and all settings and I just can't get rid of that IO state for the update. There must be a way...

Well, changing the innodb_io_capacity settings didn't help. I just increased write IO, but the wait states and non-cpu load are the same.

Yes, the redo log (ib_log*) is circular. If it's about to overwrite the tail, it will commit those transactions to disk. As for the update: the primary key. But, I think the innodb_io_capacity has a lot to do with my issue. I'm waiting for a storage upgrade so that I can change it. I noticed that at Google Cloud, it's set 10 times higher by default than AWS RDS.

About innodb_io_capacity and innodb_log_file_size btw: my write load is constant, 24 hours a day. The log is circular, so no matter how big it is, it gets full and needs to be flushed. So, do I just convert to high IO storage and set innodb_io_capacity and innodb_io_capacity_max high?

@RickJames I added the graph. And no, no deadlocks (I monitor those). I'm fully sure this is the write load, from my experience beyond this incident. I think the IO load is all checkpointing ("8305.91 log i/o's/second") and I hope to reduce it / make it faster. I just upped innodb_io_capacity to 1000 at least.

There is also a web front end that makes use of it and there was a broad incident, causing load from that as well. The write load is all from one query and the relevant one here. Also, I added some text about log sequence numbers and amount of data written. @RickJames the only one I can find is trx_rseg_history_len.avg. That the one?

@RickJames both have been added. And I noted how the parallelism works.

Delegated? BuddyNS supplements your own server. As for NAT: everything, router brand, show some config rules/screenshots, etc.

Netstat looks OK. What I find suspicious, is that nmap -p 53,52,32 ns1.sitehosters.in (random ports) show all as filtered, except 53. This suggests it's being forwarded, but to something that is not listening. Can you elaborate on your NAT config/router (in the origin question)?

Can you do netstat -l -t -u -p. Can you see your name server there? Does it actually listen on 0.0.0.0, or only on localhost? And, what name server software do you use?

I guess it's possible you found a bug? Weird.

You can send it to [email protected]

Would it be possible you give me that tcpdump? I'd be willing to have a look at it

The only thing I can think of that's different than all my bridge setups, is that you have only one interface in it

What if you reboot?

Actually, I didn't look closely enough. The fact that it worked with eth0 is weird. What is the ifconfig output? Or, can you run tcpdump -l -n -i br0 -w dumpfile -s 65535 and open that file with wireshark?

Baffling... What if you add more ports to the bridge and plug the network in those? And about wireshark; right click on lines and follow traffic. See if you can find requests without answers. Also, are all ARP requests behaving normally? They are all answered and you have no duplicates? Could you make the dump available? I could look at it, perhaps.

and what about the gateway's network config?

oh, and could you also post the network config of the router? Perhaps it has a different subnet mask or something.

Does your router work :)? Could you also post the output of brctl show and perhaps some traceroutes/tracepaths?