Re: Re: [mongodb-user] Why the performance of insert / update becomes sobad when adding an Index?
Max Schireson
Sorry for the delay busy week. Comments inline.
On May 9, 2012 10:36 PM, "mongodb.yang" <mongod...@gmail.com> wrote:
>
> Max, thanks a lot for your comprehensive reply, very thoroughly, very useful, thanks.
>
> Your second suggestion: "b) add drives if you must index the additional fields with
> random values in them", do mean that add a sharding server to dispatch the insert request? If so, we have another case for that with positive result, that is, add sharding can effectively improve the overall performance. However, we still hope the performance of a single shard can be better with index.
I was suggesting adding more disk drives in your server. The level of I/O performance you are getting looks like about what I would expect from a single midrange hard drive. If you went to say 4 or 6 drives I would expect much better performance (near linear increase) as long as the data set is spread across all the drives. Since you expect very high data volume, you may need many drives anyway.
>
> And, we cannot afford SSD because the data volume would be very high in our project in the expectation.
>
> We are considering changing the source of mongodb, putting the index in real memory, instead of mmap, to avoid being flushed as dirty. We are evaluating the workload for dev and test.
>
If your data set gets very big, your whole index will need to be in memory. If the documents are small as in your test, this would be much more expensive than putting the whole database on ssd.
> Thanks
>
> ________________________________
> yang
>
> From: Max Schireson
> Date: 2012-05-08 14:09
> To: mongodb-user
> Subject: Re: [mongodb-user] Why the performance of insert / update becomes sobad when adding an Index?
> Some comments which may partially explain what you are seeing - others
> may add more.
>
> First, the writes for the documents themselves can often be mostly
> sequential so they will be quite fast. Because the b-tree indexes
> maintain lists ordered by keys and the all the keys you have are
> random, the index inserts will be random IOs. Thus my starting point
> would be to expect roughly linear performance degradation when you
> increase the number of indexes: two indexes roughly half the
> performance of one index; three indexes roughly a third of the
> performance of one index.
>
> It is somewhat surprising to me that the performance of two additional
> indexes is that much slower - I would have expected it to be roughly
> twice what you saw, but still quite a bit slower than with just one
> index. Just to be certain the result is genuine, you might a) rerun
> the tests in another order and b) try longer and shorter runs. If the
> result is consistent let us know.
>
> I am not really an expert on all the details but my understanding is
> that MongoDB periodically fsyncs the dirty pages in RAM to disk. I
> believe this is what is causing the disk IO you are seeing despite
> having enough RAM to hold the whole data set. If you don't
> periodically sync RAM with disk, you wind up needing unbounded amounts
> of journal space and unbounded time to do a journal recovery in case
> of a crash.
>
> Because the indexes are structured in key order, it is very hard to
> get away from random IO for your inserts. Almost any b-tree or similar
> index will exhibit this behavior. There has been some academic
> research on alternative data structures but I am not aware of any
> implementations in mainstream products. If your keys were sequences
> which increased or increasing timestamps, the index writes would be
> mostly sequential and I expect your results would look very different.
> You might try for example keeping the shard key as it is but making
> one of the fields an increasing value and adding an index on that. I
> would expect much less performance impact when that index is added.
>
> 665kb/second with a 4kb block size is just over 160 IOPS. This is
> typically what you can get out of a single 7200 RPM drive, not sure
> what hardware you have? See for example
> http://www.8088.net/blog/index.php/2011/01/24/iops-comparison-solid-state-drive-ssd-15000-7200-5900-5600-rpm/?lang=en
> for some reference points.
>
> I think you'd see a huge improvement if you went to SSD. Adding
> additional drives or going to faster spinning disk would help too
> (though 15k RPM drives are quite expensive and SSD probably gives
> better bang for your buck.
>
> iostat -xm2 results during the runs might give some additional details
> but I think your best course of action will still likely be to a) move
> to SSD b) add drives if you must index the additional fields with
> random values in them.
>
> I hope this is helpful,
>
> -- Max
>
>
> On Mon, May 7, 2012 at 7:15 PM, yuanjie <yyj...@gmail.com> wrote:
> > We are trying to use mongodb as a DB solution for a project with very
> > intensive / high volume query / insert /update operations.
> >
> > Now the problem is, once we add one index for in a collection, the
> > performance of insert / update downs a lot (only 1/3 ?1/4 of before)
> >
> > Out test environment is on a server
> > CPU: Intel(R) Xeon(R) CPU E5606 @ 2.13GHz
> > Num of core: 8
> > Cache: 8M
> > Memory: 32G
> > DiskIO: Sequential Write (time dd): 81MB/s; Timing buffered disk reads
> > (hdparm): 93MB/s
> >
> > MongoDB configuration:
> > 1. ?燭here is one mongos, one config server and one shard server
> > which run on the test server
> > 2. ?燭here 4 fields in collection in the test: id (shard key, 40
> > bytes string); fieldId (8 bytes long); fieldStr1 (40 bytes string);
> > fieldStr2 (40 bytes string)
> > 3. ?燭here are 6M records in collection before test
> > 4. ?燫un 6 clients, every of them spans 20 threads, to insert 1.2M
> > records in same time: id is number (6,000,000 ?7,200,000) which is
> > hashed and visualized to be 40 bytes string; fieldId is random value;
> > fieldStr1, fieldStr2 are random string with fixed length, 40
> > 5. ?燭he below table is test result for in the combination of Indexes
> > Test a) ?燦o extra index except sharding key (id), insert 1.2M record
> > in 141 seconds, in average, 8496.1 records / s
> > Test b) ?爀nsureIndex on fieldId: 4156 records / s
> > Test c) ?爀nsureIndex on fieldStr1: 3661 records / s
> > Test d) ?爋ne compound index (ensureIndex on 揻ieldId?1, 揻ieldStr1?
> > 1): 3675 records / s
> > Test e) ?爀nsure 2 indexes on fieldId, fieldStr1: 1334 records / s
> >
> > For above table, we can see that
> > 1. ?燱ith index, the performance of insert downs a lot, when there
> > are 2 extra indexes, the performance is only 1/6 of that without extra
> > index
> > 2. ?爄ndex on 40 bytes string has more severe impact to the
> > performance than 8 bytes Integer
> > 3. ?燙ompound index has little impact to the performance of insert
> > than a single key index
> > 4. ?燤ore indexes lead to poorer performance of insert
> >
> > I checked the status of server during test of 1 string index and 2
> > indexes (the performance of insert has become very bad). I found that
> > %utils of output of iostat is fixed in 100 firmly while mongod, mongos
> > has low CPU utilization (below 30 percent) and memory utilization
> > (below 10 percent), more wired, there are 16 GB memory is available,
> > shown by output of 揻ree?
> >
> > My questions are
> > 1. ?燱hy add a single index will cause 100 percent usage of diskIO
> > while our test server has 16GB memory available?
> > 2. ?燙an I tell mongodb by a parameter that, please held the index in
> > memory without / or seldom flush onto disk?
> > 3. ?營 notice that when %utils of output of iostat stuck in 100, the
> > wMB/s is only about 3. I also found that the random write capability
> > of diskIO of my server is quit poor (only at 655KB/s when record size
> > at 4K, iozone). Is there any parameter in mongodb which can change
> > random write to be sequential write?
> >
> > --
> > You received this message because you are subscribed to the Google Groups "mongodb-user" group.
> > To post to this group, send email to mongod...@googlegroups.com.
> > To unsubscribe from this group, send email to mongodb-user...@googlegroups.com.
> > For more options, visit this group at http://groups.google.com/group/mongodb-user?hl=en.
> >
>
> --
> You received this message because you are subscribed to the Google Groups "mongodb-user" group.
> To post to this group, send email to mongod...@googlegroups.com.
> To unsubscribe from this group, send email to mongodb-user...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/mongodb-user?hl=en.
>
>
> --
> You received this message because you are subscribed to the Google Groups "mongodb-user" group.
> To post to this group, send email to mongod...@googlegroups.com.
> To unsubscribe from this group, send email to mongodb-user...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/mongodb-user?hl=en.