wiki:rozaniec_mariadb
Różaniec w MariaDB
Postanowiłem zrobić bazę danych w MariaDB, która jest w stanie bardzo szybko odmawiać różaniec.
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)
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:
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)
Procedury są bardzo proste i zawierają powtarzające się selecty słów poszczególnych modlitw:
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)
Malutki skrypt, który otwiera 24 połączenia do bazy danych oraz odmawia różaniec na 24 wątkach po 3,5GHz:
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)
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.
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.
MariaDB [rozaniec]> select count(tik) from licznik; +------------+ | count(tik) | +------------+ | 223490 | +------------+ 1 row in set (0.013 sec)
Najprostszym sposobem na zatrzymanie podprocesów ze skryptu pythona jest restart bazy danych.
Prace optymalizacyjne trwają.
wiki/rozaniec_mariadb.txt · ostatnio zmienione: 2022/10/05 17:29 przez lukasz