Yadda za a Shigar, Amintacce da Tunatar Ayyukan Ayyukan MariaDB Database Server

Sabar bayanan bayanai wani muhimmin abu ne na kayan aikin cibiyar sadarwa da ake buƙata don aikace-aikacen yau. Ba tare da ikon adanawa, ɗagawa, sabuntawa, da share bayanai ba (lokacin da ake buƙata), fa'ida da fa'idar yanar gizo da aikace-aikacen tebur na zama iyaka.

Bugu da kari, sanin yadda ake girka, sarrafa, da kuma daidaita uwar garken bayanai (domin ta yi aiki kamar yadda ake tsammani) wata fasaha ce mai mahimmanci da kowane mai gudanar da tsarin dole ya samu.

A cikin wannan labarin za mu ɗan yi bitar yadda ake shigar da amintaccen uwar garken bayanan MariaDB sannan za mu yi bayanin yadda ake daidaita shi.

Shigarwa da Tabbatar da uwar garken MariaDB

A cikin CentOS 7.x, MariaDB ya maye gurbin MySQL, wanda har yanzu ana iya samuwa a cikin Ubuntu (tare da MariaDB). Haka yake ga openSUSE.

Don taƙaitawa, za mu yi amfani da MariaDB ne kawai a cikin wannan koyawa, amma da fatan za a lura cewa ban da samun sunaye daban-daban da falsafar ci gaba, duka Tsarin Gudanar da Bayanan Bayanai (RDBMSs a takaice) kusan iri ɗaya ne.

Wannan yana nufin cewa umarnin abokin ciniki iri ɗaya ne akan duka MySQL da MariaDB, kuma fayilolin sanyi suna suna kuma suna cikin wurare iri ɗaya.

Don shigar da MariaDB, yi:

--------------- On CentOS/RHEL 7 and Fedora 23 --------------- 
# yum update && yum install mariadb mariadb-server # CentOS 

--------------- On Debian and Ubuntu --------------- 
$ sudo aptitude update && sudo aptitude install mariadb-client mariadb-server 

--------------- On openSUSE --------------- 
# zypper update && zypper install mariadb mariadb-tools # openSUSE

Lura cewa, a cikin Ubuntu, za a nemi ku shigar da kalmar sirri don mai amfani da tushen RDBMS.

Da zarar an shigar da fakitin da ke sama, tabbatar cewa sabis ɗin bayanan yana gudana kuma an kunna shi don farawa akan boot (a cikin CentOS da openSUSE kuna buƙatar yin wannan aikin da hannu, yayin da a cikin Ubuntu tsarin shigarwa zai riga ya kula da shi). na ka):

--------------- On CentOS/RHEL 7 and Fedora 23 --------------- 
# systemctl start mariadb && systemctl enable mariadb 

--------------- On openSUSE --------------- 
# systemctl start mysql && systemctl enable mysql

Sannan kunna rubutun mysql_secure_installation. Wannan tsari zai baka damar:

saita/sake saita kalmar wucewa don tushen mai amfani da RDBMS
cire shigar da ba a san su ba (don haka ba masu amfani da ingantaccen asusu damar shiga RDBMS)
ƙasa tushen tushen injuna banda localhost
cire bayanan gwaji (wanda kowa zai iya shiga)
kunna canje-canje masu alaƙa da 1 zuwa 4.

Don ƙarin cikakkun bayanai game da wannan tsari, zaku iya komawa zuwa sashin shigarwa na Post a cikin Sanya MariaDB Database a cikin RHEL/CentOS/Fedora da Debian/Ubuntu.

Ana saita uwar garken MariaDB

Ana karanta zaɓuɓɓukan daidaitawar tsoho daga fayiloli masu zuwa a cikin tsari da aka bayar: /etc/mysql/my.cnf, /etc/my.cnf, da ~ /.my.cnf .

Mafi yawan lokuta, kawai /etc/my.cnf ke wanzuwa. A kan wannan fayil ɗin ne za mu saita saitunan saitunan uwar garke (wanda za'a iya soke shi tare da saitunan iri ɗaya a cikin ~/.my.cnf ga kowane mai amfani).

Abu na farko da ya kamata mu lura game da my.cnf shi ne cewa an tsara saituna zuwa rukunoni (ko ƙungiyoyi) inda kowane nau'in suna ke rufe tare da madauri mai murabba'i.

Ana ba da saitunan tsarin uwar garke a cikin [mysqld] sashe, inda yawanci zaka sami saituna biyu na farko kawai a cikin teburin da ke ƙasa. Sauran sauran zaɓuɓɓukan da ake amfani da su akai-akai (inda aka nuna, za mu canza ƙimar tsoho tare da al'ada ɗaya daga cikin zaɓin mu):

Za mu canza wannan don koyar da sabis ɗin don sauraron kawai akan babban adireshinsa (192.168.0.13):

bind_address=192.168.0.13 bind_address=0.0.0.0 tashar tana wakiltar tashar da uwar garken bayanai za ta kasance tana sauraro.

Za mu maye gurbin tsohuwar ƙimar (3306) tare da 20500 (amma muna buƙatar tabbatar da cewa babu wani abu da ke amfani da wannan tashar jiragen ruwa):
tashar jiragen ruwa=20500

Yayin da wasu mutane za su yi jayayya cewa tsaro ta hanyar duhu ba al'ada ba ce mai kyau, canza tsoffin tashoshin aikace-aikacen don mafi girma hanya ce ta rudimentary -duk da haka tasiri- don hana binciken tashar jiragen ruwa. tashar jiragen ruwa=3306> td> innodb_buffer_pool_size shine wurin buffer (a cikin bytes) na ƙwaƙwalwar ajiya wanda an keɓe don bayanai da fihirisa waɗanda ake samun dama akai-akai lokacin amfani da Innodb (wanda shine tsoho a MariaDB) ko XtraDB azaman injin ajiya.

Za mu maye gurbin tsohuwar ƙimar da 256 MB:

innodb_buffer_pool_size=256M innodb_buffer_pool_size=134217728> td> skip_name_resolve yana nuna ko za a warware sunayen masu masaukin baki ko a'a akan haɗin da ke shigowa. . Idan an saita zuwa 1, kamar yadda za mu yi a cikin wannan jagorar, adiresoshin IP kawai.

Sai dai idan kuna buƙatar sunayen masu masaukin baki don tantance izini, yana da kyau a kashe wannan canjin (domin a hanzarta haɗi da tambayoyi) ta saita ƙimarsa zuwa 1:

skip_name_resolve=1 skip_name_resolve=0> td> query_cache_size yana wakiltar girman (a cikin bytes) da ake samu ga tambayar cache a cikin faifai, inda aka adana sakamakon SELECT queries don amfani nan gaba lokacin da aka yi tambaya iri ɗaya (zuwa rumbun adana bayanai da amfani da ƙa'ida ɗaya da saitin halaye iri ɗaya).

Ya kamata ku zaɓi girman cache ɗin tambaya wanda ya dace da bukatunku bisa 1) adadin maimaita tambayoyin, da 2) ƙimanta adadin rikodin waɗannan tambayoyin maimaitawar ana sa ran dawowa. Za mu saita wannan darajar zuwa 100 MB na yanzu:

query_cache_size=100M query_cache_size=0 (wanda ke nufin an kashe ta ta tsohuwa) max_connections shine matsakaicin adadin haɗin haɗin abokin ciniki na lokaci guda zuwa uwar garken. . Za mu saita wannan darajar zuwa 30:
max_connections=30Kowace hanyar haɗi za ta yi amfani da zaren, don haka za ta cinye ƙwaƙwalwar ajiya. Yi la'akari da wannan gaskiyar yayin saita max_connections. max_connections=151> td> thread_cache_size yana nuna lambobin zaren da uwar garken ke ware don sake amfani da su. bayan abokin ciniki ya cire haɗin kuma ya 'yantar da zaren (s) da ake amfani da shi a baya. A wannan yanayin, yana da arha (aiki-hikima) don sake amfani da zaren fiye da saka wani sabon abu.

Hakanan, wannan ya dogara da adadin haɗin da kuke tsammani. Zamu iya saita wannan ƙimar a amince zuwa rabin adadin max_connections:

thread_cache_size=15 thread_cache_size=0 (an kashe ta tsohuwa) A cikin CentOS, za mu buƙaci gaya wa SELinux don ƙyale MariaDB ta saurari tashar jiragen ruwa mara kyau (20500) kafin mu sake farawa sabis:

# yum install policycoreutils-python
# semanage port -a -t mysqld_port_t -p tcp 20500

Sannan sake kunna sabis na MariaDB.

Tuning MariaDB Performance

Don taimaka mana wajen dubawa da daidaita tsarin kamar yadda takamaiman buƙatun mu, za mu iya shigar da mysqltuner (rubutun da zai ba da shawarwari don inganta aikin uwar garken bayanan mu da haɓaka kwanciyar hankali):

# wget https://github.com/major/MySQLTuner-perl/tarball/master
# tar xzf master

Sannan canza kundin adireshi zuwa babban fayil ɗin da aka ciro daga ƙwallon ƙwallon (ainihin sigar na iya bambanta a yanayin ku):

# cd major-MySQLTuner-perl-7dabf27

kuma gudanar da shi (za a sa ku shigar da takaddun shaidar asusun MariaDB ɗin ku)

# ./mysqltuner.pl

Fitowar rubutun a cikin kanta yana da ban sha'awa sosai, amma bari mu tsallake zuwa ƙasa inda aka jera masu canji don daidaitawa tare da ƙimar shawarar:

Saitin query_cache_type yana nuna ko an kashe cache ɗin tambaya (0) ko an kunna (1). A wannan yanayin, mysqltuner yana ba mu shawarar mu kashe shi.

To me yasa aka ba mu shawarar mu kashe shi yanzu? Dalili kuwa shi ne cewa cache ɗin tambaya yana da amfani galibi a cikin yanayin karatu mai zurfi/ƙaramin rubutu (wanda ba batunmu ba ne, tunda mun shigar da uwar garken bayanan).

GARGAƊI: Kafin yin canje-canje ga daidaitawar uwar garken samarwa, ana ƙarfafa ku sosai don tuntuɓar ƙwararrun mai gudanar da bayanai don tabbatar da cewa shawarar da mysqltuner ya bayar ba za ta yi tasiri mara kyau ba akan saitin da ke akwai.

Takaitawa

A cikin wannan labarin mun bayyana yadda ake saita uwar garken bayanan MariaDB bayan mun shigar kuma mun amintar da shi. Matsalolin daidaitawa da aka jera a teburin da ke sama ƴan saituna ne kawai waɗanda za ku so kuyi la'akari yayin shirya uwar garken don amfani ko lokacin kunna ta daga baya. Koyaushe koma zuwa takaddun MariaDB na hukuma kafin yin canje-canje ko koma zuwa nasihunmu na daidaita ayyukan MariaDB:

Kamar koyaushe, kada ku yi shakka a sanar da mu idan kuna da tambayoyi ko sharhi game da wannan labarin. Shin akwai wasu saitunan uwar garken da kuke son amfani da su? Jin kyauta don rabawa tare da sauran jama'a ta amfani da fom na sharhi da ke ƙasa.