Survival Checklist

Recently, I somehow suspiciously often observe the most primitive, of the same type and rather easily solved problems on the most different web-projects. Different bases, different languages, different spheres of activity and monetization schemes. One thing unites them all - the slogan “business does not allow rewriting”. The ongoing or just completed stage of the rapid development of a project that is growing and aggressively squeezing competitors' market share gave birth to a huge pile of so-called. "Govnokoda". Doubtful architectural solutions either bring a lot of problems, or promise them in the future, but they work. The flow of new requirements does not give time to restore order, even in the infrastructure, not to mention the code. If this situation is familiar to you, welcome to cut down, learn something new and / or teach us. Whom to laugh, and to whom to cry.

“This is all only for Hyloda,” a thoughtful and insightful reader will say. Bad is a web project that does not dream of becoming a popular high-end.
')

Problems number 1: base.

The most unpleasant problems on any web-project are always connected with the database. We can easily scale everything else - from DNS-balancing to the upstream directive in the nginx config. “What about clustering?” The thoughtful reader will ask. That's the problem. I am watching a cluster of enforced databases for the third time. Twice MySQL and once MongoDB. Indexes are not configured, tables (collections - what's the difference?) Are not cleaned, but not cheap servers for the cluster are paid for. And basically, these servers are busy digging out unindexed data and building unused indexes in the name of entropy.

This group of problems is especially widespread in offices that practice the current trend of separating backend developers from admins / DevOps / NOC.

Why is it scary to keep the base in tired condition? Yes, because you lose the hell out of everything: orders, customers, SEO page rank. And why should the hosting provider overpay?

Personally, I, spoiled by the impoverished childhood, immediately have a cry from the heart: do not pay the hosting provider, pay me better.

Another of the beautiful things: if there is a tired, stormy base under your feet, and as a result - a few seconds response from the web server on almost all pages - to perform a performance instrument, carefully not touching the base.

The "n + 1" problem

It turns out that there are two major types of rape base, although a couple of months ago I personally did not suspect this, the most ridiculous of them. Have you heard of the "n + 1 problem"? I kind of remember something like that in deep junior childhood. In life, I would not have believed that something like that could break through on the sale of a commercial project. The easiest problem can be characterized by pseudocode:

list = db.query('SELECT * FROM products;')
for (item in list){
      orders = db.query('SELECT * FROM orders WHERE product_id = ?;', {product_id: product.id});
      ...
}

. access log - query log , . 50MB access log 20GB query log — . — , - , - .

: webdev — . .

go- . , , ActiveRecord . php js .

:

SELECT p.*, ( SELECT count(*) FROM comments WHERE product_id = p.id) comment_count FROM products p WHERE author_id = ?;

, — . «n+1». LIMIT. JOIN ( GROUP BY) — , — . — . , ORM . — .

- - .

: , , dev.mysql.com/doc/mysql-utilities/1.5/en/mysqlindexcheck.html www.percona.com/doc/percona-toolkit/2.1/pt-duplicate-key-checker.html, , . — , . — unindexed query log ( , ) . — . .

— . , , . MySQL, EXPLAIN SELECT «ROWS examined», query log (long_query_time = 0). , . , , sum(Rows Examined) — . — 95- «Rows Examined» vs «Rows Sent». . www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html. — . — , , .
. . — , , , « RDB — RDB ». .

 SELECT FOR UPDATE ... SKIP LOCKED; 

PostgreSQL, .

php-. — « » PHP.

, — ? — , . №1 . , - . - , , . , .

: c zip? ? mysqldump? backup?
: , - « », ALTER TABLE - . ? ? ?

10 , . , , 3- — . - . - .

- . , error . , parent_id IN( id).

- . . , - php ? ? — .

, -, / , . — .

«» - — CPU, bandwidth, RAM. , — munin/monit/sa+sar/htop / — .

- . — . UPDATE- , SELECT ( , ) , . PostgreSQL immutable tuples UPDATE .
UP1: terrier PostgeSQL , . Uber . . ?

MySQL . - , . ?

: products2, . . - - — .

: , , () . , , « , ». — bottleneck / .

: ,

№2 —

, , . — .

, — . - , - . — - .

. CodeSniffer code review code quality - «» .

— : - code review. over-pattern-usage. ? , . — . Dependency Injection — , - / , ZF 1.x. — . Repository + Entity — driven db access 100% legacy . , stateless — . ActiveRecord, .
, , SOLID . SOLID — OOP. , - .

: , (, ?) .

: — . , .

, . , ? — , -. Webdev — . apache? .htaccess- ?

№3 —

— . this. , - — / JQuery c ExtJS, / MVC , backbone.js. — , , .

: , es2015 javscript. , jQuery . . , . . , TypeScript JS: , -. .

№4 —

- . , , , . — . , / , / . . , .

- . , . : . .

. ? ?

-. ? zero-time-deployment .

zero-time-deployment. , downtime ?

, . , Jenkins , / / / / / / ? ? , .

-? deny from all ? ?

, . !

. …

.

, .

№0: .

. , , CAP- IT-. .

: , , , . — , . , , — . « ?».

: . .

№1:

. — , , / , , . « . ».

: . , . . — , .

« » — , . . bottleneck?

: .

, — sucks. Back in USSR, . . .

— . - . — , JIRA / Redmine / .

— . , , — . , — . , , « »?

, / — .