15 Mapuslanon MySQL/MariaDB Performance Tuning ug Optimization Tips

Ang MySQL usa ka gamhanan nga open source Relational Database Management System o sa mubo nga RDBMS. Gipagawas kini balik niadtong 1995 (20 anyos). Gigamit niini ang Structured Query Language nga tingali ang labing inila nga kapilian alang sa pagdumala sa sulud sulod sa usa ka database. Ang pinakabag-o nga bersyon sa MySQL mao ang 5.6.25 ug gipagawas kaniadtong 29 Mayo 2015.

Ang usa ka makapaikag nga kamatuoran bahin sa MySQL mao ang kamatuoran nga ang ngalan naggikan sa anak nga babaye ni Michael Widenius (magbubuhat sa MySQL) nga My. Bisan kung adunay daghang makapaikag nga mga kamatuoran bahin sa MySQL, kini nga artikulo gituyo aron ipakita kanimo ang pipila ka mapuslanon nga mga gawi aron matabangan ka sa pagdumala sa imong MySQL server.

Niadtong Abril 2009 ang proyekto sa MySQL gipalit sa Oracle. Ingon usa ka sangputanan usa ka MySQL community fork nga gitawag og MariaDB ang gihimo. Ang panguna nga hinungdan sa paghimo sa tinidor mao ang pagpadayon sa proyekto nga libre sa ilawom sa General Public License.

Karon ang MySQL ug MariaDB usa sa labing (kung dili ang labing) kanunay nga gigamit nga RDBMS nga gigamit alang sa mga aplikasyon sa web sama sa WordPress, Joomla, Magento ug uban pa.

Kini nga artikulo magpakita kanimo pipila ka sukaranan, apan mapuslanon nga mga tip kung giunsa ang pag-optimize sa maayo nga tune sa pasundayag sa MySQL/MariaDB. Palihug hinumdomi nga kini nga artikulo nagtuo nga na-install na nimo ang MySQL o MariaDB. Kung naghunahuna ka pa kung giunsa kini i-install sa imong sistema, mahimo nimong sundon ang among daghang mga giya dinhi:

  1. Pag-instalar sa LAMP sa RHEL/CentOS 7
  2. Pag-instalar sa LAMP sa Fedora 22
  3. Pag-set up sa LAMP sa Ubuntu 15.04
  4. Pag-instalar sa MariaDB sa Debian 8
  5. I-install ang MariaDB sa Gentoo Linux
  6. I-install ang MariaDB sa Arch Linux

Importante: Sa dili pa kita magsugod - ayaw dawata kini nga mga sugyot nga buta. Ang matag MySQL setup talagsaon ug nagkinahanglan og dugang nga panghunahuna, sa dili pa maghimo ug bisan unsang mga kausaban.

Mga butang nga kinahanglan nimong masayran:

  1. MySQL/MariaDB configuration file anaa sa /etc/my.cnf. Matag higayon nga imong usbon kining payl kinahanglan nimo nga i-restart ang MySQL nga serbisyo aron ang mga bag-ong kausaban mahimong epektibo.
  2. Alang sa pagsulat niining artikuloha ang MySQL version 5.6 gigamit isip template.

1. I-enable ang InnoDB file-per-table

Una importante nga ipasabut nga ang InnoDB usa ka storage engine. Ang MySQL ug MariaDB naggamit sa InnoDB isip default storage engine. Kaniadto ang MySQL gigamit sa pagtipig sa mga lamesa sa database ug mga indeks sa usa ka tablespace sa sistema. Kini nga pamaagi gituyo alang sa mga server nga ang bugtong katuyoan mao ang pagproseso sa database ug ang ilang storage disk wala gigamit alang sa bisan unsang ubang mga katuyoan.

Ang InnoDB naghatag ug mas flexible nga pamaagi ug ang matag impormasyon sa database gitipigan sa .ibd data file. Ang matag .ibd nga payl nagrepresentar sa kaugalingong tablespace. Nianang paagiha ang mga operasyon sa database sama sa \TRUNCATE mahimong mas paspas nga makompleto ug mahimo usab nimo nga bawion ang wala magamit nga luna kung ihulog o putlon ang usa ka lamesa sa database.

Ang laing kaayohan sa kini nga pag-configure mao ang kamatuoran nga mahimo nimong tipigan ang pipila sa mga lamesa sa database sa usa ka lahi nga aparato sa pagtipig. Kini makapauswag pag-ayo sa I/O load sa imong mga disk.

Ang innodb_file_per_table gipalihok pinaagi sa default sa MySQL 5.6 ug pataas. Imong makita kana sa /etc/my.cnf file. Ang direktiba ingon niini:

innodb_file_per_table=1

2. Tipigi ang MySQL Database Data sa Separate Partition

Mubo nga sulat: Kini nga setup magamit lamang sa MySQL, apan dili sa MariaDB.

Usahay ang pagbasa/pagsulat sa OS makapahinay sa performance sa imong MySQL server, ilabina kon nahimutang sa samang hard drive. Hinuon, irekomendar nako ang paggamit sa bulag nga hard drive (mas maayo nga SSD) alang sa serbisyo sa MySQL.

Aron makompleto, kini kinahanglan nimo nga i-attach ang bag-ong drive sa imong computer/server. Alang sa katuyoan sa kini nga artikulo, akong hunahunaon nga ang drive naa sa ilawom sa /dev/sdb.

Ang sunod nga lakang mao ang pag-andam sa bag-ong drive:

# fdisk /dev/sdb

Karon pindota ang \n” para maghimo ug bag-ong partisyon. Sunod pindota ang \p” aron himoong panguna ang bag-ong partisyon. Pagkahuman niana, ibutang ang numero sa partisyon gikan sa 1-4. Human niana imong pilion ang gidak-on sa partisyon. Pindota ang enter dinhi. Sa sunod nga lakang kinahanglan nimo nga i-configure ang gidak-on sa partisyon.

Kung gusto nimo gamiton ang tibuuk nga disk ipadayon ang pagsulod sa makausa pa. Kung dili mahimo nimo nga mano-mano nga itakda ang gidak-on sa bag-ong partisyon. Kung andam na nga pindota ang \w aron isulat ang mga pagbag-o. Karon kinahanglan namon nga maghimo usa ka filesystem alang sa among bag-ong partisyon. Kini dali nga mahimo sa:

# mkfs.ext4 /dev/sdb1

Karon among i-mount ang among bag-ong partisyon sa usa ka folder. Gihinganlan nako ang akong folder nga \ssd ug gimugna sa root directory:

# mkdir /ssd/

Andam na kami nga i-mount ang bag-ong partisyon nga bag-o lang namo gihimo sa bag-ong folder:

# mount /dev/sdb1  /ssd/

Mahimo nimong buhaton ang mount sa pagsugod pinaagi sa pagdugang sa mosunod nga linya sa /etc/fstab file.

/dev/sdb1 /ssd ext3 defaults 0 0

Karon andam ka na nga ibalhin ang MySQL sa bag-ong disk. Una ihunong ang serbisyo sa MySQL sa:

# service mysqld stop

Irekomendar ko nga hunongon nimo ang Apache/nginx aron mapugngan ang bisan unsang pagsulay sa pagsulat sa mga database:

# service httpd stop
# service nginx stop

Karon kopyaha ang tibuok nga direktoryo sa MySQL sa bag-ong drive:

# cp /var/lib/mysql /ssd/ -Rp

Mahimong magdugay kini depende sa site sa imong mga database sa MySQL. Kung nahuman na kini nga proseso, usba ang ngalan sa direktoryo sa MySQL:

# mv /var/lib/mysql /var/lib/mysql-backup

Sunod maghimo kami usa ka symlink.

# ln -s /ssd/mysql /var/lib/mysql

Karon andam ka na nga magsugod sa imong MySQL ug serbisyo sa web:

# service mysqld start
# service httpd start
# service nginx start

Niini nga punto ang imong mga database sa MySQL ma-access gikan sa bag-ong drive.