[Git][venenux/venenux-docus][master] the vertica lie vs RDBMS - real true and use cases must be for BI

0 views
Skip to first unread message

Герхард PICCORO Lenz McKAY (@mckaygerhard)

unread,
Jun 1, 2023, 1:07:38 AM6/1/23
to venenux...@googlegroups.com

Герхард PICCORO Lenz McKAY pushed to branch master at venenux / venenux-docus

Commits:

  • 94dd86c0
    by Герхард PICCORO Lenz McKAY at 2023-06-01T05:07:32+00:00 
    the vertica lie vs RDBMS - real true and use cases must be for BI

1 changed file:

Changes:

  • docs/others-infodocs-details-vertica-vs-opendbms-en.md
    1 
    +# Vertica vs RDBMS and noSQL database cases
    2 
    +
    3 
    +Document/key-value databases is typically good for unstructured/"schemaless"
    4 
    +data. Usually, the cases when you don't need to explicitly define your schema
    5 
    +up front and can just include new fields without any ceremony. Most modern
    6 
    +fiscal printers uses such way of storage in the client machine.
    7 
    +
    8 
    +In recents years those kind of databases where a boom, previously we have been
    9 
    +on the benealt of LevelDB but such engine was very unstable when crashing.
    10 
    +
    11 
    +It's often very easy to scale out document/key-value databases. Just by more
    12 
    +parts of same strutures (knows as nodes) to replicate data to is one way to
    13 
    +offer more scalability and offer more protection against data loss.
    14 
    +
    15 
    +Otherwise, complex/dynamic queries/reporting are best served from an RDBMS. Often
    16 
    +
    17 
    +#### Introduction to Vertica DB
    18 
    +
    19 
    +**Vertica** breaks the database scenario for two things that are the first things
    20 
    +technology managers really see, **its level of data compression (both in data
    21 
    +storage as well as in transmission) as well as the semi-free service** what
    22 
    +are offering. But all of those are great only if the company that implements
    23 
    +their technology is already also a company of technology.
    24 
    +
    25 
    +But what if the company trying to make new high data writing software with high
    26 
    +volume data storage isn't primarily focused on technology and can't spend that
    27 
    +much on such a thing? But part of the information os not so true at all, is
    28 
    +not open source per se. Also we have to take into account today's world of
    29 
    +compulsive political tension!
    30 
    +
    31 
    +#### DBMS like percona mysql with xtradb and myrocks
    32 
    +
    33 
    +You may think of Percona as a distributor who collects, coordinates and maintains
    34 
    +patches and distributes an enhanced version of the MySQL server.
    35 
    +
    36 
    +Inspired in RocksDB, **myrocks** is a percona improved engine that permits
    37 
    +to use SQL like request and client into the MySQL DBMS.
    38 
    +With SSD database storage, this means less space used and a higher endurance
    39 
    +of the storage over time, but lack of performance if the underliying
    40 
    +storage hardware is not SSD, so RocksDB need several key OS+HArdware
    41 
    +features to have results.
    42 
    +
    43 
    +The library is maintained by the Facebook Database Engineering Team.
    44 
    +It is a fork of Google's LevelDB optimized to exploit many CPU cores,
    45 
    +and make efficient use of fast storage, such as solid-state drives (SSD),
    46 
    +for input/output (I/O) bound workloads.
    47 
    +
    48 
    +The **XtraDB** is InnoDB with steroids. The patches themselves stem from
    49 
    +Google, Facebook and others. XtraDB scales better on massively parallel architectures
    50 
    +and especially XtraDB is much better suited for write-heavy workload. Contrary
    51 
    +to rocksDB its well suite to use in HDD or SDD, but in last will cut off the
    52 
    +SDD storage life due workload. XtraDB includes features such as crash-safe
    53 
    +replication, online backups, and hot backups that are not available in InnoDB.
    54 
    +
    55 
    +However, it's also important to note that XtraDB is not compatible with some
    56 
    +InnoDB features, such as full-text searching or spatial indexing.
    57 
    +
    58 
    +## Considerations
    59 
    +
    60 
    +#### Considerations for Percona flavours
    61 
    +
    62 
    +Percona XtraDB is compatible with InnoDB by default. You can read and write the
    63 
    +same datafiles, and all SQL queries run exactly the same. You won’t even notice
    64 
    +the difference.
    65 
    +
    66 
    +Percona PosggreSQL does not have many differences, just better integration and
    67 
    +easy of usage for administrators. This cos PostgreSQL is so complex and so scalar
    68 
    +already.
    69 
    +
    70 
    +The improvements in Percona Mysql XtraDB are subtle. They are internal fixes to
    71 
    +solve specific scaling bottlenecks. These bottlenecks don’t necessarily affect
    72 
    +your applications or environment, in which case Percona XtraDB would function
    73 
    +exactly like stock InnoDB. Some of the enhancements in Percona XtraDB proved
    74 
    +themselves useful, so later versions of Oracle MySQL and MariaDB implemented
    75 
    +and today XtraDB and InnoDB are ver similar.
    76 
    +
    77 
    +* Buffer pool mutex split into four sub-types of mutex, to reduce contention when
    78 
    +you have a high number of concurrent clients.
    79 
    +* Insert buffer options for max size and merge rate. Good when you have lots of
    80 
    +indexes and a very high rate of insert/update/delete operations.
    81 
    +* Adaptive hash may be split into multiple partitions. Good if you have a high
    82 
    +number of threads running concurrent queries over non-primary indexes, so much
    83 
    +that it’s causing contention on the Adaptive Hash Index mutex.
    84 
    +* Faster page checksum algorithm. Good if you have a high rate of page flushes
    85 
    +on SSD storage. This feature is obsolete in MySQL 5.6.
    86 
    +* Handle corrupt tables by issuing a warning and marking the table unusable,
    87 
    +instead of the default behavior of deliberately crashing the MySQL server.
    88 
    +
    89 
    +#### considerations for Vertica DB
    90 
    +
    91 
    +The biggest advantage of Vertica is the raw speed. It is extremely fast when
    92 
    +compared to other analytical databases, and it boasts features that make joins
    93 
    +extremely fast. The trade-offs are the high license costs, slow updates and
    94 
    +insatiable need to eat thru tons of disk space.
    95 
    +
    96 
    +Vertica never overwrite the data file on updates, so every time you update and
    97 
    +new SO write will happen. This is a cons of the key-value storage philosophy,
    98 
    +and for a relational base data this is not the good option.
    99 
    +
    100 
    +Vertica is not to be used to replace your relational data at OLTP database, this
    101 
    +is there to do the heavy lifting and help you do the analytics stuff with less
    102 
    +expense(time, money). Vertica its more for BI rather for information storage.
    103 
    +
    104 
    +Vertica have compressed data and encoded data, the compressed data will require
    105 
    +some extra cpu cycles while retrieved, but most of the times Vertica uses encoding
    106 
    +as following source describes http://www.aodba.com/tut_output_mysql.php?tut=6&page=vertica
    107 
    +encoding creates a smaller footprints and by doing this data retrieval will be faster.
    108 
    +
    109 
    +For a storage inventary software or product book is not the right choose.
    110 
    +Vertica is a BI tool basically.
    111 
    +
    112 
    +Vertica is extremely expensive, it not opensource, the opensource version does
    113 
    +not have all of this key features; prices it started with about 100K$ per terabyte,
    114 
    +and after it became popular now it costs 10K$ per terabyte, still this is expensive,
    115 
    +a fully protected server is worth less than 500$ per month including several
    116 
    +terabytes in Germany.
    117 
    +
    118 
    +#### the lie of Vertica open source
    119 
    +
    120 
    +Community version will only allow you to create a 3 nodes cluster and a max
    121 
    +of 1TB (no bkp available and others things are as well not possible)
    122 
    +
    123 
    +Just to put some light into the Vertica licenses
    124 
    +
    125 
    +- All development,homologation or desaster sites (they are under the initial
    126 
    +production License – no extra money)
    127 
    +- Raw data size based (valid to store up to some amount of raw data), license is
    128 
    +only applied to the RAW data loaded once loaded it can be replicated as many times
    129 
    +as you like.
    130 
    +- Term-based and peridicaly (valid until a specific date, montly by example).
    131 
    +
    132 
    +
    133 
    +## Basic Table comparison
    134 
    +
    135 
    +| Topics       | Vertica                            | Percona Mysql+Myrocks               |
    136 
    +| ------------ | ---------------------------------- | ----------------------------------- |
    137 
    +| TODO         | TODO                               | TODO                                |
    138 
    +
    139 
    +
    140 
    +## Conclusions
    141 
    +
    142 
    +SQL performs well as a transaction processing system, it works horrible when
    143 
    +trying to query it for reporting/analytic purposes. Tha is the case for Vertica,
    144 
    +and all the colum store capable (cos Vertica is primary RDBMS, not column/key based)
    145 
    +has advantages but not as sustitution for.
    146 
    +
    147 
    +You should choose Vertica if you need reporting and consulting features, mostly
    148 
    +those need at bussines inteligence, but not like invetary or storage time.
    149 
    +
    150 
    +This means any column/key based DB is just complementary to, not sustitute to, and
    151 
    +is the reason why Vertica really still is a RDBMS and not columnkey only.
    152 
    +

—
View it on GitLab.
You're receiving this email because of your account on gitlab.com. Manage all notifications · Help

Reply all
Reply to author
Forward
0 new messages