Kamis, 4 Maret 2021
  • Login
Harian Aceh Indonesia
  • HOME
  • IN-DEPTH
  • ACEH
  • NASIONAL
    • HUKUM
    • POLITIK
    • PERISTIWA
    • SOROTAN PUBLIK
  • DUNIA
  • EKONOMI
  • EDUKASI
    • LITERASI
  • ISLAM
  • OPINI
  • LIFESTYLE
  • LINGKUNGAN
  • SEJARAH
  • OTO
  • HIBURAN
  • SEPAK BOLA
    • BOLA NASIONAL
    • LIGA INGGRIS
    • LIGA ITALIA
    • LIGA SPANYOL
  • TEKNO
    • APLIKASI
    • GADGET
    • INTERNET
  • FOTO
  • VIDEO
  • CEK FAKTA
No Result
View All Result
Harian Aceh Indonesia
Kamis, 4 Maret 2021
No Result
View All Result
Harian Aceh Indonesia
No Result
View All Result
Trending Tutorial

How to optimize MySQL/MariaDB Performance on Server

Redaksi HAI Redaksi HAI
Senin, 12/08/2019 - 20:29 WIB
MySQL Perfomance Tuning

Illustrate MySQL Perfomance Tuning via Medium.com

Sebar ke FacebookSebar ke Twitter
Print Friendly, PDF & Email

MySQL and MariaDB are popular choices for free database management systems. Both use the SQL querying language to manipulate and query data. If your application or website have a lot of data and you still use default configuration of MySQL/MariaDB, it will decrease performance and efficiency on your system. Below is some tips you can apply in your MySQL/MariaDB config to increase MySQL/MariaDB performance.

Tips 1: Store MySQL Database Data on SSD Partition

Sometimes OS read/writes can slow down the performance of your MySQL server, especially if located on same hard drive. Instead, I would recommend using separate SSD disk for the MySQL service.

Tips 2: Enable InnoDB file-per-table

MySQL and MariaDB use InnoDB as default storage engine.

The InnoDB provides more flexible approach and each database information is kept in a .ibddata file. Each .ibd file represents a tablespace of its own. That way database operations such as “TRUNCATE” can be completed faster and you may also reclaim unused space when dropping or truncating a database table.

Another benefit of this configuration is the fact that you can keep some of the database tables in a separate storage device. This can greatly improve the I/O load on your disks.

The innodb_file_per_table is enabled by default in MySQL 5.6 and above. You can see that in /etc/my.cnf file. The directive looks like this:

BACAAN LAINNYA

Tuning MySQL For High Performance. IMAGE/Illustrasion

Get High Performance With Tuning These MySQL System Variables

15/02/2021 - 05:44 WIB
Begini cara mengkonversi video Youtube ke MP3. FOTO/Net

Cara Mengonversi Video YouTube menjadi Format MP3

21/01/2021 - 13:30 WIB
Penulis naskah, aktor, dan sutradara film Hollywood, Spike Lee, berbagi tips untuk maju di dunia perfilman.

Spike Lee: Riset Penting dalam Membuat Cerita dalam Film

06/12/2020 - 03:48 WIB
Pengguna ponsel dapat menghemat kuota data internet dengan duapendekatan aplikasi dan perangkat.

Dua Pendekatan untuk Hindari Kuota Internet Cepat Habis

06/11/2020 - 06:30 WIB

loading...

innodb_file_per_table=1

Tip 3: Increase InoDB buffer pool size

The InnoDB engine has a buffer pool used for caching data and indexes in memory. This of course will help your MySQL/MariaDB queries be executed significantly faster. Choosing the proper size here requires some very important decisions and good knowledge on your system’s memory consumption.

Rule: you should adjust InoDb buffer pool size = 80% RAM

Example:

Total Server RAMBuffer pool with 80% ruleRemaining RAM
1G800MB200MB
16G13G3G
32G26G6G
64G51G13G
128G102G26G
256G205G51G
512G409G103G
1024G819G205G

At lower numbers, our 80% rule looks pretty reasonable.  However, as we get into large servers, it starts to seem less sane.  For the rule to hold true, it must mean that workload memory consumption increases in proportion to needed size of the buffer pool, but that usually isn’t the case.

innodb_buffer_pool_size 800M

Tips 4: Set MySQL Max Connections

The max_connections directive tells your server how many concurrent connections are permitted.  The connection is opened only for the time MySQL query is executed – after that it is closed and new connection can take its place.

The default setting for max_connections is 100. You can see what the current setting is by running the following SQL command from the command:

show variables like "max_connections";

This will return a resultset like this

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100   |
+-----------------+-------+

Keep in mind that too many connections can cause high RAM usage and lock up your MySQL server. Usually small websites will require between 100-200 connections while larger may require 500-800 or even more. The value you apply here strongly depends on your particular MySQL/MariaDB usage.

You can dynamically change the value of max_connections, without having to restart the MySQL service by running SQL command:

set global max_connections := 300;

Or you can set in mysql.cnf like this

max_connections = 200

Rule for max connection:
You can calculate max connection using the formula:

Available RAM = Global Buffers + (Thread Buffers x max_connections)
max_connections = (Available RAM – Global Buffers) / Thread Buffers

To get the list of buffers and their values:

SHOW VARIABLES LIKE '%buffer%';

Here’s a list of the buffers and whether they’re Global or Thread:

Global Buffers: key_buffer_size, innodb_buffer_pool_size, innodb_log_buffer_size, innodb_additional_mem_pool_size, net_buffer_size, query_cache_size

Thread Buffers: sort_buffer_size, myisam_sort_buffer_size, read_buffer_size, join_buffer_size, read_rnd_buffer_size, thread_stack

Tips 5: Configure MySQL query_cache_size

From MySQL 5.6.8,query_cache_type is set to OFF by default. So if you haven’t explicitly turned it ON on old version, it may not work anymore!

If you have many repetitive queries and your data does not change often – use query cache. People often do not understand the concept behind the query_cache_size and set this value to gigabytes, which can actually cause degradation in the performance.

The reason behind that is the fact that threads need to lock the cache during updates. Usually value of 200-300 MB should be more than enough. If your website is relatively small, you can try giving the value of 64M and increase in time.

Check current status of query_cache

mysql -e "show variables like 'query_cache_%'"

+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| query_cache_limit            | 2097152   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 268435456 |
| query_cache_strip_comments   | ON        |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+

You will have to add the following settings in the MySQL configuration file:

query_cache_type = 1
query_cache_limit = 256K
query_cache_min_res_unit = 2k
query_cache_size = 80M

Meaning of variables

  • query_cache_type = 1: just turn on query cache
  • query_cache_size: Default is 1MB. You can set it upto 4GB but very high values are not recommend for sites where tables are modified quite frequently.
  • query_cache_limit: Default is 1MB. You can set it upto 4GB. Again very high values are not recommended.

Below my configuration file for MySQL on VPS 1Gb RAM you can reference:

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

key_buffer_size     = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
myisam-recover-options  = BACKUP

query_cache_type = 1
query_cache_limit   = 10M
query_cache_size        = 80M
query_cache_min_res_unit = 2k

tmp_table_size= 64M
max_heap_table_size= 64M

log_error = /var/log/mysql/error.log
expire_logs_days    = 10
max_binlog_size   = 100M
innodb_buffer_pool_size=800M
innodb_buffer_pool_instances = 3

Tags: MariaDBMariaDB PerformanceMySQLMySQL PerformanceMySQL Performance TuningMySQL TuningOptimize MySQLServer TuningSQL Tuningtips
Share120Tweet74Pin27Send

Sebelumnya

Anggota KPI Periode 2019-2022 Hasil Dari Pelanggaran Administrasi

Selanjutnya

Ungkit Pam Swakarsa di 1998, Kivlan Zen Gugat Wiranto ke PN Jaktim

BACAAN LAINNYA

Wordpress Multisite. FOTO/tolotratlt

How to Install and Set Up a WordPress Multisite

04/03/2021 - 00:38 WIB
Ilustrasi: Lenovo

Lenovo Legion 2 Pro akan Miliki Sistem Pendingin Turbo Ganda

03/03/2021 - 21:08 WIB
Kementerian Komunikasi dan Informatika (Kominfo) telah memblokir situs web Snack Video sejak 2 Maret 2021.

Kominfo Blokir Situs Web Snack Video, Ini Alasannya

03/03/2021 - 15:48 WIB
iPhone lipat.

Analis Sebut iPhone Lipat Berukuran Hingga 8 Inci

03/03/2021 - 15:46 WIB
Twitter

Twitter Mulai Uji Fitur Ruang Obrolan Audio Baru di Android

03/03/2021 - 10:16 WIB
Twitter akan memperluas penggunaan label peringatan terhadap cuitan vaksin Covid-19.

Twitter akan Sanksi Penyebar Disinformasi Vaksin Covid-19

03/03/2021 - 07:58 WIB
produk Cloud ERP SystemEver meliputi meliputi 3 bidang layanan (i-series) yaitu i1 (Account Tax Service) untuk membantu mempermudah konsultan pajak dan akuntansi dalam melakukan pelayanan kepada klien, i5 (Sales/Distribution) untuk perusahaan distribusi dan penjualan. Sedangkan untuk skala yang lebih besar seperti industri manufaktur, produk  i7 (Manufacturing) bisa menjadi solusi pengelolaan manajemen yang makin kompleks.

SystemEver Bantu Pelaku Bisnis Perkuat Transformasi Digital

03/03/2021 - 00:17 WIB
Redmi Note 10.

Xiaomi India Konfirmasi Super AMOLED untuk Seri Note 10

02/03/2021 - 19:56 WIB
Aplikasi Clue Dapatkan Izin Luncurkan Kontrasepsi Digital

Aplikasi Clue Dapatkan Izin Luncurkan Kontrasepsi Digital

02/03/2021 - 15:34 WIB
AppGallery Huawei

AppGallery Huawei Miliki 530 Juta Pengguna Aktif

02/03/2021 - 14:02 WIB
Load More

TERPOPULER

  • Isma Khaira, Warga Desa Pineung, Lhoksukon, ini ditahan setelah divonis melanggar Undang-Undang Informasi dan Transaksi Elektronik (UU ITE) karena mencemarkan nama kepala desa di Facebook. FOTO/Net

    Isma Khaira dan Bayinya Dipenjara karena UU ITE, Polisi Coba Mediasi

    4 shares
    Share 2 Tweet 1
  • 6 Laskar FPI yang Tewas Ditembak Polisi Ditetapkan Jadi Tersangka

    4 shares
    Share 2 Tweet 1
  • Cerita Makam Aulia Syeikh Bilal Butar Aceh Singkil Yang Berpindah Tempat

    3 shares
    Share 1 Tweet 1
  • Perpres Investasi Miras Dicabut, Denny Zulfikar Siregar Sentil Jokowi: Komunikasinya Buruk

    3 shares
    Share 1 Tweet 1
  • Tiga Polisi Jadi Terlapor Kasus Unlawfull Killing Laskar FPI

    3 shares
    Share 1 Tweet 1
Loading...

PERISTIWA

Kesal Ditagih Bon Tuak, Oknum Polisi Polres Binjai Letuskan Pistol

Kesal Ditagih Bon Tuak, Oknum Polisi Polres Binjai Letuskan Pistol

03/03/2021

KPK Sebut Ada Kasus Korupsi yang Kemungkinan Dihentikan Penyidikannya

KPK Sebut Ada Kasus Korupsi yang Kemungkinan Dihentikan Penyidikannya

03/03/2021

Sri Mulyani Harus Evaluasi Dirjen Pajak Suryo Utomo

Sri Mulyani Harus Evaluasi Dirjen Pajak Suryo Utomo

03/03/2021

Untuk Memudahkan Pengusutan Oleh KPK

Untuk Memudahkan Pengusutan Oleh KPK

03/03/2021

Geledah 4 Lokasi di Sulsel, KPK Amankan Dokumen-Uang Tunai Kasus Nurdin Abdullah

Geledah 4 Lokasi di Sulsel, KPK Amankan Dokumen-Uang Tunai Kasus Nurdin Abdullah

02/03/2021

  • Tentang Kami
  • Kebijakan Privacy
  • Kode Etik Jurnalistik
  • Pedoman Media Siber
  • Pedoman Pemberitaan Ramah Anak
  • Hak Jawab Dan Koreksi Berita
  • Standar Perlindungan Profesi Wartawan
  • Ketentuan Khusus
  • Menulis di HAI
  • Sitemap
  • Cookie
Aplikasi Android Harian Aceh Indonesia

© 2014 - 2021 - PT. Harian Aceh Indonesia. Made with in Indonesia.

No Result
View All Result
  • HOME
  • IN-DEPTH
  • ACEH
  • NASIONAL
    • HUKUM
    • POLITIK
    • PERISTIWA
    • SOROTAN PUBLIK
  • INTERNASIONAL
  • EKONOMI
  • EDUKASI
    • LITERASI
  • LINGKUNGAN
  • ISLAM
  • OPINI
  • SEJARAH
  • LIFESTYLE
  • KOMUNITAS
  • HIBURAN
  • OLAHRAGA
  • SEPAKBOLA
    • BOLA NASIONAL
    • LIGA ITALIA
    • LIGA INGGRIS
    • LIGA SPANYOL
  • OTOMOTIF
  • TEKNOLOGI
    • APLIKASI
    • GADGET
    • INTERNET
  • FOTO
  • VIDEO
  • CEK FAKTA
  • LOWONGAN KERJA
  • Login

© 2014 - 2021 - PT. Harian Aceh Indonesia. Made with in Indonesia.

Welcome Back!

Login to your account below

Forgotten Password?

Create New Account!

Fill the forms below to register

All fields are required. Log In

Retrieve your password

Please enter your username or email address to reset your password.

Log In