Arxiv

Posts Tagged ‘mysql’

Starting and Stopping MySQL Server

Start stop məsələsi elə də çətin olmamalıdır. Amma Start edib sonra stop edə bilməyəndə maraqlı olur 🙂
Birinci üsul:

-- START
[root@sh ~]# systemctl start mysqld.service

-- Shutdown
[root@sh ~]# systemctl stop mysqld.service

Normal qaydada işləyir və connection da gedir.

İkinci üsul:

-- START
[root@sh ~]# service mysqld start
Redirecting to /bin/systemctl start  mysqld.service

-- Shutdown
[root@sh ~]# service mysqld stop
Redirecting to /bin/systemctl stop  mysqld.service

Yenə də normal qaydada connect olmaq olur.

Üçüncü üsul:
mysqld_safe shell script-i var ki bu script çalışdırıldığında mysqld-ni avtomatik olaraq çağırır və server-i start edir. Location: /usr/bin
Sınayaq:

[root@sh ~]# cd /usr/bin
[root@sh bin]# ./mysqld_safe
130112 15:46:37 mysqld_safe Logging to '/var/log/mysqld.log'.
130112 15:46:37 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
.
.
.

Biraz gözlədim ki birdən nəsə olar amma sadəcə bu çəkildə gözləyir. Sən demə artıq mysqld_safe MySQL-i artıq start edib Və connection da gedir:

[root@sh ~]# mysql -u monty -p
Enter password: 
Welcome to the MySQL monitor.

İndi isə yuxarıda göstərilən qaydaların biri ilə Shutdown edək:

[root@sh ~]# systemctl stop mysqld.service

Xeyri yoxdu Shutdown olmur…Connection gedəcək adi qaydada

[root@sh ~]# mysql -u monty -p
Enter password: 
Welcome to the MySQL monitor.

Biraz axtarışdan sonra bu halda Shutdown etməyin 2 yolu var imiş:
1. Manual olaraq linux-da root kimi mysqld-ni kill etmək
2. mysqladmin shutdown

1.Killing
Running mysql proseslərə nəzər yetirdikdə:

[root@sh ~]# ps -ef|grep -i mysql
1000      9530     1  0 15:22 ?        00:00:02 gedit /var/log/mysqld.log
root     11573 10164  0 15:46 pts/4    00:00:00 /bin/sh ./mysqld_safe
mysql    12087 11573  0 15:46 pts/4    00:00:05 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --open-files-limit=65535 --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock
root     12518  8589  0 16:37 pts/1    00:00:00 grep --color=auto -i mysql

Bizə lazımdır PİD=12087 kill-lənsin 🙂

[root@sh ~]# kill 12087

Bundan sonra əvvəli mysqld_safe çağırılan pencərədə yeni yazı peyda olur:

[root@sh bin]# ./mysqld_safe
130112 15:46:37 mysqld_safe Logging to '/var/log/mysqld.log'.
130112 15:46:37 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130112 16:42:15 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

ENDED yazısı!
Sınamaq üçün connect olaq:

[root@sh ~]# mysql -u monty -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

2. mysqladmin shutdown

Yenidən mysqld_safe-i işlədək

[root@sh bin]# ./mysqld_safe
130112 16:47:02 mysqld_safe Logging to '/var/log/mysqld.log'.
130112 16:47:02 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
.
.
.

indi isə shutdown:

[root@sh ~]# mysqladmin -u root -p shutdown
Enter password:

Və sonda yenə ENDED görəcik

[root@sh bin]# ./mysqld_safe
130112 16:52:58 mysqld_safe Logging to '/var/log/mysqld.log'.
130112 16:52:58 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130112 16:53:17 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Təşəkkürlər 🙂

MySQL grant table and initial accounts

MySQL installation-la bərabər mysql,information_schema database-ləri gəlir.
*mysql bu yazıda server yox məhz mysql schema(database) adıdır*
mysql-də user cədvəli var ki, orada MySQL(server) user-lərin access privilege-ləri və.s məlumatları yerləşir.
mysql full table list:

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
24 rows in set (0.00 sec)

Elə bu haqda oxuyurdum ki maraqlı bir şey aşkarlamışam…Sən demə parolsuz root user-lər və ananonim user-lər mövcud imiş. Təbii ki bu təhlükəli haldı.
Beynəlxalq məsləhətə əsasən biz bütün parolsuz root-lara parol verəcik və ananonim user-ləri siləcik…

Yoxlamaq üçün bir neçə connection yerinə yetirək.
root connection no password

[root@sh ~]# mysql -u root --host=127.0.0.1
Welcome to the MySQL monitor.
.
.
.
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@127.0.0.1                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

Nə yaxşı 🙂

Anonym user connection:

[root@sh ~]# mysql -u --host=localhost
Welcome to the MySQL monitor.
.
.
.
mysql> show grants;
+--------------------------------------+
| Grants for @localhost                |
+--------------------------------------+
| GRANT USAGE ON *.* TO ''@'localhost' |
+--------------------------------------+
1 row in set (0.00 sec)

The USAGE privilege specifier stands for “no privileges.”

Həqiqətən də anonim user-lə heç bir şey etmək olmur.

mysql> use world;
ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'world'

Təcili olaraq bu yuxarıdakı 2 anonim user-i silirik:

mysql> DROP USER ''@'localhost';
Query OK, 0 rows affected (0.03 sec)

mysql> DROP USER ''@'sh.rzayev';
Query OK, 0 rows affected (0.11 sec)

Bəli silindilər:

Daha sonra parolsuz root-lara parol verək. Tək-tək fərqli parollar və yaxud toplu halda eyni parolu vermək olur:
Tək-tək:

mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('yeni_parol');
mysql> SET PASSWORD FOR 'root'@'::1' = PASSWORD('yeni_parol');
mysql> SET PASSWORD FOR 'root'@'host_name' = PASSWORD('yeni_parol');

Toplu halda:

mysql> UPDATE mysql.user SET Password = PASSWORD('yeni_parol')
    ->     WHERE User = 'root';
mysql> FLUSH PRIVILEGES;

FLUSH məcbur edir ki server grant-ları yenidən oxusun. Əksi təqdirdə yeni parollar təsirsiz qalır.
Yeni password-lər verildi:

Təşəkkürlər 🙂

Statements That Cause an Implicit Commit

MySQL Transaction
Transactions and Locks
Transaction başlatdıqda transaction ərzində daxil edilən bütün komandalar commit\rollback-i gözləməli olur. Və o zamana qədər heç bir dəyişiklik baş vermir. Lakin elə komandalar var ki, transaction daxilində olmasından asılı olmayaraq implicit commit olur.
Bunlardan Bəzilərinə baxacıq.
1.ALTER TABLE

-- cədvəlin yaradılması
mysql> create table commit_test(id int) engine=innodb;

mysql> show create table commit_test\G
CREATE TABLE `commit_test` (
  `id` int(11)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

(1)Transaction başladaq:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table commit_test modify id int not null;
Query OK, 0 rows affected (0.43 sec)
Records: 0  Duplicates: 0  Warnings: 0

Məntiqlə düşünməli idik ki, commit olunmayınca table alter olunmalı deyildi. Amma digər bir connection-dan baxdıqda artıq alter-in implicit commit olduğunun şahidi oluruq:
(2)

CREATE TABLE `commit_test` (
  `id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

2. DROP DATABASE

Ilk öncə schema-nı yaradaq:

mysql> create database for_drop;
Query OK, 1 row affected (0.11 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| backup_test        |
| cms                |
| data               |
| db1                |
| db_first           |
| exam_db            |
| for_drop    
.
.
18 rows in set (0.00 sec)

Transaction daxilində drop database-i işlədək:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> drop database for_drop;
Query OK, 0 rows affected (0.29 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

rollback etməyimizə baxmayaraq…artıq drop olunub…
Digər connection-dan baxdıqda:

mysql> use for_drop;
ERROR 1049 (42000): Unknown database 'for_drop'

3. TRUNCATE TABLE

mysql> insert into commit_test() values(1),(2),(3),(4),(5),(6),(7),(8);
Query OK, 8 rows affected (0.16 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> select * from commit_test;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
+----+
8 rows in set (0.00 sec)

Transaction daxilində truncate edək:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> truncate table commit_test;
Query OK, 0 rows affected (0.25 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

rollback-ə baxmayaraq truncate işlədi. digər connection-dan baxsaq:

mysql> select * from commit_test;
Empty set (0.00 sec)

4. RENAME TABLE

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> rename table commit_test to commit_test85;
Query OK, 0 rows affected (0.84 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

Digər connection-dan:

mysql> select * from commit_test;
ERROR 1146 (42S02): Table 'tr_blog.commit_test' doesn't exist
mysql> select * from commit_test85;
Empty set (0.00 sec)

Belə komandalar Sayca çoxdular hamısını yazmaq lazım deyil məncə.
İmplicit commit olan statements full list:
Statements That Cause an Implicit Commit

Təşəkkürlər 🙂

MySQL backup and recovery ümumi baxış

Backup-ın vacibliyindən danışmayacam. Təsadüfi deyil ki böyük proyektlərdə ayrıca backup\recovery team ayrılır. Və onların işi həqiqətən də recovery-yə yarayan backup-ları almaqdan ibarətdir. Yəni backup yalnızca mysqldump-la və yaxud da hər hansı php scritp-lə .sql və yaxud .txt faylı almaq deyildir.
Terminləri izah etməklə başlayaq.
1. Physical (Raw) backupPhysical backups consist of raw copies of the directories and files that store database contents. This type of backup is suitable for large, important databases that need to be recovered quickly when problems occur. A backup that copies the actual data files
MySQL data directory-də olan bütün fayllar,həmçinin binary log-u və.s backup etmək metodudur.
Yuxarıda da deyildiyi kimi böyük database-lər üçün əlverişlidir.
3 üsulla alınır:
Hot — MySQL işləyə-işləyə isti-isti backup almaq üsulu.MySQL Enterprise Backup bu üsuldan istifadə edir.
Cold — MySQL shut-down olunur daha sonra backup alınır
Warm — MySQL işlək vəziyyətdə saxlanılır,lakin table-lar lock olur, connection-lar bağlanır və.s

2. Logical backupA backup that reproduces table structure and data, without copying the actual data files.
Adətən mysqldump vasitəsilə alınır.

Bəs yaxşı restore nədir? recovery nədir?
Restore — sadəcə alınan backup-ı geri bərpa etməkdir.
Recovery — hər hansı crash, bədbəxt hadisə və.s-dan sonra backup-ı bərpa etməklə bərabər MySQL-in stabil işləməsini qarantiliyəcək əməliyyatlar toplusudur. Innodb crach recovery buna misal ola bilər.

Backup strategiyasını düşünərkən çox vacib suallara özünüzdə cavab tapın:
1. Siz recovery zamanı hansısa məlumatı itirməyi gözə alırsınız mı? Yəni Bazar günü saat 00:30-da backup aldıqdan sonra üstündən 20 saat keçdikdən sonra nəsə baş versə və siz bazar günkü backup-dan istifadə etsəniz son 20 saat ərzində baş vermiş dəyişiklikləri itirməyi gözə alırsınız mi?
Əgər alırsınızsa işiniz asanddır. Yox əgər deyirsinizsə ki,xeyr əsla heç bir əməliyyat itirilməməlidir o zaman sizə point-in-time recovery lazım olacaq. Yəni adicə mysqldump-la və yaxud php script-lə aldığınız full backup(logical backup) sizə yardım etməyəcək.

2. Sizin recovery zamanınız və sürətiniz nə qədər olmalıdır? Sizin üçün 3-4 saat və bəlkə də daha artıq davam edən recovery vaxtı qəbul ediləndir mi? bu 3-4-5 saat ərzində user-ləriniz dözə bilərlər mi? işləriniz yarımçıq qalar mı? və.s

3. Siz nələri recover etmək istəyirsiz? Bəlkə bütün server,bütün schema, bütün table və yaxud sadəcə 1 row, 1 statement?

Tələblərinizi gözdən keçirdin və onu bir kənara aydın şəkildə yazın.

MySQL backup və recovery teknikləri(daha advance),tool-lar və.s haqqında yazılar olacaq…
Təşəkkürlər 🙂

ALTER TABLE zamanı lock və performance problemi

Bu mövzumuz çoxlarımızın istifadə etdiyi ALTER TABLE komandasının maraqlı və eyni zamanda ziyanlı xüsusiyyətindən danışacıq. Təbii ki bu kiçik ölçülü cədvəllərdə bir çətinlik yaratmır amma olduqca böyük cədvəllər üçün baş ağrısı ola bilər. Hətta bir forumda oxuduğuma görə 1 gün və daha artıq davam edən ALTER var imiş…

ALTER TABLE nə edir? Bu komandanın iş prinsipi belədir. Məsələn bizdə table A var. A ibarətdir 3 sütundan və 100 row-dan. indi biz A-ya index əlavə etmək istəyirik(məsələn: alter table A add index(id)).
Bu zaman MySQL
*) A-nı lock edəcək(yəni A-ya heç bir insert,update,select getmiyəcək).
*) A-ya bənzər boş bir table yaradacaq (təbii ki A adlı).
*) A-da olan bütün məlumatları (indiki halda 100 row-nu) az öncə yaratdığı boş table-a copy\insert edəcək.
*) bütün insert-ləri bitirdikdən sonra köhnə A-ni drop edəcək (siləcək)

Qısa olaraq Alter bunu edir. Təbii ki bu 40-50 milyon row-luq cədvəllərdə dəhşətli bir vaxt aparacaq. Ən pis yanı da odur ki, əgər Alter 1 gün davam etməli olsa onda belə çıxır ki sizin table 1 gün select\update\insert -lərə bağlı olacaq.

İnanmıyanlara əyani olaraq göstərək…Sınaq üçün mənim əlimdə olan 2.5 milyon row-luq table olan sales-dən istifadə edəcik. Sales dump faylını burdan yükləyə bilərsiniz Sales table dump

Sales table-ı yaratdıqdan sonra və məlumatları insert etdikdən sonra original sales-ə dəyməmək məqsədilə. sales_test1 cədvəli yaradırıq.

Original sales strukturumuz budur:

CREATE TABLE `sales` (
  `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=2500005 DEFAULT CHARSET=latin1

Yeni sales_test1 cədvəlini yaradaq:

mysql> create table sales_test1 as select * from mysqlspp.sales;
Query OK, 2500004 rows affected (1 min 11.30 sec)
Records: 2500004  Duplicates: 0  Warnings: 0

sales_test1 cədvəl strukturumuz bu cürdür:

CREATE TABLE `sales_test1` (
  `SALES_ID` int(8) NOT NULL DEFAULT '0',
  `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'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

Gördüyümüz kimi bizim yeni cədvəlimiz olan sales_test1-də original sales cədvəlində olan 2 məlumat yoxdur
PRIMARY KEY (`SALES_ID`) və KEY `sales_cust_idx` (`CUSTOMER_ID`).
Bunları əlavə etmək lazımdır ki, ALTER TABLE bayaqdan elə öz vaxtını gözləyir 😛
Bəli primary key əlavə edək:

mysql> alter table sales_test1 add primary key(sales_id);
Query OK, 0 rows affected (1 min 27.46 sec)
Records: 0  Duplicates: 0  Warnings: 0

İşləmə vaxtı 1 min 27.46 sec
Alter-lə eyni vaxtda bir dənə select versək:

mysql> select * from sales_test1 where sales_id=1;
.
.
.
1 row in set (1 min 28.44 sec)

İşləmə vaxtı 1 min 28.44 sec
Gördüyümüz kimi bizim select işləməyə başlamaq üçün Alter-in bitməyini gözlədi.Yəni həqiqətən də Alter bizim table-ı lock etdi.

Sözsüz ki, bu arzu olunan hal deyil. Yəni bizə elə bir şey lazımdır ki, Alter zamanı digər istifadəçilər rahatlıqla öz update,insert və select-lərini verə bilsinlər.
Bunun üçün pt-online-schema-change tool-u mövcuddur. Bu cür tool-ların əsas məntiqi ondan ibarətdir ki, lock etmədən alter etmək.
pt-online-schema-change Percona Toolkit-in tərkibinə daxildir.
Daha ətraflı: pt-online-schema-change
Buna oxşar 3 əlavə tool var buyurun baxın:
1. Facebook Online Schema Change for MySQL
2. Large Hadron Migrator
3. oak-online-alter-table

Mən Percona Toolkit-dən istifadə edəcəm. İni isə pt-online-schema-change necə gözəl bir tool olduğuna baxin:

[root@sh ~]# pt-online-schema-change --progress time,1 --execute --user root --password 12345 --alter "add index(customer_id)" D=my_new_db,t=sales_test1
Altering `my_new_db`.`sales_test1`...
Creating new table...
Created new table my_new_db._sales_test1_new OK.
Altering new table...
Altered `my_new_db`.`_sales_test1_new` OK.
Creating triggers...
Created triggers OK.
Copying approximately 2500273 rows...
Copying `my_new_db`.`sales_test1`:   1% 00:58 remain
Copying `my_new_db`.`sales_test1`:   2% 01:13 remain
Copying `my_new_db`.`sales_test1`:   3% 01:13 remain
Copying `my_new_db`.`sales_test1`:   4% 01:24 remain
.
.
.
.
Copying `my_new_db`.`sales_test1`:  99% 00:02 remain
Copying `my_new_db`.`sales_test1`:  99% 00:01 remain
Copying `my_new_db`.`sales_test1`:  99% 00:01 remain
Copying `my_new_db`.`sales_test1`:  99% 00:00 remain
Copied rows OK.
Swapping tables...
Swapped original and new tables OK.
Dropping old table...
Dropped old table `my_new_db`.`_sales_test1_old` OK.
Dropping triggers...
Dropped triggers OK.
Successfully altered `my_new_db`.`sales_test1`.

Bəli məncə aydın olur hər şey. Bu tool-un vasitəsilə ALTER TABLE-ın nələr etdiyini incə-incə izləmək olur…
Və həmçinin eyni zamanda verdiyimiz select-ə baxın:

mysql> select * from sales_test1 where sales_id=5;
.
.
.
1 row in set (0.00 sec)

Gördüyümüz kimi heç bir lock baş vermədi…Məhz buna görə də Select-in müddəti 0.00 sec

MySQL-də Transaction və Lock haqqında daha ətraflı: Transactions and Locks

Ümidvaram ki, faydalı oldu… Təşəkkürlər 🙂

innodb_buffer_pool_size ERROR in Fedora 17

Deməli dünən gecə
innodb_buffer_pool_size -ı artırmaq istədikdə MySQL 5.5 start olunmadı və eləcə qaldı…Yani heç bir ERROR mesajı filan da verilmədiyindən biraz çətinlik yaratdı.
Məndə i3-350M processor və 3GB memory var. 32 bit-lik Fedora 17 istifadə edirəm. Zənnimcə 3 RAM üçün 32 bitlik OS yaxşı seçim olmalı idi.
Documentation-da deyilir ki, innodb_buffer_pool_size-ı memory-nin 80%-nə qədər artırmaq mümkündü. Bəlkə də bunu 64 bitlik sistemlər üçün deyiblər çünki x32-də bu % doğru deyil.
Mən də documentation-a qulaq asdım… və müəyyən elədim ki Fedora 17 mənim memory-mi tamamilə doğru görür:

[root@sh ~]# free -m
             total       used       free     shared    buffers     cached
Mem:          2950       1441       1508          0         65        622
-/+ buffers/cache:        754       2195
Swap:         4991          0       4991

Çünki məsələn 6 GB-lıq memory ilə x32 istifadə etdikdə max 3.5-ni görəcək ən azından belə şayələr var.

Həmçinin system manitor-da da hər şey yolunda görünür.

Bəli keçək işə..
2.9-un 80%-i=2.32. Yəni burdan belə nəticə çıxarmaq olar ki, innodb_buffer_pool_size=2.32GB verə bilərəm. Amma mən o qədər yox ilk öncə 1.9 GB verdim. Bu çox asan bir şeydir. my.cnf faylında [mysqld] kataloqunun altına innodb_buffer_pool_size=2040109465 (byte-la yazıram. 1.9 gb=2040109465 byte) yazırıq.
my.cnf default olaraq yerləşir /etc/my.cnf
Sizin my.cnf faylınızın harda yerləşdiyini öyrənmək üçün:

[root@sh ~]# mysql --verbose --help
mysql  Ver 14.14 Distrib 5.5.28, for Linux (i686) using readline 5.1
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
.
.
.
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 

my.cnf-i Yuxarıdakı directory-lərdən birində tapa biləcəksiz.

Bəli innodb_buffer_pool_size=2040109465-i eyd etdikdən sonra server-imizi stop\start edirik. Və dəhşətli mənzərə:
Terminaldan start edərkən belə:

[root@sh ~]# systemctl start mysqld.service
Job failed. See system journal and 'systemctl status' for details.

MySQL Workbench-dən start edərkən isə
Checked server status: Server is stopped.

Yəni heç bir hadisə baş vermir.Çox pis təsir edir adama 😛

Dərhal log-lara nəzər salmaq lazımdı təbii ki. mysqld.log yerləşir /var/log/mysqld.log
Log-da yazılan hər şeyi aydın edir:

121201 12:13:07 InnoDB: Fatal error: cannot allocate memory for the buffer pool

Bəli indi isə 1.8 GB verək. innodb_buffer_pool_size=1932735283 (yenə byte-la). Və yenə də eyni ERROR

Və ən nəhayət 1.7 GB verək. innodb_buffer_pool_size=1825361100

Nəhayət ki üzümüz güldü 😀

İndi isə ümumiləşdirmə aparaq.
2.9-un 80%-i=2.32 etdiyi halda bizim i3-350 M 3 GB-lıq x32 Fedora 17 sistemimiz İnnoDB buffer pool üçün yalnızca 1.7gb ayıra bildi.
1.7 isə 2.9-un 58.62%-dir. Deməli ki bu cür mənim sistemimə oxşar sistemlər üçün 80% yox ən çoxu 58% hesablamaq lazımdır. Əks halda buna oxşar crash-lar qaçınılmaz olacaq

Təşəkkürlər 🙂

Transactions and Locks

Bu mövzuda MySQL-də transaction və lock anlyışlarından danışacıq. Xüsusən deadlock-un nə olduğunu və MySQL-də bunun qarşısının necə alınacağına baxacıq.
*Bu yazıda MySQL Stored Procedure-dan istifadə olunub.*
İlk öncə transaction nədir? Bu haqda bir yazlm var artıq MySQL Transaction
Qısaca olaraq :
A transaction is a set of one or more SQL statements that are logically grouped together and that must be either applied to the database in their entirety or not applied at all.
Indi isə biraz Lock-dan danışaq…Lock adından da göründüyü kimi bağlamaq,kilidləmək deməkdir. Transaction mövzusunda Lock-un yeri ondan ibarətdir ki, məsələn biz bir cədvəldən məlumat çıxardıqda digər bir user həmin an bizim cədvələ update verə bilər. Və əgər birinci select-imizdə bizim məlumat deyək ki, 2000 idisə ikinci select-də bu məlumat 1500 ola bilər. Yəni biz select verərkən kimsə gəlib update və yaxud insert verəcək və bizim select-imiz hər dəfə fərqli bir məlumat çıxaracaq…Bu həmçinin elə update-in özünə də aiddir. Deyək ki, biz update edirik və İD=200 edirik digər birisi də həmin an İD-ni 205 verir…Belələliklə bizim məlumatlar öz dəqiqliyini itirmiş olur. Aşağıdakı şəkilə diqqət yetirsək (bayaqdan gimp-də şəkilə arrow əlavə eliyənə qədər canım çıxıb :D)

Şəkil üzərində daha yaxşı izah etmək olacaq bunu…Bizim balansımız 2000-dir…2 user transaction başladır…Birinci user balansımızı 100$ azaldır…Eyni anda 2ci user balansımızı 300$ artırır…İndi database hansını qəbul etməlidir? Günah bizdədir ki, cədvəli lock etməmişik və 2 user eyni anda update etməklə məşğuldur.Öz aramızdı Çox pis-pis işlərnən məşğuldurlar…Burda dəhşətli məqam odur ki, database hansı user birinci commit edir onu qəbul edəcək…Yəni ki pulumuz ya 1900 ya da 2300 olacaq, o baxır hansı user tez tərpənəcək 🙂 belə də şey olar??? uşaq oyuncağıdır bu???
Deməli lock bu imiş…2ci şəklimizə diqqət yetirək:

2ci şəklimizdə görürük ki,1ci user (transaction A) update edərkən 2ci user(transaction B) lock olunub və 2ci user yalnız 1ci transaction bitdikdən sonra(indiki halda commit) öz şəxsi yüksək sevincli update-ni etsin…Lock anlayışı bundan ibarətdir…

MySQL\İnnoDB “row-level lock”-la məşğuldur…Yəni ki bizim misalda 2ci user account_id=15 olan row-nu asanlıqla update edə bilər. Əsas odur ki 2 transaction eyni row-nu update edə bilmir…Bu da maraqlı bir xüsusiyyətdi…Həqiqətən də bir row-nu update etmək üçün bütün table-ı lock etməyin mənası yoxdu…
Biz istədiyimiz vaxt hər hansı bir table-ı lock edə bilərik əslində…Amma 99% hallarda storage engine(İnnoDB) bu işi bizim əvəzimizə görəcək.
Aşağıdakı hallardan hər hansı birində lock baş verəcək:
1) Update edərkən bütün update olunacaq row-lar lock olunur
2) İnsert etdikdə hər hansı primary key və unique key olan row-lar lock olunacaq ki, duplicate key error və.s baş verməsin…
3) LOCK TABLES -dan istifadə etməklə bütün cədvəli lock edə bilərik amma bu məsləhət görülmür…Çünki deyək ki 1 update üçün bütün cədvəli lock edib camaatı gözlətməyin bir mənası yoxdu.
4) Maraqlı bir şey də var…Select verərkən select-in sonunda FOR UPDATE və yaxud LOCK İN SHARE MODE yazmaqla biz select-dən gələn bütün row-ları lock etmiş oluruq.

Davam edək…Az öncə 4cü bənndə select-də lock-un istifadəsindən yazdıq…Select-in bu cür istifadəsi zamanı
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction ERROR-u çıxa bilər…Gəlin bu error-u sınaq üçün çıxardaq…Lakin bundan əvvəl innodb_lock_wait_timeout system variable-ından danışaq. bu system dəyişəni adından da göründüyü kimi lock-un açılması üçün gözləmə vaxtını təyin edir.
default olaraq bu vaxt 50 saniyə təşkil edir.

mysql> select @@innodb_lock_wait_timeout;
+----------------------------+
| @@innodb_lock_wait_timeout |
+----------------------------+
|                         50 |
+----------------------------+
1 row in set (0.00 sec)

biz bunu qəsdən 10 saniyə edək:

mysql> set @@innodb_lock_wait_timeout=10;
Query OK, 0 rows affected (0.00 sec)

Daha sonra 101000 row-luq bir select verək…Və eyni zamanda da bir update!
Select:

mysql> select * from customers for update;
.
.
.
101000 rows in set (0.78 sec)

Update:

mysql> update customers set customer_name='Shahriyar' where customer_id=500;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Bəli həqiqətən də select-imiz bizim cədvəli 10 saniyədən daha artıq bir müddətə lock etdiyi üçün qəşəng bir ERROR çıxdı…
Bu andan etibarən biz bu tip error-lar üçün stored procedure-lardan istifadə edəcik.Mən stored procedure-ların yaradılması, syntax xüsusiyyətləri haqqında ayrıca bir mövzu yazmaq istəmirəm.Bunun əvəzinə stored procedure-ların faydalı ola biləcəyi və çox əlverişli olduğu anlar haqqında yazacam…Ele indi həmin andır necə deyərlər…
Biz select-imizi adi şəkildə verəcik.Update-imizi isə procedure içində verəcik…Əgər ERROR çıxsa handler vasitəsilə bu ERROR-la məşğul olacıq. Əgər ERROR çıxmasa adi qaydada update olunacaq…Procedure kodumuz:

delimiter $$

create procedure lock_timeout_test
		(in_customer_name varchar(30),
		 in_customer_id int
		 )
begin 
		declare var_lock_timeout int default 0;
		
		declare lock_timeout_detected condition for 1205;
		declare continue handler for lock_timeout_detected set var_lock_timeout=1;

		set var_lock_timeout=0;
		
		start transaction;
			
			update customers set customer_name=in_customer_name
					where customer_id=in_customer_id;
		commit;

		if var_lock_timeout=1 then
				select 'Lock wait timeout exceeded ERROR xahis olunur select-lerde diqqetli olun';
				-- ve yaxud ERROR-la bagli istenilen nese!
		else 
				select 'Updated';
		end if;
	
end$$

Və yuxarıdakı kimi yenə select versək:

mysql> select * from customers for update;
.
.
.
101000 rows in set (0.41 sec)

Və procedure-umuzdan istifadə etsək:

mysql> call lock_timeout_test('Shahriyar',500);
+--------------------------------------------------------------------------+
| Lock wait timeout exceeded ERROR xahis olunur select-lerde diqqetli olun |
+--------------------------------------------------------------------------+
| Lock wait timeout exceeded ERROR xahis olunur select-lerde diqqetli olun |
+--------------------------------------------------------------------------+
1 row in set (10.88 sec)

Gözəldi elə deyil mi? 😛

Sonuncu və ən əhəmiyyətli mövzu Deadlock…
A deadlock occurs when two transactions are each waiting for the other to release a lock-they each block each other, and neither can proceed.
Şəklimizə diqqət yetirək:

Və şəkildən də göründüyü kimi Transaction A update edir, daha sonra Transaction B update edir(Transaction A-nın sonlanmasını gözləmədən) və davam edərək Transaction A yenə update edir bu zaman artıq Transaction B-ni gözləməli olur. Və ən sonunda Transaction B update edir və bu zaman da Transaction A-ni gözləməli olur…Yəni o bunu gözləyir bu onu gözləyir elə gözləyə-gözləyə qalırlar ki axırda da olur deadlock…
Deadlock baş verdikdə MySQL\İnnoDB deadlock-u avtomatik detect edəcək, 2 transaction-dan birini məcburi rollback edəcək və yekə bir error çıxardacaq…Rollback olunan transaction isə cədvələ ən az dəyişiklik etməyə çalışan transaction olacaq…
Sınaq üçün bir deadlock yaradaq 🙂 Cədvəlimiz və içindəki məlumatlar:

CREATE TABLE account_balance (
  account_id int(11) NOT NULL,
  balance decimal(6,2) NOT NULL DEFAULT '0.00',
  account_timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO account_balance (account_id,balance,account_timestamp) VALUES (1,500.00,'2005-12-13 15:12:34');
INSERT INTO account_balance (account_id,balance,account_timestamp) VALUES (2,9999.99,'2005-12-11 05:01:41');
INSERT INTO account_balance (account_id,balance,account_timestamp) VALUES (324,4000.00,'2005-07-09 16:19:57');
INSERT INTO account_balance (account_id,balance,account_timestamp) VALUES (916,2000.00,'2005-07-09 16:19:57');

Və aşağıdakıları sırası ilə yazsaq(qeyd edim ki aşağıdakılar 2 fərqli transaction-dan yazılmalıdır):

-- 1ci transaction/ ilk once bunu yazin(1)
start transaction;

update account_balance
set balance=balance-100
where account_id=2;

-- 2ci transaction/ daha sonra bunu(2)
start transaction;

update account_balance
set balance=balance-300
where account_id=1;

-- 1ci transaction/ 3cu olaraq bunu (3)
update account_balance
set balance=balance+100
where account_id=1;

-- 2ci transaction/ 4cu olaraq da bunu (4)

update account_balance
set balance=balance+300
where account_id=2;

Və şəkillərdən də göründüyü kimi ürəyimiz istiyən şəkildə ERROR-umuzu alırıq:

Deadlock hər database-də meydana gələ bilər amma MySQL\İnnoDB kimi row-level locking edən database-lərdə bu hadisə çox nadir hallarda baş verəcək…Əgər ki, siz hər hansı iş üçün şübhəlisiniz ki, burada deadlock ola bilər onu handler-lə qarşılamaq olar.Bu zaman yenə stored procedure-dan istifadə edəcik.Procedure kodumuz:

delimiter $$

create procedure deadlock_test
				(from_account int,
				 to_account int,
				 tfer_amount numeric(10,2))
begin 
		
		declare deadlock int default 0;
		declare attempts int default 0;

		tfer_loop: while(attempts<3) do
			begin 
					declare deadlock_detected condition for 1213;
					declare exit handler for deadlock_detected
						begin 
								rollback;
								set deadlock=1;
						end;
					set deadlock=0;
					
					start transaction;
					
					update account_balance
						set balance=balance-tfer_amount
					where account_id=from_account;

					update account_balance
						set balance=balance+tfer_amount
					where account_id=to_account;

					commit;
			end;
		
		if deadlock=0 then
			leave tfer_loop;
		else 
		set	attempts=attempts+1;
		end if;
		
		end while tfer_loop;

		if deadlock=1 then
			select 'Deyisiklik olmadi...3 defe cehd olunmagina baxmayaraq Deadlock ERROR';
		else 
			select concat('Updated ',attempts,' cehd olundu');
		end if;
end$$

Bizim procedure Update etməyə çalışır.Əgər update zamanı deadlock baş verərsə, 3 dəfə transaction-u yenidən başladacaq(while loop). 3 dəfə cəhd etdikdən sonra yenə deadlock aradan qalxmayırsa loop-dan çıxır və
“Deyisiklik olmadi…3 defe cehd olunmagina baxmayaraq Deadlock ERROR” mesaji gosterilir.
Yox əgər heç bir deadlock baş vermirsə, sakitcə update olunur və Updated mesajı verilir.Vəssalam 🙂

Təbii ki bizim kodumuza bu qədər Error handling yerləşdirdikdə onun oxunaqlığı azalır. Yuxarıdakı proceduru daha oxunaqlı vəziyyətə gətirək. Həmçinin aşağıdakı koddan error handler, loop və if-lər çıxardılmışdır. Burada account_id-lər select tərəfindən növbə ilə lock olunduğu üçün heç bir deadlock təhlükəsi yoxdur. Prosedur kodumuz:

delimiter $$

create procedure deadlock_test2
		(from_account int,
		to_account int,
		tfer_amount numeric(10,2))
begin 
	declare local_account_id int;
	declare lock_cursor cursor for
		select account_id
			from account_balance
		where account_id in(from_account,to_account)
	    order by account_id
		for update;

	start transaction;
	
	open lock_cursor;
	fetch lock_cursor into local_account_id;

	update account_balance
		set balance=balance-tfer_amount
	where account_id=from_account;

	update account_balance
		set balance=balance+tfer_amount
	where account_id=to_account;

	close lock_cursor;

	commit;

end$$

Deadlock haqqında bu qədər. Gördüyümüz kimi bu problemdən 2 növ prosedurla yaxa qurtarmaq olur 🙂
Gələn yazımızda Lock Strategy-lərdən danışacıq…
Fikirlərinizi bölüşməyi unutmayın…Təşəkkürlər…

Kateqoriyalar: MySQL Etiketlər: , , ,