Arxiv

Archive for İ

Reclaim space after delete operation

Bu yaxınlarda maraqlı hadisəyə rast gəlmişəm…Məsələnin ümumi məğzi bundan ibarətdir:
~
Hər hansı cədvəldən məlumat silindikdən sonra onun tutduğu yer (hdd olaraq) silinmir.
Yəni, 250 MB-lik cədvəlin yarısını sildikdə məntiqlə düşündükdə yaddaş azalmalı idi, lakin azalmır.
~

Cədvəlimizin həcmi 252M:

root@sh-v3:/var/lib/mysql/mysqlspp# ll -h | grep sales2.ibd
-rw-rw---- 1 mysql mysql 252M Sep 28 00:26 sales2.ibd

İçindəki məlumat sayı:

mysql> select count(*) from sales2;
+----------+
| count(*) |
+----------+
|  2500003 |
+----------+
1 row in set (0.50 sec)

cədvəl strukturu:

CREATE TABLE `sales2` (
  `SALES_ID` int(8) NOT NULL AUTO_INCREMENT,
  `CUSTOMER_ID` decimal(8,0) NOT NULL,
  `PRODUCT_ID` decimal(8,0) NOT NULL,
  `SALE_DATE` datetime NOT NULL,
  `QUANTITY` decimal(8,0) NOT NULL,
  `SALE_VALUE` decimal(8,0) NOT NULL,
  `DEPARTMENT_ID` decimal(8,0) DEFAULT '0',
  `SALES_REP_ID` decimal(8,0) DEFAULT '0',
  `GST_FLAG` decimal(8,0) DEFAULT NULL,
  `sale_status` char(1) DEFAULT NULL,
  `FREE_SHIPPING` char(1) DEFAULT '',
  `DISCOUNT` decimal(8,0) unsigned DEFAULT '0',
  PRIMARY KEY (`SALES_ID`),
  KEY `sales_cust_idx` (`CUSTOMER_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2500004 DEFAULT CHARSET=latin1

Cədvəldən sales_id-si 1000000-dan böyük olan yəni, 1.500.000 recordu silək:

mysql> delete from sales2 where sales_id>1000000;
Query OK, 1500003 rows affected (1 min 29.56 sec)

Cədvəl həcmimiz dəyişmədi 252 MB:

root@sh-v3:/var/lib/mysql/mysqlspp# ll -h | grep sales2.ibd
-rw-rw---- 1 mysql mysql 252M Sep 28 00:39 sales2.ibd

Ümumi izahı:
When you remove rows, they are just marked as deleted on disk but space will be consumed by InnoDB files which can be re-used later when you insert/update more rows but it will never shrink.

Test edək:

mysql> select count(*) from sales3;
+----------+
| count(*) |
+----------+
|  2500003 |
+----------+
1 row in set (41.34 sec)

Cədvəlimizin həcmi:

root@sh-v3:/var/lib/mysql/mysqlspp# ll -h | grep sales3.ibd
-rw-rw---- 1 mysql mysql 252M Sep 28 00:31 sales3.ibd

Cədvəldə olan məlumatları silək və eyni qədərini təzədən insert edək:

mysql> delete from sales3 where sales_id>1000000;
Query OK, 1500003 rows affected (2 min 7.22 sec)

mysql> insert into sales3 select * from sales where sales_id>1000000;
Query OK, 1500003 rows affected (3 min 7.98 sec)
Records: 1500003  Duplicates: 0  Warnings: 0

mysql> select count(*) from sales3;
+----------+
| count(*) |
+----------+
|  2500003 |
+----------+
1 row in set (0.48 sec)

Və həcmə baxırıq:

root@sh-v3:/var/lib/mysql/mysqlspp# ll -h | grep sales3.ibd
-rw-rw---- 1 mysql mysql 252M Sep 28 01:27 sales3.ibd

Bəli həqiqətən də space sonrakı istifadə üçün saxlanılır.

Gəlin Truncate-ə baxaq. Truncate internal olaraq cədvəli drop edir, daha sonra da təzədən yaradır və bu da o deməkdir ki, tutulmuş yaddaş avtomatik boşalır:

mysql> truncate table sales2;
Query OK, 0 rows affected (0.81 sec)

root@sh-v3:/var/lib/mysql/mysqlspp# ll -h | grep sales2.ibd
-rw-rw---- 1 mysql mysql 112K Sep 28 01:45 sales2.ibd

Bəs deyək ki, biz cədvəldə olan məlumatları yaddaş boşalsın deyə silirik onda necə?(qeyd edək ki, system reboot-dan sonra belə, yaddaş boşalmır).
Truncate məntiqindən belə çıxdı ki,əgər mən cədvəli reorganize etsəm tutlmuş yaddaş boşaldılacaq. Cədvəli reorganize etməyin isə əsas 2 yolu mövcuddur (İnnoDB-dən gedir söhbət)
1. optimize table
2. “empty alter”

Gəlin hər 2 üsulu test edək:

mysql> delete from sales3 where sales_id>1000000;
Query OK, 1500003 rows affected (1 min 22.59 sec)

root@sh-v3:/var/lib/mysql/mysqlspp# ll -h | grep sales3.ibd
-rw-rw---- 1 mysql mysql 252M Sep 28 01:52 sales3.ibd

Reorginizing:

mysql> optimize table sales3;
+-----------------+----------+----------+-------------------------------------------------------------------+
| Table           | Op       | Msg_type | Msg_text                                                          |
+-----------------+----------+----------+-------------------------------------------------------------------+
| mysqlspp.sales3 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| mysqlspp.sales3 | optimize | status   | OK                                                                |
+-----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (1 min 31.52 sec)

Diqqət!
optimize table statement temporary cədvəl yaradaraq məlumatları oraya kopyalayır.
Bu da full table lock ilə müsaiyət olunur. Dolayısı ilə siz öz cədvəlinizə optimize table zamanı çata bilməyəcəksiniz. Bundan əlavə əgər sizdə tmp yaddaş kifayət qədər deyilsə out-of-memory də yaşaya bilərsiniz. Sübut:

mysql> show processlist;
+----+------+-----------+----------+---------+------+-------------------+-----------------------+
| Id | User | Host      | db       | Command | Time | State             | Info                  |
+----+------+-----------+----------+---------+------+-------------------+-----------------------+
|  5 | root | localhost | mysqlspp | Query   |   89 | copy to tmp table | optimize table sales3 |
|  6 | root | localhost | mysqlspp | Query   |    0 | init              | show processlist      |
+----+------+-----------+----------+---------+------+-------------------+-----------------------+
2 rows in set (0.00 sec)

Test edirik və görürük ki, space boşaldıldı 96 MB:

root@sh-v3:/var/lib/mysql/mysqlspp# ll -h | grep sales3.ibd
-rw-rw---- 1 mysql mysql  96M Sep 28 01:55 sales3.ibd

İkinci üsul “empty alter” dediyimiz üsuldur ki, onsuz da İnnoDB olan cədvəli yeniden alter edib engine İnnoDB versək, bu nəticədə eyni cədvəli verəcək bizə, lakin reorganize edilmiş şəkildə:

mysql> delete from sales3 where sales_id>1000000;
Query OK, 1500003 rows affected (51.99 sec)

mysql> alter table sales3 engine=innodb;
Query OK, 1000000 rows affected (2 min 42.82 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

Dərhal yoxlayırıq və 96 M:

root@sh-v3:/var/lib/mysql/mysqlspp# ll -h | grep sales3.ibd
-rw-rw---- 1 mysql mysql  96M Sep 28 02:16 sales3.ibd

Bəli hər 2 üsul işləyir. 3-cü bir üsul da ağlıma gəlib ki, əgər cədvəldən müəyyən məlumatları silmək istəyiriksə, silib daha sonra reorganize etmek evezine:
1. eyni strukturda yeni boş cədvəl yaradırıq (table1)
2. məlumat silinməsi baş verəcək cədvəldən (table2) silinmədən sonra qalacaq məlumatları silinmədən əvvəl boş table1-imizə insert edirik.
3. insert bitdikdən sonra table2-ni drop edirik
4. table1-i rename edirik table2-yə.

Bu üsul da teorik olaraq işləməlidir.
Təşəkkürlər 😉

Advertisements

Getting rownum from subquery(emulating rownum)

Oracle rownum-a oxşar bir sorğu yazmaq haqqında bir mövzu yazmışdım.
Lakin bu dəfə nəsə daha maraqlı bir problem yarandı.
Əvvəlki Mövzu

Qısa olaraq Turnir cədvəlində olan Komandaları xalı çox olandan yəni, liderdən aşağı olaraq sıralamaq lazımdır. Bununla yanaşı Hər komandanın tutduğu yeri də göstərmək lazımdır.
Komandaların ümumi xal hesabı aşağıdakı sorğu ilə tapılır:

 
select
  t.id,
  t.name,
  count(*) as total_matchs,
  sum(case when g.wins>0 then 1 else 0 end) as wins,
  sum(case when g.wins=0 then 1 else 0 end) as draw,
  sum(case when g.wins=0 then 1 else 0 end) as lost,
  sum(case sign(g.wins)
    when 1 then 3
    when 0 then 1
    else 0 end) as total_point,
  sum(g.GF) as GF,
  sum(g.GA)as GA,
  sum(g.GF)-sum(g.GA) as average
from
 (
  select 
    t.id, 
	l.match, 
	sum(case when t.id=l.team then 1 else -1 end) as wins,
	sum(case when t.id=l.team then 1 else 0 end) as GF,
	sum(case when t.id=l.team then 0 else 1 end) as GA
  from wd_teams t
  join wd_matches m on t.id in (m.home, m.away)
  join wd_timeline l on l.`match`=m.id and l.goal=1
  group by t.id, l.match
  )g
inner join wd_teams t on t.id=g.id
group by t.id, t.name
order by total_point desc,average  desc;

Nəticə:

İndi isə bizə lazımdır ki, hər komandanın neçənci yerdə olduğunu da göstərək.
Aşağıdakı üsuldan istifadə edirik:

 
set @row_num=0;

select
  @row_num:=@row_num+1 as rownum,
  t.id,
  t.name,
  count(*) as total_matchs,
  sum(case when g.wins>0 then 1 else 0 end) as wins,
  sum(case when g.wins=0 then 1 else 0 end) as draw,
  sum(case when g.wins=0 then 1 else 0 end) as lost,
  sum(case sign(g.wins)
    when 1 then 3
    when 0 then 1
    else 0 end) as total_point,
  sum(g.GF) as GF,
  sum(g.GA)as GA,
  sum(g.GF)-sum(g.GA) as average
from
 (
  select 
    t.id, 
	l.match, 
	sum(case when t.id=l.team then 1 else -1 end) as wins,
	sum(case when t.id=l.team then 1 else 0 end) as GF,
	sum(case when t.id=l.team then 0 else 1 end) as GA
  from wd_teams t
  join wd_matches m on t.id in (m.home, m.away)
  join wd_timeline l on l.`match`=m.id and l.goal=1
  group by t.id, l.match
  )g
inner join wd_teams t on t.id=g.id
group by t.id, t.name
order by total_point desc,average  desc;

Nəticə:

Səbəbi tam olaraq aydın olmadığından bu halın adını qoyuruq:
“unexpected” behaviour

Gördüyümüz kimi sıralama bizdə düzgün çıxmadı.
Həlli üçün isə aşağıdakı yoldan istifadə etmişəm. Böyük sorğunu from hissəyə əlavə edib, geri dönən nəticə əsasından row_num edirik:

 
select 
@row_num:=@row_num+1 as rownum,
zz.*
from (
select
  t.id,
  t.name,
  count(*) as total_matchs,
  sum(case when g.wins>0 then 1 else 0 end) as wins,
  sum(case when g.wins=0 then 1 else 0 end) as draw,
  sum(case when g.wins=0 then 1 else 0 end) as lost,
  sum(case sign(g.wins)
    when 1 then 3
    when 0 then 1
    else 0 end) as total_point,
  sum(g.GF) as GF,
  sum(g.GA)as GA,
  sum(g.GF)-sum(g.GA) as average
from
 (
  select 
    t.id, 
	l.match, 
	sum(case when t.id=l.team then 1 else -1 end) as wins,
	sum(case when t.id=l.team then 1 else 0 end) as GF,
	sum(case when t.id=l.team then 0 else 1 end) as GA
  from wd_teams t
  join wd_matches m on t.id in (m.home, m.away)
  join wd_timeline l on l.`match`=m.id and l.goal=1
  group by t.id, l.match
  )g
inner join wd_teams t on t.id=g.id
group by t.id, t.name) as zz
inner join
(select @row_num:=0) as r
order by zz.total_point desc,zz.average  desc

Nəticə:

Bu bizə lazım olan nəticədir.
Və bu sorğunu da from-a salmaqla biz daha da dəqiq olaraq istədiyimiz komandanın İD-sini verməklə o kamanda haqqında məlumatı onun turnir cədvəlində hazırkı tutduğu yeri öyrənə bilərik:

 
select
xx.*
from
(
select 
@row_num:=@row_num+1 as rownum,
zz.*
from (
select
  t.id,
  t.name,
  count(*) as total_matchs,
  sum(case when g.wins>0 then 1 else 0 end) as wins,
  sum(case when g.wins=0 then 1 else 0 end) as draw,
  sum(case when g.wins=0 then 1 else 0 end) as lost,
  sum(case sign(g.wins)
    when 1 then 3
    when 0 then 1
    else 0 end) as total_point,
  sum(g.GF) as GF,
  sum(g.GA)as GA,
  sum(g.GF)-sum(g.GA) as average
from
 (
  select 
    t.id, 
	l.match, 
	sum(case when t.id=l.team then 1 else -1 end) as wins,
	sum(case when t.id=l.team then 1 else 0 end) as GF,
	sum(case when t.id=l.team then 0 else 1 end) as GA
  from wd_teams t
  join wd_matches m on t.id in (m.home, m.away)
  join wd_timeline l on l.`match`=m.id and l.goal=1
  group by t.id, l.match
  )g
inner join wd_teams t on t.id=g.id
group by t.id, t.name) as zz
inner join
(select @row_num:=0) as r
order by zz.total_point desc,zz.average  desc) as xx
where xx.id=2;

Nəticə:

 
+--------+----+----------+--------------+------+------+------+-------------+------+------+---------+
| rownum | id | name     | total_matchs | wins | draw | lost | total_point | GF   | GA   | average |
+--------+----+----------+--------------+------+------+------+-------------+------+------+---------+
|      1 |  2 | Qarabağ  |            3 |    3 |    0 |    0 |           9 |    9 |    2 |       7 |
+--------+----+----------+--------------+------+------+------+-------------+------+------+---------+
1 row in set (0.01 sec)

Task completed 😉

Kateqoriyalar: MySQL Etiketlər:

MySQL Backup and backup prepare script usage video tutorial

MySQL backup ve prepare script-lerin istifadesi haqqinda video dersliyim:

Backuper: https://github.com/ShahriyarR/mysql_physical_backuper

Prepare: https://github.com/ShahriyarR/mysql_physical_backup_prepare

Kateqoriyalar: MySQL Video Tutorials

Setting up SSH public/private keys

Python ilə remote server-ə directory copy etmək istərkən, Python paramiko modulu aşağıdakı error-u verdi:

import paramiko
  File "/usr/local/lib/python3.3/site-packages/paramiko-1.11.0-py3.3.egg/paramiko/__init__.py", line 64, in <module>
ImportError: No module named 'transport'

Bu da Python 3 ilə paramiko-nun problemidir ki, bug kimi qeyd olunub.
Dolayısı ilə daha başqa üsullar axtararkən, məcburən test üçün Localdakı Ubuntu 12.04 ilə VirtualBox-da olan Centos 6.4 arasında public/private key connection verməli oldum. Əsas məqsəd parol yazmadan ssh connection açmaqdır.

Lakin biraz əziyyətli oldu ona görə də bura qeyd edirəm.

Remote Centos-da:

service sshd start

Ubuntu-da:
1.

root@sh-asus:~# ssh-keygen -t dsa
Generating public/private dsa key pair.
Enter file in which to save the key (/root/.ssh/id_dsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_dsa.
Your public key has been saved in /root/.ssh/id_dsa.pub.
The key fingerprint is:
7a:8a:0b:46:72:91:e7:ab:c4:70:a5:20:e5:da:29:48 root@sh-asus
The key's randomart image is:
+--[ DSA 1024]----+
|  .              |
| o .             |
|oE+ o            |
|++ B             |
|* B .   S        |
| O   . .         |
|  = . . .        |
| o o . o         |
|  . o..          |
+-----------------+

2. İndi isə bu key-i remote-a copy etməliyik:

root@sh-asus:~# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.1.105
/usr/bin/ssh-copy-id: ERROR: No identities found

3. Yuxarıdakı error-u həll etmək üçün:

root@sh-asus:~/.ssh# ssh-add
Could not open a connection to your authentication agent.

4. İndiki error-u da həll etmək üçün:

root@sh-asus:~# eval `ssh-agent`
Agent pid 19779

root@sh-asus:~# ssh-add
Identity added: /root/.ssh/id_dsa (/root/.ssh/id_dsa)

5. İdentity add olduqdan sonra:

root@sh-asus:~# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.1.105
root@192.168.1.105's password: 
Now try logging into the machine, with "ssh '192.168.1.105'", and check in:

  ~/.ssh/authorized_keys

to make sure we haven't added extra keys that you weren't expecting.

6. Və parol olmadan ssh connection yaradırıq:

root@sh-asus:~# ssh 192.168.1.105
Last login: Fri Sep 13 15:52:55 2013 from 192.168.1.103
[root@dhcppc5 ~]# cd .ssh
Kateqoriyalar: Linux Etiketlər:

Ümumi MySQL replication anlayışı,iş prinsipi

Bu video dərsdə ümumi olaraq MySQL high availability və onun ilkin növü olan MySQL replication-dan danışmışam.
ümidvaram ki, hər kəsə faydalı olacaqdır.

Kateqoriyalar: MySQL Video Tutorials

Ümumi MySQL arxitekturası və query processing

Bu video dərs düşünürəm ki, yeni başlayanlara həmçinin intermediate developer-lərə faydalı olacaqdır.

Ümumi MySQL strukturu və query processing-dən danışmışam.

Faydalı olması ümidi ilə.

Kateqoriyalar: MySQL