from PoisonedPrisonPanda@discuss.tchncs.de to programming@programming.dev on 02 May 22:39
https://discuss.tchncs.de/post/35558678
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.
threaded - newest
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:
yes. the problem is, we are fetching products from an API. and since we thought processing power will be a limiting factor, we thought that sorting out duplicates would reduce load.
but since the different microservices which process the data are taking different times we are using the sql tables as a pool. this should help upcscaling by using multiple microservices.
cloud services are yet not a solution as we are still in development.
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.
It doesnât matter. Your database is very complex.
You search 10 million records on every request and you wonder why itâs slow?
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.
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.
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.
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.
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).
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.
Well. For now the system is not yet running on the new hardware.
It is now a pondering process of whether migrating everything as it is to the new hardware and then optimize/refactor.
Or refactor before (or at least develop a plan) and then improve during migrationâŚ
Nice to hear. Thanks. I will share updates.
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?
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.
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.
so no. No narrowing.
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.
Sorry for taking so long to get back to you on this, but Iâm not always on Lemmy. Thereâs always more code to be written - you know how it is, Iâm sure.
Given the constraints you outline, one other avenue of attack could be to consider the time-sensitivity of product updates and the relative priority thereof. If itâs acceptable for updates to products to lag somewhat, you can at least perform them at a lower rate over longer time, thus reducing hardware load at any given time. If the periodic updates are make to the same per-product values, you could even potentially get smart and replace queued updates not yet performed, if theyâre superseded by a subsequent change before theyâre actually committed thus further reducing load.
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
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.
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.
Indexing is the most answered step. But for foreign key, to my understanding, I apologize is this is maybe wrong, would lead to split the data into separate tables all related by this key right? What would be the difference in splitting the columns of a table into multiple tables - all related by an mutual column, lets say âidâ?
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% chance that it can be solved with very little effort, by adding indexes based on studying the slow query executions.
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?
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.
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.
You really have to see what the db is doing to understand where the bottlenecks are, i.e. find the query plans. Itâs ok if itâs just single selects. Look for stuff like table scans that shouldnât happen. How many queries per second are there? Remember that SSDâs have been a common thing for maybe 10 years. Before that it was HDDâs everywhere, and people still ran systems with very high throughput. They had much less ram then than now too.
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.
thank you, I convinced management to migrate to a modern hardware, and we switch to PostgreSQL together with refactoring our design and approach.
Locks are handled by SQL. but yes, multiple tables are read, written and the final table compared with multiple requests/transactions (connections?) simultaneously. Split into smaller chunks would nonetheless mean that the query would loop through the whole table - in chunks? how would this help with simultaneous transactions?
could you tell me what book it is đ
first of all many thanks for the bullets. Good to have some guidance on where to start.
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?
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.
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)?
correct, introducing caching can result in returning outdated data for awhile, which is usually not a huge deal. those caches can get tricky, but they should take pressure from your db, if youâre scenario is read heavy, which is often the case. Research existing caching solutions before running ahead and implementing something from scratch, especially if you need a cache distirbuted between multiple instances of your service. In the Java world that would be something like Infinispan, but your ecosystem might over better integration with other solutions.
having management on board is great and the new hardware should help a lot, migrating to another RDBMS sounds scary, but probably worth it if your organisation has more expertise with it.
they wonât help you with your duplicates, they will help speed up your reads but could slow down writes. building a good index is not trivial, but nothing is when it comes to performance tuning a database, itâs tradeoff after tradeoff. The best way to handle identical rows of data is to not write them usually, but i donât know your system nor its history, maybe there is or was a good reason for its current state.
Lotta smarter people than me have already posted better answers in this thread, but this really stood out to me:
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.
I need to dedupe the to-be-processed data with the data thats already processed in the âfinalâ table. We are working with hundreds of millions of products therefore we thought about âsimplyâ using random batches from the data to be processed. But thanks to the many replies Ive learned already that our approach was in the beginning already wrong.
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 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:
That was a bit of a hasty write, so thereâs probably some issues with it, but thatâs the gist
BTW. nice username.
Thanks haha
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.
All processing and SQL related transactions are executed via python. But should not have any influence since the SQL server is the bottleneck.
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.
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).
Thanks for giving it a good read through! If youâre getting on nvme ssds, you may find some of your problems just go away. The difference could be insane.
I was reading something recently about databases or disk layouts that were meant for business applications vs ones meant for reporting and one difference was that on disk they were either laid out by row vs by column.
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.
yes thats me. But thanks to the numerous replies to this thread, I have no a clearer picture about culprits and steps where to start with.
The tradeoffs you mentioned are exactly why we are in this mess. In the beginning with no knowledge we thought that certain measures would help us. but it turned out that those poor decisions led to the wrong direction.
Thank you for reply.
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.
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).
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?
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.
May you elaborate what you mean with read replicas? Storage in memory?
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.
Without knowledge about your schema, I donât know enough to answer this. However, the database doesnât need to scan all rows in a table to check if a value exists if you can build an index on the relevant columns. If your products have some unique ID (or tuple of columns), then you can usually build an index on those values, which means the DB builds what is basically a lookup table for those indexed columns.
Without going into too much detail, you can think of an index as a way for a DB to make a âcontainsâ (or âretrieveâ) operation drop from O(n) (check all rows) to some much faster speed like O(log n) for example. The tradeoff is that you need more space for the index now.
This comes with an added benefit that uniqueness constraints can be easily enforced on indexed columns if needed. And yes, your PK is indexed by default.
Read more about index in Postgresâs docs. It actually has pretty readable documentation from my experience. Or read a book on indexes, or a video, etc. The concept is universal.
This highly depends on your needs. Iâll link PGâs docs on replication though.
If youâre migrating right now, I wouldnât think about this too much. Replicas basically are duplicates of your database hosted on different servers (ideally in different warehouses, or even different regions if possible). Replicas work together to stay in sync, but depending on the kind of replica and the kind of query, any replica may be able to handle an incoming query (rather than a single central database).
If all you need are backups though, then replicas could be overkill. Either way, you definitely donât want prod data all stored in a single machine, usually. I would talk to your management about backup requirements and potentially availability/uptime requirements.
âThey simply go through the whole tableâ⌠thatâs the problem. A full table scan should be avoided at all costs.
Learn: how to run and read an explain plan, indexes, keys, constraints, and query optimization (broadly you want to locate individual records as quickly as possible by using the most selective criteria).
You also need to learn basic schema design and to familiarize yourself with normalization.
Avoid processing huge result sets in your application. The database is good at answering questions about data it contains. It isnât just a big bucket to throw data into to retrieve later.
What can be more selective than "if ID = âXXXâ? Yet the whole table still has to be reviewed until XXX is found?
based on a quick review of normalization, I doubt that this helps me - as we are not experiencing such links in the data. For us we âsimplyâ have many products with certain parameters (title, description, etc.) and based on those we process the product and store the product with additional output in a table. However to not process products which were already processed, we want to dismiss any product which is in the processing pipeline which is already stored in the âfinalâ table.
thats probably the biggest enlightment I have got since we started working with a database.
Anyway I appreciate your input. so thank you for this.
If you are searching by a primary key or other indexed id you should be fine. Here are a couple of articles to check out:
www.atlassian.com/data/âŚ/how-does-indexing-work
red-gate.com/âŚ/postgresql-indexes-what-they-are-aâŚ
The TLDR is a where clause that hits an index doesnât have to go through all the rows in the table.
When detecting duplicates gets expensive, the secret is to process them anyway, but in a way that de-duplicates the result of processing them.
Usually, that means writing the next processing step into a (new) table whose primary key contains every detail that could make a record a duplicate.
Then, as all the records are processed, just let it overwrite that same record with each duplicate.
The resulting table is a list of keys containing no duplicates.
(Tip: This can be a good process to run overnight.)
(Tip: be sure the job also marks each original record as processed/deduped, so the overnight job only ever has to look at new unprocessed/un-deduped records.)
Then, we drive all future processing steps from that new de-duplicated table, joining back to only whichever of the duplicate records was processed last for the other record details. (Since theyâre duplicates anyway, we donât care which one wins, as long as only one does.)
This tends to result in a first single pass through the full data to process to create the de-duplicated list, and then a second pass through the de-duplicated list for all remaining steps. So roughly
2n
processing time.(But the first
n
can be a long running background job, and the secondn
can be optimized by indexes supporting the needs of each future processing step.)