Vacations and IBM DB2 Bootcamp

Long time no new posts, but I was on my vacations, and soon after that I’ve attended in IBM DB2 bootcamp organized in Cracow.
These bootcamps are really nice move from IBM.
Lots of new informations in very short time, interesting lectures and labs.
You can check on official page for some more details : http://www.ibm.com/developerworks/data/bootcamps
they organize them all over the world and it’s totaly free.
What is more, you can try to pass certificate during this course (with no charge).
 I’ve passed 000-N18 and 000-610 (DB2 10.1 Fundamentals) ; )

Memcached tutorial part 2 - stats

In this part I'm gonna move on with some administration issues. First of all we to check on which port memcached is running. There are two ways :
1. Check the config file vim /etc/memcached.conf

# memory
-m 128

2. Check system processlist ps aux | grep memcached

/usr/bin/memcached -m 128 -p 11211 -u nobody -l 127.0.0.1

Ok so when we are sure that deamon is running we can telnet (or use nc) to connect

nc 127.0.0.1 11211

The basic command to list statistic is "stats"

stats
STAT pid 1574 // process id 
STAT uptime 3866 // deamon uptime
STAT time 1344373852 // server uptime 
STAT version 1.4.2 //version of memcached
STAT pointer_size 32 //size of pointer in bits 
STAT rusage_user 0.008000 //
STAT rusage_system 0.024001
STAT curr_connections 6 //number of current open connections
STAT total_connections 19 //number of total connection to server 
STAT connection_structures 8
STAT cmd_get 12 //Total numer of 'get' commands 
STAT cmd_set 4 // Total number of 'set' commands 
STAT cmd_flush 0 // Total number of 'flush' commands 
STAT get_hits 12 // total number of requests that return value
STAT get_misses 6 // total number of missed requests 
STAT delete_misses 0
STAT delete_hits 0
STAT incr_misses 0
STAT incr_hits 0
STAT decr_misses 0
STAT decr_hits 0
STAT cas_misses 0
STAT cas_hits 0
STAT cas_badval 0
STAT bytes_read 1040 //total number of bytes returned by sevrer 
STAT bytes_written 6143 //total number of bytes saved by sevrer
STAT limit_maxbytes 134217728 //memory limit
STAT accepting_conns 1
STAT listen_disabled_num 0
STAT threads 4
STAT conn_yields 0
STAT bytes 382 //number of bytes used by current items (curr_items)
STAT curr_items 4 // current number of items stored in memory
STAT total_items 5 //total number of items stored from the beginning
STAT evictions 0

To get more details visit mysql manual page.
Next useful command is stats items We can see informations about given slab.

curr_item = 7 
...
..
stats items 
STAT items:1:number 3 //total number of items
STAT items:1:age 2118 //age of the oldest item in slab
STAT items:1:evicted 0
STAT items:1:evicted_nonzero 0
STAT items:1:evicted_time 0
STAT items:1:outofmemory 0
STAT items:1:tailrepairs 0
STAT items:3:number 4
STAT items:3:age 3832
STAT items:3:evicted 0
STAT items:3:evicted_nonzero 0
STAT items:3:evicted_time 0
STAT items:3:outofmemory 0
STAT items:3:tailrepairs 0

So we can see that in slab 1 there are 3 objects and 4 items in slab 3
To understand next command stats slabs we first have to know what slab is . Think about slab like a partition. Memcached writes data to diffrent partition (slabs), based on the data size, so that memory allocation is more optimal In this example we can se that we have two slabs 1 and 3. Each slab have unique id

stats slabs

STAT 1:chunk_size 80
STAT 1:chunks_per_page 13107
STAT 1:total_pages 1
STAT 1:total_chunks 13107
STAT 1:used_chunks 3
STAT 1:free_chunks 1
STAT 1:free_chunks_end 13103
STAT 1:mem_requested 168
STAT 1:get_hits 0 //
STAT 1:cmd_set 15
STAT 1:delete_hits 0
STAT 1:incr_hits 0
STAT 1:decr_hits 0
STAT 1:cas_hits 0
STAT 1:cas_badval 0

STAT 3:chunk_size 136
STAT 3:chunks_per_page 7710
STAT 3:total_pages 1
STAT 3:total_chunks 7710
STAT 3:used_chunks 4
STAT 3:free_chunks 0
STAT 3:free_chunks_end 7706
STAT 3:mem_requested 434
STAT 3:get_hits 33 //total number of get commands 
STAT 3:cmd_set 4 // total numer of sets commands 
STAT 3:delete_hits 0
STAT 3:incr_hits 0
STAT 3:decr_hits 0
STAT 3:cas_hits 0
STAT 3:cas_badval 0

STAT active_slabs 2 //number of active slabs 
STAT total_malloced 2097120//total amount of allocated memory by all slabs

If we want to see detailed view of stored objects we can use command "stats cachedump [slab id ] [numer of items ,0 - all]"

stats cachedump 1 0
ITEM duaa [5 b; 1344369986 s]
ITEM du [2 b; 1344369986 s]
ITEM dupa [4 b; 1344369986 s]
END
stats cachedump 3 0
ITEM bb3c75a2fe760dda6df236766eec90d3 [27 b; 1344369986 s]
ITEM dbf8adc42d9c4a0bd67da4702b45d059 [26 b; 1344369986 s]
ITEM 0cef10de0f9ba7b0231665da6aa4ee85 [27 b; 1344369986 s]
ITEM ced34df387fd279cdf1c78989c44f7c2 [26 b; 1344369986 s]

To read value we can use "get [key_name]" command :

get ced34df387fd279cdf1c78989c44f7c2
VALUE ced34df387fd279cdf1c78989c44f7c2 4 26
a:1:{s:5:"count";s:1:"7";}

To delete key use "delete [key_name]"

delete ced34df387fd279cdf1c78989c44f7c2
DELETED

So there are the most popular ways to view memcached statistic. In next part we gonna se some gui tools in action ; )

Memcached tutorial part 1 - installation , examples

If you don't know what memcached is, ask uncle google ; ) In short it's a memory caching system that allows to reduce database load by putting values into RAM. Ok so installation on debian/ubuntu is as simple as that :

sudo apt-get install memcached php5-memcached

Afret installation you can check if memcached is working:

netstat -tap | grep memcached

You should get something like this:

tcp        0      0 localhost:11211         *:*                     LISTEN      1547/memcached 

By default deamon is listening on port 11211 You can change it by modifying config file : /etc/memcached.conf If everything works fine we can try it out :) You put value in cache by using 'set' method to read the value you have to know the key name and use 'get' method.

m = new Memcached;
$m->addServer('127.0.0.1', 11211);

$foo = "foo";

$m->set('f',$foo);
var_dump($m->get('f'));

$m->delete('f');
var_dump($m->get('f'));

output:

string(3) "foo"
bool(false)

Ok so the same with query :

$sql = "select count(id) as count from test.test";
$res = mysql_query($sql);
$row = mysql_fetch_assoc($res);
$count = $row['count'];
var_dump($count);
$m->set(md5($sql),$count);

var_dump($m->get(md5($sql)));

output:

string(1) "7"
string(1) "7"

So now we can create simple caching function :

function memQuery($sql,$m){

    $key = md5($sql);
    var_dump($m->get($key));

    if($m->get($key)===FALSE){

        echo "mysql:";
        $res = mysql_query($sql);
        $row = mysql_fetch_assoc($res);
        $m->set($key,$row);//store the result
        return $row;

    }else{
        echo "mysql:";
        return $m->get($key);
    }
}

In next part I'm gonna show some administration issues.

Mysql cache timeout

Important info I've found today:

MySQL's query cache destroys the entire cache for a given table whenever that table is changed. On a high-traffic site with updates happening many times per second, this makes the the cache practically worthless. In fact, it's often harmful to have it on, since there's a overhead to maintain the cache.

source
soon more about caching ;)

Mysql Can't create table (errno: 150)

Ok so we get this error when creating foreign key . So what now ?

create table users (
id_user int(11) unsigned not null primary key auto_increment, 
name varchar(64)
)Engine=InnoDB;

insert into users set name='Admin';


create table test_relation (
id int(11) unsigned NOT NULL primary key AUTO_INCREMENT, 
id_user int(10)  not null ) 
engine=InnoDB;

alter table test_relation add foreign key(id_user) references users(id_user);

Can't create table 'test.#sql-440_ba' (errno: 150)

This message says complete nothing ; ) To get more detailed information use

mysql> show innodb status;

and locate "LATEST FOREIGN KEY ERROR" part.

120717 15:39:19 Error in foreign key constraint of table test/#sql-440_ba:
foreign key(id_user) references test.users(id_user):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.

Yeah, so we got info that there is something wrong with column types. Quick debug:

mysql> alter table test_relation modify  id_user  int(10) unsigned not null;
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table test.test_relation add foreign key(id_user) references test.users(id_user);
Query OK, 0 rows affected (0.29 sec)
Records: 0  Duplicates: 0  Warnings: 0

and voila works perfect ; )

Sphinx part 3 - Php api

Ok this is third and probably the last part about sphinx search engine. So I'm gonna show how to use sphinx api with php. I'm gonna use the same database as in previous tutorials (articles)

mysql> select * from articles;
+------------+------------------------+-----------------------------------------+---------+
| id_article | title                  | content                                 | ratings |
+------------+------------------------+-----------------------------------------+---------+
|          1 | Euro 2012 in Poland    | some content about euro 2012            |       5 |
|          2 | Second article         | bla dog cat house                       |       2 |
|          3 | Euro 2012 last results | Poland loses to czech republic          |       4 |
|          4 | Fourth article         | lorem ipsum black yellow red blue green |       1 |
+------------+------------------------+-----------------------------------------+---------+

and config : sudo vim /etc/sphinxsearch/articles.conf

source src_articles
{
        type            = mysql
        sql_host        = localhost
        sql_user        = root
        sql_pass        = krzysiek2000
        sql_db          = test

        sql_attr_uint = ratings


        sql_query =  select id_article,title,content,ratings from test.articles;
        sql_query_info = select id_article,title,content,ratings 
from test.articles where id_article = $id;
}


index index_articles
{
        source = src_articles
        path = /var/lib/sphinxsearch/data/index_articles
        docinfo = extern
        charset_type = utf-8
}

indexer
{
    mem_limit = 32m
}

searchd
{

        port  = 3312
        log = /var/log/searchd/searchd.log
        query_log = /var/log/searchd/query.log
        pid_file = /var/log/searchd/searchd.pid

}

First index data:

indexer -c /etc/sphinxsearch/articles.conf index_articles

Let's start the searchd deamon

sudo searchd -c /etc/sphinxsearch/articles.conf

Check is it working :

ps aux | grep search
root      4366  0.0  0.0  14384  1020 pts/1    S    23:37   0:00 searchd -c /etc/sphinxsearch/articles.conf

Great, so quick test :

search -c /etc/sphinxsearch/articles.conf Poland
displaying matches:
1. document=1, weight=1557, ratings=5
 id_article=1
 title=Euro 2012 in Poland
 content=some content about euro 2012
 ratings=5 
2. document=3, weight=1557, ratings=4
 id_article=3
 title=Euro 2012 last results
 content=Poland loses to czech republic
 ratings=4

Everything seems to be fine ;) So now let's do the same search but we gonna use php Use can find sphinxapi.php file on offical site http://sphinxsearch.com/downloads/release/ Just download the source package. There is also api to ruby,python and java.

SetServer('localhost', 3312);
$client->SetConnectTimeout(1);
$client->SetArrayResult(true);

// Query the index
$results = $client->Query('Poland');

//output the result 
print_r($results['matches']);

?>

php articles.php 
Array
(
    [0] => Array
        (
            [id] => 1
            [weight] => 1
            [attrs] => Array
                (
                    [ratings] => 5
                )

        )

    [1] => Array
        (
            [id] => 3
            [weight] => 1
            [attrs] => Array
                (
                    [ratings] => 4
                )

        )

)

Perfect ! So now when we have the results we can foreach on array to get details data

foreach($results['matches'] as $i=>$arr){

    $id_article = $arr['id'];
    //select data about specific article
}

And that's all Of course what I show is just a little percentage of what offers you sphinx. If you are interested in the topic, ask uncle google about more information ; )

How to check linux ports

So here's quick tip. How to find out what process has which port. First logon root and to open list port use :

lsof -i

or use netstat

netstat -tulpn

Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 0.0.0.0:1551            0.0.0.0:*               LISTEN      2443/sh         
tcp        0      0 0.0.0.0:1553            0.0.0.0:*               LISTEN      2445/kadu       
tcp        0      0 127.0.0.1:631           0.0.0.0:*               LISTEN      1739/cupsd      
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      1261/postgres   
tcp        0      0 0.0.0.0:25              0.0.0.0:*               LISTEN      1687/master     
tcp        0      0 0.0.0.0:9306            0.0.0.0:*               LISTEN      1201/searchd    
tcp        0      0 0.0.0.0:9312            0.0.0.0:*               LISTEN      1201/searchd    
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      1232/mysqld     

To se what processs is bound to let say 3305:

root@work:~# lsof -i :3306

COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  1250 mysql   10u  IPv4   5421      0t0  TCP localhost:mysql (LISTEN)

Now let's take a closer look at proces id (PID)

root@work:~# ls -l /proc/1250/exe
lrwxrwxrwx 1 root root 0 2012-06-25 19:18 /proc/1250/exe -> /usr/sbin/mysqld

And that's all ; )

Sphinx part 2 – how the search engine works

Ok in this part we gonna use this simple table:

create table articles( 
id_article int unsigned not null primary key auto_increment, 
title varchar(64) not null, 
content text not null, 
ratings int unsigned 
);

insert into articles set title='Euro 2012 in Poland',content='some content about euro 2012',ratings=5; 
insert into articles set title='Second article',content='bla dog cat house',ratings=2; 
insert into articles set title='Euro 2012 last results',content='Poland loses to czech republic',ratings=4; 
insert into articles set title='Fourth article',content='lorem ipsum black yellow red blue green',ratings=1; 

Ok now the config file (I'm gonna use the very basic configuration) By default the deamon is running, so we have to stop it

sudo /etc/init.d/sphinxsearch stop 

Ok so first of all we to index our data sudo indexer -c /etc/sphinxsearch/articles.conf index_articles

Sphinx 2.0.4-id64-release (r3135) 
Copyright (c) 2001-2012, Andrew Aksyonoff 
Copyright (c) 2008-2012, Sphinx Technologies Inc (http://sphinxsearch.com) 

using config file '/etc/sphinxsearch/articles.conf'... 
indexing index 'index_articles'... 
WARNING: Attribute count is 0: switching to none docinfo 
collected 4 docs, 0.0 MB 
sorted 0.0 Mhits, 100.0% done 
total 4 docs, 187 bytes 
total 0.008 sec, 21623 bytes/sec, 462.53 docs/sec 
total 2 reads, 0.000 sec, 0.2 kb/call avg, 0.0 msec/call avg 
total 6 writ
es, 0.000 sec, 0.2 kb/call avg, 0.0 msec/call avg 

We see that 4 records have been indexed. So now let's do the basic search : We want to find all artciles that contains word 'euro'

search -c /etc/sphinxsearch/articles.conf euro
using config file '/etc/sphinxsearch/articles.conf'... 
index 'index_articles': query 'euro ': returned 2 matches of 2 total in 0.000 sec 

displaying matches: 
1. document=1, weight=2578 
2. document=3, weight=1557 

We see that we found two records with id 1 and 3. Ok now let's search the word house

search -c /etc/sphinxsearch/articles.conf house
displaying matches: 
1. document=2, weight=1695 

so that was easy . No we gonna modify out configuration file a little bit: Next thing is filtering by attributes. To our source section in config file we add sql_attr_uint attribute

 source src_articles
{
        type            = mysql
        sql_host        = localhost
        sql_user        = root
        sql_pass        = krzysiek2000
        sql_db          = test
        sql_attr_uint = ratings

        sql_query =  select id_article,title,content,ratings from test.articles;
        sql_query_info = select id_article,title,content,ratings from test.articles where id_article = $id;
}

search -c /etc/sphinxsearch/articles.conf -f ratings 5 euro
displaying matches: 
1. document=1, weight=2578, ratings=5 
 id_article=1 
 title=Euro 2012 in Poland 
 content=some content about euro 2012 
 ratings=5 

We perform a query that returns rows containg word 'euro' where rating equals 5. In next , we gonna perform similar searches with php api.

Sphinx part 1 - basic installation and config

Long time no see ;) I was quite busy at work and in free time I learn about one awsome thing called sphinx search server http://sphinxsearch.com/
Starting from now , next few post gonna by about sphinx.It's pretty awesome full-text search engine.
I'm only gonna said that on table containing ~40 milion record (with partitions, right indexing and so on) classic mysql full-text search last about 10-15s. When I do this with sphinx it's less than 1s :)
So let's get started ;)
At first I'm gonna show how to install sphinx on Ubuntu/Debian and check is it running properly.

1. Installation:

apt-get install sphinxsearch

and that's why I love ubuntu : D


2. Basic elements
Ok, so before we start the party, few words to remember

indexer – a tool for indexing our data sources
searchd - deamon responsible for searching data
search – a command line tool for searching data
searchapi – api for programming languages (in this tutorial we gonna focus on php)

Ok so when we're ready let's see the config file

vim /etc/sphinxsearch/sphinx.conf

 source test_src
{ 
    type            = mysql 
    sql_host        = localhost 
    sql_user        = myuser 
    sql_pass        = mypass 
    sql_db          = database_name 

    sql_query =  select id,name from history

} 


index index_test
{ 
    source              = test_src
    path                = /var/lib/sphinxsearch/data/index_test
    docinfo             = extern 
    charset_type        = utf-8 
} 

indexer 
{ 
    mem_limit       = 32M 
} 


searchd 
{ 
        port = 3312 
        log  = /var/log/searchd/searchd.log 
        query_log = /var/log/searchd/query.log 
        pid_file = /var/log/searchd/searchd.pid 
} 

The typical config contains four parts (search,search,index,indexer).
The source,index and indexer are required.
Fileds in source and index par are quite intuitive and should not cause problems.
sql_query defines the query that will give data to our source
docinfo defines how exactly docinfo will be physically stored on disk and RAM
In next part I will show how to prepare basic config and start sphinx deamon.

Mysql Case Binary

Today I've discovered how usefull can be binary operator ;)
I will show how to use it, with 'case' operator.
The main difference between normal case and binary is that Case binary check the binary value of argument.
Let's see that on exaples:

mysql> SELECT CASE  'A'  WHEN 'a' THEN 1 WHEN 'A' THEN 2 END;
+------------------------------------------------+
| CASE  'A'  WHEN 'a' THEN 1 WHEN 'A' THEN 2 END |
+------------------------------------------------+
|                                              1 | 
+------------------------------------------------+

mysql> SELECT CASE BINARY 'A'  WHEN 'a' THEN 1 WHEN 'A' THEN 2 END;
+------------------------------------------------------+
| CASE BINARY 'A'  WHEN 'a' THEN 1 WHEN 'A' THEN 2 END |
+------------------------------------------------------+
|                                                    2 | 
+------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CASE  'HoMe'  WHEN 'HOME' THEN 1 WHEN 'HoMe' THEN 2 END;
+---------------------------------------------------------+
| CASE  'HoMe'  WHEN 'HOME' THEN 1 WHEN 'HoMe' THEN 2 END |
+---------------------------------------------------------+
|                                                       1 | 
+---------------------------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT CASE BINARY 'HoMe'  WHEN 'HOME' THEN 1 WHEN 'HoMe' THEN 2 END;
+---------------------------------------------------------------+
| CASE BINARY 'HoMe'  WHEN 'HOME' THEN 1 WHEN 'HoMe' THEN 2 END |
+---------------------------------------------------------------+
|                                                             2 | 
+---------------------------------------------------------------+
1 row in set (0.00 sec)

So as you can see, no need to apply addition lower() and upper() function to compare strings.
If you wanna learn something more about binary operator see manual
http://dev.mysql.com/doc/refman/5.0/en/charset-binary-op.html

mysq dual table

Did you know that there is 'dual' table in mysql ?

mysql> select 1+1 from dual;
+-----+
| 1+1 |
+-----+
|   2 |
+-----+
1 row in set (0.00 sec)

I'm shocked : )

Edit file on remote server with vim

As simple as that

vim scp://username@host/files/config.php

[Mysql] Execute query in shell on remote host

Ok quick post.
Ever wondered how to execure query in mysql on remote host ?

mysql -u USER_NAME -p -h HOST_ADRES users -e 
"select name,surname from employees where id_employee=1"

linux 25 php security best practices

Ok, back again
First of all , I've seen that there have been some visitors from other countries so, now I'm gonna write my post in english (also to improve my forgotten language skills:) )
So I want to share some interesting article that I've found today :
http://upshell.wordpress.com/2012/02/21/linux-25-php-security-best-practices-for-sys-admins/
Write some comments soon ;)