Le blog de pingou - Tag - mongoDB - CommentsLe blog de pingou, ses actualités sur Fedora, ses RPMs, ses tests, son Linux... :-)
Pingou's weblog, his fedora's news, his RPMs, his tests, his Linux... :-)2022-02-17T10:46:15+01:00pingouurn:md5:66db5ce1ed1a80cb2f424695b4bb7780DotclearPostgreSQL vs MongoDB - pingouurn:md5:4cbe2a2c8735cd6d7b21dbfa95be2a642012-05-29T18:22:03+02:002012-05-29T17:22:03+02:00pingou<p>@Richard,</p>
<p>That's kinda of the idea, I have a basket I want to put fruits in it and I wonder which is/are the most appropriate.</p>PostgreSQL vs MongoDB - Richard Buckerurn:md5:2d550ae49495f8b984f0cb0b132868422012-05-25T16:38:11+02:002012-05-29T17:15:52+02:00Richard Bucker<p>Your conclusion that the performance delta is negligible is totally wrong. Clearly on a single transaction basis it might appear that way but when you throw tens of thousands of transactions at the database(s) it will turn into something quite significant. Also, you are really comparing a CAP implementation against an ACID implementation. They solve different problems and therefore you have apples and oranges.</p>PostgreSQL vs MongoDB - pingouurn:md5:35164dcd0130bec87a886ab5fce50f7f2012-05-25T11:47:44+02:002012-05-25T10:47:44+02:00pingou<p>@ Carsten,</p>
<p>Thanks a lot for all this work, I am looking forward to run it here but that already looks pretty awesome :)</p>PostgreSQL vs MongoDB - csengerurn:md5:bd42405707bc2fa0eba4cceb015a91c82012-05-25T11:19:49+02:002012-05-25T10:19:49+02:00csenger<p>I've experimented with the data using a postres fulltext index to do the search_content() query.<br />
You can find the changes on github: <a href="https://github.com/csenger/kittybenchmark" title="https://github.com/csenger/kittybenchmark" rel="ugc nofollow">https://github.com/csenger/kittyben...</a> (the sensitive visualizations are broken in the fork).</p>
<p>It takes ~ 0.08 seconds to get the count for 'rawhid*' (~17000 rows). 1 Second to get the plain data with engine.execute() (without using the orm) and ~1.5 seconds to get full objects. The index on 'content' is 165 MB big. As said it can only do postfix wildcard searches. Fetching 30 emails from somewhere the middle of the sorted result set also takes ~ 0.08 seconds.</p>
<p>Beside the pure speed improvement it will also scale much better than table scans cause it needs to load less data into memory.</p>
<p>There is one pitfalls in doing the search_(content|subject)* with *big* result sets in postgres. Postgres might use an index but needs to store the result set in memory to process it further. Otherwise it stores information in which parts of the table results where found, and scans these parts later for hits. The setting to specify the max. amount of memory is `work_mem` and defaults to 1MB. For the full text index searches the result need's to fit into `work_mem` or it will suffer badly. I<br />
<a href="http://archives.postgresql.org/pgsql-performance/2007-05/msg00369.php" title="http://archives.postgresql.org/pgsql-performance/2007-05/msg00369.php" rel="ugc nofollow">http://archives.postgresql.org/pgsq...</a> is a good explanation of the consequences of `work_mem`.</p>PostgreSQL vs MongoDB - Vurn:md5:90ae14b2bd80094c7be0e20154ac70c92012-05-22T19:29:08+02:002012-05-22T18:30:09+02:00V<p>Right, because youre tests are read only... eh, silly me. ;)</p>PostgreSQL vs MongoDB - pingouurn:md5:07c9f71c3cb46613f2673490ba9acc662012-05-22T08:49:04+02:002012-05-22T07:49:04+02:00pingou<p>@V,<br />
Thanks for the advise, I just tested it but the difference between MonoDB and PostgreSQL remains about the same.</p>PostgreSQL vs MongoDB - Vurn:md5:b410073eafae0ff728f6d762cae771762012-05-22T02:16:57+02:002012-05-22T01:16:57+02:00V<p>If you are considering MongoDB then you probably don't care about ACID compliance. Then turn off fsync in PostgreSQL config and test again, you'll be surprised.</p>
<p>Also SQLA does not support expression indexes, you need to create one "manually" via DDL, but attach that to table post-create event in your app and it remains defined by your SQLA layer.</p>PostgreSQL vs MongoDB - aminurn:md5:50ea4996647b7c25f3c19da248eb53d72012-05-21T05:35:42+02:002012-05-21T04:35:42+02:00amin<p>you are comparing "2 technologies". Try comparing Postgresql nosql hstore vs Mongodb. Should be interesting.</p>PostgreSQL vs MongoDB - fdrurn:md5:9dd4b7e978b053b529581faf9e4a889e2012-05-21T02:34:33+02:002012-05-21T01:34:33+02:00fdr<p>@pingou</p>
<p>with regard to the kernel parameters: you definitely want to tune those. All they do is allow more SysV shared memory to be allocated. Oracle has a similar SysV-backed system, and nearly identical settings required. I think there is a high likelihood that these kernel tweaks can and will be made optional in the 9.3 (which is the next-next release). The gory details are that POSIX shared memory lacks a safety feature that SysV shared memory has and mmap is not portable to all systems, if you are wondering as to why the difficulty.</p>
<p>However, as I said, these shared buffer settings may not matter because your data set is very tiny. They probably would boost it by some percentage because it avoids copying out of the kernel buffers and there is some reduced lock contention, but you don't have very many processors and it's not clear to me how many postgres clients you are running with.</p>
<p>I'd like to express a lot of thanks for doing the work to make this benchmark possible, including the code so it can be iterated upon.</p>PostgreSQL vs MongoDB - pingouurn:md5:f70e3768115c66cbcbfac038e368de882012-05-20T21:05:24+02:002012-05-20T20:06:20+02:00pingou<p>@damm,</p>
<p>I believe your questions have already been answered in the previous comments.</p>
<p>And I never said it would be the most fair test possible, I wanted to test two systems for my need using my hardware so I did and these are the results.</p>PostgreSQL vs MongoDB - dammurn:md5:492e1fd13e2d2e52bab5d5484b5af1542012-05-20T20:41:41+02:002012-05-20T19:41:41+02:00damm<p>Looking over this, I find many critical flaws in this post.</p>
<p>1. Can you post your configuration files? Looking at this I don't see anything to indicate you have configured PostgreSQL in a way where it can actually perform?</p>
<p>2. Your also aware how the MVC model differs from MongoDB's model and your results may be worth very little?</p>
<p>3. I would suggest that your results are flawed due to 1) and that you should update to 9.1 and have a better configured server. (e.g. allocating at least 70% or more memory to effective cache)</p>PostgreSQL vs MongoDB - pingouurn:md5:e819b02cfb5daa4412bd3cd6830aaf162012-05-20T19:21:02+02:002012-05-20T18:21:02+02:00pingou<p>@ Arthur,</p>
<p>- The dataset is negligible for such a machine that's true but I did not tune the database systems to adjust it to the machine.</p>
<p>- For the version I agree and it has been mentioned before but they are the version present in the repos thus the version run on the servers (unless specific work/configuration).</p>
<p>I will try to replicate this test on a Fedora 17 with "only" 8Go of ram to see how it goes then.</p>PostgreSQL vs MongoDB - Arthururn:md5:23f4f9a7d619fa66b07cda88ad12eb432012-05-20T19:06:30+02:002012-05-20T18:06:30+02:00Arthur<p>I know this a specific test, but some things to consider:</p>
<p>The test dataset is negligible for such a powerful machine.</p>
<p>Test ran with a recent MongDB and an outdated Postgres.</p>
<p>MongoDB uses all available memory, Postgress need to be manually tunned in the config files.</p>PostgreSQL vs MongoDB - pingouurn:md5:fc196caa17ca902c8b9e28058bade6eb2012-05-20T18:19:58+02:002012-05-20T17:19:58+02:00pingou<p>@Alex,</p>
<p>I do not know precisely. The test might be specific but it is basically using the packages on which we would like to see the application running.</p>
<p>Everything is there to duplicate the work on another platform, let me know if you need help to do so ;-)</p>
<p>@fdr</p>
<p>I can make the shared_buffers 4G but iiuc the documentation mentions some kernel parameters to twick, not sure I want to do this. Plus is it realistic to dedicate 4G of RAM to PostgreSQL for a mailman server? I am not convinced.</p>
<p>If I re-run the tests on a Fedora machine, I'll post the new results allowing us to have a better view on the performance of the newer versions.</p>
<p>Good point regarding SQLAlchemy but I would prefer to keep having an ORM and I started with the one I know best.</p>PostgreSQL vs MongoDB - fdrurn:md5:cd36104b57616a1b1c52d96af4c6a06d2012-05-20T18:06:35+02:002012-05-20T17:06:35+02:00fdr<p>Usually shared_buffers is about 1/4th of system memory, so many gigabytes, in your case. However, I wonder if you are CPU bound (can you check?) in which case using more specialized data types and indexes would help more. You might also want to post the plan of the queries you are issuing (via EXPLAIN) if you want to be sure they are using the index.</p>
<p>I think your work would be interesting to the pgsql-performance mailing lists, although the version is pretty old.</p>
<p>Another thing to worry about (if you are doing this) is SQL compilation time from SQLAlchemy. I like SQLAlchemy, but my recollection is that it is really quite slow at query formation; a cost of its generality.</p>PostgreSQL vs MongoDB - Alexurn:md5:fb27c87db883a2feea1e3465bf75a15b2012-05-20T17:56:31+02:002012-05-20T16:56:31+02:00Alex<p>Is the RHEL default config at all different from the Postgres default config?</p>
<p>If so, this seems like an awfully specific comparison: a recent (but not latest) patch version of a 3-year-old release, using the config changes made by one Linux distro.</p>PostgreSQL vs MongoDB - pingouurn:md5:f5977ac6cdf32e004a7e35f67b42c6ab2012-05-20T17:31:36+02:002012-05-20T16:31:36+02:00pingou<p>@Derek Arnold,</p>
<p>With 64MB I don't see any changes in the results of the searches, the order remains the same and the difference between MongoDB and PostgreSQL also.</p>PostgreSQL vs MongoDB - pingouurn:md5:9f3fc912f970c0639c656bf722551c442012-05-20T17:12:13+02:002012-05-20T16:12:13+02:00pingou<p>@giulivo,</p>
<p>Ok I see what you mean now, thanks :)</p>PostgreSQL vs MongoDB - giulivourn:md5:724ff8d4d3775aa96744782c8c6b2eff2012-05-20T17:06:52+02:002012-05-20T16:06:52+02:00giulivo<p>I don't query mongo from the html. Here are the two corrected links pointing to the db util. Javascript is executed inside mongo</p>
<p>1. <a href="https://github.com/giulivo/opinoid/blob/master/webapp/db.py#L83" title="https://github.com/giulivo/opinoid/blob/master/webapp/db.py#L83" rel="ugc nofollow">https://github.com/giulivo/opinoid/...</a><br />
2. <a href="https://github.com/giulivo/opinoid/blob/master/webapp/db.py#L25" title="https://github.com/giulivo/opinoid/blob/master/webapp/db.py#L25" rel="ugc nofollow">https://github.com/giulivo/opinoid/...</a></p>PostgreSQL vs MongoDB - pingouurn:md5:6050bfb0c2c68a6bcc1b3304530bf0982012-05-20T16:51:24+02:002012-05-20T15:51:24+02:00pingou<p>@Derek Arnold,</p>
<p>Apparently my configuration is at:<br />
shared_buffers = 32MB<br />
I have doubled it and restarted, let's see how different the results are :-)</p>