123
 123

Tip: 看不到本站引用 Flickr 的图片? 下载 Firefox Access Flickr 插件 | AD: 订阅 DBA notes --

2017-02-25 Sat

21:25 Sponsored: 64% off Code Black Drone with HD Camera [del.icio.us] (151 Bytes) » 车东[Blog^2]
Our #1 Best-Selling Drone--Meet the Dark Night of the Sky!
03:46 DATASCAPE PODCAST EPISODE 4 – Discussing Oracle Licensing in the Public Cloud (2944 Bytes) » Official Pythian Blog

In this episode, I am joined by my colleague and Oracle expert, Bjoern Rost, who will help guide us through Oracle licensing, what’s changed, and how it’s going to affect us. We’ll also discuss licensing on prem and in the public cloud.

Bjoern has been working in IT for 15 years and started his career working for a company that helped ISV’s publish their software and deliver their software to end-clients. This process involved setting up the whole stack and architecting it from data center floor all the way to application service.

The Oracle Database has always fascinated Bjoern because of its complexity and the various opportunities to influence the way it works. Bjoern’s passion for the Oracle Database has driven him to specialized in it. He currently works as a Principal Consultant at Pythian where he helps his colleagues and their clients achieve the very best they can from the Oracle Database.

Key Points From This Episode:

  • Hear about Bjoern’s background in IT and how he got started working with the Oracle Database.
  • Find out how Oracle licensing works, and why it presents such complexities.
  • Understand the purpose of the Oracle Core Factor Table and how it helps to determine on-premises licensing.
  • Discover how, most often, VCPU’s are translated into CPU’s and which rules are applied.
  • Learn about the motivation behind the changes made in the translation rules, and how it impacts licensing.
  • Hear why the move to cloud will affect enterprise and the drivers behind it.
  • Understand the reason behind pricing volatility in the cloud and its overall effects.
  • Bjoern shares his advice to customers when setting up both on-premise and cloud-based systems; evaluate what you need, consolidate, and make an investment.
  • Find out why moving off of Oracle may be a viable option, and understand why evaluating migration costs is a key factor.
  • Hear which project Bjoern is most proud of and the book that has most impacted his career.
  • Discover whether Bjoern prefers a standing or sitting desk, laptop or desktop, Mac or PC, iPhone or Android.
  • Bjoern shares his most used app and how it helps him on a daily basis.
  • Links Mentioned in Today’s Episode:
    Oracle Database
    The Phoenix Project
    Bjoern on Twitter (@brost)
    Pythian’s Blog

    2017-02-24 Fri

    10:36 Reading Execution Plans for Parallel DML (9828 Bytes) » Official Pythian Blog

    Parallel execution in Oracle seems to be a topic of myths, mysteries and misunderstandings. In this post I hope to clear up at least one of them by diving into a discussion about parallel DML. Actually, I want to show you two things that can go wrong with it.

    My demo setup and case are pretty simple, I just create two copies of dba_objects:

    SQL> create table a as select * from dba_objects;
    
    Table created.
    
    SQL> create table b as select * from dba_objects;
    
    Table created.

    Now let’s delete from one of the tables with a subselect to the other one, and let’s make it parallel. When, if and how it makes sense to actually use parallel DML for a speedup is not the point of this post. In fact, this is a poor example as the overhead of extra communication and spinning up the processes just made the actual delete run slower.

    SQL> delete /*+ PARALLEL(a 42) */ from a where object_id in (select object_id from b);
    
    91277 rows deleted.
    
    SQL> rollback;

    And at first glance the execution plan for it seems to confirm that the operation was performed in parallel:

    PLAN_TABLE_OUTPUT
    -------------------------------------
    SQL_ID	1q2m84vq594pj, child number 0
    -------------------------------------
    delete /*+ PARALLEL(a 42) */ from a where object_id in (select
    object_id from b)
    
    Plan hash value: 1475376193
    
    --------------------------------------------------------------------------------------------------------------------
    | Id  | Operation	 	   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
    --------------------------------------------------------------------------------------------------------------------
    |   0 | DELETE STATEMENT	   |	      |       |       |   855 (100)|	      |        |      | 	   |
    |   1 |  DELETE 		   | A	      |       |       | 	   |	      |        |      | 	   |
    |   2 |   PX COORDINATOR	   |	      |       |       | 	   |	      |        |      | 	   |
    |   3 |    PX SEND QC (RANDOM)	   | :TQ10002 | 91277 |   891K|   855	(1)| 00:00:01 |  Q1,02 | P->S | QC (RAND)  |
    |*  4 |     HASH JOIN SEMI BUFFERED|	      | 91277 |   891K|   855	(1)| 00:00:01 |  Q1,02 | PCWP | 	   |
    |   5 |      PX RECEIVE 	   |	      | 91277 |   445K|   427	(1)| 00:00:01 |  Q1,02 | PCWP | 	   |
    |   6 |       PX SEND HASH	   | :TQ10000 | 91277 |   445K|   427	(1)| 00:00:01 |  Q1,00 | P->P | HASH	  |
    |   7 |        PX BLOCK ITERATOR   |	      | 91277 |   445K|   427	(1)| 00:00:01 |  Q1,00 | PCWC | 	   |
    |*  8 | 	TABLE ACCESS FULL  | A	      | 91277 |   445K|   427	(1)| 00:00:01 |  Q1,00 | PCWP | 	   |
    |   9 |      PX RECEIVE 	   |	      | 91278 |   445K|   427	(1)| 00:00:01 |  Q1,02 | PCWP | 	   |
    |  10 |       PX SEND HASH	   | :TQ10001 | 91278 |   445K|   427	(1)| 00:00:01 |  Q1,01 | S->P | HASH	  |
    |  11 |        PX SELECTOR	   |	      |       |       | 	   |	      |  Q1,01 | SCWC | 	   |
    |  12 | 	TABLE ACCESS FULL  | B	      | 91278 |   445K|   427	(1)| 00:00:01 |  Q1,01 | SCWP | 	   |
    --------------------------------------------------------------------------------------------------------------------

    But a closer look reveals that the actual delete that we wanted to run in parallel is performed only after (above and to the left in the execution plan) the parallel part coordinator is done with the query. Also, because we only specified parallelism for Table A, the scan of Table B is being performed sequentially. The results are then distributed to parallel workers, and hash joined against the parallel query of A. Those results are buffered and returned to the coordinator which then serially performs the actual deletes. Sounds silly? It is. And the second issue is that even though the hint gets ignored for the DELETE part, it is still applied to the query and join, forcing a half-parallel operation.

    Enable parallel DML mode

    The issue to be aware of with parallel DML in Oracle is that it is disabled by default and you have to explicitly enable it in the session before running parallel DML operations.

    And at least DBMS_XPLAN in 12c will also warn you about not using parallel DML in it’s output:

    Note
    -----
       - Degree of Parallelism is 42 because of hint
       - PDML is disabled in current session

    So let’s try the same thing again, but enable parallel DML this time:

    SQL> alter session enable parallel dml;
    
    Session altered.
    
    SQL> delete /*+ PARALLEL(a 42) */ from a where object_id in (select object_id from b);
    
    91277 rows deleted.
    
    SQL> rollback;
    
    PLAN_TABLE_OUTPUT
    -------------------------------------
    SQL_ID	1q2m84vq594pj, child number 1
    -------------------------------------
    delete /*+ PARALLEL(a 42) */ from a where object_id in (select
    object_id from b)
    
    Plan hash value: 2691386263
    
    --------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation			 | Name     | Rows  | Bytes | Cost (%CPU)| Time     |	 TQ  |IN-OUT| PQ Distrib |
    --------------------------------------------------------------------------------------------------------------------------
    |   0 | DELETE STATEMENT		 |	    |	    |	    |	438 (100)|	    |	    |	    |		 |
    |   1 |  PX COORDINATOR 		 |	    |	    |	    |		 |	    |	    |	    |		 |
    |   2 |   PX SEND QC (RANDOM)		 | :TQ10003 | 91277 |	891K|	438   (1)| 00:00:01 |  Q1,03 | P->S | QC (RAND)  |
    |   3 |    DELETE			 | A	    |	    |	    |		 |	    |  Q1,03 | PCWP |		 |
    |   4 |     PX RECEIVE			 |	    | 91277 |	891K|	438   (1)| 00:00:01 |  Q1,03 | PCWP |		 |
    |   5 |      PX SEND HASH (BLOCK ADDRESS)| :TQ10002 | 91277 |	891K|	438   (1)| 00:00:01 |  Q1,02 | P->P | HASH (BLOCK|
    |*  6 |       HASH JOIN SEMI BUFFERED	 |	    | 91277 |	891K|	438   (1)| 00:00:01 |  Q1,02 | PCWP |		 |
    |   7 |        PX RECEIVE		 |	    | 91277 |	445K|	 11   (0)| 00:00:01 |  Q1,02 | PCWP |		 |
    |   8 | 	PX SEND HASH		 | :TQ10000 | 91277 |	445K|	 11   (0)| 00:00:01 |  Q1,00 | P->P | HASH	 |
    |   9 | 	 PX BLOCK ITERATOR	 |	    | 91277 |	445K|	 11   (0)| 00:00:01 |  Q1,00 | PCWC |		 |
    |* 10 | 	  TABLE ACCESS FULL	 | A	    | 91277 |	445K|	 11   (0)| 00:00:01 |  Q1,00 | PCWP |		 |
    |  11 |        PX RECEIVE		 |	    | 91278 |	445K|	427   (1)| 00:00:01 |  Q1,02 | PCWP |		 |
    |  12 | 	PX SEND HASH		 | :TQ10001 | 91278 |	445K|	427   (1)| 00:00:01 |  Q1,01 | S->P | HASH	 |
    |  13 | 	 PX SELECTOR		 |	    |	    |	    |		 |	    |  Q1,01 | SCWC |		 |
    |  14 | 	  TABLE ACCESS FULL	 | B	    | 91278 |	445K|	427   (1)| 00:00:01 |  Q1,01 | SCWP |		 |
    --------------------------------------------------------------------------------------------------------------------------

    Now we see the DELETE operation below the PX COORDINATOR, which means it gets executed in parallel by the parallel workers. B is still read serially because we only specified table A in the hint. Let me just add this 100% parallel plan for the sake of completeness…

    PLAN_TABLE_OUTPUT
    -------------------------------------
    SQL_ID	02w21a0rgz02m, child number 1
    -------------------------------------
    delete /*+ PARALLEL(42) */ from a where object_id in (select object_id
    from b)
    
    Plan hash value: 149866034
    
    --------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation			 | Name     | Rows  | Bytes | Cost (%CPU)| Time     |	 TQ  |IN-OUT| PQ Distrib |
    --------------------------------------------------------------------------------------------------------------------------
    |   0 | DELETE STATEMENT		 |	    |	    |	    |	 23 (100)|	    |	     |	    |		 |
    |   1 |  PX COORDINATOR 		 |	    |	    |	    |		 |	    |	     |	    |		 |
    |   2 |   PX SEND QC (RANDOM)		 | :TQ10003 | 91277 |	891K|	 23   (0)| 00:00:01 |  Q1,03 | P->S | QC (RAND)  |
    |   3 |    DELETE			 | A	    |	    |	    |		 |	    |  Q1,03 | PCWP |		 |
    |   4 |     PX RECEIVE			 |	    | 91277 |	891K|	 23   (0)| 00:00:01 |  Q1,03 | PCWP |		 |
    |   5 |      PX SEND HASH (BLOCK ADDRESS)| :TQ10002 | 91277 |	891K|	 23   (0)| 00:00:01 |  Q1,02 | P->P | HASH (BLOCK|
    |*  6 |       HASH JOIN SEMI BUFFERED	 |	    | 91277 |	891K|	 23   (0)| 00:00:01 |  Q1,02 | PCWP |		 |
    |   7 |        PX RECEIVE		 |	    | 91277 |	445K|	 11   (0)| 00:00:01 |  Q1,02 | PCWP |		 |
    |   8 | 	PX SEND HASH		 | :TQ10000 | 91277 |	445K|	 11   (0)| 00:00:01 |  Q1,00 | P->P | HASH	 |
    |   9 | 	 PX BLOCK ITERATOR	 |	    | 91277 |	445K|	 11   (0)| 00:00:01 |  Q1,00 | PCWC |		 |
    |* 10 | 	  TABLE ACCESS FULL	 | A	    | 91277 |	445K|	 11   (0)| 00:00:01 |  Q1,00 | PCWP |		 |
    |  11 |        PX RECEIVE		 |	    | 91278 |	445K|	 11   (0)| 00:00:01 |  Q1,02 | PCWP |		 |
    |  12 | 	PX SEND HASH		 | :TQ10001 | 91278 |	445K|	 11   (0)| 00:00:01 |  Q1,01 | P->P | HASH	 |
    |  13 | 	 PX BLOCK ITERATOR	 |	    | 91278 |	445K|	 11   (0)| 00:00:01 |  Q1,01 | PCWC |		 |
    |* 14 | 	  TABLE ACCESS FULL	 | B	    | 91278 |	445K|	 11   (0)| 00:00:01 |  Q1,01 | PCWP |		 |
    --------------------------------------------------------------------------------------------------------------------------

    Conclusion

    Keep in mind that parallel DML has to be enabled at the session level specifically. And when reviewing execution plans with parallel DML, make sure the update, insert or delete operation is below the PX coordinator or else you will serialize again before performing the actual operation.

    09:57 An Introduction to the MongoDB Database Profiler (18311 Bytes) » Official Pythian Blog

    MongoDB’s database profiler lets us collect some performance data about operations happening on a MongoDB instance.

    In this post, I will show you how we can enable the profiler, what kind of information it can store for us, how to query this information, and how using it may impact your database’s performance. The MongoDB version used in these tests was 3.2.4. While not the latest, I found no reference to improvements or fixes to the profiler in more recent releases.

    Finally, I’ll briefly discuss a couple of alternatives to reduce or eliminate the impact, while still gaining some insight into your MongoDB workload.

    I want to thank my colleague, Igor Donchovski, for his valuable input while reviewing this post.

    Enabling the profiler

    The profiler can be enabled on a per-database or per-instance basis, and its work is controlled by the Profiling Level we choose.

    We’ll only show the per-database approach, as using the profiler for an entire instance is described by the manual as something one would do on development or testing environments. I suspect this may be due to the impact the profiler may have on performance, something we’ll discuss later on this post.

    Let’s say I have a metrics database on my MongoDB instance. To enable profiling for it, I need to execute the following command from the mongo shell:

    > use metrics
    switched to db metrics
    > db.setProfilingLevel(1, 50)
    { "was" : 0, "slowms" : 100, "ok" : 1 }
    

    This sets the profiling level for the metrics database to 1, which means ‘log all operations that take more than slowms milliseconds to execute’, and at the same time, sets slowms to 50.

    This will create a system.profile capped collection on the metrics database, and will write into it performance data about operations on the metrics database that exceed 50 milliseconds to run.

    If we want to log all operations instead, we just need to set the profiling level to 2:

    > db.setProfilingLevel(2)
    { "was" : 1, "slowms" : 50, "ok" : 1 }
    

    We can see this in action here:

    > db.metrics01.insert({device_id: 104935, temp: 28.4})
    WriteResult({ "nInserted" : 1 })
    > db.metrics01.insert({device_id: 104935, temp: 28.1})
    WriteResult({ "nInserted" : 1 })
    > db.system.profile.find()
    { "op" : "insert", "ns" : "metrics.metrics01", "query" : { "insert" : "metrics01", "documents" : [ { "_id" : ObjectId("58ac8fbdbac007479c598a16"), "device_id" : 104935, "temp" : 28.4 } ], "ordered" : true }, "ninserted" : 1, "keysInserted" : 1, "numYield" : 0, "locks" : { "Global" : { "acquireCount" : { "r" : NumberLong(1), "w" : NumberLong(1) } }, "Database" : { "acquireCount" : { "w" : NumberLong(1) } }, "Collection" : { "acquireCount" : { "w" : NumberLong(1) } } }, "responseLength" : 29, "protocol" : "op_command", "millis" : 0, "ts" : ISODate("2017-02-21T19:06:37.097Z"), "client" : "127.0.0.1", "appName" : "MongoDB Shell", "allUsers" : [ ], "user" : "" }
    { "op" : "insert", "ns" : "metrics.metrics01", "query" : { "insert" : "metrics01", "documents" : [ { "_id" : ObjectId("58ac8fbfbac007479c598a17"), "device_id" : 104935, "temp" : 28.1 } ], "ordered" : true }, "ninserted" : 1, "keysInserted" : 1, "numYield" : 0, "locks" : { "Global" : { "acquireCount" : { "r" : NumberLong(1), "w" : NumberLong(1) } }, "Database" : { "acquireCount" : { "w" : NumberLong(1) } }, "Collection" : { "acquireCount" : { "w" : NumberLong(1) } } }, "responseLength" : 29, "protocol" : "op_command", "millis" : 0, "ts" : ISODate("2017-02-21T19:06:39.682Z"), "client" : "127.0.0.1", "appName" : "MongoDB Shell", "allUsers" : [ ], "user" : "" }
    

    It is important to mention that MongoDB also writes all operations that take more than slowms milliseconds to run to the mongod.log file, regardless of profiler level. Additionally, if the profiler level is 2, all operations are written both to the system.profile collection and to the log file.

    What the profiler tells us

    To see what kind of information we can get by using the database profiler, let’s query a single entry, and pretty print it while we’re at it:

    > db.system.profile.find().limit(1).pretty()
    {
    	"op" : "insert",
    	"ns" : "metrics.metrics01",
    	"query" : {
    		"insert" : "metrics01",
    		"documents" : [
    			{
    				"_id" : ObjectId("58ac8fbdbac007479c598a16"),
    				"device_id" : 104935,
    				"temp" : 28.4
    			}
    		],
    		"ordered" : true
    	},
    	"ninserted" : 1,
    	"keysInserted" : 1,
    	"numYield" : 0,
    	"locks" : {
    		"Global" : {
    			"acquireCount" : {
    				"r" : NumberLong(1),
    				"w" : NumberLong(1)
    			}
    		},
    		"Database" : {
    			"acquireCount" : {
    				"w" : NumberLong(1)
    			}
    		},
    		"Collection" : {
    			"acquireCount" : {
    				"w" : NumberLong(1)
    			}
    		}
    	},
    	"responseLength" : 29,
    	"protocol" : "op_command",
    	"millis" : 0,
    	"ts" : ISODate("2017-02-21T19:06:37.097Z"),
    	"client" : "127.0.0.1",
    	"appName" : "MongoDB Shell",
    	"allUsers" : [ ],
    	"user" : ""
    }
    

    We can see that each profiler document contains useful information about an operation. The manual provides a description in this page. Here are a couple of things to take into consideration:

    • The ‘query’, ‘command’ and ‘updateobj’ fields will be truncated if the corresponding document exceeds 50kb.
    • the ‘millis’ field is supposed to be the “time in milliseconds from the perspective of mongod from the beginning of the operation to the end of the operation” (quoting the manual), yet I have found that for very fast operations, a 0 will be displayed even if it takes more than 0 milliseconds. So watch out for potential resolution problems. This could be dependent on the target architecture (I tried this on CentOS running on VirtualBox running on macOS, YMMV), but it is something to take into consideration if you intend to rely on this data point for decision-making. I have obtained more accurate results by measuring response time by sniffing the network.

    Impact on performance

    The manual states that the profiler has a minor effect on performance, but in some basic tests (sysbench oltp) I have observed a noticeable impact on throughput when the profiler is enabled and configured to log all operations.

    The following graph shows throughput (in transactions per seconds, in the context of MongoDB, sysbench transactions are just a sequence of operations) over time (in seconds) for varying concurrency levels (client threads) and with profiler level set to 0 (profiler disabled) and 2 (profiler enabled and logging all operations):

    MongoDB Profiler Impact

    The graph shows that, when logging all operations, profiler impact is not minimal. It would be interesting to see how this sustains over time (for example, by leaving the workload running for several hours), to see if anything noteworthy happens when the profiler cap is reached. For this post, it is enough to say that the manual may be overly optimistic on its assessment of the profiler’s impact.

    I used pt-pmp to take some aggregated stack traces during the 32 threads runs, and it seems the negative impact could be due to lock contention on the system.profile collection:

    Here we can see the aggregated stack traces when running with profiler level 0:

    Tue Feb 21 14:05:03 UTC 2017
    4 pthread_cond_timedwait,__wt_cond_wait_signal,__wt_cond_auto_wait_signal,__wt_cond_auto_wait,__wt_evict_thread_run,__wt_thread_run,start_thread(libpthread.so.0),clone(libc.so.6)
    1 sigwait(libpthread.so.0),mongo::::signalProcessingThread,execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
    1 select(libc.so.6),mongo::Listener::initAndListen,execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
    1 pthread_cond_wait,std::condition_variable::wait,mongo::waitForShutdown,mongo::::_initAndListen,main
    1 pthread_cond_wait,std::condition_variable::wait,mongo::DeadlineMonitor::deadlineMonitorThread,execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
    1 pthread_cond_timedwait,__wt_cond_wait_signal,__wt_cond_auto_wait_signal,__wt_cond_auto_wait,__log_wrlsn_server,start_thread(libpthread.so.0),clone(libc.so.6)
    1 pthread_cond_timedwait,__wt_cond_wait_signal,__wt_cond_auto_wait_signal,__log_server,start_thread(libpthread.so.0),clone(libc.so.6)
    1 pthread_cond_timedwait,__wt_cond_wait_signal,__sweep_server,start_thread(libpthread.so.0),clone(libc.so.6)
    1 pthread_cond_timedwait,__wt_cond_wait_signal,__log_file_server,start_thread(libpthread.so.0),clone(libc.so.6)
    1 pthread_cond_timedwait,__wt_cond_wait_signal,__ckpt_server,start_thread(libpthread.so.0),clone(libc.so.6)
    1 pthread_cond_timedwait,std::thread::_Impl::_M_run,execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
    1 pthread_cond_timedwait,mongo::RangeDeleter::doWork,execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
    1 pthread_cond_timedwait,mongo::::PeriodicTaskRunner::run,mongo::BackgroundJob::jobBody,execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
    1 pthread_cond_timedwait,mongo::FTDCController::doLoop,execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
    1 nanosleep(libpthread.so.0),mongo::sleepsecs,mongo::TTLMonitor::run,mongo::BackgroundJob::jobBody,execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
    1 nanosleep(libpthread.so.0),mongo::sleepsecs,mongo::ClientCursorMonitor::run,mongo::BackgroundJob::jobBody,execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
    1 nanosleep(libpthread.so.0),mongo::sleepmillis,mongo::WiredTigerKVEngine::WiredTigerJournalFlusher::run,mongo::BackgroundJob::jobBody,execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
    

    Versus the same when profiler level is 2:

    Tue Feb 21 14:05:46 UTC 2017
    24 pthread_cond_timedwait,mongo::CondVarLockGrantNotification::wait,mongo::LockerImpl::lockComplete,mongo::Lock::ResourceLock::lock,mongo::Collection::_insertDocuments,mongo::Collection::insertDocuments,mongo::Collection::insertDocument,mongo::profile,mongo::assembleResponse,mongo::ServiceEntryPointMongod::_sessionLoop,std::_Function_handler::_M_invoke,mongo::::runFunc,start_thread(libpthread.so.0),clone(libc.so.6)
    8 pthread_cond_timedwait,mongo::CondVarLockGrantNotification::wait,mongo::LockerImpl::lockComplete,mongo::Lock::ResourceLock::lock,mongo::Collection::_insertDocuments,mongo::Collection::insertDocuments,mongo::Collection::insertDocument,mongo::profile,mongo::::finishCurOp[clone.prop.247],mongo::performUpdates,mongo::CmdUpdate::runImpl,mongo::::WriteCommand::run,mongo::Command::run,mongo::Command::execCommand,mongo::runCommands,mongo::::receivedCommand[clone.isra.110][clone.prop.124],mongo::assembleResponse,mongo::ServiceEntryPointMongod::_sessionLoop,std::_Function_handler::_M_invoke,mongo::::runFunc,start_thread(libpthread.so.0),clone(libc.so.6)
    4 pthread_cond_timedwait,__wt_cond_wait_signal,__wt_cond_auto_wait_signal,__wt_cond_auto_wait,__wt_evict_thread_run,__wt_thread_run,start_thread(libpthread.so.0),clone(libc.so.6)
    1 sigwait(libpthread.so.0),mongo::::signalProcessingThread,execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
    1 select(libc.so.6),mongo::Listener::initAndListen,execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
    1 sched_yield(libc.so.6),__log_wrlsn_server,start_thread(libpthread.so.0),clone(libc.so.6)
    1 pthread_cond_wait,std::condition_variable::wait,mongo::waitForShutdown,mongo::::_initAndListen,main
    1 pthread_cond_wait,std::condition_variable::wait,mongo::DeadlineMonitor::deadlineMonitorThread,execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
    1 pthread_cond_timedwait,__wt_cond_wait_signal,__wt_cond_auto_wait_signal,__log_server,start_thread(libpthread.so.0),clone(libc.so.6)
    1 pthread_cond_timedwait,__wt_cond_wait_signal,__sweep_server,start_thread(libpthread.so.0),clone(libc.so.6)
    1 pthread_cond_timedwait,__wt_cond_wait_signal,__log_file_server,start_thread(libpthread.so.0),clone(libc.so.6)
    1 pthread_cond_timedwait,__wt_cond_wait_signal,__ckpt_server,start_thread(libpthread.so.0),clone(libc.so.6)
    1 pthread_cond_timedwait,std::thread::_Impl::_M_run,execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
    1 pthread_cond_timedwait,mongo::RangeDeleter::doWork,execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
    1 pthread_cond_timedwait,mongo::::PeriodicTaskRunner::run,mongo::BackgroundJob::jobBody,execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
    1 pthread_cond_timedwait,mongo::FTDCController::doLoop,execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
    1 nanosleep(libpthread.so.0),mongo::sleepsecs,mongo::TTLMonitor::run,mongo::BackgroundJob::jobBody,execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
    1 nanosleep(libpthread.so.0),mongo::sleepsecs,mongo::ClientCursorMonitor::run,mongo::BackgroundJob::jobBody,execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
    1 nanosleep(libpthread.so.0),mongo::sleepmillis,mongo::WiredTigerKVEngine::WiredTigerJournalFlusher::run,mongo::BackgroundJob::jobBody,execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
    

    I suspect this locking is in place so that writing to the profiler is safe even when mmapv2 is the storage engine in use, but I plan to dig deeper into this and publish my results in another post.

    It’s important to highlight that while the machine used to run these tests has a single disk (a consumer grade SSD), I/O was not the bottleneck, with a maximum utilization of 4% for the tests, regardless of the profile level used.

    Again, for the time being, the takeaway message is that the profiler does not come for free (what does?), so you should test its impact on your workload and hardware before using it in production.

    Alternatives

    Currently, I can think of two alternatives to MongoDB’s database profiler that either reduce or eliminate its performance impact.

    One is to use Percona Server for MongoDB, which provides a rate limiting feature for the profiler. This is similar to the rate limit feature Percona implemented for MySQL, so it gives us a way to log all the operations but only for a subset of the sessions. I have not measured how this reduces the performance impact yet, but expect it to at least make the profiler more usable in ‘full capture’ mode.

    The second one is to use VividCortex, which lets you monitor a MongoDB workload by sniffing packets. This eliminates the profiler impact, and has the added benefit that you get more accurate response time data than what the profiler gives us (at least at the moment). On the other hand, the ‘what does come for free?’ question applies here too, as this is not open source software.

    Conclusion

    MongoDB’s database profiler can be a useful tool to gain insight into a MongoDB workload, but proper testing should be performed before using it in production to capture all traffic, as its impact on performance is not negligible.

    Some alternatives (both Open Source and Proprietary) exist, that can mitigate or eliminate the profiler’s performance impact, and we can also hope that the overhead will be reduced in future MongoDB releases.

    2017-02-23 Thu

    03:01 Datascape Podcast Episode 3 – All About Data Lakes with Danil Zburivsky (1657 Bytes) » Official Pythian Blog

    I started hearing the term ‘data lake’ a few years ago but didn’t pay a ton of attention to it. Today, the term’s still around and so is the hype. According to this article on Wikipedia (https://en.wikipedia.org/wiki/Data_lake) the term is poorly defined and has morphed significantly since it was coined by James Dixon.

    Does it mean Hadoop? Does it mean we put everything in one spot? Do we need to document what’s there?

    My Google search yielded all sorts of contradictory articles about ‘data lakes’ so I decided to talk to my colleague, Danil Zburivsky, to find out what’s real, what isn’t and what people are using it for.

    Join Danil and I for the third episode of the Datascape Podcast as we sift through the murky term ‘data lake’ for some clarity.

    You can find it on iTunes, Google Play, Sound Cloud, and just about anywhere that shares podcasts.