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 ; )