AnotherLauncher

Symptom:

Slowness observed. "node select queries are taking up majority of time resulting in slowness of the application"

in PDOStatement::execute called at /core/lib/Drupal/Core/Database/StatementWrapper.php (145)...Core\Database\StatementWrapper::execute called at /core/lib/Drupal/Core/Database/Connection.php (937)...Database\Connection::query called at /core/lib/Drupal/Core/Database/Driver/pgsql/Connection.php (183)...

These queries are taking longer than 5 seconds.

SELECT "base_table"."vid" AS "vid", "base_table"."nid" AS "nid" + FROM + "node" "base_table" + INNER JOIN "node" "node" ON "node"."nid" = "base_table"."nid" + INNER JOIN "node_field_data" "node_field_data" ON "node_field_data"."nid" = "base_table"."nid" + WHERE ((LOWER("node"."uuid") IN (LOWER('$1')))) AND ("node_field_data"."default_langcode" IN ('$2'))

Analysis:

Repeat the issue locally:

Edit postgresql.conf

log_statement = 'none' log_min_duration_statement = 5000

I also set `log_statement = 'all'` initally but that creates a huge file which you should be careful of not to overload your system. Though it can be helpful to see everything.

Otherwise setting `log_min_duration_statement = 5000` means only log queries that take over 5 seconds.

Then there was some time figuring out how to replicate the issue. But eventually I found it was related to saving an article based on some custom event we have.

The above codeblock points to the execute() function in Driver/pgsql

the execute() function uses the compile() function which uses translateCondition()

in translateCondition() we are seeing the `['where'] = 'LOWER('` . section which we can see in the slow logs. We saw this same function with similar behavior existing in Drupal 8. But for some reason it was not a performance issue then. Now with Drupal 9 the performance impact is now more visible.

public static function translateCondition(&$condition, SelectInterface  $sql_query, $case_sensitive) { if (is_array($condition['value']) && $case_sensitive === FALSE) { $condition['where'] = 'LOWER(' . $sql_query->escapeField($condition['real_field']) . ') ' . $condition['operator'] . ' ('; $condition['where_args'] = []; // Only use the array values in case an associative array is passed as an // argument following similar pattern in // \Drupal\Core\Database\Connection::expandArguments(). $where_prefix = str_replace('.', '_', $condition['real_field']); foreach ($condition['value'] as $key => $value) { $where_id = $where_prefix . $key; $condition['where'] .= 'LOWER(:' . $where_id . '),'; $condition['where_args'][':' . $where_id] = $value; } $condition['where'] = trim($condition['where'], ','); $condition['where'] .= ')'; } parent::translateCondition($condition, $sql_query, $case_sensitive); } }

In general we likely would not want to change the Core execute() function or the translateCondition() function since this is good behavior most of the time.

Though it is odd that "PostgreSQL is a case-sensitive database by default" and yet the default ends up fitting into the condition `$case_sensitive === FALSE`. Potentially core should be updated. But this should likely be a discussion with the maintainers.

['where'] = 'LOWER(' . is part of:

translateCondition() function which is called in core/lib/Drupal/Core/Entity/Query/Sql/pgsql/Condition.php

New Relic:

In New Relic we can see `Postgres node select` was consuming 84% of the db operations.

Cluster Control:

In ClusterControl we can see the top slowest Query was the `SELECT "base_table"."vid" AS` as seen above and it had a near 3 million count in just a few days.

Fix:

Alter the query that uses LOWER for the condition that matches 'LOWER("node"."uuid")

/** * Implements hook_query_TAG_alter(). * * @param \Drupal\Core\Database\Query\AlterableInterface $query */ function core_query_entity_query_node_alter(Drupal\Core\Database\Query\AlterableInterface $query) { $conditions = &$query->conditions(); if (isset($conditions[0]['field']) && $conditions[0]['field'] == 'LOWER("node"."uuid") IN (LOWER(:node_uuid0))') { $conditions[0]['field'] = '"node"."uuid" = :node_uuid0'; } }

Now after the fix, the LOWER still hits all other queries it normally would hit, except for the condition of node uuid

We now see it as :

The current tools on the market target an ID element on the page and use JavaScript to replace the div and fade in a new section. (Crazyegg, Marketo RTP...)

I decided that the AB test issue requires a custom build approach.

This post will outline how I built this custom AB test solution and how I measure success.

Step 1: Build the functionality to randomize 2 pages to fill the frontpage routing

https://www.drupal.org/project/random_frontpage this is a good example of a seed idea for how I started building the custom functionality.

this should give you the functionality at /frontpages

Update the Basic site settings to show the front page as /frontpages

Then under Administration > Configuration > Front Pages you can input the node ids for the AB test, and display mode as full

Step 2: Add a field towards the top of the homepage to customize an ID that differs between both versions A and B

Add a field in the hero block to accept a text value for AB testing

Update the html rendered to use the value from the field created above

Confirm a value renders in the source code

Step 3: Leverage GTM to use a tag to send GA an event based off a trigger that finds the ID from Step 2.

(This is due to GA not deploying until the Cookie consent policy is accepted)

Create 2 tags and 2 triggers. Each tag will have a push event to GA such as<script>ga('send', 'event', { eventCategory: 'AB-test', eventAction: 'homepage-1'})</script>

The purpose of this comes from the rare instance where we see a spike in traffic. It would be helpful to see what the user agent was for that spike to be able to identify or filter out that bad traffic.

How to do:

Start by creating a custom dimension in GA:

"Scope" is on the session for this case since the user agent makes sense tied to a session. "Active" is set to true since we want to track this. "Name" is just for reading purposes. Note the dimension value is 18 in this case. We will use this later.

Next head over to GTM.

Here we need to create a custom variable. For this, we create a JavaScript variable and set the Global Variable Name to the javascript necessary to get the info we want. In this case navigator.userAgent work for our purposes.

Still in GTM, we need to make a tag and connect everything. Here I have set up a GA Universal analytic tag, with the type of event. The category name is for ease of filtering the events. The action here should be the GTM Variable name wrapped in {{ curly brackets }} . Set Non-interaction hit to true so that this does not affect the bounce rate. Set the tag firing options to once per event.

I have added the Variable for the GA tracking ID here as well. By clicking into the icon with the i or information we see a place where we can connect the GA custom dimension. In the custom dimension section, use the 18 id from earlier and give it the dimension value of the GTM variable name.

Publish your GTM changes.

Finally, head back over to GA and check out the real-time events. Filter by User Agent (the category) and you should now start to see the event action column filled with the user agent values.

It is simple, but I absolutely love enabling errors and warnings in config > development > logging and errors... Makes working in Drupal way easier.

when creating a class  always use either [public, private, protected]

methods are inside of a class

PHP Magic Mehtods  __construct()  __destruct()  __get()  __set()  https://php.net/manual/en/language.oop5... to learn more about these methods

The creation of an object is called "instantiating" it. Syntax in PHP uses the keyword new. $toothless = new dragon;

the class you extend from is the parent class, while the class you create is the subclass

 class Dragon extends Animal {    public function burnThings(){      // code to burninate    }    public function hoardGold(){      // code to horde gold    }  }

Namespace is important

Interfaces  Provide an Outline  Declare what methods must be implemented  Leave details to the class implementing the interface  Example: FormInterface  https://php.net/manual/en/language.oop5.interfaces.php

Drupal 8 uses a MVC architecture  Model = Business Logic  View = Display Logic  Controller = Glue between Model and View

MVC The path Drupal is given by the http request is routed by Drupal's front controller within index.php to the php controller file (or a form) specified for the path a site visitor is requesting. Once a request is routed... your controller returns a response - typically in an array. (which in turn gets Drupalled and is displayed as a page)

Controller  Controller vs Front Controller  Controller File

 Controllers in Drupal should be named in the format of ModuleNameController.php, and should live in the /src/Controller/ folder its project, as per the PSR-4 class autoloader standard. You will need to create these folders in your module's directory

Services - to package reusable functionality  access the db  sending an email

 Globally available Class methods  - Database service  - Email service  - Path aliasing  - Session manager  - String translation  You can create a new service in your module using a modulename.services.yml file

 database:    class: Drupal\Core\Database\Connection    factory: Drupal\Core\Database\Database::getConnection    arguments: [default]

 For example    Global $user data is now captured by setting:    $user = \Drupal\user\Entity\User::load(\Drupal::currentUser());

   Sometimes can be accomplished with $user = $this->currentUser();

   Either way you can get $user values with:    $user->get('uid')->value;    $user->get('body')->value;    $user->get('name')->value;

 Likewise:  Global $language = Drupal::languageManager()->getLanguage(Language::TYPE_INTERFACE)

Plugins  Small pieces of functionality that are swappable  Implement different behaviors via a common interface    images    blocks

 D8 still Depends on Hooks  Fundamental Drupal procedural function writing tool  Allows you to latch on to or override Drupal functions that are named hook_function() or theme_function()

   function <module_name>_<hook_name>(args){      //code to be executed;      //possibly alter an argument;      //possibly return something;    }

These last several months have been crazy. Since January I have been racing to implement my wishlist for this website. I manage the SEO efforts and have seen accomplishments that experts in the industry seem not to understand and multiple experts have inquired as to how I achieved these successes. We have been making advancements in so many sections of the site, from integrating a security scan into our development pipeline to strengthening the frequency of our security reviews, boosting our scan capability to Improve our SEO technical audits and making improvements in site speed. We even picked out a top leader SEO agency to help guide us to the best quality we can be at our current state. It is exciting that I can say that I lead the team through a backend version update and there are some exciting web related changes coming soon. 

It is a good feeling being valued at a cybersecurity company. I am very impressed with the quality of each day and I am pleased that I am able to contribute solidly to the team and company. Today our team won the top two spots on a mock sharktank competition within our division. Work hard, play hard. 

These days I get to write a bunch of front-end code for Drupal, and I get to analyze big data and find areas for improvement for the company within Google Analytics. The senior SEO person has moved on to a new company, and so now I have been pushed into the role of both web dev and owner of the SEO for the entire website. Luckily I am used to learning fast. I already have uncovered some major areas for improvement, and I have delivered a number of presentations and have another one coming up soon. I have a great boss too, someone who reminds me of how I like to be a boss. But this time around I get to be the grunt task person, which is refreshing compared to a couple of jobs ago being responsible for people’s pay rate and jobs. It is pretty crazy that I once was a CTO of a company of 40+. But I am much happier now that I get to focus on the ground level action. 

Well, what a journey lol. Last year I was at my 2nd tech job. That job was great for me as learning everything the hard way is beneficial more to one’s work ethic and character rather than one’s technical skills. That said it was fantastic to work next to professionals who slayed code and could do anything. They knew their worth, they knew their priorities and they had no shame at all. They strived for wholesome perfection every day. They taught me so much more than how to write for Drupal. They taught me what a strong business with freedom in the individual looks like. They taught me how to foster teamwork from people who are busy. They taught me how to handle conflict, how to own my failures and move forward in a win win win perspective. They taught me nothing is guaranteed and speed is king. If you can build it faster than expected, under budget, and done with good quality then you can get more work to do. More work is more dollars. But most of all they taught me to let myself live outside of work. Work is a vehicle to get to your living. Work fast and well so that you can do more of the stuff you want to do. My boss would get a crazy amount of stuff done as well as raise a family and go mountain biking almost daily. When I did leave the company, it was on a good note, encouraged by my coworkers and celebrated over a pizza and beer.

This year I am at my 3rd tech job. This time I feel great. The pay is average to what I expected. I am perfectly experienced for the role in a strange turn of events for the better. The people I work with are my age or in the vicinity of my age plus 10 or so years. This company treats me with respect in ways that throw me off due to not seeing it in myself many times. But the difference is, this time around I can actually do the things I am supposed to do. I no longer have to learn how to do something every time I get an assignment. It is a pretty exciting feeling. And I hope to keep growing my technical skills on side projects. 

Life has been improving, work has become much more valuable in gaining real experience. It seemed like the first year and a half of my tech experience was like a testing or training to see if I was really worth investing in. Since October I have been allowed to write code and fight through real problems on significant client pages. My first job was so crazy with stupid politics, scandals and unhelpful excitement. If you saw House of Cards, there were a lot of similarities I noticed in the work environment, and the entire story existed in 1.3 years where I started at $15 an hour as a low intern for graphic design who was just grateful to have a job, to leaving the company as the CTO and the size of the company went from 4 people to up to 40. The experience was potentially netflix worthy. I left that company in January 2017, and then joined the Pod team in April 2017. Since then, I have been focused on learning, and eager to focus much more attention on the actual development, while also learning to sharpen my project management skills. I feel super grateful for them taking me in. Everyone I work with is a master in their area, and I am pretty sure everyone is at minimum 15 years more experienced than I. 

Additionally, I have side hobbies and goals for the near future. 

Hr.glass

KaseyWoodworking Etsy Shop

Painting

Reading Goals

Health and Fitness Goals

Engaging in meeting the Drupal community at Meetups

Just finished my wireframes, its been a lot of slow thinking about how to plan this thing. What I did was I went around to some of the artists I admire and borrowed one thing they did well on their site for mobile. I am also thinking about the experience of a visitor to the site. Eventually there will be some call to action, but for now I want exposure. 

Next step to my pet drupal project, Make wireframes for the desktop view.