Insert/ Update Scalability (more records, continually longer lookups --- solution?)

Question

posted this before but never received a clear answer.

I'm working on an ETL project and am basically feeding JSON records of "tickets" into a MySQL database. (actually come to think of it, may be migrating to SQL server, but I'm not sure that will be a huge difference).

These JSON records are the "current data" for each ticket.

So it will say, for example:

Ticket_Id: 1 Ticket_Subject: Hi Ticket_Issue: Greeting Ticket_Status: Open

In the future I might get a record like:

Ticket_Id: 1 Ticket_Subject: Hi Ticket_Issue: Greeting Ticket_Status: Closed

These update records are stored in the order in which they are made ... so it's pretty easy to sweep through each day, parse the JSON, and perform an Insert/ Update into a SQL database/ table with Ticket records.

Here's the issue. There's currently 100k tickets in the database. In 6 months there may be 500k tickets.

The ETL process is already fairly slow, and I need to work on optimizing each step. For now, I want to focus on the SQL Insert/ Update.

Currently it's lookup against 100k rows ... soon it will be looking up against 500k ... eventually 2 million ... you get the idea. This is NOT scalable currently.

Luckily, I know that these tickets become "archived"/ uneditable after 30 days of inactivity. They become "closed" permanently and will never be changed or updated again.

Thus, there may be a logical solution that somehow transfers, locks, moves, partitions, ignores .... I simply don't know ... these "closed/ archived" tickets ... so I only have to perform daily lookups against 30k active tickets any given day. Not a million.

This seems like an extremely common issue (virtually ANY large insert/ update conundrum) .... but I have not found many simple ideas online.

I'm wondering what is a simple, maintainable solution to this problem. Even if the "simplest" is sadly two different tables or what have you. I have no problem doing a "union" select statements --- in actuality the 'select' query times are already super fast and not a problem. The problem is the insert/ update/ lookup/ write times.

Any ideas?

Answer 1

Parse the JSON when the record in inserted, not later. (OK, maybe you need to collect the JSON in a "staging" table between arrival and processing.)

When you parse the JSON, extract only the fields that you need to manipulate/search/etc in MySQL. Be conservative. Don't extract many fields. Leave the JSON as is in case you need the details later. It would even be good to compress it and store it into a BLOB -- to save space, hence decrease I/O.

What queries will you be doing on the data? Studying them is critical to the design of a Data Warehouse.

Properly indexed (etc), a table that grows by a factor of 10 may be only 10% slower. Don't panic, but do look at the queries. I have seen billion-row tables that hum along nicely.

It sounds like you are inserting only about 2 rows per minute; is that correct? 100 per second is when it starts to get challenging.

Let's worry about the "archiving" after we have covered some of the issues I mentioned.