Author: Tom

Index? Yes, please

by Tom

When altering some tables, I got into optimization adding indexes.

I have one table – users

mysql> select count(1) from users;
+----------+
| count(1) |
+----------+
|   389900 |
+----------+
1 row in set (0.08 sec)

and doing one query:

mysql> SELECT COUNT(*) FROM `users`  WHERE (last_active_at > '2014-01-07 00:17:42') AND (last_logoff <> last_active_at or last_logoff is null);
+----------+
| COUNT(*) |
+----------+
|      913 |
+----------+
1 row in set (0.23 sec)

and slow-log complains

# Schema: test  Last_errno: 0  Killed: 0
# Query_time: 0.365658  Lock_time: 0.000060  Rows_sent: 1  Rows_examined: 389894  Rows_affected: 0
# Bytes_sent: 65
SET timestamp=1389055417;
SELECT COUNT(*) FROM `users`  WHERE (last_active_at > '2014-01-07 00:23:36') AND (last_logoff <> last_active_at or last_logoff is null);

from his you can see it did full table scan (Rows_examined: 389894) which is kinda wrong.

so, let’s explain

mysql> explain SELECT COUNT(*) FROM `users`  WHERE (last_active_at > '2014-01-07 00:17:42') AND (last_logoff <> last_active_at or last_logoff is null);
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL | 385645 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

Hmm, “type” shows no index has been used and ALL table scanned. WRONG

Let’s add an index – we have two fields, last_active_at and last_logoff, let’s try with last_logoff only

mysql> alter table users add index ix_last_logoff (last_logoff);
Query OK, 0 rows affected (2.16 sec)
mysql> explain SELECT COUNT(*) FROM `users`  WHERE (last_active_at > '2014-01-07 00:17:42') AND (last_logoff <> last_active_at or last_logoff is null);
+----+-------------+-------+------+----------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys  | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+----------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | users | ALL  | ix_last_logoff | NULL | NULL    | NULL | 385646 | Using where |
+----+-------------+-------+------+----------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

hmm, still using full table scan. Let’s drop and create combined index

mysql> alter table users drop index ix_last_logoff;
Query OK, 0 rows affected (1.40 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table users add index ix_last_logoff (last_logoff, last_active_at);
Query OK, 0 rows affected (2.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

and what our explain friend shows now?

mysql> explain SELECT COUNT(*) FROM `users`  WHERE (last_active_at > '2014-01-07 00:17:42') AND (last_logoff <> last_active_at or last_logoff is null);
+----+-------------+-------+-------+----------------+----------------+---------+------+--------+--------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows   | Extra                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | users | index | ix_last_logoff | ix_last_logoff | 12      | NULL | 385646 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) FROM `users`  WHERE (last_active_at > '2014-01-07 00:17:42') AND (last_logoff <> last_active_at or last_logoff is null);
+----------+
| COUNT(*) |
+----------+
|      913 |
+----------+
1 row in set (0.23 sec)

Oops. Explain shows it’s using index (bit better than ALL), but still fetches 385646 rows. Still wrong. Let’s switch columns in index.

mysql> alter table users drop index ix_last_logoff;
Query OK, 0 rows affected (3.95 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table users add index ix_last_logoff (last_active_at, last_logoff);                                                         
Query OK, 0 rows affected (3.81 sec)
Records: 0  Duplicates: 0  Warnings: 0

What explain says now?

mysql> explain SELECT COUNT(*) FROM `users`  WHERE (last_active_at > '2014-01-07 00:17:42') AND (last_logoff <> last_active_at or last_logoff is null);
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | users | range | ix_last_logoff | ix_last_logoff | 6       | NULL |  963 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

Great! and select?

mysql> SELECT COUNT(*) FROM `users`  WHERE (last_active_at > '2014-01-07 00:17:42') AND (last_logoff <> last_active_at or last_logoff is null);
+----------+
| COUNT(*) |
+----------+
|      924 |
+----------+
1 row in set (0.00 sec)

0.20secs improvement.

Explanation? It’s simple. last_active_at is doing range scan against a value and against second column, thus first referenced. If last_logoff is first in combined index, it can’t be used for last_active_at > part of the query.

redis sentinel with ruby (on rails)

by Tom

In the last article I introduced how to install and use redis sentinel. As I’m using ruby, I need to use this new redis configuration with ruby (on rails).

For ruby on rails use redis-sentinel gem.

Then your redis initializer will look like

sentinels = [
  { host: '10.0.0.1', port: 17700 },
  { host: '10.0.0.2', port: 17700 },
  { host: '10.0.0.3', port: 17700 },
  { host: '10.0.0.4', port: 17700 }
]
# redis master name from sentinel.conf is 'master'
Redis.current = Redis.new(master_name: 'master', sentinels: sentinels)

You can use your redis then as usual.

When using sidekiq, configuration is pretty simple too

require 'sidekiq/web'
require 'redis-sentinel'
require 'sidetiq/web'

rails_root = ENV['RAILS_ROOT'] || File.dirname(__FILE__) + '/../..'
rails_env = ENV['RAILS_ENV'] || 'development'

sentinels = [
  { host: '10.0.0.1', port: 17700 },
  { host: '10.0.0.2', port: 17700 },
  { host: '10.0.0.3', port: 17700 },
  { host: '10.0.0.4', port: 17700 }
]

redis_conn = proc { 
  Redis.current = Redis.new(master_name: 'master', sentinels: sentinels) 
}
redis = ConnectionPool.new(size: 10, &redis_conn)

Sidekiq.configure_server do |config|
  config.redis = redis
end

Sidekiq.configure_client do |config|
  config.redis = redis
end

You can test your configuration. Run rails console and test with 

Loading production environment (Rails 3.2.16)
1.9.3p448 :001 > Redis.current.keys("*").count
 => 746
1.9.3p448 :002 > Redis.current
 => #<Redis client v3.0.5 for redis://10.0.0.2:6379/0>

if you see “127.0.0.1:6379”, something is probably wrong. Then try to set/get some key and check Redis.current once again.

redis sentinel setup

by Tom

Prerequisities

  • multiple clients with redis 2.8.2+ installed

Do I need sentinel? If you want to have some kind of redis failover (there’s no cluster yet) – yes. Sentinels continuously monitor every redis instance and change configuration of given redis node(s) – if specified number of sentinels decided whether master is down, then they elect and promote new master and set other nodes as a slave of this master.

Looks interesting? Yes. It is. But. There’s a little time gap between electing and switching to the new master. You have to resolve this on application level.

Basically. Initial setup expects all nodes running as a master, with manual set slaveof ip port in redis-cli on meaned redis slaves. Then run sentinel and it does the rest.

sample redis configururation files follow:

daemonize yes
pidfile /usr/local/var/run/redis-master.pid
port 6379
bind 10.0.0.1
timeout 0
loglevel notice
logfile /opt/redis/redis.log
databases 1
save 900 1
save 300 10
save 60 10000
stop-writes-on-bgsave-error yes
rdbcompression yes
rdbchecksum yes
dbfilename master.rdb

dir /usr/local/var/db/redis/
slave-serve-stale-data yes
slave-read-only no
slave-priority 100
maxclients 2048
maxmemory 256mb

# act as binary log with transactions
appendonly yes

appendfsync everysec
no-appendfsync-on-rewrite no
auto-aof-rewrite-percentage 100
auto-aof-rewrite-min-size 64mb
lua-time-limit 5000
slowlog-log-slower-than 10000
slowlog-max-len 128
hash-max-ziplist-entries 512
hash-max-ziplist-value 64
list-max-ziplist-entries 512
list-max-ziplist-value 64
set-max-intset-entries 512
zset-max-ziplist-entries 128
zset-max-ziplist-value 64
activerehashing yes

client-output-buffer-limit normal 0 0 0
client-output-buffer-limit slave 256mb 64mb 60
client-output-buffer-limit pubsub 32mb 8mb 60

and sentinel configuration file:

port 17700
daemonize yes
logfile "/opt/redis/sentinel.log"

sentinel monitor master 10.0.0.1 6379 2
sentinel down-after-milliseconds master 4000
sentinel failover-timeout master 180000
sentinel parallel-syncs master 4

Start all of your redis nodes with redis config and choose master. Then run redis console and set all other nodes as a slave of given master, using command slaveof 10.0.0.1 6379. Then you can connect to your master and verify, if there are all of your slave nodes, connected and syncing – run info command in your master redis console. Output should show you something like this

- snip -

# Replication
role:master
connected_slaves:3
slave0:ip=10.0.0.2,port=6379,state=online,offset=17367254333,lag=1
slave1:ip=10.0.0.3,port=6379,state=online,offset=17367242971,lag=1
slave2:ip=10.0.0.4,port=6379,state=online,offset=17367222381,lag=1

- snip-

To test, if your sentinel works, just shutdown your redis master and watch sentinel log. You should see something like this

[17240] 04 Dec 07:56:16.289 # +sdown master master 10.24.37.144 6379
[17240] 04 Dec 07:56:16.551 # +new-epoch 1386165365
[17240] 04 Dec 07:56:16.551 # +vote-for-leader 185301a20bdfdf1d5316f95bae0fe1eb544edc58 1386165365
[17240] 04 Dec 07:56:17.442 # +odown master master 10.0.0.1 6379 #quorum 4/2
[17240] 04 Dec 07:56:18.489 # +switch-master master 10.0.0.1 6379 10.0.0.2 6379
[17240] 04 Dec 07:56:18.489 * +slave slave 10.0.0.3:6379 10.0.0.3 6379 @ master 10.0.0.2 6379
[17240] 04 Dec 07:56:18.490 * +slave slave 10.0.0.4:6379 10.0.0.4 6379 @ master 10.0.0.2 6379
[17240] 04 Dec 07:56:28.680 * +convert-to-slave slave 10.0.0.1:6379 10.0.0.1 6379 @ master 10.0.0.2 6379

explained line by line

+sdown master master 10.24.37.144 6379

master is subjectively down (maybe)

+odown master master 10.0.0.1 6379 #quorum 4/2

master is objectively down (oh, really), two of four sentinels have the same opinion

+switch-master master 10.0.0.1 6379 10.0.0.2 6379

so we switch to another master – chosen 10.0.0.2

+slave slave 10.0.0.3:6379 10.0.0.3 6379 @ master 10.0.0.2 6379

reconfigure 10.0.0.3 as a slave of new master 10.0.0.2

+convert-to-slave slave 10.0.0.1:6379 10.0.0.1 6379 @ master 10.0.0.2 6379

sorry, former master, you have to serve as a slave now

+sdown, -odown? + means ‘is’, – means ‘is no longer’. Then “+sdown” can be translated as “is subjectively down” and “-odown” like “is no longer objectively down”. Simple, huh? :)

PS: take my configuration files as a sample. Feel free to modify to match your need and check redis/sentinel configuration docs to get deeper knowledge about configuration options.

Kaštanový koláč Gordona Ramseye

by Tom

Ingredience

  • 250g loupaných, vařených kaštanů
  • 250g kvalitní hořké čokolády
  • 250g másla, nesoleného
  • 125g cukru (krupice, krystal)
  • 4 vejce
  • 125ml smetany
  • 125ml plnotučného mléka

Příprava je primitivní. Ale musí se dělat pečlivě :)

Máslo a čokoládu rozehřejeme ve vodní lázni. Cukr utřeme se žloutky. Smetanu, mléko a nakrájené kaštany dáme vařit – jakmile směs přejde do varu, sundáme a rozmixujeme. Ušleháme sníh ze 4 bílků. Čokoládovou směs, cukr a kaštany smícháme a vymícháme dohladka. Opatrně vmícháme ušlehaný sníh. Dáme péct do vyhřáté trouby (170oC) na 20-30minut.

Po pečení vyndáme, necháme chvíli chladnout – dort se stáhne a popraská – a podáváme buď ještě teplé nebo necháme úplně vychladnout.

Pár tipů:
Pokud máte vejce v lednici, nechte je pár hodin ohřát a stabilizovat se. Sníh musí být lesklý a pevný – ideálně šlehat v kovové misce, přidat špetku soli.

20131219-222524.jpg

rails + passenger + nginx maintenance mode

by Tom

I need to add maintenance page to some rails app, running with passenger and nginx. Here’s some config and steps.

You just need to add static html file to app_root/public/maintenance.html – and I assume css files on /assets url.

so, here’s nginx config:

server {
  listen 80;
  server_name = www.example.com;
  root /home/deploy/www.example.com/public;
  passenger_enabled on;
  passenger_min_instances 5;

  set $maintenance 0;

  # is there maintenance file set?
  if (-f $document_root/../tmp/maintenance.txt) {
    set $maintenance 1;
  }

  # exclude /assets
  if ( $uri ~* ^/assets\/\.* ) {
    set $maintenance 0;
  }

  # in maintenance mode - send 503 status
  if ($maintenance = 1) {
    return 503;
  }

  # maintenance mode
  error_page 503 @503;

  # rewrite everything to maintenance.html
  location @503 {
    rewrite ^ /maintenance.html last;
    break;
  }
}

setting maintance mode is really simple – set app_root/tmp/maintenance.txt file – when escaping, just remove that file.

Need to split big SQL dump into separate databases?

by Tom

I do. So I’ve written small bash script to do so. I think it’s self explanatory and does it’s job well :)

#!/bin/bash

if [[ $# -lt 1 ]]; then
  echo "Usage: $0 filename"
  exit 1
fi

FILE=$1
echo "Spliting "$FILE""

# default filename for sql headers
dbname=header

cat $FILE | while read line; do
 # echo $line
  if [[ $line =~ ^USE\ \`([^\`]*)\`\; ]]; then
    dbname=${BASH_REMATCH[1]}
    echo "Found db '$dbname'"
  fi
  echo $line >> $dbname.sql
done

I do NOT have CREATE DATABASE in sql file, thus set to USE..

Change image in UIImageView – RubyMotion

by Tom

I needed to change image in my UIImageView, but simply setting new image using myImageView.setImage didn’t work. There’s simple workaround

@button = UIView.alloc.initWithFrame(frame)
buttonImage = UIImageView.alloc.initWithFrame(@button.bounds)
buttonImage.setTag(1) # set any number you want
buttonImage.autoresizingMask = UIViewAutoresizingFlexibleWidth | UIViewAutoresizingFlexibleHeight
@button.addSubview buttonImage

and now set the image

self.button.viewWithTag(1).setImage(UIImage.imageNamed('answer_bar_white.png').resizableImageWithCapInsets(UIEdgeInsetsMake(18, 18, 18, 18)))

you can remove resizableImageWithCapInsets – I’m using square images to make UIImageView of any size.

Blink LED2 – LPC1769

by Tom

LPCXpresso finally beaten. Installed new v5, imported all the CMSIS needed and setup brand new, my very first, MCU example code :) I’m using LPC1769 sample board from Embedded Artists. 

#ifdef __USE_CMSIS
#include "LPC17xx.h"
#endif

#include <cr_section_macros.h>
#include <NXP/crp.h>

// Variable to store CRP value in. Will be placed automatically
// by the linker when "Enable Code Read Protect" selected.
// See crp.h header for more information
__CRP const unsigned int CRP_WORD = CRP_NO_CRP ;

int main(void) {
    // Set P0_22 to 00 - GPIO
    LPC_PINCON->PINSEL1 &= (~(3 << 12));
    // Set GPIO - P0_22 - to be output
    LPC_GPIO0->FIODIR |= (1 << 22);

    volatile static uint32_t i;
    while (1) {
        LPC_GPIO0->FIOSET = (1 << 22); // Turn LED2 on
        for (i = 0; i < 1000000; i++);
        LPC_GPIO0->FIOCLR = (1 << 22); // Turn LED2 off
        for (i = 0; i < 1000000; i++);
    }
    return 0;
}

and the same in assembler (from http://pygmy.utoh.org/riscy/cortex/led-lpc17xx.html)

;;; led-lpc17xx.asm
;;; written by Frank Sergeant
;;;    frank@pygmy.utoh.org
;;;    http://pygmy.utoh.org/riscy
;;; This program is in the public domain.  See http://pygmy.utoh.org/riscy/cortex/
;;; for notes about the program and how to assemble, link, and burn to flash.

;;; Blink the LED on the LPCXpresso LPC1769 ARM Cortex M3 board
;;; (or any LPC17xx ARM board with perhaps minor modifications).

;;; The LED on the Xpresso board is labeled LED2 and is just to the
;;; left of (inside of) J6-36.  It is connected to P0.22. The LED is
;;; on when P0.22 is high.
        
;;; Directives
        .thumb                  ; (same as saying '.code 16')
        .syntax unified

;;; Equates

        .equ LED_MASK, 0x00400000 ; i.e., bit 22
        
        .equ PINSEL0,  0x4002C000
        .equ PINSEL1,  0x4002C004

        .equ FIO0DIR,      0x2009C000 ; port direction, 0 (default) = input
        .equ FIO0MASK,     0x2009C010 ; port direction, 0 (default) = input
        .equ FIO0PIN,      0x2009C014
        .equ FIO0SET,      0x2009C018
        .equ FIO0CLR,      0x2009C01C
        
        .equ STACKINIT,   0x10004000

        .equ LEDDELAY,    300000

.section .text
        .org 0

;;; Vectors
vectors:
        .word STACKINIT         ; stack pointer value when stack is empty
        .word _start + 1        ; reset vector (manually adjust to odd for thumb)
        .word _nmi_handler + 1  ;
        .word _hard_fault  + 1  ;
        .word _memory_fault + 1 ;
        .word _bus_fault + 1    ;
        .word _usage_fault + 1  ;

_start:

        ldr r6, = PINSEL1
        ;; set P0.22 as a GPIO pin
        ;; P0.22 is controlled by bits 13:12 of PINSEL1
        ;; xxxx xxxx xxxx xxxx xx11 xxxx xxxx xxxx
        ;;    0    0    0    0    3    0    0    0

        ldr r0, [r6]
        bic r0, r0, # 0x00003000  ; clear bits 13:12 to force GPIO mode
        str r0, [r6]


        ;; set LED output pin (i.e. P0.22) as an output
        ldr r6, = FIO0DIR             ; for PORT0
        mov r0, # LED_MASK            ;  all inputs except for pin 22
        str r0, [r6]
        
        ;; r0 still contains LED_MASK 
        ldr r5, = FIO0CLR
        ldr r6, = FIO0SET

loop:
        str r0, [r5]            ; clear P0.22, turning off LED
        ldr r1, = LEDDELAY
delay1:
        subs r1, 1
        bne delay1

        str r0, [r6]            ; set P0.22, turning on LED
        ldr r1, = LEDDELAY
delay2:
        subs r1, 1
        bne delay2

        b loop                 ; continue forever

_dummy:                        ; if any int gets triggered, just hang in a loop
_nmi_handler:
_hard_fault:
_memory_fault:
_bus_fault:
_usage_fault:
        add r0, 1
        add r1, 1
        b _dummy