SQL is The Way to get Information out of a Database.
TL;DR: Slightly frustrated by CLI-interfaces, I decided to "slurp" some data into the RDMBS. Now I'm using SQL to inspect the data. SQL is still The Best Way.
Let me Show you...
Background: I want to see Information, Easily.
While experimenting with yugabyte, I was learning to "query" the system via the web-pages (web-guis are at ports 7000, 9000 and 15433, and there may be more). And I learned to type long commands in to yb-admin (link) and the additional-downloadable yugatool (link).
Those tools are OK, and I recommed you investigate them. But I wanted more flexibility in my "viewing of information".
My typical questions were :
Where are the Tablets of my Table located ?
Which nodes are Leading, Following, or just standing-by ?
Which tables are Not Affected if I lose node42 from my cluster ?
And in the near Future I would expect questions like:
Can I verify that my data is geo-distributed the way it was Intended ?
Of course, the CLI tools _could_ answer those questions. But in most cases, finding an answer took multiple runs of yb-admin, with copy-paste of long uuid-strings between commands, and searching for names, uuid-strings, in the terminal-output or in the web-guis.
I will also subtly point to rule-4 of the 12 rules for RDBMS (link).
As a Database person, as a DataBeast, I needed SQL !
Remedy: a set of Relations (tables, view) in the RDBMS.
Part of the remedy is in the pg_catalog, I can query pg_tables and pg_class, and the "backslash-d" commands even show some of the yugabyte info (index-hash or index-asc, for example). In an earlier blog, I created some queries to dig into the yb-specifics of a table (link), for example to show the nr of tablets (shards) below a table.
For quite some time, I find I need more...
And I decided to do some typing, scraping, inserting and Query-ing.
The Model:
A wile ago, I created an Entity-Relation Diagram representing some of the YB system. Here is my updated version, with the "Information I Need":
In this model there are currently 5 entities:
Table (tabl): The object that holds the user-data. e.g. employee: id, name, DoB etc...
Tablet (tblt): The object that stores the data, the "shard". And each Tablet is actually replicated 3x (when RF=3)
TSever (tsrv): The process that stores and retrieves the tablet data. A TServer generally runs on a node (a server, machine, or container).
There are two linking-entities:
Tablet-to-Table (tbtt): in YB, a table can be split (sharded) over multiple tablets, or co-located with other tables in a single tablet. Hence there is a n:m relation between Tablet-Table.
TabletReplica-to-TServer (ttrp): Each Tablet is stored 3x (when RF=3), and those repicas are distributed over TServers, whereby 1 TServer has the role of "Leader", and the others are "Followers".
Note: I know there is a lot more to a YB-Cluster and to a YB-Universe, but this is my starting point and this is the minimum I need to answer my urgent questions.
The scraping of the data:
Warning: This trick is primitive, but effective for my first needs. Maybe you should not rely on this "trick" for Serious Operational Systems. It is more of a PoC.
All of the information I need to fill those entities can be found, displayed, from yb-admin and yugatool. My trick is to run those tools, and read (scrape, slurp) the data from stdout.
To this end, I have a table called yb_infc with a flat-text field to "slurp" data from programs, let me show two table-definitions:
The ybx_intf table only serves to read data from copy-commands, the ybx_tsrv table will hold the data concerning the TServers, the processes that do the actual work for storing and retrieving data, and which are located on a node, or a host (in my case: a docker-container).
I can now use an old psql trick, the copy-command, to slurp the data in. And then use SQL (notably substr and split_part) to extract my data. For example, this is how I extracted the data for tserver processes:
And with a few simple psql commands, I got my data into ybx_tsrv.
Just to prove it worked: The yugatool output for "cluster_info" looks like this, and yes, all the data concurs.
QED, Proof it worked...The data from the yugatool-screen output was scraped into the intf table and converted into records in the ybx_tsrv table in which each record represents a TServer process.
Note: This screen-scraping, or "slurping of data" depends on the layout of the yugatool output. I would expect yugabyte itself to do a much better job of exposing the relevant data into views or tables: A more capable coder than myself should be able to get this data straight from the source (the master-process) without having to copy-slurp-substring in my roundabout way.
Anyway, I repeated this process for all Entities, and ended up with the data in my tables. Some copy-insert-commands got a little complex, and some needed looping-over-all-tables+tablets (sources on request - not particularly proud of my own quick-hack).
But with a bit of tweaking, I ended up with pieces of code in sql- and sh-files to fill all of my Entities.
And once I verified that data, I was able to ... Query using SQL!
The Result, Answers from SQL:
Now after I got the Data ingested and checked, Let's ask those questions again:
Where are the Tablets of my Table located ?
Which nodes are Leading, Following, or just standing-by ?
To answer "where are they", I created a view to combine table-info and tserver-info with the following fields:
And I can now query this view, and find out where t01 and t16 are located:
The Table t16 has 16 tablets and is more spread out over all available nodes. If you count the Ldrs: there are 16. If you count the followers, there are 32. Again, the RF=3, leads to 16x3=48 replicas of Tablets, nicely spread out over the hosts.
Furthermore, from the same view, I can determine the answer to:
Which tables are Not on node7 or on node8 ?
Just in case I want to take some nodes, or a rack, out of commission, there is this one table that doesnt touch on those nodes:
This concurs with what we have seen of Table t01 above. And I verified, using yb-admin. That verification was quite a bit of typing/scripting with yb-admin, and worse-so when a table has 16 tablets. It would take a lot of screenshots to prove...
Are you still curious to see which TServer-nodes are acting as Leader or Follower for tablets of, say, Table t04 (which is a 4-shard table)?
Just aks:
And for Table t04, we see indeed:
There are 4 leading TServers, e.g. 4 tablets/shards, for which the Leaders are nicely spread over 4 nodes. And there are indeed 8 Followers, also more or less divided over the nodes. 4 leaders + 8 followers = 12 replicas, as expected for a 4-tablet table with RF=3.
And in Future, if we add Cloud / Region / Zone information from the nodes, can we Verify that our data is geo-distributed the way it was Intended ?
For example: Are Tables t408 and t669 Really located on TServers in the San Jose area? Such a future-question would be child-play with a good SQL interface.
Tip: Query the yb_servers(), it has the Cloud / Region / Zone info right there, and can be joined on the host-column.
I've stated it before:
In an RDBMS, Everything is SQL, and SQL is Everything...
Learnings and Shortcomings...
Offering a SQL (query-able) interface is The Way to present information to DataBase-professionals, and to interfaces or APIs that need it.
SQL-views will allow every "user", human or API, to combine and filter the data in ways they see fit, rather then having to scrape information off a webpage or terminal (or filter it from a MB-size JSON document)
SQL-views of important components should generally allow faster, more efficient, inspection of the system, and faster diagnose of problems or bottlenecks.
Furthermore, using SQL concurs nicely with how PostgreSQL and any RDBMS in general are used to presenting data. Re-check the 12 Rules of Codd/Date.
-------- End of Blogpost ---- some remarks + questions below -----
Remark: Primitive ERD...
My ERD is currently quite primitive, mainly to "make the point". In future, I would include things like placement-information (cloud/region/zone) and possibly some dynamic-info like "size" or IO-activity per server or per table/tablet.
I imagine someone doing an SQL query to verify that "their data" are actually geo-located in the area where they want them, possibly to adhere to regulatory-demands.
Remark: Timeliness of information...
This process of "slurping" the data means the information is a snapshot in time. I've noticed that Tablets can move around, and Leaders get elected/demoted every now and then. It would be much better if this info was queried straight from the YB-catalog or YB-Metadata. But that is not for me to implement at the moment. Maybe a suggestion for option 101 of yb-admin?
Remark: Source-code for the scraper...?
Crafting the code (sql and sh) was more time-consuming than I had hoped. And the resulting code doesnt look pretty. I'm not proud of "the hack". You can Probably Do Better yourself. But anyone wants a copy: DM or mail. And definitely "Use at Own Risk".
Q: Do you See It ?
How many ppl, apart from myself, see SQL as a key-component in examining any RDBMS, or Any Database at all?
If I am the only one who "sees this", there is no point in pursuing it. But if others, possibly with experience from other RDBMSes out there, agree, I think we have a point to make to yugabyte.
Q: What is missing in the ERD ?
Does anyone have a better ERD, or suggestions for improvement ? DM or mail... From experience, I know that having a good ERD, and with that, a Good Mental Picture of your system really helps the Thinking and Troubleshooting.
Comments
Post a Comment