Storage of payment logs - what to choose, sql, nosql or files?

Question

MySQL

There is some software that makes payments and these are the signs:
payments (payment_id int auto_increment primary key) - you don't need to know anything more about it
payment_log (payment_id int, text text, date_add datetime default now());
There are several thousand payments per day (10-15, in different ways - at the moment there are about 35 million records), for each payment a different number of logs. from a couple of records, up to several dozen. They are not written for a work cycle, but the log may be added after some time. For example, a payment is in progress due to problems at the gateway, and we log each status poll once every n minutes. Therefore, it will not be possible to compose something like {'payment_id': 123, 'logs': [...., ...., ....]}, write it down and forget it. You need a quick addition to a list (or some other structure) by key.
there are no problems with the payment table, it works quickly with the correct indexes, and so on. Payment logs also work fine, but somehow I don’t want to store them in the main database, because they are needed only for display in the web interface, and then at the time of some proceedings.
Option 1. Leave it as it is, the downside is that you don’t like it aesthetically, the backup time increases, and so on and so forth. everything is obvious here.
option 2. Transfer logs to nosql (some kind of mongo or radish), I didn’t really have much to do in this vein, so I don’t know
option 3. Transfer to files tied to id: for id 12345678 the file will lie somewhere in / mnt / payment_logs/1/2/3/4/5/1234578.txt - also not very aesthetically
pleasing
In general, I ask for help, where is it better to store data like {'payment_id': 123, 'logs': [{'date_add': '...', 'text': 'log #1'}, {'date_add': '. ..', 'text': 'log #2'}] and a quick addition to the logs collection by payment_id.

Answer 1

It is possible in Logstash .

Answer 2

Text files are normal.
If you are going to really look for something there regularly - then some kind of ElasticSearch (if sharpened for multi-server - per cluster) or SphinxSearch (if sharpened for speed)

Answer 3

It’s a pity that you don’t have postgresql, and since you need to rarely and infrequently access this data, you could partition this table and move the table storage to another disk (slow and inexpensive).