Setting the correct total maximum connections of your server
depends on how large your memory is. Each connection thread
consumes an amount of your total memory. For example you have 4GB
memory in your server and each thread is using around 2MB of RAM,
a 100 concurrent connections is actually eating up a total of
200MB of your memory.
So how do we compute the max_connections?
1. First is you need to do is get the value of the following
global variables. Examine and take note of their sizes. The value
stored in the database is actually in bytes so you might need to
convert it to Kilobytes or Megabyte or Gigabytes by dividing it
to 1024
Example:
1,073,741,824 bytes
1,073,741,824 / 1024 = 1,048,576 kilobytes
1,073,741,824 /1024 / 1024 = 1,024 megabytes
1,073,741,824 / 1024 / 1024 / 1024 = 1
gigabytes
You can use the SHOW command to display …
Wed, 2016-10-26 04:51Jan Lindstrom
From MariaDB 10.1 there is a feature where the InnoDB page size can be configured to be larger than the default 16K for normal, uncompressed tables. However, there has been little performance results that show whether the page size really effects the transaction performance or response time. In this blog, we study effects of page size on three different storage devices using the same benchmark(s). These devices are:
- Traditional hard disk
- SSD (Tree Intel X25-E Extreme SSDSA2SH032 G1GN 2.5-inch 32GB SATA II SLC Internal Solid State Drive as RAID-0)
- FusionIO NVM device (ioMemory SX300-1600 with VSL driver 4.2.1 build 1137 and NVMFS 1.1.1)
Results from different devices should not be compared to each other, as there are other variables like device bandwidth and different file systems. Instead, we will look at page size effect on each device …
[Read more]The MySQL Server itself
The differences between the MySQL Community Server and the MySQL Enterprise Server 5.7 are as follows as claimed by Oracle:
- The license of the MySQL Server
- Only MySQL Enterprise Edition has the Enterprise plug-ins (Thread Pool, PAM, Audit, etc.)
- Certifications and Indemnification support for the MySQL Enterprise Server
- The MySQL Community Server statically links against yaSSL and readline vs MySQL Enterprise Server OpenSSL and libedit
The license of the MySQL Server
The MySQL Community Server is licensed under the GNU General
Public License version 2 whereas the MySQL Enterprise Server is
under an Oracle proprietary license as you can see from the
following diffs of 2 random files:
shell> diff mysql-5.7.16-linux-glibc2.5-x86_64/share/charsets/latin1.xml …[Read more]
I’ve been wanting an easy way to play around with MySQL Cluster lately, the latest 7.5 release is now based on the MySQL 5.7 branch, so it also has the sys schema packaged, adding more areas for me to add sys integration that is more cluster specific – especially given all of the new tables that were added within ndbinfo.
There’s a couple of examples of docker images that wrap older MySQL Cluster packages out there, but:
- Nothing up to date, I wanted to use MySQL Cluster 7.5.4
- Nothing that uses docker networks, rather than the old link style
- Nothing that helps to orchestrate starting the containers, I don’t want anything fancy, just to start a Cluster of a certain shape (n of …
Here is the third in a series of posts about CTEs, a new feature of MySQL 8.0, available in this Labs release. In the first post we had explored the new SQL syntax, and in the second we had applied it to generating series.…
Disk space issues are common, and they’re often difficult to solve quickly. One way to recover some space is by enabling InnoDB compression.
First, of course, you want to make sure you’ve covered alternative solutions. Can you archive data? Do partitioning/sharding? These generally involve application changes and can take longer.
You may need to first do conversion to InnoDB.
While compression is available for MyISAM via myisampack, and this can be useful for some use cases (for example, if you are rotating out tables on a monthly basis), it makes the tables read-only, so generally you will want to first convert MyISAM tables to InnoDB.
Things to watch for in the schema: After working on functional and performance issues with full-text indexes after conversion to InnoDB, I wouldn’t recommend it. Application changes are also required to rewrite queries. You can consider outsourcing these to a tool …
[Read more]
InnoDBOnline buffer pool resize:
- Configure innodb_buffer_pool_size offline (at startup) or online, while the server is running
- The operation is performed in chunks
- Chunk size is defined by the innodb_buffer_pool_chunk_size configuration option
- Buffer pool size always be equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances
- Default innodb_buffer_pool_chunk_size is 128M
Reference:
https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html
Online RENAME INDEX operation:
- ALTER TABLE RENAME INDEX requires only meta-data change.
Reference:
…
Prepare the RPM repository server
Install the software to create a RPM repository server (on an Ubuntu web server):
shell> sudo apt-get install createrepo
Create the directory structures and initialize the repositories:
shell> sudo mkdir -p /var/www/html/repos/centos/7/os/x86_64 shell> sudo createrepo /var/www/html/repos/centos/7/os/x86_64/
Copy the MySQL Enterprise RPM packages to the repository:
shell> sudo cd /var/www/html/repos/centos/7/os/x86_64/ shell> sudo unzip -v /download/V790254-01.zip # MySQL Enterprise Server 5.7.16 Archive: /download/V790254-01.zip Length Method Size Cmpr Date Time CRC-32 Name --------- ------ -------- ---- ---------- ----- -------- ---- 23979568 Stored 23979568 0% 2016-09-29 16:56 ae6693b1 mysql-commercial-client-5.7.16-1.1.el7.x86_64.rpm 45327096 Stored 45327096 0% 2016-09-29 16:56 bb83c965 …[Read more]
If you have already Red Hat Enterprise Linux 7 (RHEL 7) or CentOS 7 in use you probably found out, that it is not that easy to install MySQL Enterprise Server because there are already pre-installed MariaDB 5.5 libraries:
shell> yum localinstall mysql-commercial-client-5.7.16-1.1.el7.x86_64.rpm mysql-commercial-libs-5.7.16-1.1.el7.x86_64.rpm mysql-commercial-server-5.7.16-1.1.el7.x86_64.rpm mysql-commercial-common-5.7.16-1.1.el7.x86_64.rpm Loaded plugins: fastestmirror Examining mysql-commercial-client-5.7.16-1.1.el7.x86_64.rpm: mysql-commercial-client-5.7.16-1.1.el7.x86_64 Marking mysql-commercial-client-5.7.16-1.1.el7.x86_64.rpm to be installed Examining mysql-commercial-libs-5.7.16-1.1.el7.x86_64.rpm: mysql-commercial-libs-5.7.16-1.1.el7.x86_64 Marking mysql-commercial-libs-5.7.16-1.1.el7.x86_64.rpm to be installed Examining mysql-commercial-server-5.7.16-1.1.el7.x86_64.rpm: mysql-commercial-server-5.7.16-1.1.el7.x86_64 Marking …[Read more]