Anna FilinaReduce number of queries (28.10.2014, 15:24 UTC)

Customers often call me because their site is slow. One of the most common problems I found was a high number of queries that get executed for every single page hit. When I say a lot, I mean sometimes more than 1000 queries for a single page. This is often the case with a CMS which has been customized for the client’s specific needs.

In this article, aimed at beginner to intermediate developers, I will explain how to figure out whether the number of queries might be a problem, how to count them, how to find spots to optimize and how to eliminate most of these queries. I will focus specifically on number of queries, otherwise I could write a whole tome. I’ll provide code examples in PHP, but the advice applies to every language.

Start from the top

Start by looking at your browser’s inspector. I will demonstrate using Chrome. Open the inspector and reload the page. Click the Network tab and the first result will be the document generated by your script.

Inspector

You’ll notice that it takes 950 ms to load. Don’t stop there, check the breakdown of that time by hovering over the timeline bar to eliminate any doubt.

inspector-2

According to this chart, most of the time is spent by the server generating the page. The two most likely reasons are: you either use too much CPU (processing) or too much disk (files or database). Now that we narrowed the problem down a bit, it’s time to see whether we in fact have a lot of queries.

Count queries

If you’re using a CMS or a framework, then you probably have access to a single function that sends all the queries or even logs them. For example, with Moodle (a learning system used by many universities) you can call $DB->perf_get_queries() at the bottom of the page to output the number of queries executed. Check the documentation of your product to find these functions.

If all the code is your own, then wrap all database calls in a single function and add a counter. Example:

protected $num_queries = 0;
public function query_db($sql, $params) {
  $this->num_queries++;
  // execute here
}
public function get_num_queries() {
  return $this->num_queries;
}

My rule of thumb is to focus on pages that execute 10 or more queries. You can also log the individual queries in an array so that you can have access to a full list. This list will be helpful helpful to find which ones are very similar and can be combined.

One common reason for so many queries is executing them in a loop. For example, I often see a query for a list of Categories, a loop on these categories and then another query to get the list of Subcategories. Example:

$categories = get_categories();
foreach ($categories as $category) {
  $subcategories = get_categories($category->id);
}

Just here, there is a potential for dozens of queries. Sometimes, I see nested loops, where people would iterate on the subcategories to get the list of Courses. Now you’re probably up to 100 queries. Throw in a query to get some additional information for each Course, such as whether the user has completed them and you might be over 1000 queries.

Optimize

Once you get a log of all the queries that were executed, look for ones that look almost the same. Example:

select * from course_categories where parent = 0;
select * from course_categories where parent = 1;
select * from course_categories where parent = 3;
select * from course_categories where parent = 15;
select * from course_categories where parent = 22;

I can tell right away that the first query gets all top-level categories and we then iterate on them to get subcategories for each. The best way to fix this is to combine. You do this by executing a hand-crafted query in the code instead of relying on the built-in functions:

SELECT
cat.id AS cat_id, cat.parent, cat.name AS cat_name,
subcat.id AS subcat_id, subcat.parent, subcat.name AS subcat_name
FROM course_categories AS cat
LEFT JOIN course_categories AS subcat ON subcat.parent = cat.id
WHERE cat.parent = 0
ORDER BY subcat.parent;

This will fetch both the categories and their subcategories. It might look a bit complex, but I’m really just using table and column aliases. As a bonus, we only fetch the columns that we need instead of * (star), since getting data that we don’t need is just slowing things down. The results you get back will look like this:

+--------+--------------

Truncated by Planet PHP, read more at the original (another 2464 bytes)

Link
labs @ Qandidate.comAdding a unique request id using middleware (28.10.2014, 15:00 UTC)

In our event-sourced applications built using Broadway we have a full log of all events that happened in our application. We also log all commands using monolog. We did however miss some traceability between the commands and the actual events: we logged both the commands and the events but could not determine which commands led to which events.

In order to achieve this we decided to add a request id to each of our requests. We can now add the request id to the command log and add the request id to the metadata of our DomainMessages using the MetadataEnricherInterface

There are some webservers that provide a solution for this problem. One of these is an nginx module, and there is also an apache module. However, we wanted our solution to be webserver-independent. The result: we decided to tackle our problem the way we know best; in PHP.

We created a middleware for Symfony that adds a request id header to the request's headers. Check out the source code. You can add it to your application by changing a few lines of code in your app.php file. Just have a look at these 'before' and 'after' codeblocks:

∞ labs @ Qandidate.com Permalink

Link
Cal EvansInterview with Erika Heidi Reinaldo (28.10.2014, 05:00 UTC) Link
Anthony FerraraYou're Doing Agile Wrong (27.10.2014, 16:00 UTC)
To some of you, this may not be new. But to many of the people preaching "Agile Software Development", Agile is not what you think it is. Let me say that again, because it's important: You're Doing Agile Wrong.

Read more »
Link
Fabien PotencierThe PHP Security Advisories Database (26.10.2014, 07:07 UTC)

A year and a half ago, I was very proud to announce a new initiative to create a database of known security vulnerabilities for projects using Composer. It has been a great success so far; many people extended the database with their own advisories. As of today, we have vulnerabilities for Doctrine, DomPdf, Laravel, SabreDav, Swiftmailer, Twig, Yii, Zend Framework, and of course Symfony (we also have entries for some Symfony bundles like UserBundle, RestBundle, and JsTranslationBundle.)

The security checker is now included by default in all new Symfony project via sensiolabs/SensioDistributionBundle; checking vulnerabilities is as easy as it can get:

$ ./app/console security:check

If you are not using Symfony, you can easily use the web interface, the command line tool, or the HTTP API. And of course, you are free to build your own tool, based on the advisories stored in the "database".

Today, I've decided to get one step further and to clarify my intent with this database: I don't want the database to be controlled by me or SensioLabs, I want to help people find libraries they must upgrade now. That's the reason why I've added a LICENSE for the database, which is now into the public domain.

Also, even if I've been managing this database since the beginning with only good intentions, it is important that the data are not controlled by just one person. We need one centralized repository for all PHP libraries, but a distributed responsibility. As this repository is a good starting point, I've decided to move the repository from the SensioLabs organization to the FriendsOfPHP organization.

I hope that these changes will help the broader PHP community. So, who wants to help?

Link
Matthias NobackSymfony in Barcelona (25.10.2014, 22:00 UTC)

Pizza

I just ate a nice pizza at my hotel room in Barcelona. The funny thing is (at least, to a Dutch guy that is): they wouldn't be able to give me a pizza before 20:00h. At that time in my home country we have long forgotten our desserts, cleaned the dishes and are starting to think about sleeping (just kidding). Anyway, I got the pizza, it was good and now I'm here to write a little report on the things that happened during the last three days.

It all started a couple of months ago when rising Symfony community star Marc Morera (who, by the way, is going to do the Symfony Walk in a couple of weeks) asked me to come to Barcelona and do some consultancy for the company that is working hard on a new Symfony e-commerce product called Elcodi. They were in the process of making some decisions on package architecture and bundle design issues and they asked me to help them with it. Of course I said "yes"! And here we are.

Now you need to know that Elcodi is part of a commercial enterprise. However, the Elcodi team creates open source e-commerce software. A very interesting, and also challenging thing to do. While this premise is already quite fascinating, Elcodi takes some extra steps to really make a difference in the world of companies that open-source their software. Elcodi values its contributors very much and has actually made a great effort to organise a community day for several intimate contributors and developer-friends.

Elcodi Community Day

As you might have guessed by now: I was part of the community day too and was asked to do a workshop for the attendees. The funny thing is, I only prepared a talk on package design and I had some material from a previous High Quality Bundles workshop.

To me personally it was quite hard to actually not have a schedule for the day, but in the end, everything worked out very well and we just followed the natural flow of the conversations, the questions that people had in mind and the subjects upon which the Elcodi team and the other attendees were trying to decide.

Grizzly Bar Barcelona

To finish this story: the night before the Elcodi Community Day I was also asked to do my "The Naked Bundle" talk for the Symfony Barcelona Usergroup. It was just very surprising to see that the event was about to take place right in something called the Grizzly Bar (see the photo on the right).

It turned out to be a very cosy place and lots of people showed up. At this night, I was talking about decoupling your code from the framework code and letting go of its conventions. So you can imagine that it was a big surprise that the organisers had also invited Javier Eguiluz to do his talk on the Symfony Best Practices during the same night. At the same time, this was the best thing the organisers could have done!

It was very nice to finally meet Javier in person. He is a very important Symfony community member and also co-author of the new Symfony Best Practices book. Talking about the best practices and watching Javier's presentation finally made some important things very clear to me:

  1. As I had already previously concluded: the official Symfony Best Practices are to be considered an opinion, just as mine and yours. However, they are intended to be constructive and helpful for developers who start using Symfony. They shouldn't be bothered at first to make all those difficult decisions, driven by discussions about code design.
  2. Looking at the Symfony Best Practices from this perspective really makes them quite good.

Of course, there are some things in the document that I wouldn't recommend, but nevertheless: in a few weeks I'm going to train a group of developers who are starting to work with Symfony and I'm definitely going to resort to the Best Practices to teach them the quick way to get started. Of course, It wouldn't be me if I would not also give them a sneak peek of the road that lies ahead and the numerous options you have to write better code (or at least, "better imho").

Truncated by Planet PHP, read more at the original (another 832 bytes)

Link
SitePoint PHPStrategic Archive Extraction with Distill (25.10.2014, 16:00 UTC)

Perhaps you are building an application which depends on archives; for example, you constantly have to download archives and extract files from them. There are many libraries out there that can help you get files extracted from an archive, and a new player in town capable of doing this job is Distill.

With Distill, you can easily extract an archive into a specified directory. You can also give multiple archives to Distill and let it pick the most optimal one, as per a strategy you define yourself. Let’s dive into the code to see what we can achieve with Distill.

If you want to follow along, you can have a look at this Github repository to check out the code.

Setup

Before we can start using Distill, do note that at the moment of writing, it only supports Unix based systems. The reason for this is that Distill uses command line tools which are currently only available on Unix based systems.

Continue reading %Strategic Archive Extraction with Distill%

Link
Symfony CMF1.2 stable released (25.10.2014, 04:00 UTC)

Today we have finally tagged the last packages and with this the CMF has reached 1.2! Unfortunately we ended up setting HHVM as an allowed failure in our test setup due to an configuration issue on Travis CI with HHVM 3.3, however things were working fine with 3.2 and we expect once the configuration issue is resolved, things will work fine with 3.3 as well. All packages however are now also tested against PHP 5.6, as well as Symfony 2.5 and the upcoming 2.6.

In terms of features, as mentioned in a previous news item PHPCR ODM has seen significant improvements related to the events system, the native translation support and performance related to collections. Another big step forward is that we are now also compatible with the recently released SonataAdminBundle 2.3, which provides significant improvements especially on the UI side. Furthermore, DoctrinePHPCRBundle now optionally supports the PHPCR shell. Other than that most Bundles have seen incremental improvements related to developer experience, performance or minor feature additions.

Likely the biggest step forward for the CMF however is the first stable release of the RoutingAutoBundle and its underlying library. With it users can automate the creation of routes. For example when a new content document is created, a route document is automatically created. Or when a route is moved, a redirect from the previous location is created as well. This makes it a lot easier to leverage the ability of the CMF to keep the content and route structure separate, as it allows a best of both worlds approach where most route related work is automated, but it still is possible to easily manually manage the route structure independent of the content structure.

As always we are also thrilled to see a steady increased in contributors providing feedback, fixes and improvements small or large. In order to allow users to quickly get a taste of what is possible, we have of course also updated our demo to use the latest version of the sandbox. The documentation has also been updated to cover the new features.

Link
SitePoint PHPBuilding an Ad Manager in Symfony 2 (24.10.2014, 16:00 UTC)

Just this once won’t hurt - I am not going to write about Sass but Symfony. I had to do a little bit of backend at work and ended up with an interesting problem to solve, involving quite a lot of things so I thought it wouldn’t be such a bad idea to write an article about it.

But first, let me explain. The main idea was to build an ad manager. What the hell is an ad manager you say? Let’s say you have some places on your site/application to display ads. We do have things like this on our site, and one of our teams is (partially) dedicated to bringing those places to life with content.

Now for some boring reasons I won’t list here, we couldn’t use an existing tool, so we were doomed to build something from scratch. As usual, we wanted to do a lot without much coding, while keeping an overall simplicity for the end user (who is not a developer). I think we came up with a fairly decent solution for our little project.

Here are the features we set up:

  • YAML configuration + FTP access;
  • Either images, videos or HTML content;
  • Ability to customize cache duration;
  • Either sliders (yes, the pattern sucks) or random item in collection.

Continue reading %Building an Ad Manager in Symfony 2%

Link
Anthony FerraraWhat's In A Type (24.10.2014, 16:00 UTC)
There has been a lot of talk about typing in PHP lately. There are a couple of popular proposals for how to clean up PHP's APIs to be simpler. Most of them involve changing PHP's type system at a very fundamental level. So I thought it would be a good idea to talk about that. What goes into a type?

Read more »
Link
LinksRSS 0.92   RDF 1.
Atom Feed   100% Popoon
PHP5 powered   PEAR
ButtonsPlanet PHP   Planet PHP
Planet PHP