Set up MySQL Master Slave Replication on Docker Containers

Set up MySQL Master Slave Replication on Docker Containers

https://github.com/junhsieh/docker-compose/tree/master/mysql-replication

MySQL resolve Host name to IP Address or Vice Versa

MySQL resolve Host name to IP Address or Vice Versa

# resolveip 192.168.5.11

# resolveip mysql-slave

Reference:

https://dev.mysql.com/doc/refman/5.7/en/resolveip.html

Go at Google: Language Design in the Service of Software Engineering

Go at Google: Language Design in the Service of Software Engineering

https://talks.golang.org/2012/splash.article

Good C string library

Good C string library

http://site.icu-project.org/

Reference:

https://stackoverflow.com/questions/4688041/good-c-string-library

Content-Length not sent when gzip compression enabled in Apache?

Content-Length not sent when gzip compression enabled in Apache?

Philippe: "Apache uses chunked encoding only if the compressed file size is larger than the DeflateBufferSize. Increasing this buffer size will therefore prevent the server using chunked encoding also for larger files, causing the Content-Length to be sent even for zipped data."

Reference:

https://serverfault.com/questions/183843/content-length-not-sent-when-gzip-compression-enabled-in-apache/183856#183856

https://stackoverflow.com/questions/2287950/how-do-you-set-the-correct-content-length-header-when-the-webserver-automaticall

Jet Profiler for MySQL

Jet Profiler for MySQL

is a real-time query performance and diagnostics tool for the MySQL database server. It's core features:

Query, table and user performance
Graphical visualisation
Low overhead
User friendly

Reference:

https://www.jetprofiler.com/

InnoDB memory usage buffer pool status

InnoDB memory usage buffer pool status

Making sense of INNODB buffer pool stats

After having read this page in the mysql documentation, I tried to make sense of our current InnoDB usage. Currently, we allocate 6GB of RAM for the buffer pool. Our database size is about the same. Here's the output from show engine innodb status\G (we're running v5.5)

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 6593445888; in additional pool allocated 0
Dictionary memory allocated 1758417
Buffer pool size   393215
Free buffers       853
Database pages     360515
Old database pages 133060
Modified db pages  300
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 7365790, not young 23099457
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1094342, created 185628, written 543182148
0.00 reads/s, 0.00 creates/s, 37.32 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 360515, unzip_LRU len: 0
I/O sum[2571]:cur[0], unzip sum[0]:cur[0]

I wanted to know how well we're utilizing the buffer cache. After initially glancing at the output, it appeared that we are indeed using it, based off of the Pages made young and not young have numbers in them and Buffer pool hit rate is 1000 / 10000 (which I saw elsewhere on the web that this means it's being used pretty heavily. True?)

What's throwing me through a loop is why the young-making rate and not are both at 0/1000 and the young/s and non-young/s accesses are both at 0. Those would all indicate that it's not being used at all, right?

Can anyone help make sense of this?

This is in pages not bytes:

The Buffer pool size   393215

To see the Buffer Pool size in GB run this:

Note: As of MySQL 5.7.6 the "information_schema" is merged into performance_schema. So just change "information_schema" to "performance_schema" in the query to make it work.

SELECT FORMAT(BufferPoolPages*PageSize/POWER(1024,3),2) BufferPoolDataGB FROM
(SELECT variable_value BufferPoolPages FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') A,
(SELECT variable_value PageSize FROM information_schema.global_status
WHERE variable_name = 'Innodb_page_size') B;

This is the number of pages with data inside the Buffer Pool:

Database pages     360515

To see the amount of data in the Buffer Pool size in GB run this:

SELECT FORMAT(BufferPoolPages*PageSize/POWER(1024,3),2) BufferPoolDataGB FROM
(SELECT variable_value BufferPoolPages FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
(SELECT variable_value PageSize FROM information_schema.global_status
WHERE variable_name = 'Innodb_page_size') B;

To see the percentage of the Buffer Pool in use, run this:

SELECT CONCAT(FORMAT(DataPages*100.0/TotalPages,2),' %') BufferPoolDataPercentage FROM
(SELECT variable_value DataPages FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
(SELECT variable_value TotalPages FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') B;

This is the number of pages in the Buffer Pool that have to be written back to the database. They are also referred to as dirty pages:

Modified db pages  300

To see the Space Taken Up by Dirty Pages, run this:

SELECT FORMAT(DirtyPages*PageSize/POWER(1024,3),2) BufferPoolDirtyGB FROM
(SELECT variable_value DirtyPages FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_dirty') A,
(SELECT variable_value PageSize FROM information_schema.global_status
WHERE variable_name = 'Innodb_page_size') B;

To see the Percentage of Dirty Pages, run this:

SELECT CONCAT(FORMAT(DirtyPages*100.0/TotalPages,2),' %') BufferPoolDirtyPercentage FROM
(SELECT variable_value DirtyPages FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_dirty') A,
(SELECT variable_value TotalPages FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') B;

As for the other things in the display, run this:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';

You'll see all the status variables for the Buffer Pool. ou can apply the same queries against whatever you need to examine.

The buffer pool is divided into two part, a young list and a not-young list. The making rate shows how many pages in the buffer pools are being shuffled between the two lists.

Pages made young are not-young pages being made (i.e. being read out of the cache. Pages made not-young are pages moved from the young list because either they are too old, or because the young list is full.

The rate at pages are moved between the two depends upon how much of the buffer pool is currently being used vs the size of the young pool. Set at zero means your active set (the pages you are using) is smaller than the young pool.

Reference:

http://dba.stackexchange.com/questions/56494/making-sense-of-innodb-buffer-pool-stats

http://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool.html

How much memory do I need for InnoDB buffer pool?

How much memory do I need for InnoDB buffer pool?

The following query gives you the recommended InnoDB buffer pool size based on all InnoDB Data and Indexes with an additional 60%:

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) AS RIBPS_GB FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

+-------+
| RIBPS |
+-------+
|     8 |
+-------+

With this output, you would set the following in /etc/my.cnf:

[mysqld]
innodb_buffer_pool_size=8G

After few days, run this query to see the actualy GB of memory in use in the InnoDB buffer pool:

SELECT (PagesData*PageSize)/POWER(1024,3) DataGB FROM
(SELECT variable_value PagesData
FROM performance_schema.global_status
WHERE variable_name='Innodb_buffer_pool_pages_data') A,
(SELECT variable_value PageSize
FROM performance_schema.global_status
WHERE variable_name='Innodb_page_size') B;

You need buffer pool a bit (say 10%) larger than your data (total size of Innodb TableSpaces):

If you want to accommodate an addition 10%, plus account for 25% increase in data and indexes over time, the following query will produce exactly what you need to set innodb_buffer_pool_size in /etc/mysql/mysql.conf.d/mysqld.cnf:

SET @growth = 1.25;

SELECT
 CONCAT(
  CEILING(RIBPS / POWER(1024, pw)),
  SUBSTR(' KMGT', pw + 1, 1)
 ) Recommended_InnoDB_Buffer_Pool_Size
FROM
 (
  SELECT
   RIBPS,
   FLOOR(LOG(RIBPS) / LOG(1024)) pw
  FROM
   (
    SELECT
     SUM(data_length + index_length) * 1.1 * @growth AS RIBPS
    FROM
     information_schema. TABLES AAA
    WHERE
     ENGINE = 'InnoDB'
    GROUP BY
     ENGINE
   ) AA
 ) A;

Reference:

http://blog.ijun.org/2016/02/innodb-memory-usage-buffer-pool-status.html

https://dba.stackexchange.com/questions/27328/how-large-should-be-mysql-innodb-buffer-pool-size

https://dba.stackexchange.com/questions/125164/information-schema-global-variables-alternative-in-5-7-more-info-about-show-com

http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/

Disable TLS 1.0 and 1.1 in Apache 2.4

Disable TLS 1.0 and 1.1 in Apache 2.4:

# vim /etc/apache2/mods-available/ssl.conf

SSLProtocol all -SSLv3 -TLSv1 -TLSv1.1

Verify if TLS 1.0 and 1.1 are supported:

# openssl s_client -connect mydomain.com:443 -tls1

# openssl s_client -connect mydomain.com:443 -tls1_1

Note: If you get the certificate chain and the handshake you know the system in question supports TLS 1.1.

Reference:

https://serverfault.com/questions/638691/how-can-i-verify-if-tls-1-2-is-supported-on-a-remote-web-server-from-the-rhel-ce

When checking Apache's gzip deflate compression, I realized Apache was sending "Transfer-Encoding: chunked" and not sending the "Content-Length" header

When checking Apache's gzip deflate compression, I realized Apache was sending "Transfer-Encoding: chunked" and not sending the "Content-Length" header

Andy: "Chunked output occurs when Apache doesn't know the total output size before sending, as is the case with compressed transfer (Apache compresses data into chunks when they reach a certain size, then despatches them to the browser/requester while the script is still executing). You could be seeing this because you have mod_deflate or mod_gzip active.

You can disable mod_deflate per file like so (more here)

    SetEnvIfNoCase Request_URI get_file\.php$ no-gzip dont-vary

It's best left on in general as it greatly increases the speed of data transfer.
"

Reference:

https://serverfault.com/questions/59047/apache-sending-transfer-encoding-chunked

Manually obtaining TLS/SSL certificates from Let's Encrypt

Official build of EFF's Certbot tool for obtaining TLS/SSL certificates from Let's Encrypt.

https://hub.docker.com/r/certbot/certbot/

# certbot certonly --manual --preferred-challenges http --email me@example.com -d mydomain.com