Query Logging - one file and more nodes in a cluster

[Slawek Korwel]

Hello group! 

I'm looking for an explanation how to build my proxysql installation.

- my proxysql works in docker swrm as services.

- I need to have 3 nodes in the proxysql cluster for HA. I want all of them to record query logs into the same log file - first issue. How to achieve that? My tests show me, that only the lastly opened node can write (docker volume shared for all of them) 

- why proxysql truncates that log file upon every start??? How to change it / stop it? I can't lost any single line of log, but my proxysql instances can restart anytime (docker)

I appreciate for any help!

Thanks,

[René Cannaò]

Hi Slawek,

Thank you for sharing your experience with ProxySQL.

I will answer your questions in different order.

Q: why proxysql truncates that log file upon every start?

A: proxysql does not truncate any log upon every start

Q:  I need to have 3 nodes in the proxysql cluster for HA. I want all of them to record query logs into the same log file - first issue. How to achieve that?

A: Short answer: Please do not do that!

Longer answer:

It is generally a bad idea to have multiple processes (not proxysql, but in general) writing on the same file at the same time, that can lead to race conditions, partial writes, data corruptions, etc .

In fact, you are already experiencing data corruption: as I wrote previously, proxysql doesn't truncate any file.

Q: My tests show me, that only the lastly opened node can write (docker volume shared for all of them) 

In your case, it is likely that all proxysql instances are writing to the file, but using different offsets. If you are seeing entries from only 1 instance, it likely means it has override the entries from the other(s) instance(s).

To get into more specific on how ProxySQL operates.

proxysql could open the file in append-only mode, but this doesn't completely eliminate issues related to data consistency and race conditions, resulting in interleaving or overlapping writes, potentially leading to inconsistent or garbled data.

Finally, proxysql performs its own log rotation: this could be impossible if multiple processes operates on the same file.

You can simply concatenate together the files once they are closed .

Thanks,

René

--
You received this message because you are subscribed to the Google Groups "proxysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/proxysql/a625d08d-8d94-4588-985e-7623b1243095n%40googlegroups.com.