Friday, October 27, 2017

Postgresql partituoning (few simple steps)

History

I created a simple application for logistic purpose based on Django, PostgresSQL, and Postgis.  This is a service receiving a position of a truck and place it on the map. Service is really simple and uses a virtual machine with small performance.  The main problem of the current solution is a small performance of this cloud server.  Most of the cloud providers use LAN for attaching virtual server's drives. As result speed of disk I/O is really small.  


root@sergiy:/home/serg# fdisk -l 
Disk /dev/xvda1: 20 GiB, 21474836480 bytes, 41943040 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes


Disk /dev/xvda2: 1 GiB, 1073741824 bytes, 2097152 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
root@sergiy:/home/serg# hdparm -tT /dev/xvda1

/dev/xvda1:
 Timing cached reads:   18876 MB in  1.99 seconds = 9499.04 MB/sec
 Timing buffered disk reads:  98 MB in  3.11 seconds =  31.55 MB/sec
root@sergiy:/home/serg# hdparm -tT /dev/xvda1

/dev/xvda1:
 Timing cached reads:   19510 MB in  1.99 seconds = 9805.60 MB/sec
 Timing buffered disk reads:  58 MB in  3.05 seconds =  19.04 MB/sec


I'm sure that drive has a good speed but network's bottleneck makes this 19.04MB/sec.  The simplest way to solving this is preparing real server or add more memory for this virtual server.  But I'm not ready to make this money spree!
My application adds records to databases on time base. Records are never updated and it is really rare event adding records related to the past. Sometimes user makes SELECT based on time range related to the business period (day, week, month).  In this case, Postgres table partitioning is a good way to do.

Database analyzing and preparing. 

Big table with my data is named as car_points and has next structure:


geocar=# \d car_points
                                    Table "public.car_points"
   Column    |           Type           |                        Modifiers                        
-------------+--------------------------+---------------------------------------------------------
 id          | integer                  | not null default nextval('car_points_id_seq'::regclass)
 Speed       | integer                  | not null
 CreatedTime | timestamp with time zone | not null
 Course      | integer                  | not null
 Altitude    | integer                  | not null
 Sat         | integer                  | not null
 Point       | geometry(Point,4326)     | not null
 Car_id      | integer                  | not null
 SensorData  | jsonb                    | not null
Indexes:
    "car_points_pkey" PRIMARY KEY, btree (id)
    "car_points_0d7f622d" btree ("Car_id")
    "car_points_Point_id" gist ("Point")
    "idx_time" btree ("CreatedTime")

Column 'id' is required by django.  I dont know how to remove it. 'CreatedTime' is the column which is used as partitioning criteria. 'Point' column is part of PostGIS functionality (This made some headache for me).
   General information about partitioning is here.  Postgres information about partitioning.  I've decided to use inherit feature for my table.  Also, it is good to have a special trigger for creating the new partition.
First and really important! Make backup!

I hope you are doing the backup on regular basis. I'm sure that better to have two no needed backup that no backup in the critical moment.
Create a function for insert record in the correct table:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
CREATE OR REPLACE FUNCTION createpar() RETURNS trigger
AS
$BODY$
DECLARE
   partition_date TEXT; 
   partition TEXT;
 --  point TEXT;
BEGIN
   -- 
   partition_date := to_char(NEW."CreatedTime", 'YYYY_WW');
   partition := TG_TABLE_NAME || '_' ||partition_date;
   IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=partition)
   THEN
    RAISE NOTICE 'Creating partition: %',partition;
    -- Should add correct week check 
    EXECUTE 'CREATE TABLE ' || partition || ' (check (to_char("CreatedTime", ''YYYY_WW'') = ''' || partition_date || ''')) INHERITS (' || TG_TABLE_NAME || ');';
   END IF;
   EXECUTE 'INSERT INTO ' || partition || '(id, "Speed", "CreatedTime", "Course", "Altitude", "Sat", "Point", "Car_id", "SensorData" ) VALUES (' ||
   NEW."id" ||','||
   NEW."Speed" || ',''' ||
   NEW."CreatedTime"|| ''',' ||
   NEW."Course"||','||
   NEW."Altitude" || ','||
   NEW."Sat" || ',' ||   
    ' ''' || NEW."Point" || '''::geometry' ||','||
   NEW."Car_id" || ','''  ||
   NEW."SensorData" ||
''') RETURNING id;';
--   INSERT INTO partition  VALUES(NEW.*);
   RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

Source code of this function is here: postgrsfunction
I would like to clarify some code:
String #9-10  generates name for our partition  using next format : table name plus year and week number. If no partition for new record  then partition is created #16.  Function calculate year and week in to string and check if partition with  this name is presen.
After this our record is inserted in our new partioned table.  Unfortunately EXECUTE  can not insert postgis geometry corectly. (NEW.* has geometry column , but INSERT requires geometry as string with geometry tailing tag).  If you dont have PostGIS column you can use NEW.* directly (string#29)

After this I've add triger for every new  inserted record using this function.


geocar=#                                 
CREATE TRIGGER createpartitiontrigger BEFORE INSERT on car_points FOR EACH ROW EXECUTE PROCEDURE  createpar();
Few minutes later new partition is created:

geocar=# \d
                           List of relations
  Schema  |               Name                |   Type   |    Owner    
----------+-----------------------------------+----------+-------------
 public   | car_car                           | table    | cartracking
 public   | car_car_id_seq                    | sequence | cartracking
 public   | car_points                        | table    | cartracking
 public   | car_points_2017_43                | table    | cartracking
 public   | car_points_id_seq                 | sequence | cartracking
  Time to make simple analize:

geocar=# EXPLAIN ANALYZE select  count(*)  from car_points_2017_43  where "CreatedTime" > '2017-10-26';
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=882.14..882.15 rows=1 width=8) (actual time=89.705..89.706 rows=1 loops=1)
   ->  Seq Scan on car_points_2017_43  (cost=0.00..803.62 rows=31407 width=0) (actual time=7.821..58.318 rows=29811 loops=1)
         Filter: ("CreatedTime" > '2017-10-26 00:00:00+03'::timestamp with time zone)
 Planning time: 0.139 ms
 Execution time: 89.768 ms
(5 rows)

geocar=# EXPLAIN ANALYZE select  count(*)  from car_points  where "CreatedTime" > '2017-10-26';
                                                                     QUERY PLAN                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=13748.21..13748.22 rows=1 width=8) (actual time=655.247..655.249 rows=1 loops=1)
   ->  Append  (cost=0.43..13656.98 rows=36490 width=0) (actual time=24.448..589.689 rows=68910 loops=1)
         ->  Index Only Scan using idx_time on car_points  (cost=0.43..12853.36 rows=5083 width=0) (actual time=24.446..347.378 rows=39099 loops=1)
               Index Cond: ("CreatedTime" > '2017-10-26 00:00:00+03'::timestamp with time zone)
               Heap Fetches: 39099
         ->  Seq Scan on car_points_2017_43  (cost=0.00..803.62 rows=31407 width=0) (actual time=0.040..116.462 rows=29811 loops=1)
               Filter: ("CreatedTime" > '2017-10-26 00:00:00+03'::timestamp with time zone)
 Planning time: 0.309 ms
 Execution time: 655.309 ms
(9 rows)

SELECT uses our new partition  for data  but index from parent table.  In this case It will be good create index for our table and move data from parent table to partituoned table.  Of course this requires data dumping and reloading but does not requires stopping database as MySQL !


I've used idea from StreamBright: https://medium.com/@StreamBright/creating-partitions-automatically-in-postgresql-7006d68c0fbb

Monday, October 23, 2017

Upgrade postgres 9.4 to 9.6 at Debian linux

I'm using virtual server based on Debian Linux for some my free projects.  One of them is based on Postgres with PostGIS extension.   This windy rainy day is a good time for executing some upgrade. Also I would like to add partman extension for partituoning.

Checking new version. 

Update package list :
aptitude update 
Get: 1 http://ftp.debian.org/debian jessie-updates InRelease [145 kB]                                                                                           
Get: 2 http://security.debian.org stretch/updates InRelease [62.9 kB]                                                                                    
Get: 3 http://security.debian.org jessie/updates InRelease [63.1 kB] 

 Check for new version:


dpkg-query -l postgresql* 
Desired=Unknown/Install/Remove/Purge/Hold
| Status=Not/Inst/Conf-files/Unpacked/halF-conf/Half-inst/trig-aWait/Trig-pend
|/ Err?=(none)/Reinst-required (Status,Err: uppercase=bad)
||/ Name                                  Version                 Architecture            Description
+++-=====================================-=======================-=======================-===============================================================================
un  postgresql-7.4                        <none>                  <none>                  (no description available)
un  postgresql-8.0                        <none>                  <none>                  (no description available)
un  postgresql-9.1                        <none>                  <none>                  (no description available)
ii  postgresql-9.4                        9.4.12-0+deb8u1         amd64                   object-relational SQL database, version 9.4 server
un  postgresql-9.4-postgis-2.0-scripts    <none>                  <none>                  (no description available)
ii  postgresql-9.4-postgis-2.1            2.1.8+dfsg-4            amd64                   Geographic objects support for PostgreSQL 9.4
un  postgresql-9.4-postgis-2.1-scripts    <none>                  <none>                  (no description available)
ii  postgresql-9.4-postgis-scripts        2.1.8+dfsg-4            all                     Geographic objects support for PostgreSQL 9.4 -- scripts
un  postgresql-client                     <none>                  <none>                  (no description available)
ii  postgresql-client-9.4                 9.4.12-0+deb8u1         amd64                   front-end programs for PostgreSQL 9.4
ii  postgresql-client-common              181                     all                     manager for multiple PostgreSQL client versions
ii  postgresql-common                     181                     all                     PostgreSQL database-cluster manager
ii  postgresql-contrib-9.4                9.4.12-0+deb8u1         amd64                   additional facilities for PostgreSQL
un  postgresql-doc-9.4                    <none>                  <none>                  (no description available)
un  postgresql-doc-9.6                    <none>                  <none>                  (no description available)

We have installed version 9.4 Checking status of running cluster:

root@sergiy:~# pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.4 main    5432 online postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log

Host has one cluster version 9.4. At this moment we can install new one Postgres using new claster with a new version.  I'm including few new packages:

oot@sergiy:~# aptitude search postgresql-9.6
p   postgresql-9.6                                                                - object-relational SQL database, version 9.6 server                                      
p   postgresql-9.6-asn1oid                  
....

root@sergiy:~# aptitude install postgresql-9.6  postgresql-9.6-postgis-scripts    postgresql-9.6-postgis-2.3  postgresql-9.6-partman 
The following NEW packages will be installed:
  ghostscript{a} i965-va-driver{a} libaec0{a} libarmadillo7{a} libass5{a} libasyncns0{a} libavc1394-0{a} libavcodec57{a} libavdevice57{a} libavfilter6{a} 
...
3 packages upgraded, 138 newly installed, 1 to remove and 108 not upgraded.
Need to get 84.3 MB of archives. After unpacking 370 MB will be used.
Do you want to continue? [Y/n/?] y
...

Setting up postgresql-9.6 (9.6.4-0+deb9u1) ...
Creating new cluster 9.6/main ...
  config /etc/postgresql/9.6/main
  data   /var/lib/postgresql/9.6/main
  locale en_US.UTF-8
  socket /var/run/postgresql
  port   5433
update-alternatives: using /usr/share/postgresql/9.6/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
[ ok ] Starting PostgreSQL 9.6 database server: main.

Package's part is finished. next step is to migrate our database to the new version.

Database upgrade. 

The first step is dropping our new DB claster created by an installer. 


pg_dropcluster 9.6 main --stop 

Check active DB cluster:

root@sergiy:~# pg_lsclusters 
Ver Cluster Port Status Owner    Data directory               Log file
9.4 main    5432 online postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log

Only one old (9.4 ) is active.  It is a time to upgrade DB. I've stopped all clients which are using the database.


root@sergiy:~# pg_upgradecluster 9.4 main 
Disabling connections to the old cluster during upgrade...
Restarting old cluster with restricted connections...
Creating new cluster 9.6/main ...
  config /etc/postgresql/9.6/main
  data   /var/lib/postgresql/9.6/main
  locale en_US.UTF-8
  socket /var/run/postgresql
  port   5433
Disabling connections to the new cluster during upgrade...
Roles, databases, schemas, ACLs...
Fixing hardcoded library paths for stored procedures...
Upgrading database geocar...
Analyzing database geocar...
Fixing hardcoded library paths for stored procedures...
Upgrading database template1...
Analyzing database template1...
Fixing hardcoded library paths for stored procedures...
Upgrading database postgres...
Analyzing database postgres...
Re-enabling connections to the old cluster...
Re-enabling connections to the new cluster...
Copying old configuration files...
Copying old start.conf...
Copying old pg_ctl.conf...
Stopping target cluster...
Stopping old cluster...
Disabling automatic startup of old cluster...
Configuring old cluster to use a different port (5433)...
Starting target cluster on the original port...
Success. Please check that the upgraded cluster works. If it does,
you can remove the old cluster with

  pg_dropcluster 9.4 main
I would like to wait some time to be sure that 9.6 is OK  for me.  I 'm going to delete old cluster next week.  Check if new cluster uses old port 5432 by next command:

root@sergiy:~# pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.4 main    5433 down   postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log
9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log

That are all. I'm goint enable partituoning at this database next day
I've used idea from this URL:

Wednesday, September 13, 2017

checking host hardware using linux kernel

How to collect information about  host without trivial system command

  I'm using a lot of hardware boxes with no limit on distro types and versions.  In this case using system command such as ifconfig has a limited usage.  Also,  system config files have a different path and best way for me is getting information from the kernel. Of course,  I'm using cat command for doing this.
Next table has information about hardware. Let me know if something is interesting for you and you know something interesting

hardware information in the linux kernel
file path information
/proc/cpuinfo CPU
/proc/meminfo memory
/sys/block List of block devices. HDD has sd or hd at the beginning
/sys/class/block/{digit}/device/model HDD drive and vendor
/sys/block/{digit}/size HDD size
/sys/block List of block devices. HDD has sd or hd at the beginning
/sys/bus/pci/devices/0000:{digit}/net System name of the network card
/proc/net/route Netcard names with VLAN datas, network and netmask
/proc/net/fib_trie route caching (also has information about local IPs)
/proc/version kernel version and uptime

Simple bonus

I've written a simple python script for  checking all information at remote host via SSH connection. you need ssh key  for connection only.  Link is here: python code is here.

Tuesday, June 13, 2017

Postfix can not start via systemd (simple fix)

Solving problem related to systemd process

I like postfix.   This is really smart and secure mail server. I'm helping above  dozen clients around the world and  tunning  postfix is really fun task. This morning I was downgrading postfix  to the stable version for one of the my friends and come across interesting issue. 

root@newserver:/etc/init.d# systemctl  status  postfix 
● postfix.service
   Loaded: masked (/dev/null; bad)
   Active: inactive (dead) since вт 2017-06-13 14:35:41 EEST; 1h 48min ago
 Main PID: 25145 (code=exited, status=0/SUCCESS)

чер 13 14:47:09 newserver systemd[1]: Stopped postfix.service.
чер 13 14:47:29 newserver systemd[1]: Stopped postfix.service.
чер 13 14:58:22 newserver systemd[1]: Stopped postfix.service.
чер 13 14:58:23 newserver systemd[1]: Stopped postfix.service.
чер 13 15:05:20 newserver systemd[1]: Stopped postfix.service.
чер 13 15:29:06 newserver systemd[1]: Stopped postfix.service.
чер 13 15:29:06 newserver systemd[1]: Stopped postfix.service.
чер 13 15:32:06 newserver systemd[1]: Stopped postfix.service.
чер 13 15:32:07 newserver systemd[1]: Stopped postfix.service.
чер 13 15:35:54 newserver systemd[1]: Stopped postfix.service.
Warning: postfix.service changed on disk. Run 'systemctl daemon-reload' to reload units.

 I don't like systemd.  This service takes so much responsibility IMHO.  I'm preferring  init.d  scripts  (looks like I'm old fashion guy). If service can not start then checking startup files is correct way.
I have not seen it in default  systemd directory and  check  that other files are present  and are correct. finally detect it under /etc/default:
root@newserver:/lib/systemd/system# ls pos*
postgresql.service  postgresql@.service
root@newserver:/lib/systemd/system# cat postgresql.service 
# systemd service for managing all PostgreSQL clusters on the system. This
# service is actually a systemd target, but we are using a service since
# targets cannot be reloaded.

[Unit]
Description=PostgreSQL RDBMS

[Service]
Type=oneshot
ExecStart=/bin/true
ExecReload=/bin/true
RemainAfterExit=on

[Install]
WantedBy=multi-user.target

root@newserver:/lib/systemd/system# ls /etc/systemd/system/
clamav-daemon.socket.d/      network-online.target.wants/ sockets.target.wants/        syslog.service               
getty.target.wants/          paths.target.wants/          sshd.service                 timers.target.wants/         
multi-user.target.wants/     postfix.service              sysinit.target.wants/        


But  this file is  softlink   to /dev/null only:

root@newserver:/lib/systemd/system# ls /etc/systemd/system/postfix.service  -la
lrwxrwxrwx 1 root root 9 чер 13 14:35 /etc/systemd/system/postfix.service -> /dev/null

I've  deleted this only and reinstalled postfix for generating new one. After this postfix  is going to be green.
root@newserver:/lib/systemd/system# systemctl  daemon-reload
root@newserver:/lib/systemd/system# systemctl   status postfix
● postfix.service - LSB: Postfix Mail Transport Agent
   Loaded: loaded (/etc/init.d/postfix; bad; vendor preset: enabled)
  Drop-In: /run/systemd/generator/postfix.service.d
           └─50-postfix-$mail-transport-agent.conf
   Active: inactive (dead) since вт 2017-06-13 14:35:41 EEST; 1h 57min ago
     Docs: man:systemd-sysv-generator(8)
 Main PID: 25145 (code=exited, status=0/SUCCESS)

чер 13 14:47:09 newserver systemd[1]: Stopped postfix.service.
чер 13 14:47:29 newserver systemd[1]: Stopped postfix.service.
чер 13 14:58:22 newserver systemd[1]: Stopped postfix.service.
чер 13 14:58:23 newserver systemd[1]: Stopped postfix.service.
чер 13 15:05:20 newserver systemd[1]: Stopped postfix.service.
чер 13 15:29:06 newserver systemd[1]: Stopped postfix.service.
чер 13 15:29:06 newserver systemd[1]: Stopped postfix.service.
чер 13 15:32:06 newserver systemd[1]: Stopped postfix.service.
чер 13 15:32:07 newserver systemd[1]: Stopped postfix.service.
чер 13 15:35:54 newserver systemd[1]: Stopped postfix.service.
root@newserver:/lib/systemd/system# apt-get install  --reinstall  postfix
Зчитування переліків пакунків... Виконано
Побудова дерева залежностей                        
Зчитування інформації про стан... Виконано
оновлено 0, встановлено 0 нових, 1 перевстановлено, 0 відмічено для видалення і 8 не оновлено.
Необхідно завантажити 0 B/1 374 kB архівів.
Після цієї операції об'єм зайнятого дискового простору зросте на 0 B.
Передналаштування пакунків...
(Reading database ... 73453 files and directories currently installed.)
Preparing to unpack .../postfix_2.11.3-1+deb8u2_amd64.deb ...
Unpacking postfix (2.11.3-1+deb8u2) over (2.11.3-1+deb8u2) ...
Processing triggers for man-db (2.7.4-1) ...
Processing triggers for ufw (0.33-2) ...
Processing triggers for systemd (229-2) ...
Setting up postfix (2.11.3-1+deb8u2) ...

Postfix configuration was not changed.  If you need to make changes, edit
/etc/postfix/main.cf (and others) as needed.  To view Postfix configuration
values, see postconf(1).

After modifying main.cf, be sure to run '/etc/init.d/postfix reload'.

Running newaliases
root@newserver:/lib/systemd/system# systemctl  daemon-reload
root@newserver:/lib/systemd/system# systemctl   status postfix
● postfix.service - LSB: Postfix Mail Transport Agent
   Loaded: loaded (/etc/init.d/postfix; bad; vendor preset: enabled)
  Drop-In: /run/systemd/generator/postfix.service.d
           └─50-postfix-$mail-transport-agent.conf
   Active: active (running) since вт 2017-06-13 16:34:26 EEST; 12s ago
     Docs: man:systemd-sysv-generator(8)
 Main PID: 25145 (code=exited, status=0/SUCCESS)
   CGroup: /system.slice/postfix.service
           ├─20894 /usr/lib/postfix/master
           ├─20895 pickup -l -t unix -u -c
           └─20896 qmgr -l -t unix -u

чер 13 16:34:26 newserver systemd[1]: Starting LSB: Postfix Mail Transport Agent...
чер 13 16:34:26 newserver postfix[20782]: Starting Postfix Mail Transport Agent: postfix.
чер 13 16:34:26 newserver systemd[1]: Started LSB: Postfix Mail Transport Agent.
чер 13 16:34:26 newserver postfix/master[20894]: daemon started -- version 2.11.3, configuration /etc/postfix

Thursday, May 11, 2017

Another one MySQL monitor

Detecting MySQL loading

I've seen strange and randomize increasing  of the  MySQL db loading but mytop did not help to sorted out with this issue. I'm not Mike Tyson and my reaction is more slow then CPU. If I don't catch this dynamically I will catch this statistically. 

Main idea

Select active request by cron and write it ti file. After this statistic analyze for long period of time sort  this problem out. 

Selecting information  from  MySQL 

Please add python mod MySQLdb  and set correct credentials up.


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
import os, MySQLdb

def main():
    """ checking process list and save it to file"""
    LOGFILENAME = 'dbusage.log'
    MYSQLUSERNAME = 'root'
    MYSQLPASSWORD = 'password'
    MYSQLHOST = 'localhost'
    logfile = open(LOGFILENAME, 'a')
    mydb = MySQLdb.connect(host = MYSQLHOST,
                           user = MYSQLUSERNAME,
                           passwd = MYSQLPASSWORD)
    mycursor = mydb.cursor()
    mycursor.execute("""SELECT   TIME_MS, INFO   FROM information_schema.processlist WHERE  COMMAND='Query' ORDER BY TIME_MS DESC  LIMIT 15 ;""")
    sqlresult = mycursor.fetchall()
    for executingtime, sqlrequest  in sqlresult:
        logfile.write(str(time.time()) +';'+str(executingtime)+';'+sqlrequest+'\n')
    logfile.close()
    return 0

if __name__ == "__main__":
    """ collect data about active mysql running sql and save it to file"""
    main()


    After this add  executing  this script by  cron daemon.

Analyzing data 

I've ignored 'COMMIT' request but  you can update it easy.  Script analyzes log file and build dictionary  with based on command & table and summarize running time . 


!-- HTML generated using hilite.me -->
#!/usr/bin/env python
import os
import time
import argparse
from itertools import cycle


def analizesql(sql):
    """ Analize SQL and returt commandand usedtable as sum of two
    string such as COMMITsession """
    sqllastlist = sql.split(' ')
    sqlcommand = sqllastlist[0].rstrip()
    nofound = True
    if sqlcommand == 'UPDATE':
        usedtable = sqllastlist[1]
    elif sqlcommand == 'SELECT':
        sqllistcycle = cycle(sqllastlist)
        nextelement = sqllistcycle.next()
        while nofound:
            thiselement, nextelement = nextelement, sqllistcycle.next()
            if thiselement == 'FROM':
                nofound = False
                usedtable = nextelement
    #  elif sqlcommand == 'COMMIT\n':
    #      sqlcommand
    else:
        usedtable = ''
    return sqlcommand+usedtable


def timeinsecfromstr(timestr):
    """Convert  string such as 1h 1d 1m  to seconds  """
    switcher = {
      "M": 60, 
      "H": 3600,
      "D": 86400,
      }
    deltatype =timestr[-1:].capitalize()
    return  int(timestr[:-1])* switcher.get(deltatype)
def main():
    """ """
    parser = argparse.ArgumentParser()
    parser.add_argument('--timedelta', type=str, help='time in format int + M H D', default='1H')
    timeinsec= parser.parse_args().timedelta
    STARTTIMEHOURREPORT = time.time() - timeinsecfromstr(timeinsec)
    logfile = open('./dbusage.log', 'r')
    usagedict = {}
    linecount = 0 
    for line in logfile:
        if float(line.split(';')[0]) > STARTTIMEHOURREPORT:
     linecount+=1
            usagetime = float(line.split(';')[1])
            currentsql = line.split(';')[2]
            commandtable = analizesql(currentsql)
            if usagedict.has_key(commandtable):
                usagedict[commandtable] += usagetime
            else:
                usagedict[commandtable] = usagetime
    usagedict_s = [(usagetime, commandtable) for commandtable, usagetime in usagedict.iteritems()]
    usagedict_s.sort(reverse=True)
    print("="*56)
    print("Total records detected:%s" %(linecount,))
    print("="*56)
    for v, k in usagedict_s:
 print('{0:<35} {1:>20}'.format(k,v/linecount))
    print("="*56)
if __name__ == "__main__":
    main()

Also you can set different period of time for longer checking.  All questions are welcomed.  

Thursday, May 4, 2017

Sending request using request library

Using request library for Pirates

 Few years ago using urllib2 was a simplest way for sending and receiving  HTTP Request. But now I'm using  Request library . This  is more simple and powerful. Unfortunately Request is not include in default install and Pip  should be used . 

Installation

 Procedure is really simple  and near to standard :

MacBook-Pro-Hohlov:pirates skhohlov$ virtualenv ./env
New python executable in ./env/bin/python2.7
Also creating executable in ./env/bin/python
Installing setuptools, pip, wheel...done.
MacBook-Pro-Hohlov:pirates skhohlov$ source ./env/bin/activate
(env)MacBook-Pro-Hohlov:pirates skhohlov$ 
(env)MacBook-Pro-Hohlov:pirates skhohlov$ 
(env)MacBook-Pro-Hohlov:pirates skhohlov$ pip install requests
Collecting requests
  Using cached requests-2.13.0-py2.py3-none-any.whl
Installing collected packages: requests
Successfully installed requests-2.13.0
You are using pip version 7.1.2, however version 9.0.1 is available.
You should consider upgrading via the 'pip install --upgrade pip' command.


Few lines of code. 

 Request has a good documentation at : http://docs.python-requests.org/en/master/   It will be nice to check it if you have any misunderstanding.   Lets back to pirates game.  One of the first request is three  requests for selecting  correct segment for playing. List   is a powerful and very important  feature of the python  language.  If you  have worked  with C or C++  you should  know  how  much time requires understanding of pointers and correct using. Python  is doing this in  light and easy to understand way. Good tutorial  is here : https://www.tutorialspoint.com/python/python_lists.htm    
I've created a function  hot to send requests and select proper segment for  future requests: 



 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
def detectdefaultsegment(gamesession, config):
    """ function for detecting default user segment"""
    BaseSegmentURLList = [
    'https://kpppen3s00.plrm.zone/PiratesPpEng3/Segment00/segment.ashx',
    'https://kpppen2s00.plrm.zone/PiratesPpEng2/Segment00/segment.ashx',
    'https://kpppen1s01.plrm.zone/PiratesPPeng/Segment00/segment.ashx']
        
    gamesession.headers.update({'sign-code':config['sign-code'], 'server-method':'GetTouch','client-ver':config['client-ver']})
    for  BUrl in BaseSegmentURLList:        
        answer=gamesession.post(BUrl,data="[\""+config['userid']+"\"]")
        if answer.text[0] is not '!':
            segmenturl = BUrl
    return segmenturl

How its works ?
 Lines  3-6 are  our python list  of the  url.
 Line 8 is adding some header to the request  such as client version , sever method etc.
 Lines 9-12 are python loop:  request is send to each url .  Correct answer is started from  digits (timestamp),  incorrect is started from symbol "!".  Correct URL is returned.
All script code are placed at : https://pastebin.com/ZB4LiXuw 

Looks like I will write only SignIn function. Other part is not interested for me.

Generating Signature and Sign in function. 

Article about  swf working with swf shows how to application "crypting" request  again bad guys. 


def generateSignature(jsonstring, servercommand,config ):
    """Generate checksum for sign-code header.
    Each POST request has this one."""
    matrix_string = "The Matrix has you..."    
    return hashlib.md5((matrix_string+jsonstring+servercommand+config['userid']+config['authkey']).encode('ascii')).hexdigest()

and our first  Sign in function  is look like :


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
def SignIn(gamesession, config, mainurl):
   """ function for SignIn. This not auth function,
   this function is used for marking for setting up flag
   for first signing in per day. """        
   JSON_STRING = """{"s":{"x":null,"gr":null,"l":"en-US","I'm":false,"s":null,"tg":0,"m":"myemail","ar":null,"de":null,"t":0,"a":false,"n":"nickname","vk":{"b":false},"u":"//cdn01.x-plarium.com/browser/content/portal/common/avatars/default_avatar.jpg","i":"pp24685509","fl":false,"np":false,"p":null,"d":"nickname;null;null;en_US;0;;","tip":null},"mr":"","l":"","i":"0","f":[""],"c":{"ht":"Portal","put":null,"i":1,"v":"1.0"},"t":-180,"rr":"https://plarium.com/en/strategy-games/pirates-tides-of-fortune/"}"""
   parsed_string = json.loads(JSON_STRING)
   print("email:{c[m]}, username:{c[n]}, userid:{c[i]}".format(c=parsed_string["s"]))
   print json.dumps(parsed_string)
   sign =  generateSignature(JSON_STRING, 'SignIn', config)
   gamesession.headers.update({'sign-code':sign, 'server-method':'SignIn','client-ver':config['client-ver'], 'signin-authSeed':config['authSeed'], 'signin-authKey':config['authkey'], 'signin-userId':config['userid']})
   answer = gamesession.post(mainurl,data=JSON_STRING)
   return answer


Main problem is understanding a string "JSON_STRING".  I've sorted out that this string contains nickname , email, link to photo etc.   Everyone  can get this string from  checking headers procedure, I've informed about this one or two weeks ago. Strings #6-8 print  this information.  Those string have debuging purpose only.  Strings #10-12 are preparing request  and sending it to server.
Other server command requires a similar job to our SignIn  function.
I have no interest for this and  I would like to do something  funny and smart  (Ex: Arduino or AI )

Tuesday, April 25, 2017

Starting python programming

Programming is easy. Really easy.

Many years ago programming requires good skills in hardware and software. "How computers works" was really important question 30 years ago. Cracking Enigma computer or first computer at a nuclear submarine were more simplest the modern cell phone.  This is a reason why building mathematical solution requires perfect engineering skill.
Computer is more powerful now and  tarting programming does not requires long term eduction. For example: driving car does not requires deep knowing in  engines and physics, you are driver not mechanics.

Downloading software. 

First  download  python.python 
Also text editor  or powerful IDE is needed. I like Emacs but a lot of peoples love pycharm. You can download it  from PyCharm. Python is really popular and a lot of system have it included. I'm planning  to use some additional libraries  for our application. It is good idea do not touch your original software. Correct  way is virtual environment usage. 
 Linux people can take a look at : Virtual environment for Linux and IMac
Windows  people  should take a look at : Virtual environment for Windows


First our python  program

Using your favorite editor  type next code: 



#!/usr/bin/env python
# -*- coding: utf-8 -*-
""" Small  script for plarium game.
This application has education purposes only.
"""

def main():
    """main function for this one"""
    print("Hello!")
    return 0

if __name__=="__main__":
    main()


First string  informs system that python will be used. 
Second one  is setting code page for our script.  My system  uses utf-8.  Next one  is python doc string. This can help other guys to understand your code.  Of course,   you can move out  this string. 
Starting  from def  to the end it is default python code  structure.   Also you can delete  all code except string print("Hello!")  and result is a same.   I've added some additional string for making good programmer's writing skill.

Working with files

Of course, writing 'hello'  is really simple and everyone can do this.  More interested is working with files. Writing simple  files  is simple but interesting from performance point of view as I wrote early List in python performance.  I think it is not a bad idea to start from writing and reading configurations files. One of a simplest modules for this task is ConfigParser in python 3.0  it has been renamed to configparser. 
add please to our script after doc string next code: 
import os, ConfigParser

def readconfigfile():
    """ Readinf importamt values from config files"""
    configparser=ConfigParser.RawConfigParser()
    configparser.readfp(open('./config.cfg'))
    userid=configparser.get('user', 'userid')
    authkey=configparser.get('user', 'authkey')
    return userid, authkey
 and update main function to  the next one:

def main():
    """main function for this one"""
    userid,authkey=readconfigfile()
    print("Hello!")
    
    print('userid:{0}, authkey:{1}'.format(userid,authkey))
    return 0
also create a simple file config.cfg with simple data:

[user]
userid=12344
authkey=34fewfsad

Of course, you can set those values using our investigation from firefox development tools: getting UserID and authkey Run it and our script print values from our config file. Also we can use those data for future programming.