hi my dears, I have an issue at work where we have to work with millions (150 mln~) of product data points. We are using SQL server because it was inhouse available for development. however using various tables growing beyond 10 mln the server becomes quite slow and waiting/buffer time becomes >7000ms/sec. which is tearing our complete setup of various microservices who read, write and delete from the tables continuously down. All the stackoverflow answers lead to - its complex. read a 2000 page book.
the thing is. my queries are not that complex. they simply go through the whole table to identify any duplicates which are not further processed then, because the processing takes time (which we thought would be the bottleneck). but the time savings to not process duplicates seems now probably less than that it takes to compare batches with the SQL table. the other culprit is that our server runs on a HDD which is with 150mb read and write per second probably on its edge.
the question is. is there a wizard move to bypass any of my restriction or is a change in the setup and algorithm inevitable?
edit: I know that my questions seems broad. but as I am new to database architecture I welcome any input and discussion since the topic itself is a lifetime know-how by itself. thanks for every feedbach.
sounds like some changes would be a good idea 😅
haha. relating to a switch to ssd? or in which direction?
sounds like lots of directions:
- why are duplicates such a frequent problem, sounds like upstream solutions are needed there?
- SSD would be faster read/write, yes (your data shouldn’t be on a single hard-drive, it should be regularly backed up at least - make the HDD the backup and copy the main database to SSD?); you might even consider a cloud service like AWS RDS
- for some use-cases, a noSQL database can be faster for reading - but it’s contextual
While I get that SO can be monstrously unhelpful, database optimization is a whole profession so I think we need a bit more to help
A few directions we could go here: Post your SQL query. This could be a structure or query issue. Best case, we could do some query optimization. Also, have you looked into indexing?
Where are your bottlenecks coming from? Is your server desined for a I/O intensive workload like databases. Sequential read speed is not a good metrix.
What about concurrency? If this is is super read/write intensive, optimization could depend on where data is written while you’re reading
All the stackoverflow answers lead to - its complex. read a 2000 page book.
This is an exceptionally good answer and you’re doing everything possible to avoid doing it, when you could have been half way done with the book by now probably. Database administration is a profession, not a job. It requires specialized training to do it well and doing everything possible to avoid that training and knowledge won’t help you one bit.
my queries are not that complex.
It doesn’t matter. Your database is very complex.
they simply go through the whole table to identify any duplicates
You search 10 million records on every request and you wonder why it’s slow?
is there a wizard move to bypass any of my restriction or is a change in the setup and algorithm inevitable?
No. Database administration is very difficult. Reading that 2000 page book is essential for setting up infrastructure to avoid a monolithic setup like this in the first place.
the other culprit is that our server runs on a HDD which is with 150mb read and write per second probably on its edge.
lol wtf
Realistically, this setup is 10 years too old. How large is your database? Is there any reason why it can’t be run in memory? 10 million lines isn’t insurmountable. Full text with a moderate number of tables could be ~10GB–no reason that can’t be run in memory with Redis or other in-memory database or to update to a more modern in-memory database solution like Dice.
Your biggest problem is the lack of deduplication and normalization in your database design. If it’s not fixed now, it’ll simply get worse YOY until it’s unusable. Either spend the time and money now, or spend even more time and money later to fix it. 🤷♂️
tl;dr: RTFM.
Realistically, this setup is 10 years too old
thanks for this input. This was the winning argument for my boss for migrating to a modern server. While I admit that I see many flaws in our design, we are now working on refactoring our architecture and approach itself.
Thanks to the other numerous answers leading me to the right direction (hopefully).
This was the winning argument for my boss for migrating to a modern server.
Exceptionally good news! Glad it’s working out. Be sure to make a new post when you decide what you go with, I’m sure people here would enjoy hearing about your approach.
Sort of harsh approach, but I get it.
Though I did learn the most while having a lot of data and had issues with performance.
Studying Postgres in that job was the absolute best part, I learned so much, and now I can’t find a problem Postgres can’t fix.
There was a running joke in my last office that I was paid to promote Pg because every time MySQL fucked something up, I would bring up how Postgres would solve it. I even did several presentations.
Then we migrated to Postgres and suddenly everything is stable as a rock, even under worse conditions and way more data.
I just love Postgres so much.
Sometimes it feels like postgres is cheating (in a good way)
Compared to MySQL most definitely.
Granted, Oracle has pushed some fresh air into it, but still it has a long way to go.
Sort of harsh approach, but I get it.
Yeah. To me it feels like we used a powertool as a hammer. Brute force in the wrong way.
As an update: I was able to convince my people to migrate to a modern server - altogether we also switch from SQL server to PostgreSQL. During this migration we also try to refactor our workflow since it was flawed by design.
So, many thanks for the input.
To paraquote H. L. Mencken: For every problem, there is a solution that’s cheap, fast, easy to implement – and wrong.
Silver bullets and magic wands don’t really exist, I’m afraid. There’s amble reasons for DBA’s being well-paid people.
There’s basically three options: Either increase the hardware capabilities to be able to handle the amount of data you want to deal with, decrease the amount of data so that the hardware you’ve got can handle it at the level of performance you want or… Live with the status quo.
If throwing more hardware at the issue was an option, I presume you would just have done so. As for how to viably decrease the amount of data in your active set, well, that’s hard to say without knowledge of the data and what you want to do with it. Is it a historical dataset or time series? If so, do you need to integrate the entire series back until the dawn of time, or can you narrow the focus to a recent time window and shunt old data off to cold storage? Is all the data per sample required at all times, or can details that are only seldom needed be split off into separate detail tables that can be stored on separate physical drives at least?
To paraquote H. L. Mencken: For every problem, there is a solution that’s cheap, fast, easy to implement – and wrong.
This can be the new slogan of our development. :')
I have convinced management to switch to a modern server. In addition we hope refactoring our approach (no random reads, no dedupe processes for a whole table, etc.) will lead us somewhere.
As for how to viably decrease the amount of data in your active set, well, that’s hard to say without knowledge of the data and what you want to do with it. Is it a historical dataset or time series?
Actually now. We are adding a layer of processing products to an already in-production system which handles already multiple millions of products on a daily basis. Since we not only have to process the new/updated products but have to catch up with processing the historical (older) products as well its a massive amount of products. We thought since the order is not important to use a random approach to catch up. But I see now that this is a major bottleneck in our design.
If so, do you need to integrate the entire series back until the dawn of time, or can you narrow the focus to a recent time window and shunt old data off to cold storage?
so no. No narrowing.
Is all the data per sample required at all times, or can details that are only seldom needed be split off into separate detail tables that can be stored on separate physical drives at least?
Also no IMO. since we dont want a product to be processed twice, we want to ensure deduplication - this requires knowledge of all already processed products. Therefore comparing with the whole table everytime.
What? Problems like this usually come down to some missing indexes. Can you view the query plan for your slow queries? See how long they are taking? IDK about SQL Server but usually there is a command called something like ANALYZE, that breaks down a query into the different parts of its execution plan, executes it, and measures how long each part takes. If you see something like “FULL TABLE SCAN” taking a long time, that can usually be fixed with an index.
If this doesn’t make any sense to you, ask if there are any database gurus at your company, or book a few hours with a consultant. If you go the paid consultant route, say you want someone good at SQL Server query optimization.
By the way I think some people in this thread are overestimating the complexity of this type of problem or are maybe unintentionally spreading FUD. I’m not a DB guru but I would say that by now I’m somewhat clueful, and I got that way mostly by reading the SQLlite docs including the implementation manuals over a few evenings. That’s probably a few hundred pages but not 2000 or anything like that.
First question: how many separate tables does your DB have? If less than say 20, you are probably in simple territory.
Also, look at your slowest queries. They likely say SELECT something FROM this JOIN that JOIN otherthing bla bla bla. How many different JOINs are in that query? If just one, you probably need an index; if two or three, it might take a bit of head scratching; and if 4 or more, something is possibly wrong with your schema or how the queries are written and you have to straighten that out.
Basically from having seen this type of thing many times before, there is about a 50% that it can be solved with very little effort, by adding indexes based on studying the slow query executions.
First question: how many separate tables does your DB have? If less than say 20, you are probably in simple territory.
Currently about ~50. But like 30 of them are the result of splitting them into a common column like “country”. In the beginning I assumed this lead to the same as partitioning one large table?
Also, look at your slowest queries
The different queries itself take not long because of the query per se. but due to the limitation of the HDD, SQL reads as much as possible from the disk to go through a table, given that there are now multiple connections all querying multiple tables this leads to a server overload. While I see now the issue with our approach, I hope that migrating the server from SQL server to postgreSQL and to modern hardware + refactoring our approach in general will give us a boost.
They likely say SELECT something FROM this JOIN that JOIN otherthing bla bla bla. How many different JOINs are in that query?
Actually no JOIN. Most “complex” query is INSERT INTO with a WHEN NOT EXIST constraint.
But thank you for your advice. I will incorporate the tips in our new design approach.
Database performance tuning is its own little world and there are lots of complexities and caveats depending on the database system.
With MSSQL, the first thing you should check is your indexes. You should have indexes on commonly queried fields and any foreign keys. It’s the best place to start because indexing alone can often make or break database performance.
What is the execution path? What indexes are being hit? What are the keys? Can you separate things by, for example, category since dupes wouldn’t typically exist there? There are lots of potential things that might tell you more or improve performance, but this is super vague.
Ms sql is trash
Indexes are great but probably don’t get you far if it is already really slow.
Running anything on a Hdd is a joke
You read write and compare continuously? Did you try to split it into smaller chunks?
I’d prefer MS SQL over Oracle SQL any day. And PG SQL over both of them.
- spent time to generate/optomize your indexes.
- faster storage/cpu/ram for your rdbms
- get the data needed by specific services into the service, only get the data from a central place if you have to (spinning up a new instance, another service changes state of data you need, which is a warning sign in itself that your architecture is brittle…)
- faster storage/cpu/ram
- generate indexes
- 2nd level cache shared between services
- establish a faster datastore for often requested data thats used by multiple services (that might be something like redis, or another rdbms on beefier hardware)
- optimize queries
- generate indexes
- faster storage/cpu/ram
first of all many thanks for the bullets. Good to have some guidance on where to start.
2nd level cache shared between services
I have read about this related to how FB does it. In general this means that fetching from the DB and keep it in memory to work with right? So we assume that the cached data is outdated to some extend?
faster storage/cpu/ram faster storage/cpu/ram faster storage/cpu/ram
I was able to convince management to put money into a new server (SSD thank god). So thank you for your emphasizes. We are also migrating to PostgreSQL from SQL server, and refactor the whole approach and design in general.
generate indexes
How would indeces help me when I want to ensure that no duplicate row is added? Is this some sort of internal SQL constraint or what is the difference to compare a certain list of rows with an existing table (lets say column id)?
Indexes and pagination would be good starts
with pagination you mean paginating to split the query into chunks during comparison of a give data set with a whole table?
yes? maybe, depending on what you mean.
Let’s say you’re doing a job and that job will involve reading 1M records or something. Pagination means you grab N number at a time, say 1000, in multiple queries as they’re being done.
Reading your post again to try and get context, it looks like you’re identifying duplicates as part of a job.
I don’t know what you’re using to determine a duplicate, if it’s structural or not, but since you’re running on HDDs, it might be faster to get that information into ram and then do the job in batches and update in batches. This will also allow you to do things like writing to the DB while doing CPU processing.
BTW, your hard disks are going to be your bottleneck unless you’re reaching out over the internet, so your best bet is to move that data onto an NVMe SSD. That’ll blow any other suggestion I have out of the water.
BUT! there are ways to help things out. I don’t know what language you’re working in. I’m a dotnet dev, so I can answer some things from that perspective.
One thing you may want to do, especially if there’s other traffic on this server:
- use WITH (NOLOCK) so that you’re not stopping other reads and write on the tables you’re looking at
- use pagination, either with windowing or LIMIT/SKIP to grab only a certain number of records at a time
Use a HashSet (this can work if you have record types) or some other method of equality that’s property based. Many Dictionary/HashSet types can take some kind of equality comparer.
So, what you can do is asynchronously read from the disk into memory and start some kind of processing job. If this job does also not require the disk, you can do another read while you’re processing. Don’t do a write and a read at the same time since you’re on HDDs.
This might look something like:
offset = 0, limit = 1000 task = readBatchFromDb(offset, limit) result = await task data = new HashSet\<YourType>(new YourTypeEqualityComparer()) // if you only care about the equality and not the data after use, you can just store the hash codes while (!result.IsEmpty) { offset = advance(offset) task = readBatchFromDb(offset, limit) // start a new read batch dataToWork = data.exclusion(result) // or something to not rework any objects data.addRange(result) dataToWrite = doYourThing(dataToWork) // don't write while reading result = await task await writeToDb(dataToWrite) // to not read and write. There's a lost optimization on not doing any cpu work } // Let's say you can set up a read or write queue to keep things busy abstract class IoJob { public sealed class ReadJob(your args) : IoJob { Task\<Data> ReadTask {get;set;} } public sealed class WriteJob(write data) : IoJob { Task WriteTask {get;set;} } } Task\<IoJob> executeJob(IoJob job){ switch job { ReadJob rj => readBatchFromDb(rj.Offset, rj.Limit), // let's say this job assigns the data to the ReadJob and returns it WriteJob wj => writeToDb(wj) // function should return the write job } } Stack\<IoJob> jobs = new (); jobs.Enqueue(new ReadJob(offset, limit)); jobs.Enqueue(new ReadJob(advance(offset), limit)); // get the second job ready to start job = jobs.Dequeue(); do () { // kick off the next job if (jobs.Peek() != null) executeJob(jobs.Peek()); if (result is ReadJob rj) { data = await rj.Task; if (data.IsEmpty) continue; jobs.Enqueue(new ReadJob(next stuff)) dataToWork = data.exclusion(data) data.AddRange(data) dataToWrite = doYourThing(dataToWork) jobs.Enqueue(new WriteJob(dataToWrite)) } else if (result is WriteJob wj) { await writeToDb(wj.Data) } } while ((job = jobs.Dequeue()) != null)BTW, your hard disks are going to be your bottleneck unless you’re reaching out over the internet, so your best bet is to move that data onto an NVMe SSD. That’ll blow any other suggestion I have out of the water.
Yes, we are currently in the process of migrating to PostgreSQL and to a new hardware. Nonetheless the approach we are using is a disaster. So we will refactor our approach as well. Appreciate your input.
I don’t know what language you’re working in.
All processing and SQL related transactions are executed via python. But should not have any influence since the SQL server is the bottleneck.
WITH (NOLOCK)
Yes I have considered this already for the next update. Since our setup can accept dirty reads - but I have not tested/quantified any benefits yet.
Don’t do a write and a read at the same time since you’re on HDDs.
While I understand the underlying issue here, I do not know yet how to control this. Since we have multiple microservices set up which are connected to the DB and either fetch (read), write or delete from different tables. But to my understanding since I am currently not using NOLOCK such occurrences should be handled by SQL no? What I mean is that during a process the object is locked - so no other process can interfere on the SQL object?
Thanks for putting this together I will review it tomorrow again (Y).
That was a bit of a hasty write, so there’s probably some issues with it, but that’s the gist
BTW. nice username.
Lotta smarter people than me have already posted better answers in this thread, but this really stood out to me:
the thing is. my queries are not that complex. they simply go through the whole table to identify any duplicates which are not further processed then, because the processing takes time (which we thought would be the bottleneck). but the time savings to not process duplicates seems now probably less than that it takes to compare batches with the SQL table
Why aren’t you de-duping the table before processing? What’s inserting these duplicates and why are they necessary to the table? If they serve no purpose, find out what’s generating them and stop it, or write a pre-load script to clean it up before your core processing queries access that table. I’d start here - it sounds like what’s really happening is that you’ve got a garbage query dumping dupes into your table and bloating your db.
could you tell me what book it is 👀
Do you remember the part of education where they talked about tradeoffs? How making decision a means x, y, x good things and a, b, c bad things? Because it’s reading strongly like your system design methodology was “this is the path of least resistance so I’m doing that”.
Most code is not complex. Good code is usually very easy to read and understand.
Just because you can read and understand the queries you wrote doesn’t mean they’re efficient or that you’re using good design.
So yes. Stack Overflow is going to tell you to RTFM. Because someone needs to sit down with this mess, determine the pros and cons of the system design, and figure out where to start overhauling.
If you are new to something and want to learn, seek resources and educate yourself with them. Learning takes time, and there are no shortcuts.
A hot DB should not run on HDDs. Slap some nvme storage into that server if you can. If you can’t, consider getting a new server and migrating to it.
SQL server can generate execution plans for you. For your queries, generate those, and see if you’re doing any operations that involve iterating the entire table. You should avoid scanning an entire table with a huge number of rows when possible, at least during requests.
If you want to do some kind of dupe protection, let the DB do it for you. Create an index and a table constraint on the relevant columns. If the data is too complex for that, find a way to do it, like generating and storing hashes, sorting lists/dicts, etc just so that the DB can do the work for you. The DB is better at enforcing constraints than you are (when it can do so).
For read-heavy workflows, consider whether caches or read replicas will benefit you.
And finally back to my first point: read. Learn. There are no shortcuts. You cannot get better at something if you don’t take the time to educate yourself on it.
A hot DB should not run on HDDs. Slap some nvme storage into that server if you can. If you can’t, consider getting a new server and migrating to it.
Did this because of the convincing replies in this thread. Migrating to modern hardware and switch SQL server with PostgreSQL (because its used by the other system we work with already, and there is know-how available in this domain).
You should avoid scanning an entire table with a huge number of rows when possible, at least during requests.
But how can we then ensure that I am not adding/processing products which are already in the “final” table, when I have no knowledge about ALL the products which are in this final table?
Create an index and a table constraint on the relevant columns. … just so that the DB can do the work for you. The DB is better at enforcing constraints than you are (when it can do so).
This is helpful and also what I experienced. In the peak of the period where the server was overloaded the CPU load was pretty much zero - all processing happened related to disk read/write. Which was because we implemented poor query design/architecture.
For read-heavy workflows, consider whether caches or read replicas will benefit you.
May you elaborate what you mean with read replicas? Storage in memory?
And finally back to my first point: read. Learn. There are no shortcuts. You cannot get better at something if you don’t take the time to educate yourself on it.
Yes, I will swallow the pill. but thanks to the replies here I have many starting points on where to start.
RTFM is nice - but starting with page 0 is overwhelming.