# Single node, large database index performance **URL:** \ **Category:** Elasticsearch\ **Created:** [May 24, 2021, 8:25pm UTC](https://discuss.elastic.co/t/single-node-large-database-index-performance/273865 "2021-05-24T20:25:25Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![twbarr](https://avatars.discourse-cdn.com/v4/letter/t/b19c9b/32.png) [@twbarr](https://discuss.elastic.co/u/twbarr)\ **Post date:** [May 24, 2021, 8:25pm UTC](https://discuss.elastic.co/t/single-node-large-database-index-performance/273865/1 "2021-05-24T20:25:25Z")
I have a set of documents that I'm trying to load into a single large index on a single node. About seven million documents, about 400gb of text. The fields are in different files, so I'm just using a script that loads each file, creates a bulk upsert, and submits it to the local DB. As the load proceeds, though, the database seems to slow to a crawl and eventually pass my 10-second bulk update timeouts. I can index just the first half of the docs quickly, or just the second half of the docs quickly, so I know that there's nothing special about some docs in the DB. I've done all the standard tricks like setting the refresh interval to -1, adjusting the number of shards (from 1 to 4 to 10), disabling disk swap on the server, and none seem to have much effect. I'm using a fast machine (8-core Xeon, 32gb of RAM, 2x4T SSDs in RAID 0), but is this just too much data to expect ES to work with given how much RAM I have? Any other thoughts would be appreciated. ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [May 24, 2021, 8:51pm UTC](https://discuss.elastic.co/t/single-node-large-database-index-performance/273865/2 "2021-05-24T20:51:15Z")
What is the structure and size of the documents? Are you using nested mappings? How are you updating the documents? Whic h version of Elasticsearch are you using? ---
**Author:** ![twbarr](https://avatars.discourse-cdn.com/v4/letter/t/b19c9b/32.png) [@twbarr](https://discuss.elastic.co/u/twbarr)\ **Post date:** [May 24, 2021, 9:00pm UTC](https://discuss.elastic.co/t/single-node-large-database-index-performance/273865/3 "2021-05-24T21:00:51Z")
The documents are mostly a couple of hundred kilobytes, though there are some that are up to 20MB. They look kinda like this: ```auto {'field':'big block of text', 'other-field':'bigger block of text', 'list-o-data': [{'num':1, 'text:'medium block of text'}, {'num':2, 'text':'medium block of text'}]} ``` I'm running 7.12.1 on Ubuntu. Each of the top-level fields (including the entire list) is added to the docs in the database using bulk updates with doc\_as\_upsert to preserve the other fields. I'm letting the low-level Python library's streaming\_bulk handler set up the requests into bulk calls. All in one thread. ---
**Author:** ![elasticforme](https://avatars.discourse-cdn.com/v4/letter/e/f05b48/32.png) [@elasticforme](https://discuss.elastic.co/u/elasticforme)\ **Post date:** [May 24, 2021, 9:02pm UTC](https://discuss.elastic.co/t/single-node-large-database-index-performance/273865/4 "2021-05-24T21:02:14Z")
7 million record is nothing. like Christian said it might be some other issue that you have to figure out and this forum can def help you. I upload 6million record in 45 min pulling from database. using python bulk insert. insert 3000 record in bulk. ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [May 24, 2021, 9:04pm UTC](https://discuss.elastic.co/t/single-node-large-database-index-performance/273865/5 "2021-05-24T21:04:12Z")
How many items can the list have? Is this mapped as nested? Are you upserting these one by one? Each time you update a document the whole document is reindexed. If you perform a large number of updates of each document you will be reindexing the large content many times over, which may be slow. Have you considered modelling this using parent-child instead of nested documents (if that is used)? ---
**Author:** ![twbarr](https://avatars.discourse-cdn.com/v4/letter/t/b19c9b/32.png) [@twbarr](https://discuss.elastic.co/u/twbarr)\ **Post date:** [May 24, 2021, 9:17pm UTC](https://discuss.elastic.co/t/single-node-large-database-index-performance/273865/6 "2021-05-24T21:17:40Z")
I'm only performing about four or five updates total on each document. I batch up the entire list and upsert that at once. ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [May 24, 2021, 9:24pm UTC](https://discuss.elastic.co/t/single-node-large-database-index-performance/273865/7 "2021-05-24T21:24:28Z")
Ok, that is better than I feared, but still means all that text in these large documents is analyzed and indexed multiple times, likelyb getting slower as they grow. ---
**Author:** ![twbarr](https://avatars.discourse-cdn.com/v4/letter/t/b19c9b/32.png) [@twbarr](https://discuss.elastic.co/u/twbarr)\ **Post date:** [May 24, 2021, 9:36pm UTC](https://discuss.elastic.co/t/single-node-large-database-index-performance/273865/8 "2021-05-24T21:36:35Z")
If I use parent-child documents, can I still perform queries like (first-field:foo AND second-field:bar) if they're in separate documents? ---
**Author:** ![twbarr](https://avatars.discourse-cdn.com/v4/letter/t/b19c9b/32.png) [@twbarr](https://discuss.elastic.co/u/twbarr)\ **Post date:** [May 26, 2021, 12:34am UTC](https://discuss.elastic.co/t/single-node-large-database-index-performance/273865/9 "2021-05-26T00:34:12Z")
I'm going to try to do all the fields as different docs and see if that fixes the performance issue before worrying about how to structure the joins. I'll let ya know! ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [June 23, 2021, 12:35am UTC](https://discuss.elastic.co/t/single-node-large-database-index-performance/273865/10 "2021-06-23T00:35:04Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.