====== Różaniec w MariaDB ======

Postanowiłem zrobić bazę danych w MariaDB, która jest w stanie bardzo szybko odmawiać różaniec. 

<code>
MariaDB [rozaniec]> describe modlitwy;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| tytul | varchar(50) | YES  |     | NULL    |                |
| slowa | text        | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.001 sec)

MariaDB [rozaniec]> describe tajemnice;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| rodzaj | varchar(40) | YES  |     | NULL    |                |
| numer  | smallint(6) | YES  |     | NULL    |                |
| nazwa  | varchar(60) | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.001 sec)

MariaDB [rozaniec]> describe licznik;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| tik   | tinyint(1) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.001 sec)
</code>

Ponieważ w tabeli licznik umieszczany jest nowy wiersz po każdym różańcu, jest ona typu //Memory//, żeby szybciej działać.

Dla ułatwienia utworzono procedury:

<code>
MariaDB [rozaniec]> show procedure status;
+----------+---------------------+-----------+-----------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db       | Name                | Type      | Definer               | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+----------+---------------------+-----------+-----------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| mysql    | AddGeometryColumn   | PROCEDURE | mariadb.sys@localhost | 2022-10-05 16:21:50 | 2022-10-05 16:21:50 | INVOKER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| mysql    | DropGeometryColumn  | PROCEDURE | mariadb.sys@localhost | 2022-10-05 16:21:50 | 2022-10-05 16:21:50 | INVOKER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| rozaniec | dziesiatek_rozanca  | PROCEDURE | root@localhost        | 2022-10-05 16:23:45 | 2022-10-05 16:23:45 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| rozaniec | odmawianie_rozanca  | PROCEDURE | root@localhost        | 2022-10-05 16:23:45 | 2022-10-05 16:23:45 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| rozaniec | poczatek_rozanca    | PROCEDURE | root@localhost        | 2022-10-05 16:23:45 | 2022-10-05 16:23:45 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| rozaniec | tajemnice_bolesne   | PROCEDURE | root@localhost        | 2022-10-05 16:23:45 | 2022-10-05 16:23:45 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| rozaniec | tajemnice_chwalebne | PROCEDURE | root@localhost        | 2022-10-05 16:23:45 | 2022-10-05 16:23:45 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| rozaniec | tajemnice_radosne   | PROCEDURE | root@localhost        | 2022-10-05 16:23:45 | 2022-10-05 16:23:45 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| rozaniec | tajemnice_swiatla   | PROCEDURE | root@localhost        | 2022-10-05 16:23:45 | 2022-10-05 16:23:45 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+----------+---------------------+-----------+-----------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
9 rows in set (0.001 sec)
</code>

Procedury są bardzo proste i zawierają powtarzające się selecty słów poszczególnych modlitw:

<code>
MariaDB [rozaniec]> show create procedure odmawianie_rozanca;
+--------------------+-------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure          | sql_mode                                                                                  | Create Procedure                                                                                                                                                       | character_set_client | collation_connection | Database Collation |
+--------------------+-------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| odmawianie_rozanca | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `odmawianie_rozanca`()
BEGIN while 1=1 do call tajemnice_chwalebne(); insert into licznik (tik) values (1); end while; END | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+--------------------+-------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.000 sec)

MariaDB [rozaniec]> show create procedure tajemnice_chwalebne;
+---------------------+-------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure           | sql_mode                                                                                  | Create Procedure                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | character_set_client | collation_connection | Database Collation |
+---------------------+-------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| tajemnice_chwalebne | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `tajemnice_chwalebne`()
BEGIN call poczatek_rozanca(); select nazwa from tajemnice where rodzaj='Chwalebne' and numer=1; call dziesiatek_rozanca(); select nazwa from tajemnice where rodzaj='Chwalebne' and numer=2; call dziesiatek_rozanca(); select nazwa from tajemnice where rodzaj='Chwalebne' and numer=3; call dziesiatek_rozanca(); select nazwa from tajemnice where rodzaj='Chwalebne' and numer=4; call dziesiatek_rozanca(); select nazwa from tajemnice where rodzaj='Chwalebne' and numer=5; call dziesiatek_rozanca(); END | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+---------------------+-------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.001 sec)
</code>

Malutki skrypt, który otwiera 24 połączenia do bazy danych oraz odmawia różaniec na 24 wątkach po 3,5GHz:

<code>
cat rozaniec.py 
#!/usr/bin/python

import subprocess
command = 'mysql -e "call odmawianie_rozanca()" rozaniec &>/dev/null'

p1 = subprocess.Popen(command, shell=True)
p2 = subprocess.Popen(command, shell=True)
p3 = subprocess.Popen(command, shell=True)
p4 = subprocess.Popen(command, shell=True)
p5 = subprocess.Popen(command, shell=True)
p6 = subprocess.Popen(command, shell=True)
p7 = subprocess.Popen(command, shell=True)
p8 = subprocess.Popen(command, shell=True)
p9 = subprocess.Popen(command, shell=True)
p10 = subprocess.Popen(command, shell=True)
p11 = subprocess.Popen(command, shell=True)
p12 = subprocess.Popen(command, shell=True)
p13 = subprocess.Popen(command, shell=True)
p14 = subprocess.Popen(command, shell=True)
p15 = subprocess.Popen(command, shell=True)
p16 = subprocess.Popen(command, shell=True)
p17 = subprocess.Popen(command, shell=True)
p18 = subprocess.Popen(command, shell=True)
p19 = subprocess.Popen(command, shell=True)
p20 = subprocess.Popen(command, shell=True)
p21 = subprocess.Popen(command, shell=True)
p22 = subprocess.Popen(command, shell=True)
p23 = subprocess.Popen(command, shell=True)
p24 = subprocess.Popen(command, shell=True)
</code>

Przekierowanie wyjścia do /dev/null nie jest wymagane, ale wąskim gardłem okazało się łącze sieciowe z serwera do laptopa, które nie nadążało z przesyłaniem słów modlitwy w celu wypisania ich na terminal.

{{ :wiki:rozaniec_htop.jpg?400 |}}

Po jakimś czasie zaczyna robić się głośno.

Serwer jest w stanie odmawiać ponad dwieście tysięcy różańców w ciągu minuty.

<code>
MariaDB [rozaniec]> select count(tik) from licznik;
+------------+
| count(tik) |
+------------+
|     223490 |
+------------+
1 row in set (0.013 sec)
</code>

Najprostszym sposobem na zatrzymanie podprocesów ze skryptu pythona jest restart bazy danych.

Prace optymalizacyjne trwają.