Archive

Archive for July, 2014

Theory of Constraints: the rise of NoSQL

July 9th, 2014

 

nosql

http://martinfowler.com/bliki/NoDBA.html

Why did NoSQL arise?  The rise of NoSQL makes no sense if you read an article like “Why You Should Never Use MongoDB”  and the ycombinator replies . Then I read something like this “Why You Should Use MongoDB” and don’t find any reasons just  ranting about why the first article was shoddy.

One theory proposed by Martin Fowler is that NoSQL arose as a way to get around the constraint of the DBA bottleneck. When developers want to change the schema or get copies of the source database the DBAs are just seen as an obstacle. The obstacle of provisioning environment is told in nail biting prose in Gene Kim’s “The Phoenix Project”

Long before NoSQL and The Phoenix project, about 13 years ago, I was the DBA at a startup, back in the first dot com bubble. As a startup we were trying to get our project to market ASAP. The development team was working crazy hours and the schema was changing all the time.  As the DBA, it was my responsibility to validate all the schema changes and apply the schema changes to the development database. Such validation and application could take a couple hours considering all the other work that was on my plate. This couple hour delay was considered such a hinderance to developers that they unilaterly decided to dump the structured schema and go to an EAV  data store. EAV stands for “Entity Attribute  Values” model. From wikipedia

There are certain cases where an EAV schematic is an optimal approach to data modelling for a problem domain. However, in many cases where data can be modelled in statically relational terms an EAV based approach is an anti-pattern which can lead to longer development times, poor use of database resources and more complex queries when compared to a relationally-modelled data schema.

 

The EAV “schema” (more like schema-less) meant that perfromance was dreadful and the SQL was impossible to debug as it was completely obfuscated. An example of an EAV query might look ike

Select f.symbol
from strings_eav  eav1,
       integer_eav  eav2,
       strings_eav  eav3,
       fundmstr f
where
        f.id = eav1.id
 and eav1.id = eav2.id
 and eav1.id = eav3.id
 and eav1.attr_num = (select attar_num from attributes where attr_id = :"SYS_B_00")
 and eav1.attr_value=:"SYS_B_01"
 and eav1.act_date <= SYSDATE and eav1.end_date > SYSDATE
 and eav2.attr_num = (select attar_num from attributes where attr_id = :"SYS_B_02")
 and eav2.attr_value=:"SYS_B_03"
 and eav2.act_date <= SYSDATE and eav2.end_date > SYSDATE
 and ((f.def_attr_templ = :"SYS_B_04"
         and eav3.attr_num=(select attar_num from attributes where attr_id = :"SYS_B_05")
         and eav3.attr_value >= to_date(:"SYS_B_06")
         and eav3.act_date <= SYSDATE and eav3.end_date > SYSDATE 
         and exists ( select null from integer_eav eav4 
                           where f.id = eav4.id
                                     and eav4.id in ( select attr_num 
                                                             from attributes 
                                                             where attar_id = :"SYS_B_07"
                                                                  or  attar_id = :"SYS_B_08")
                                     and eav4.attr_value = :"SYS_B_09"
                                     and eav4.act_date <= SYSDATE and eav4.end_date > SYSDATE)
       and not exists ( select null from integer_eav eav5
                              where f.id = eav5.id
                                     and eav5.id = ( select attr_num 
                                                             from attributes 
                                                             where attar_id = :"SYS_B_10")

 

Eventually as development stabilized and performance was abominable we were able to get everyone on board and move the EAV back into a structured schema. Once we went to a structured relational schema the load on the database fell from completely saturated to almost idle.

Moral of the story

The ironic thing is that many of the NoSQL setups are basically EAVs and/or databases that allow users to make “schema” type changes with out the intervention of a DBA. For example in Mongo or Couchdb, since they are JSON based, you can just add new fields to the JSON and the database deals with it. If you are using a database like Riak, it’s just a key  value store, which is basically an EAV.

EAV can be good for quick development but for production it’s completely unreadable thus impossible to debug and the performance is atrocious when it comes to relational type database operations of selecting all the fields in a row and joining rows from different “tables” together.

Instead of using EAV one could give each developer a virtual relational database, like using Delphix with Oracle, and let them work as fast as they can and then let them test out merging there changes in virtual databases before merging them in to trunk which is itself a virtual database that can be branched from and merged into.

Virtual databases take up almost no storage and can be made in minutes with a developer self service interface.

Screen Shot 2014-07-03 at 10.18.02 AM

NOTES: https://sites.google.com/site/oraclemonitor/eav

 

 

 

Uncategorized

kksfbc child completion

July 2nd, 2014

I’ve run into the wait “kksfbc child completion” a few times over the past but found very little written about it. I don’t have an explanation, but I have something that might be as good – a way to reproduce it. By being able to reproduce I at least test theories about it.
I ran a tight loop of

insert into t value(1);
See anything strange? I put “value” instead of “values” so the insert errored out. Thus causing parsing every execute. So this wait seems to be related to parsing. Could run further tests by correcting and adding a different litteral value to the insert to see what kind of waits might result.
Another tell tale sign that there was an error was that I also recieved “SQL*Net break/reset to client” which is a sign of an error in the sql statements that the application is attempting to execute.
oracle-l response was:
From: K Gopalakrishnan <kaygopal_at_gmail.com>
Date: Sun, 11 Feb 2007 02:00:45 -0500
Message-ID: <3b0f44a10702102300h3b9120dh139c030da47878e@mail.gmail.com>
David,
kks is Kompilation Layer function to Find the Bounded Cursor. Typically related to parsing (hard) which sometimes calls kksscl to scan the child list on those cursors when the cursors are shared.
Dion Cho says:
It’s literal meaning is “waiting till building the child cursor completes”, which is naturally related to the hard parse issue and multiple children problem.

The main reasons are known to be massive hard parses or library cache bugs. 

Uncategorized

Redo log waits in Oracle

July 1st, 2014

Redo

Redo is written to disk when
User commits
Log Buffer 1/3 full (_log_io_size)
Log Buffer fills 1M
Every 3 seconds
DBWR asks LGWR to flush redo
Sessions Committing wait for LGWR

Redo Log Wait Events

Log file Sync

 

Wait for redo flush upon:
Commit
Rollback
Arguments
P1 = buffer# in log buffer that needs to be flushed
P2 = not used
P3 = not used
Commit less
Often possible in loops that commit every loop
Commit every 50 or 100 instead
Put redo on dedicated disk
Use Raw Device and/or Direct IO
More Radical
Consider Ram Disks
Can stripe if redo writes are comparable to stripe size
Striping shouldn’t hurt
Striping can help
Ex: imp – can have large redo writes – can improve by 10-30%
Alternate disks for redo and archiving of redo
Possibly 10gR2
ALTER SYSTEM SET COMMIT_WRITE = BATCH, NOWAIT
Commit could be lost if machine crash
Or IO error

log buffer space

 

Wait for space in the redo log buffer in SGA
Solution
Increase log_buffer parameter in init.ora
Above 3M log_buffer little affect, if still a problem then backup is at disk level
Improve disk IO for redo
Faster disk
Raw file
Direct IO
Dedicated disk
p1, p2, p3 – no values

log file switch (archiving needed)

No p1,p2,p3
Database “hangs” for transactions
archive log stop;
– make room in log_archive_dest
archive log start;

 

 

log file switch (checkpoint incomplete)

 

No p1,p2,p3 args
Wait for checkpoint to complete because all log files are full
Solutions
Add more log files
Increase size of log files

log file switch (private strand flush incomplete)

Like a “log file switch Completion”

log file switch completion

 

No p1,p2,p3
Wait for lgwr to switch log files when generating redo
Solution:
Increase redo log file size
Reduces frequency of switches
What happens when a log file switch occurs:
Get next log file from control file
Get Redo Copy and Redo Allocation latch
Flush redo
Close File
Update Controlfile
Set new file to Current
Set old file to Active
If in Archivelog mode add file to archive list
Open all members of new logfile group
Write the SCN to the headers
Enable redo log generation
DBWR makes a list of blocks that need to be written out in order to over write the Redo log file a list of blocks that need to be written out in order to over write the Redo log file

switch logfile command

Same as log file switch completion but the command is executed by the dba
Alter system switch logfile;

Redo Log Sizing Concerns and Considerations

What happens to recovery time if I change my redo log file sizes
Larger Redo Log size can increase recovery time but
There are init.ora parameters to limit this
Standby DB: ARCHIVE_LAG_TARGET
Seconds, limits lag between primary and standby
Increases log file switches
FAST_START_MTTR_TARGET
Seconds to Recovery
Easy and accuracy
Is overridden by FAST_START_IO_TARGET
Is overridden by LOG_CHECKPOINT_INTERVAL
alter system set fast_start_mttr_target=17 scope=both;
SQL> select ESTIMATED_MTTR from V$INSTANCE_RECOVERY;
ESTIMATED_MTTR
--------------
21

 

 

Uncategorized