emGee Software Solutions Custom Database Applications

Share this

Web Technologies

CSS Modules

Echo JS - Sun, 03/11/2018 - 17:01
Categories: Web Technologies

Concurrent sandbox deployment

Planet MySQL - Sun, 03/11/2018 - 15:16

Version 0.3.0 of dbdeployer has gained the ability of deploying multiple sandboxes concurrently. Whenever we deploy a group of sandboxes (replication, multiple) we can use the --concurrent flag, telling dbdeployer that it should run operations concurrently.

What happens when a single sandbox gets deployed? There are six sets of operations:

  1. Create the sandbox directory and write down its scripts;
  2. Run the initialisation script;
  3. Start the database server;
  4. Run the pre-grants SQL commands (if any;)
  5. Load the grants;
  6. Run the post-grants SQL commands (if any;)

When several sandboxes are deployed concurrently, dbdeployer runs only the first step, and then creates a list of commands with an associated priority index. These commands are assembled for every sandbox, and then executed concurrently for every step.
The sequence of events for a deployment of three sandboxes in replication would be like this:

  1. Create the sandbox skeleton for every sandbox;
  2. Initialise all database servers;
  3. start all the servers;
  4. run the pre-grants, grants, post-grants scripts.
  5. Runs the group initialisation script (start master and slaves, or setup group replication).

Depending on the computer architecture, the server version, and the number of nodes, the speed of deployment can increase from 2 to 5 times.

Let's see an example:

$ time dbdeployer deploy replication 5.7.21
real 0m13.789s
user 0m1.143s
sys 0m1.873s

$ time dbdeployer deploy replication 5.7.21 --concurrent
real 0m7.780s
user 0m1.329s
sys 0m1.811s

There is a significant speed increase. The gain rises sharply if we use an higher number of nodes.

$ time dbdeployer deploy replication 5.7.21 --nodes=5
real 0m23.425s
user 0m1.923s
sys 0m3.106s

$ time dbdeployer deploy replication 5.7.21 \
--nodes=5 --concurrent
real 0m7.686s
user 0m2.248s
sys 0m2.777s

As we can see, the time for deploying 5 nodes is roughly the same used for 3 nodes. While the sequential operations take time proportionally with the number of nodes, the concurrent task stays almost constant.

Things a re a bit different for group replication, as the group initialisation (which happens after all the servers are up and running) takes more time than the simple master/slave deployment, and can't be easily reduced using the current code.

A similar optimisation happens when we delete multiple sandboxes. Here the operation is at sandbox level (1 replication cluster = 1 sandbox) not at server level, and for that reason the gain is less sharp. Still, operations are noticeably faster.

There is room for improvement, but I have seen that the total testing time for dbdeployer test suite has dropped from 26 to 15 minutes. I think it was a week end well spent.

Categories: Web Technologies

CSS-Tricks Chronicle XXXIII

CSS-Tricks - Sun, 03/11/2018 - 07:48

It's been many months since our last CSS-Tricks Chronicle. As a reminder, these are just little roundups of news of a slightly more personal nature and that of the site itself and surrounding projects.

Last update, I wasn't even a dad yet! That's changed 😍. My daughter is going in for her four-month checkup today!

I'm also working out of a brand new office here in Bend, Oregon. We split the space with CraftCMS. It's the first time I've had an office that I have real (part) ownership over. We've built out a kitchen area in it and are decorating it and fleshing it out to be useful and fun for all of us.

One particularly cool thing, we splurged on a VocalBooth. It's got the whole medium-fancy podcasting setup in there, so it's pretty darn good sound quality for the podcasting we do. Plus it's a nice place to take a call or meeting as well.

I made a round of updates to my microsite The Power of Serverless for Front-End Developers. My goal with that site is to explain the idea to the best of my ability and how it can be a useful thing to know about particular for front-end developers to do more with the skills they already have. Once you're sold on that, it's still a huge world to wrap your head around. One of the issues is understanding how many different services are out there and what roles they are trying to play. So the Services section is decently fleshed out and organized to help with that.

Over on CodePen Radio, I got to chat about all this with Marie Mosley. That's the first time I've really talked about it, so it kinda resulted in a big ol' thought dump.

I've had so many thoughts about this "serverless" stuff, I figured I'd formulate them into a proper conference talk. I'm not doing too many of those this year, but I'll definitely be at An Event Apart Seattle (April 2-4), and Front-End Design Conference (April 25-27). Come!

I got to speak with Jonathan Denwood and John Locke on the WP-Tonic Podcast.

Tons going on at CodePen, of course. We're always podcasting all about that, so if you're particularly interested in behind-the-scenes CodePen, CodePen Radio is your friend. As I imagine any business, we're always hard at work on a mix of projects. Some largely internal, like rearchitechting our email system, and some directly for our users, like rewriting our realtime systems to make features like Collab Mode way better.

As the year flipped over, of course we rounded up the Most Hearted list, so if you missed that, check it out.

ShopTalk just had a major milestone! Our 300th episode! Dave and I take the opportunity to talk about then and now. We've had loads of fun and educational episodes this year though, so if you just pick and choose episodes to listen to, I bet you'll find one worth a crack.

The post CSS-Tricks Chronicle XXXIII appeared first on CSS-Tricks.

Categories: Web Technologies

Checking User Threads With gdb in MySQL 5.7+

Planet MySQL - Sun, 03/11/2018 - 07:42
In one of my gdb-related posts last year I noted that there is no more simple global list of user threads in MySQL 5.7+:
"I had highlighted Global_THD_manager singleton also as during my next gdb sessions I had found out that simple global list of threads is also gone and in 5.7 everything is done via that Global_THD_manager. This is a topic for some other post, though."In that post and many times later when I had to deal with MySQL 5.7+ I just checked OS threads one by one in gdb using thread  1 ... thread N commands. This is not efficient at best, as I also hit numerous background threads that I often do not care about. So, a couple of weeks ago I finally decided to get back to this topic and find out how to check just user threads one by one in recent MySQL versions. I had a nice hint by Shane Bester on how to get information about $i-th thread (that he shared in one of his comments to my Facebook post):
set $value = (THD**)(Global_THD_manager::thd_manager->thd_list.m_buff.data + (sizeof(THD**) * $i))I've attached gdb to an instance of Percona Server 5.7.x that I had running in my CentOS 6.9 VM and tried few commands to check types and content of the Global_THD_manager elements:
(gdb) p Global_THD_manager::thd_manager
$1 = (Global_THD_manager *) 0x7fab087fd000
(gdb) p Global_THD_manager::thd_manager->thd_list
$2 = {m_size = 2, m_capacity = 500, m_buff = {{
      data = "\000\060b\344\252\177\000\000\000\220i\344\252\177\000\000\000\200x\344\252\177", '\000' <repeats 3977 times>, align = {<No data fields>}}},
  m_array_ptr = 0x7fab087fd010, m_psi_key = 0}
So, we see that internally there is some array of elements thd_list with m_size items (2 in my case) probably stored in some pre-allocated buffer of m_capacity (500) elements, stored in m_buff.data. The type of elements is not clear, but we can try Shane's hint and assume that they are of type THD**. Let's try to check what we see there after type castings:
(gdb) p (THD**)(Global_THD_manager::thd_manager->thd_list.m_buff.data)
$4 = (THD **) 0x7fab087fd010
(gdb) p  *(THD**)(Global_THD_manager::thd_manager->thd_list.m_buff.data)
$5 = (THD *) 0x7faae4623000
(gdb) p  **(THD**)(Global_THD_manager::thd_manager->thd_list.m_buff.data)
$6 = {<MDL_context_owner> = {
    _vptr.MDL_context_owner = 0x1c51f50}, <Query_arena> = {
...So, we get reasonable addresses and when we dereference the resulting THD** pointer twice we indeed get a structure that looks like THD of MySQL 5.7+ (it's very different, say, in MariaDB 10.1.x), with reasonable content (that is huge and skipped above).

I've tried to get processlist id of thread based on findings of that post using intermediate gdb variables:

(gdb) set $ppthd = (THD**)(Global_THD_manager::thd_manager->thd_list.m_buff.data)
(gdb) p *($ppthd)
$7 = (THD *) 0x7faae4623000

(gdb) set $pthd = *($ppthd)
(gdb) p $pthd->m_thread_id
$10 = 5and then directly, using offsets and checking for security contexts of threads:
(gdb) p  (**(THD**)(Global_THD_manager::thd_manager->thd_list.m_buff.data)).m_main_security_ctx.m_user
$14 = {m_ptr = 0x7faae463b060 "myuser", m_length = 6, m_charset = 0x1d21760,
  m_alloced_length = 8, m_is_alloced = true}
(gdb) p  (**(THD**)(Global_THD_manager::thd_manager->thd_list.m_buff.data + (sizeof(THD**)))).m_main_security_ctx.m_user
$15 = {m_ptr = 0x7faae46b1090 "root", m_length = 4, m_charset = 0x1d21760,
  m_alloced_length = 8, m_is_alloced = true}
(gdb) p  (**(THD**)(Global_THD_manager::thd_manager->thd_list.m_buff.data + (sizeof(THD**)))).m_thread_id
$16 = 9to confirm that I correctly get user names and thread ids for both 2 user threads I had in that "list". As usual Shane Bester was right!

Now, if you want to get more details about Global_THD_manager, you can just check the sql/mysqld_thd_manager.h file. I was interested mostly in the following:
  int get_num_thread_running() const { return num_thread_running; }
  uint get_thd_count() const { return global_thd_count; }

  static Global_THD_manager *thd_manager;

  // Array of current THDs. Protected by LOCK_thd_list.
  typedef Prealloced_array<THD*, 500, true> THD_array;
  THD_array thd_list;

  // Array of thread ID in current use. Protected by LOCK_thread_ids.
  typedef Prealloced_array<my_thread_id, 1000, true> Thread_id_array;
  Thread_id_array thread_ids;First of all, how consistent it is to use both int and uint data types for values that are always >=0... The fact that our thd_list elements is actually some template-based container, Prealloced_array, it also interesting, as it would be useful to find out how it is implemented. We can find all relevant details in the include/prealloced_array.h file. I'd like to highlight the following here:
"The interface is chosen to be similar to std::vector."

  size_t         m_size;
  size_t         m_capacity;
  // This buffer must be properly aligned.
  my_aligned_storage<Prealloc * sizeof(Element_type), MY_ALIGNOF(double)>m_buff;
Element_type *m_array_ptr;To summarize, MySQL 5.7+ uses more C++ now, with templates, singletons, iterators and more, but still Oracle prefers to implement their own container types instead of using some standard ones. One of these generic types, Prealloced_array, is widely used and is easy to deal with in gdb, as long as you know the element type.
Categories: Web Technologies

Design Microsites

CSS-Tricks - Sat, 03/10/2018 - 09:31

Google, Airbnb, Slack, MailChimp, Facebook, Etsy, IBM, Dropbox... everybody has a design site these days.

Direct Link to ArticlePermalink

The post Design Microsites appeared first on CSS-Tricks.

Categories: Web Technologies

This Week in Data with Colin Charles 31: Meltdown/Spectre Performance Regressions and Percona Live 2018

Planet MySQL - Fri, 03/09/2018 - 16:25

Join Percona Chief Evangelist Colin Charles as he covers happenings, gives pointers and provides musings on the open source database community.

Have you been following the Meltdown/Spectre performance regressions? Some of the best blog posts have been coming from Brendan Gregg, who’s keynoting at Percona Live this year. We’ve also got Scott Simpson from Upwork giving a keynote about how and why they use MongoDB. This is in addition to all the other fun talks we have, so please register now. Don’t forget to also book your hotel room!

Even though the Percona Live conference now covers much more than just MySQL, it’s worth noting that the MySQL Community Awards 2018: Call for Nominations! is happening now. You have until Friday, March 15, 2018, to make a nomination. Winners get into the Hall of Fame. Yes, I am also on the committee to make selections.

Another highlight: Open-sourcing a 10x reduction in Apache Cassandra tail latency by Dikang Gu of Instagram (Facebook). This is again thanks to RocksDB. Check out Rocksandra, and don’t forget to register for Percona Live to see the talk: Cassandra on RocksDB.

This week, I spent some time at Percona Headquarters in Raleigh, North Carolina. The building from the outside is pictured well in Google Maps. I thought it might be fun to show you a few photos (the office is huge with quite a handful working there despite the fact that Percona is largely remote).

Percona awards and bookshelf, featuring some very antique encyclopedias.


Peter Zaitsev, Percona CEO, outside his office (no, it is not an open office plan – everyone has rooms, including visitors like myself).


We’re all at SCALE16x now – so come see our talks (Peter Zaitsev and I are both speaking), and we have a booth where you can say hello to Rick Golba, Marc Sherwood and Dave Avery.

Releases Link List Upcoming appearances
  • SCALE16x – Pasadena, California, USA – March 8-11 2018
  • FOSSASIA 2018 – Singapore – March 22-25 2018

I look forward to feedback/tips via e-mail at colin.charles@percona.com or on Twitter @bytebot.

Categories: Web Technologies

MySQL : command delimiter curiosity – go & ego

Planet MySQL - Fri, 03/09/2018 - 14:21

Recently, I received a question related to GO as delimter to send a query. The user got some generated statements from a third party tool that looked like this:

/* CreateTable VersionInfo */ CREATE TABLE VersionInfo (Version BIGINT NOT NULL) ENGINE = INNODB GO /* VersionMigration migrated */ /* VersionUniqueMigration migrating ========================================== */ /* CreateIndex VersionInfo (Version) */ CREATE UNIQUE INDEX UC_Version ON VersionInfo (Version ASC) GO

and so on…

To be honest I was lost ?! I never heard about that syntax and I was convinced that this was not valid (and you?)…

But in fact it is ! It’s the long command name for \g and EGO is the one for \G.

You can try help in the client (see the manual) and you will see it:

ego (\G) Send command to mysql server, display result vertically. ... go (\g) Send command to mysql server.

I wasn’t aware of this… So I tried of course:

node1 [localhost] {root} ((none)) > select now() -> go ->

so it failed, it expected ‘;‘, ‘\g‘ or ‘\G‘ to send the command…

Then I found this bug #69534 where the solution was explained: --named-commands. In fact the client needs to be started with this option to enable the long named commands, see the manual. The manual is not that clear as it takes for example \q and quit… but in fact both are working even when --named-commands is not enabled.

Let’s try starting the client with that option to see if it works:

node1 [localhost] {msandbox} ((none)) > select now() -> go +---------------------+ | now() | +---------------------+ | 2018-03-09 22:49:04 | +---------------------+ 1 row in set (0.00 sec)

Wuuhuuu It works \o/

So I learned something about MySQL that was completely NOT new. I don’t know how useful this can be other than for a quiz but at least if you have a tool generating statements like these ones, now you know how to use them even without replacing all GO by ‘;‘.

Categories: Web Technologies

Extinct & Endangered

CSS-Tricks - Fri, 03/09/2018 - 12:07

I've been watching a lot of nature documentaries lately. I like how you can either pay super close attention to them, or use them as background TV. I was a massive fan of the original Blue Planet, so it's been cool watching the Blue Planet II episodes drop recently, as one example. A typical nature documentary will always have a little look how bad we're screwing up the environment twist, which is the perfect time and place for such a message.

Speaking of perfect time and place, why not remind ourselves of all the endangered animals out there with placeholder images! That's what Endangered Species Placeholders is. It's like PlaceKitten, but for environmental good.

I also just came across this free icon set of extinct animals. &#x1f622;

Direct Link to ArticlePermalink

The post Extinct & Endangered appeared first on CSS-Tricks.

Categories: Web Technologies

Vox Accessibility Guidelines

CSS-Tricks - Fri, 03/09/2018 - 12:05

I remember seeing these accessibility guidelines from Vox a while ago but it’s still interesting to go over them again today and see if there’s anything missing from my own process when it comes to improving accessibility.

And there’s an awful lot to remember! Color contrast, alt-text, keyboard navigation, focus states, and ARIA attributes are only a small snippet of the total number of things we ought to be mindful of when designing websites and so this checklist is certainly helpful for giving us all a good nudge from time to time.

Plus, it's worth remembering that there are small tweaks we can make and ways to advocate for improved accessibility in our projects.

Direct Link to ArticlePermalink

The post Vox Accessibility Guidelines appeared first on CSS-Tricks.

Categories: Web Technologies

Cache amplification

Planet MySQL - Fri, 03/09/2018 - 10:47
How much of the database must be in cache so that a point-query does at most one read from storage? I call this cache-amplification or cache amplification. The answer depends on the index structure (b-tree, LSM, something else). Cache amplification can join read, write and space amplification. Given that RWS was renamed RUM by the excellent RUM Conjecture now we have CRUM which is close to crummy. I briefly wrote about this in a previous post.

To do at most 1 storage read for a point query:
  • clustered b-tree - everything above the leaf level must be in cache. This is a key/pointer pair per leaf block. The InnoDB primary key index is an example.
  • non-clustered b-tree - the entire index must be in cache. This is a key/pointer pair per row which is much more memory than the cache-amplification for a clustered-btree. Non-covering secondary indexes with InnoDB are an example, although in that case everything you must also consider the cache-amplification for the PK index.
  • LSM - I assume there is a bloom filter per SST. Bloom filters for all levels but the max level should be in cache. Block indexes for all levels should be in cache. Data blocks don't have to be in cache. I assume there are no false positives from the bloom filter so at most one data block will be read. Note that with an LSM, more space amplification means more cache amplification. So cache-amp is worse (higher) for tiered compaction than for leveled.
  • something else - there have a been a few interesting variants on the same theme that I call index+log -- BitCask, ForestDB and WiscKey. These are similar to a non-clustered b-tree in that the entire index must be in cache so that the storage read can be spent on reading the data from the log.
I have ignored hash-based solutions for now but eventually they will be important. SILT is a great example of a solution with excellent cache-amplification.

Updated to correct what should be in cache for the LSM.
Categories: Web Technologies