Planet MySQL

Advanced WordPress Search using WpSolr and ElasticSearch

Finding good content on your website is really important. The search feature improves user interaction and helps you to build a readership on your website. WordPress uses the default MySQL database to perform a search which is not great. MySQL is not built for search and if you are serious about building an authority website on WordPress then the search is the module you need to pay good attention.

I have already implemented WordPress search with ElasticSearch here. In this article, I am going to use and review the amazing product called ‘WPSOLR’ built for search.

What is WPSOLR

WpSolr is an advanced WordPress search plugin that can work with Apache Solr and Elasticsearch.

WPSOLR provides out of the box search solution with the following features:

  • Built-in language, synonyms, stop words.
  • Search filter using checkbox, radio box, sliders, and other UI elements.
  • Unlimited posts search (thousands, millions, hundreds of millions….)
  • Boosting the results.
  • The live suggestion in the search box.
  • SEO friendly.
How WPSOLR Works

WPSOLR works with Apache Solr and Elasticsearch. Assuming you want to index your data in Elasticsearch. In this case, WpSolr plugin will index the data from MySQL database of WordPress to Elasticsearch.

The default WordPress search will be replaced with WPSOLR Search once the data is indexed and search is configured.

Check out this image for graphical representation.

How to use WPSOLR plugin

In order to use WPSOLR plugin, you need to have Elasticsearch installed either locally or deploy it on any free server.

You can follow this tutorial to install the ElasticSearch on openshift platform.

Once Elasticsearch is installed. You can install WPSOLR WordPress plugin.

Once the plugin is installed, we need to connect it to our Elasticsearch. Make sure you have an Elasticsearch URL ready.

Open the WPSOLR plugin admin panel.

Click on “Connect to your Elasticsearch / Apache Solr” button. This will open up another window.

In this screen, select the “Elasticsearch” from search engine dropdown. Give your index a name, if the index is not created, WPSOLR will create it for you.

Paste the Elasticsearch URL in server host text box and provide the port. You can use the key and secret provided by the WPSOLR.

Once done then click on the button provided at the bottom of the screen.

Now you need to replace the default WordPress search. Go to the results options screen to do that.

How much it costs?

WPSOLR costs 29 euros a month, or 199 euros a year. However, the team has come up with a coupon for you guys for 15% off on your order. You can use coupon codeforgeek15 on checkout to avail it.

You need to install Solr or Elasticsearch on your server before purchasing the plugin.

Conclusion

There is a search as a service solution that will cost you more than $10,000 for a million records. WPSOLR lets you index and searches as much data as you want on a flat price. This is really amazing and needed for the developer community. Kudos to the team for such an awesome product.

SQL Aliases Example | Alias In SQL Tutorial

SQL Aliases Example | Alias In SQL Tutorial is today’s topic. Aliases are the temporary names that exist for a particular query. It is used for better understanding and reducing the workload of writing big column names. Aliases are the temporary name and the changes made with the column name while retrieving does not reflect in the original database.

SQL Aliases Example

An Alias is a shorthand for the table or column name. Aliases reduce the amount of typing required to write the query. Complex queries with the aliases are generally easier to read. Aliases are useful with JOINs and SQL aggregates: SUM, COUNT, etc. SQL Alias only exists for the duration of that query.

Aliases are useful in these scenarios. When there is more than one table involved in the query Functions are used in that query. When the column names are big or not very readable. When there are two or more columns are combined together. Alias is also used when more than one table is involved, i.e., when the joins are used. These types of Aliases are known as Table Alias.

SYNTAX: (For column alias) Select column as alias_name FROM table_name; PARAMETERS:
  1. Column: Fields in the table.
  2. Alias_name: Temporary names that are to be used apart from the original column names.
  3. Table_name: Name of the table.
SYNTAX: (For Table Alias) Select column from table_name as alias_name; PARAMETERS:
  1. Column: Fields in the table.
  2. Table_name: Name of the table.
  3. Alias_name: Temporary names that are to be used apart from the original table names.
NOTE:
  1. The alias name should be enclosed within the quotes if it contains whitespace.
  2. The alias name is valid within the scope of the SQL statement.

Let’s understand the above syntaxes with the help of an example.

EXAMPLE:

Consider two tables:

Students ID NAME CITY 1 Shubh Kolkata 2 Karan Allahabad 3 Suraj Kota 4 Akash Vizag

 

Marks: ID NAME MARKS Age 1 Shubh 90 21 2 Rounak 91 21 3 Suraj 92 22 4 Akash 93 22

 

Let’s Begin with column alias:

QUERY: Select ID AS Roll, Name from Students where city like ‘K%’; Output: Roll NAME CITY 1 Shubh Kolkata 3 Suraj Kota

 

Explanation:

So, in the above query, we have used AS keyword to change the name of ID column to Roll and displayed the details of those students whose city first character belongs with K.

For more information about showing the information in this format, refer to SQL WILDCARD OPERATORS.

In the above example, we have also filtered the data using Where clause and Select an ID as Roll and name columns from a table.

Now, Let’s discuss an example for table alias.

Query: Select S.ID, S.NAME, S.CITY, M.MARKS from Students AS S, Marks AS M where S.ID=M.ID; Output: ID NAME CITY MARKS 1 Shubh Kolkata 90 3 Suraj Kota 92 4 Akash Vizag 93

 

Explanation:

So, in the above query, we have displayed the marks of those students whose Id’s in students and marks table is same.

Finally, SQL Aliases Example | Alias In SQL Tutorial is over.

The post SQL Aliases Example | Alias In SQL Tutorial appeared first on AppDividend.

How can I tell which Tungsten Connector mode I am using: Bridge, Proxy/Direct or Proxy/SmartScale?

Overview The Skinny

Part of the power of Tungsten Clustering for MySQL / MariaDB is its intelligent MySQL Proxy, known as the Tungsten Connector. Tungsten Connector has three main modes, and depending on the type of operations you are performing (such as if you need read-write splitting), we help you choose which mode is best.

The Question Recently, a customer asked us:

How can I tell which Tungsten Connector mode I am using: Bridge, Proxy/Direct or Proxy/SmartScale?

The Answer Connect and Observe

You may login through the Connector to tell the difference between Bridge mode and Proxy mode (either Direct or SmartScale):

In Proxy mode, you will see the -tungsten tag appended to the Server version string:

tungsten@db1:/opt/continuent/software/tungsten-clustering-6.0.5-41 # tpm connector Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 34 Server version: 5.7.26-log-tungsten MySQL Community Server (GPL)

Once logged into the Connector in Proxy mode, you have the full set of interactive tungsten commands available:

mysql> tungsten help; +---------------------------------------------------------------------------------------------------------------------------------+ | Message | +---------------------------------------------------------------------------------------------------------------------------------+ | tungsten connection status: display information about the connection used for the last request ran | | tungsten connection count: gives the count of current connections to each one of the cluster datasources | | tungsten cluster status: prints detailed information about the cluster view this connector has | | tungsten show [full] processlist: list all running queries handled by this connector instance | | tungsten show variables [like '<string>']: list connector configuration options in use. The <string> may contain '%' wildcards | | tungsten flush privileges: reload user.map and refresh user credentials | | tungsten mem info: display memory information about current JVM | | tungsten gc: calls garbage collector | | tungsten help: display this help message | +---------------------------------------------------------------------------------------------------------------------------------+

For more information about the Connector’s command-line interface, please visit http://docs.continuent.com/tungsten-clustering-6.0/connector-inline.html

For Bridge mode, you will not see that:

tungsten@db1:/opt/continuent/software/tungsten-clustering-6.0.5-41 # tpm connector Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 34 Server version: 5.7.26-log MySQL Community Server (GPL)

In Bridge mode, the tungsten commands do not work:

mysql> tungsten help; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'tungsten help' at line 1

The Library Please read the docs!

For more information about the Tungsten Connector:

For more documentation about Tungsten software, please visit https://docs.continuent.com

Summary The Wrap-Up

In this blog post we discussed how one can tell which Tungsten Connector mode is in use: Bridge, Proxy/Direct or Proxy/SmartScale.

Tungsten Clustering is the most flexible, performant global database layer available today – use it underlying your SaaS offering as a strong base upon which to grow your worldwide business!

For more information, please visit https://www.continuent.com/solutions

Want to learn more or run a POC? Contact us

Shinguz: Who else is using my memory - File System Cache analysis

See also our former articles:

When we do analysis of MariaDB Database servers we also check the memory (RAM and Swap) available:

# free --kilo --wide total used free shared buffers cache available Mem: 16106252 4329952 703356 199008 307872 10765072 11042748 Swap: 31250428 528684 30721744

The values for buffers and especially for cache can be sometimes quite big. In this case they use about 10 GiB. So let us have a look what these things called buffers and cache are, using our valuable RAM... When we check the man pages of free we will find:

# man free ... buffers Memory used by kernel buffers (Buffers in /proc/meminfo) cache Memory used by the page cache and slabs (Cached and Slab in /proc/meminfo) buff/cache Sum of buffers and cache

So let us check a more fine grained information in /proc/meminfo which is an interface to the kernel data structures:

# cat /proc/meminfo | grep -e ^Cached -e Slab -e Buffers Buffers: 307872 kB Cached: 10155156 kB Slab: 609916 kB

Same values! Then let us have a look at the man pages of proc what we can find about these values:

# man proc ... Buffers Relatively temporary storage for raw disk blocks that shouldn't get tremendously large (20MB or so). Cached In-memory cache for files read from the disk (the page cache). Doesn't include SwapCached. Slab In-kernel data structures cache.

So it looks like we have a raw I/O Cache (called Buffer Cache) and a File System I/O Cache (called Page Cache). So how does this work? What is a raw I/O? And is a Files System I/O cached once (Cached) or twice (Cached and Buffers)?

When we dig a bit deeper we can find that prior to Linux Kernels 2.4 the two Caches were distinct. So that was a waste of memory (RAM). It seems like today this is not the case any more [1], [2], [3]. And man pages are a bit out of date or at least not very precise?

Analysing the Linux Page Cache

A very good source when it comes to Linux Performance Tuning and Measuring is Brendan Gregg's Website. To measure Linux Page Cache Hit Ratio he provides a tool called cachestat which is part of the perf-tools collection on GitHub.

With cachestat we get a per second statistics of the Buffer Cache and the Page Cache (without Slabs), Cache Hits, Cache Misses, Dirty Buffer Entries in the Cache and a Cache Hit Ratio:

# sudo cachestat Counting cache functions... Output every 1 seconds. HITS MISSES DIRTIES RATIO BUFFERS_MB CACHE_MB 1419 8 0 99.4% 338 9406 1368 0 0 100.0% 338 9406 1391 0 0 100.0% 338 9406 8558 0 29 100.0% 338 9406 31870 0 163 100.0% 338 9406 1374 0 24 100.0% 338 9406 1388 0 0 100.0% 338 9406 1370 0 0 100.0% 338 9406 1388 0 0 100.0% 338 9406

Brendan Gregg also mentions a tool called pcstat (on GitHub) by Al Tobey which gets Page Cache Statistics for Files. Unfortunately I had some problems building it on my Ubuntu 16.04 with Go version 1.6. So I built it on an Ubuntu 18.04 (Go 1.10) and copied it over to to Ubuntu 16.04):

# export GOPATH=/tmp/ # cd $GOPATH # go get golang.org/x/sys/unix # go get github.com/tobert/pcstat/pcstat # bin/pcstat $GOPATH/bin/pcstat

Then I tried pcstat out against a MariaDB 10.4 instance. In the output we can see how big the files are in bytes, how many pages of 4 kib this corresponds to, how many of these 4 kib pages are cached and the percentage of pages cached:

# pcstat /home/mysql/database/mariadb-104/data/ib* /home/mysql/database/mariadb-104/data/test/test* +------------------------------------------------------+----------------+------------+-----------+---------+ | Name | Size (bytes) | Pages | Cached | Percent | |------------------------------------------------------+----------------+------------+-----------+---------| | /home/mysql/database/mariadb-104/data/ib_buffer_pool | 14642 | 4 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/ibdata1 | 79691776 | 19456 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/ib_logfile0 | 268435456 | 65536 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/ib_logfile1 | 268435456 | 65536 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/ibtmp1 | 12582912 | 3072 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/test/test.frm | 1097 | 1 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/test/test.ibd | 13631488 | 3328 | 0 | 000.000 | +------------------------------------------------------+----------------+------------+-----------+---------+

When we run pcstat over time with the famous watch command we can even see how the Page Cache is heating up:

# watch -d -n 1 'pcstat /home/mysql/database/mariadb-104/data/ib* /home/mysql/database/mariadb-104/data/test/test* ; free -w' +------------------------------------------------------+----------------+------------+-----------+---------+ | Name | Size (bytes) | Pages | Cached | Percent | |------------------------------------------------------+----------------+------------+-----------+---------| | /home/mysql/database/mariadb-104/data/ib_buffer_pool | 14642 | 4 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/ibdata1 | 79691776 | 19456 | 2416 | 012.418 | | /home/mysql/database/mariadb-104/data/ib_logfile0 | 268435456 | 65536 | 3165 | 004.829 | | /home/mysql/database/mariadb-104/data/ib_logfile1 | 268435456 | 65536 | 5890 | 008.987 | | /home/mysql/database/mariadb-104/data/ibtmp1 | 12582912 | 3072 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/test/test.frm | 1097 | 1 | 1 | 100.000 | | /home/mysql/database/mariadb-104/data/test/test.ibd | 13631488 | 3328 | 1164 | 034.976 | +------------------------------------------------------+----------------+------------+-----------+---------+ total used free shared buffers cache available Mem: 16106252 4329952 703356 199008 307872 10765072 11042748 Swap: 31250428 528684 30721744

An other tool which was discussed on Brendans Website was vmtouch - the Virtual Memory Toucher (on GitHub, Documentation). With vmtouch we can see for example how much of the directory /home/mysql/database/mariadb-104/data (datadir) is currently in cache:

# vmtouch -f /home/mysql/database/mariadb-104/data Files: 503 Directories: 9 Resident Pages: 29356/231060 114M/902M 12.7% Elapsed: 0.009668 seconds

Or more fine grained how much of InnoDB System Files are currently in memory:

# vmtouch -f -v /home/mysql/database/mariadb-104/data/ib* /home/mysql/database/mariadb-104/data/ib_buffer_pool [ ] 0/4 /home/mysql/database/mariadb-104/data/ibdata1 [oOooooo ooooo ] 2416/19456 /home/mysql/database/mariadb-104/data/ib_logfile0 [o oOO] 3165/65536 /home/mysql/database/mariadb-104/data/ib_logfile1 [OOOOOOOOOOOOOOOOOOOOOo ] 23192/65536 /home/mysql/database/mariadb-104/data/ibtmp1 [ ] 0/3072 Files: 5 Directories: 0 Resident Pages: 28773/153604 112M/600M 18.7% Elapsed: 0.005499 seconds

A further question to answer is: Can I see all files cached in the Page Cache? So it seem like this is not easily possible:

There is no efficient search mechanism for doing the reverse - getting a file name belonging to a data block would require reading all inodes and indirect blocks on the file system. If you need to know about every single file's blocks stored in the page cache, you would need to supply a list of all files on your file system(s) to fincore. But that again is likely to spoil the measurement as a large amount of data would be read traversing the directories and getting all inodes and indirect blocks - putting them into the page cache and evicting the very page cache data you were trying to examine. [5]

Also in this article we can read about the Linux File Tools (linux-ftools) by Google. It seems to be a bit more complicated to make them work. So I let it be.

How is the Page Cache related to MariaDB

After all this technical O/S discussion, how is Linux Page Cache related to your MariaDB Database? Your MariaDB Database caches Data and Indexes as well. For the InnoDB Storage Engine this is the InnoDB Buffer Pool and for the Aria Storage Engine this is the Aria Page Cache Buffer. So if your MariaDB Database caches pages and if your Linux O/S caches pages the probability is high they cache the same data twice and thus waste valuable RAM! Fortunately InnoDB is configurable in a way it does NOT cache InnoDB files in the Page Cache. This is controlled with the InnoDB Server Variable innodb_flush_method.

When we look at InnoDB Files which were opened in a "normal" way (default: innodb_flush_method = fsync) we get the following information about how the files were opened (man 2 open and [6]):

# lsof +fg ./ib* COMMAND PID USER FD TYPE FILE-FLAG DEVICE SIZE/OFF NODE NAME mysqld 2098 mysql 7uW REG RW,LG,0x80000 8,1 79691776 9175185 ./ibdata1 mysqld 2098 mysql 11uW REG RW,LG,0x80000 8,1 268435456 9175186 ./ib_logfile0 mysqld 2098 mysql 12uW REG RW,LG,0x80000 8,1 268435456 9175187 ./ib_logfile1 mysqld 2098 mysql 13uW REG RW,LG,0x80000 8,1 12582912 9175280 ./ibtmp1

The interesting column here is the FILE-FLAG column which indicates (man lsof):

# man lsof FILE-FLAG when g or G has been specified to +f, this field contains the contents of the f_flag[s] member of the kernel file structure and the kernel's per-process open file flags (if available); `G' causes them to be displayed in hexadecimal; `g', as short-hand names; two lists may be displayed with entries separated by commas, the lists separated by a semicolon (`;'); the first list may contain short-hand names for f_flag[s] values from the following table: DIR direct LG large file RW read and write access

The output is not so clear or completely understandable yet thus we want to have the open file flags in hexadecimal notation:

# lsof +fG ./ib* COMMAND PID USER FD TYPE FILE-FLAG DEVICE SIZE/OFF NODE NAME mysqld 2098 mysql 7uW REG 0x88002;0x0 8,1 79691776 9175185 ./ibdata1 mysqld 2098 mysql 11uW REG 0x88002;0x0 8,1 268435456 9175186 ./ib_logfile0 mysqld 2098 mysql 12uW REG 0x88002;0x0 8,1 268435456 9175187 ./ib_logfile1 mysqld 2098 mysql 13uW REG 0x88002;0x0 8,1 12582912 9175280 ./ibtmp1

The Linux Kernel open file flags can be found here: fcntl.h. I have extracted the most relevant open file flags for our examination:

#define O_RDWR 00000002 (oct, 0x00002) #define O_DIRECT 00040000 (oct, 0x04000) /* direct disk access hint */ #define O_LARGEFILE 00100000 (oct, 0x08000) #define O_CLOEXEC 02000000 (oct, 0x80000) /* set close_on_exec */

So we can see that these 4 InnoDB files where opened with O_RDWR (RW), O_LARGE_FILE (LG) and O_CLOEXEC (not available (yet?) in lsof translation output).

Now let us start the MariaDB Database with the server variable set to: innodb_flush_method = O_DIRECT and check how the files where opened:

# lsof +fg ./ib* COMMAND PID USER FD TYPE FILE-FLAG DEVICE SIZE/OFF NODE NAME mysqld 2098 mysql 7uW REG RW,DIR,LG,0x80000 8,1 79691776 9175185 ./ibdata1 mysqld 2098 mysql 11uW REG RW,LG,0x80000 8,1 268435456 9175186 ./ib_logfile0 mysqld 2098 mysql 12uW REG RW,LG,0x80000 8,1 268435456 9175187 ./ib_logfile1 mysqld 2098 mysql 13uW REG RW,DIR,LG,0x80000 8,1 12582912 9175280 ./ibtmp1 # lsof +fG ./ib* COMMAND PID USER FD TYPE FILE-FLAG DEVICE SIZE/OFF NODE NAME mysqld 2098 mysql 7uW REG 0x8c002;0x0 8,1 79691776 9175185 ./ibdata1 mysqld 2098 mysql 11uW REG 0x88002;0x0 8,1 268435456 9175186 ./ib_logfile0 mysqld 2098 mysql 12uW REG 0x88002;0x0 8,1 268435456 9175187 ./ib_logfile1 mysqld 2098 mysql 13uW REG 0x8c002;0x0 8,1 12582912 9175280 ./ibtmp1

We can see a new flag DIR or 0x04000 which means the files where opened with O_DIRECT. But only the InnoDB Temporary Table Tablespace and the InnoDB System Tablespace but not the two InnoDB Transaction Logs.

Translation of hex to oct: 0x8c002 = 02140002.

But what does O_DIRECT mean? Looking at the open(2) man pages we can find:

# man 2 open O_DIRECT (since Linux 2.4.10) Try to minimize cache effects of the I/O to and from this file. In general this will degrade performance, but it is useful in special situations, such as when applications do their own caching. File I/O is done directly to/from user-space buffers. The O_DIRECT flag on its own makes an effort to transfer data synchronously, but does not give the guarantees of the O_SYNC flag that data and necessary meta‐ data are transferred. To guarantee synchronous I/O, O_SYNC must be used in addition to O_DIRECT.

So O_DIRECT is exactly what we want in this case: Bypassing the File System Page Cache to not cache the Database blocks twice!

To verify the impact we run pcstat again:

# pcstat /home/mysql/database/mariadb-104/data/ib* /home/mysql/database/mariadb-104/data/test/test* +------------------------------------------------------+----------------+------------+-----------+---------+ | Name | Size (bytes) | Pages | Cached | Percent | |------------------------------------------------------+----------------+------------+-----------+---------| | /home/mysql/database/mariadb-104/data/ib_buffer_pool | 16020 | 4 | 4 | 100.000 | | /home/mysql/database/mariadb-104/data/ibdata1 | 79691776 | 19456 | 140 | 000.720 | | /home/mysql/database/mariadb-104/data/ib_logfile0 | 268435456 | 65536 | 36844 | 056.219 | | /home/mysql/database/mariadb-104/data/ib_logfile1 | 268435456 | 65536 | 65536 | 100.000 | | /home/mysql/database/mariadb-104/data/ibtmp1 | 12582912 | 3072 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/test/test.frm | 1097 | 1 | 1 | 100.000 | | /home/mysql/database/mariadb-104/data/test/test.ibd | 67108864 | 16384 | 13400 | 081.787 | +------------------------------------------------------+----------------+------------+-----------+---------+

But... part of the InnoDB Tablespace files is still cached! Also checking the total amount of Buffers and Cache shows the same:

# free total used free shared buff/cache available Mem: 16106252 4401788 368200 456716 11336264 10691792 Swap: 31250428 1348440 29901988

So restarting the MariaDB database does not purge the Page Cache! Note: This is important to notice because bypassing the Page Cache helps to not wasting valuable RAM but it makes Database restart much more costly because Page Cache does not help/support InnoDB Buffer Pool heating any more!

Then let us clear the Linux Page Cache and check the result:

# echo 1 > /proc/sys/vm/drop_caches # free -w total used free shared buffers cache available Mem: 16106252 4395892 10539864 441708 696 1169800 10882984 Swap: 31250428 1348428 29902000

Checking with pcstat shows now that all InnoDB pages are wiped out of the Page Cache:

# pcstat /home/mysql/database/mariadb-104/data/ib* /home/mysql/database/mariadb-104/data/test/test* +------------------------------------------------------+----------------+------------+-----------+---------+ | Name | Size (bytes) | Pages | Cached | Percent | |------------------------------------------------------+----------------+------------+-----------+---------| | /home/mysql/database/mariadb-104/data/ib_buffer_pool | 16020 | 4 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/ibdata1 | 79691776 | 19456 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/ib_logfile0 | 268435456 | 65536 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/ib_logfile1 | 268435456 | 65536 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/ibtmp1 | 12582912 | 3072 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/test/test.frm | 1097 | 1 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/test/test.ibd | 67108864 | 16384 | 0 | 000.000 | +------------------------------------------------------+----------------+------------+-----------+---------+

And after a while running traffic on the test table we can see that InnoDB Transaction Log Files are cached again in the Page Cache but NOT the InnoDB Tablespace files:

# pcstat /home/mysql/database/mariadb-104/data/ib* /home/mysql/database/mariadb-104/data/test/test* +------------------------------------------------------+----------------+------------+-----------+---------+ | Name | Size (bytes) | Pages | Cached | Percent | |------------------------------------------------------+----------------+------------+-----------+---------| | /home/mysql/database/mariadb-104/data/ib_buffer_pool | 16020 | 4 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/ibdata1 | 79691776 | 19456 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/ib_logfile0 | 268435456 | 65536 | 3012 | 004.596 | | /home/mysql/database/mariadb-104/data/ib_logfile1 | 268435456 | 65536 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/ibtmp1 | 12582912 | 3072 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/test/test.frm | 1097 | 1 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/test/test.ibd | 71303168 | 17408 | 0 | 000.000 | +------------------------------------------------------+----------------+------------+-----------+---------+

Also with vmtouch we can see the difference:

./vmtouch -f -v /home/mysql/database/mariadb-104/data/ib* /home/mysql/database/mariadb-104/data/test/test* /home/mysql/database/mariadb-104/data/ib_buffer_pool [ ] 0/4 /home/mysql/database/mariadb-104/data/ibdata1 [ ] 0/19456 /home/mysql/database/mariadb-104/data/ib_logfile0 [o oOOOo ] 4252/65536 /home/mysql/database/mariadb-104/data/ib_logfile1 [ ] 0/65536 /home/mysql/database/mariadb-104/data/ibtmp1 [ ] 0/3072 /home/mysql/database/mariadb-104/data/test/test.frm [ ] 0/1 /home/mysql/database/mariadb-104/data/test/test.ibd [ ] 0/17408 Files: 7 Directories: 0 Resident Pages: 4252/171013 16M/668M 2.49% Elapsed: 0.003264 seconds

And also cachestat shows the effect of a flushed Buffer Cache and Page Cache:

# ./cachestat Counting cache functions... Output every 1 seconds. HITS MISSES DIRTIES RATIO BUFFERS_MB CACHE_MB 677882 19 740 100.0% 67 1087 679213 10 700 100.0% 67 1087 677236 0 732 100.0% 67 1087 685673 11 932 100.0% 67 1088 677933 5 703 100.0% 67 1088

Caution: Depending on your underlying I/O system it makes nevertheless sense to run your MariaDB Database with innodb_flush_method = fsync in certain cases! See also PostgreSQL behaviour.

Note: This information could also be interesting for PostgreSQL DBAs because they do redundant buffering with their shared_buffers (why plural? It is just one!?!) and the O/S Page Cache as well!

What is Slab

Beside Buffer Cache and the Page Cache itself we have a third thing in the /proc/meminfo statistics listed as Slabs. So what are Slabs? Slab seems to be a specific memory management (allocation) mechanism. It is used for frequently used objects in the Linux Kernel (buffer heads, inodes, dentries, etc.) [7-15]. So it contains something like other Linux Kernel Buffers and Kernel Caches.

What kind of other Linux Kernel Buffer and Kernel Caches exists can be found with the following command:

# sudo cat /proc/slabinfo slabinfo - version: 2.1 # name : tunables : slabdata nf_conntrack_1 14183 15275 320 25 2 : tunables 0 0 0 : slabdata 611 611 0 ext4_groupinfo_4k 8575 8596 144 28 1 : tunables 0 0 0 : slabdata 307 307 0 i915_gem_vma 523 950 320 25 2 : tunables 0 0 0 : slabdata 38 38 0 UDPv6 120 120 1088 30 8 : tunables 0 0 0 : slabdata 4 4 0 tw_sock_TCPv6 2668 2668 280 29 2 : tunables 0 0 0 : slabdata 92 92 0 request_sock_TCPv6 24 72 328 24 2 : tunables 0 0 0 : slabdata 3 3 0 TCPv6 68 105 2112 15 8 : tunables 0 0 0 : slabdata 7 7 0 cfq_queue 391 442 232 17 1 : tunables 0 0 0 : slabdata 26 26 0 mqueue_inode_cache 72 72 896 18 4 : tunables 0 0 0 : slabdata 4 4 0 fuse_request 20 40 400 20 2 : tunables 0 0 0 : slabdata 2 2 0 fuse_inode 1 21 768 21 4 : tunables 0 0 0 : slabdata 1 1 0 fat_cache 102 408 40 102 1 : tunables 0 0 0 : slabdata 4 4 0 hugetlbfs_inode_cache 28 84 584 28 4 : tunables 0 0 0 : slabdata 3 3 0 squashfs_inode_cache 25 50 640 25 4 : tunables 0 0 0 : slabdata 2 2 0 jbd2_journal_handle 340 340 48 85 1 : tunables 0 0 0 : slabdata 4 4 0 jbd2_journal_head 2040 2040 120 34 1 : tunables 0 0 0 : slabdata 60 60 0 jbd2_revoke_table_s 260 512 16 256 1 : tunables 0 0 0 : slabdata 2 2 0 jbd2_revoke_record_s1152 1408 32 128 1 : tunables 0 0 0 : slabdata 11 11 0 ext4_inode_cache 208751 210840 1072 30 8 : tunables 0 0 0 : slabdata 7028 7028 0 ext4_free_data 320 448 64 64 1 : tunables 0 0 0 : slabdata 7 7 0 ext4_allocation_cont 128 128 128 32 1 : tunables 0 0 0 : slabdata 4 4 0 ext4_io_end 392 560 72 56 1 : tunables 0 0 0 : slabdata 10 10 0 ext4_extent_status 64412 77928 40 102 1 : tunables 0 0 0 : slabdata 764 764 0 dquot 144 160 256 16 1 : tunables 0 0 0 : slabdata 10 10 0 mbcache 226 292 56 73 1 : tunables 0 0 0 : slabdata 4 4 0 dio 273 350 640 25 4 : tunables 0 0 0 : slabdata 14 14 0 pid_namespace 42 42 2224 14 8 : tunables 0 0 0 : slabdata 3 3 0 ip4-frags 32 64 248 16 1 : tunables 0 0 0 : slabdata 4 4 0 RAW 396 396 896 18 4 : tunables 0 0 0 : slabdata 22 22 0 UDP 68 68 960 17 4 : tunables 0 0 0 : slabdata 4 4 0 tw_sock_TCP 10750 11136 280 29 2 : tunables 0 0 0 : slabdata 384 384 0 request_sock_TCP 96 96 328 24 2 : tunables 0 0 0 : slabdata 4 4 0 TCP 119 136 1920 17 8 : tunables 0 0 0 : slabdata 8 8 0 blkdev_queue 27 48 1336 24 8 : tunables 0 0 0 : slabdata 2 2 0 blkdev_requests 394 506 368 22 2 : tunables 0 0 0 : slabdata 23 23 0 blkdev_ioc 516 546 104 39 1 : tunables 0 0 0 : slabdata 14 14 0 user_namespace 104 104 304 26 2 : tunables 0 0 0 : slabdata 4 4 0 dmaengine-unmap-256 15 15 2112 15 8 : tunables 0 0 0 : slabdata 1 1 0 sock_inode_cache 1707 1950 640 25 4 : tunables 0 0 0 : slabdata 78 78 0 file_lock_cache 665 665 208 19 1 : tunables 0 0 0 : slabdata 35 35 0 net_namespace 40 40 7296 4 8 : tunables 0 0 0 : slabdata 10 10 0 shmem_inode_cache 3315 3432 656 24 4 : tunables 0 0 0 : slabdata 143 143 0 taskstats 96 96 328 24 2 : tunables 0 0 0 : slabdata 4 4 0 proc_inode_cache 6895 7072 624 26 4 : tunables 0 0 0 : slabdata 272 272 0 sigqueue 100 100 160 25 1 : tunables 0 0 0 : slabdata 4 4 0 bdev_cache 29 76 832 19 4 : tunables 0 0 0 : slabdata 4 4 0 kernfs_node_cache 43625 44982 120 34 1 : tunables 0 0 0 : slabdata 1323 1323 0 mnt_cache 518 546 384 21 2 : tunables 0 0 0 : slabdata 26 26 0 inode_cache 17519 17668 568 28 4 : tunables 0 0 0 : slabdata 631 631 0 dentry 424185 439992 192 21 1 : tunables 0 0 0 : slabdata 20952 20952 0 buffer_head 1112865 1112865 104 39 1 : tunables 0 0 0 : slabdata 28535 28535 0 vm_area_struct 53945 55300 200 20 1 : tunables 0 0 0 : slabdata 2765 2765 0 files_cache 260 299 704 23 4 : tunables 0 0 0 : slabdata 13 13 0 signal_cache 509 630 1088 30 8 : tunables 0 0 0 : slabdata 21 21 0 sighand_cache 346 405 2112 15 8 : tunables 0 0 0 : slabdata 27 27 0 task_struct 1189 1269 3584 9 8 : tunables 0 0 0 : slabdata 141 141 0 Acpi-Operand 5703 5824 72 56 1 : tunables 0 0 0 : slabdata 104 104 0 Acpi-Parse 1314 1314 56 73 1 : tunables 0 0 0 : slabdata 18 18 0 Acpi-State 204 204 80 51 1 : tunables 0 0 0 : slabdata 4 4 0 Acpi-Namespace 4077 4182 40 102 1 : tunables 0 0 0 : slabdata 41 41 0 anon_vma 19831 21522 80 51 1 : tunables 0 0 0 : slabdata 422 422 0 numa_policy 170 170 24 170 1 : tunables 0 0 0 : slabdata 1 1 0 radix_tree_node 321937 327740 584 28 4 : tunables 0 0 0 : slabdata 11705 11705 0 trace_event_file 3985 4002 88 46 1 : tunables 0 0 0 : slabdata 87 87 0 ftrace_event_field 86541 88570 48 85 1 : tunables 0 0 0 : slabdata 1042 1042 0 idr_layer_cache 533 555 2096 15 8 : tunables 0 0 0 : slabdata 37 37 0 kmalloc-8192 1246 1246 8192 4 8 : tunables 0 0 0 : slabdata 502 502 0 kmalloc-4096 658 720 4096 8 8 : tunables 0 0 0 : slabdata 90 90 0 kmalloc-2048 1955 2144 2048 16 8 : tunables 0 0 0 : slabdata 134 134 0 kmalloc-1024 44217 44384 1024 16 4 : tunables 0 0 0 : slabdata 2774 2774 0 kmalloc-512 3037 3808 512 16 2 : tunables 0 0 0 : slabdata 238 238 0 kmalloc-256 17465 20384 256 16 1 : tunables 0 0 0 : slabdata 1274 1274 0 kmalloc-192 27708 28665 192 21 1 : tunables 0 0 0 : slabdata 1365 1365 0 kmalloc-128 140581 143744 128 32 1 : tunables 0 0 0 : slabdata 4492 4492 0 kmalloc-96 168044 168378 96 42 1 : tunables 0 0 0 : slabdata 4009 4009 0 kmalloc-64 117533 123264 64 64 1 : tunables 0 0 0 : slabdata 1926 1926 0 kmalloc-32 80425 90368 32 128 1 : tunables 0 0 0 : slabdata 706 706 0 kmalloc-16 9513 11264 16 256 1 : tunables 0 0 0 : slabdata 44 44 0 kmalloc-8 6616 7168 8 512 1 : tunables 0 0 0 : slabdata 14 14 0 kmem_cache_node 320 320 64 64 1 : tunables 0 0 0 : slabdata 5 5 0 kmem_cache 208 208 256 16 1 : tunables 0 0 0 : slabdata 13 13 0

If you want to see the most frequently used (hottest) Slabs you can see them top-like with slabtop. If you press c you can sort the Slabs by CACHE_SIZE:

# sudo slabtop Active / Total Objects (% used) : 2249113 / 2280136 (98.6%) Active / Total Slabs (% used) : 70256 / 70256 (100.0%) Active / Total Caches (% used) : 86 / 121 (71.1%) Active / Total Size (% used) : 597547.86K / 605445.30K (98.7%) Minimum / Average / Maximum Object : 0.01K / 0.26K / 18.56K OBJS ACTIVE USE OBJ SIZE SLABS OBJ/SLAB CACHE SIZE NAME 294308 289889 98% 0.57K 10511 28 168176K radix_tree_node 105030 104435 99% 1.05K 3501 30 112032K ext4_inode_cache 745446 745446 100% 0.10K 19114 39 76456K buffer_head 59984 59909 99% 1.00K 3749 16 59984K ecryptfs_inode_cache 47520 47157 99% 1.00K 2970 16 47520K kmalloc-1024 215166 214987 99% 0.19K 10246 21 40984K dentry 139744 138452 99% 0.12K 4367 32 17468K kmalloc-128 179508 179011 99% 0.09K 4274 42 17096K kmalloc-96 47140 45768 97% 0.20K 2357 20 9428K vm_area_struct 14700 14700 100% 0.55K 525 28 8400K inode_cache ...
Literature Taxonomy upgrade extras:  memory RAM cache file system o_direct innodb_flush_method postgresql innodb tablespace

Assessing MySQL Performance Amongst AWS Options – Part Two

See part one of this series here

This post is part two of my series “Assessing MySQL Performance Amongst AWS Options”, taking a look at how current Amazon RDS services – Amazon Aurora and Amazon RDS for MySQL – compare with Percona Server with InnoDB and RocksDB engines on EC2 instances. This time around, I am reviewing the total cost of one test run for each database as well as seeing which databases are the most efficient.

First, a quick recap of the evaluation scenario:

The benchmark scripts

For these evaluations, we use the sysbench/tpcc LUA test with a scale factor of 500 warehouses/10 tables. This is the equivalent of 5000 warehouses of the official TPC-C benchmark.

Amazon MySQL Environments

These are the AWS MySQL environments under analysis:

  • Amazon RDS Aurora
  • Amazon RDS for MySQL with the InnoDB storage engine
  • Percona Server for MySQL with the InnoDB storage engine on Amazon EC2
  • Percona Server for MySQL with the RocksDB storage engine on Amazon EC2
Technical Setup – Server

These general notes apply across the board:

  • AWS region us-east-1(N.Virginia) was used for all tests
  • Server and client instances were spawned in the same availability zone
  • All data for tests were prepared in advance, stored as snapshots, and restored before the test
  • Encryption was not used

And we believe that these configuration notes allow for a fair comparison of the different technologies:

  • AWS EBS optimization was enabled for EC2 instances
  • For RDS/Amazon Aurora only a primary DB instance was created and used
  • In the case of RDS/MySQL, a single AZ deployment was used for RDS/MySQL
  • EC2/Percona Server for MySQL tests were run with binary log enabled

Finally, here are the individual server configurations per environment:

Server test #1: Amazon RDS Aurora
  • Database server: Aurora MySQL 5.7
  • DB instances: r5.large, r5.xlarge, r5.2xlarge, r5.4xlarge
  • volume: used ~450GB(>15000 IOPS)
Server test #2: Amazon RDS for MySQL with InnoDB Storage Engine
  • Database server: MySQL Server 5.7.25
  • RDS instances: db.m5.large, db.m5.xlarge, db.m5.2xlarge, db.m5.4xlarge
  • volumes(allocated space):
    • gp2: 5400GB(~16000 IOPs)
    • io1: 700GB(15000 IOPs)
Server test #3: Percona Server for MySQL with InnoDB Storage Engine
  • Database server: Percona Server 5.7.25
  • EC2 instances: m5.large, m5.xlarge, m5.2xlarge, m5.4xlarge
  • volumes(allocated space):
    • gp2: 5400GB(~16000 IOPs)
    • io1: 700GB(15000 IOPs)
Server test #4: Percona Server for MySQL with RocksDB using LZ4 compression
  • Database server: Percona Server 5.7.25
  • EC2 instances: m5.large, m5.xlarge, m5.2xlarge, m5.4xlarge
  • volumes(allocated space):
    • gp2: 5400GB(~16000 IOPs)
    • io1: 350GB(15000 IOPs)
Technical Setup – Client

Common to all tests, we used an EC2 instance: m5.xlarge. And now that we have established the setup, let’s take a look at what we found.

Costs

Now we are getting down to the $’s! First, let’s review the total cost of one test run for each database:

Sorting the costs of one test run in order from cheapest to most expensive we see this order emerge:

  1. EC2/gp2 carrying server tests #3 or #4 featuring Percona Server for MySQL [represents the LEAST cost in $’s]
  2. RDS/gp2 carrying server test #2, RDS/MySQL
  3. EC2/io1 carrying server tests #3 or #4
  4. RDS/io1 carrying server test #2, RDS/MySQL
  5. RDS/Aurora, server test #1  [GREATEST COST IN $’s]

How does that translate to $’s? Let’s find out how the structure of these costs looks like for every database. Before we study that, though, there are some things to bear in mind:

  • Our calculations include only server-side costs
  • Per instance, the price we used as a baseline was RESERVED INSTANCE STANDARD 1-YEAR TERM
  • For RDS/Amazon Aurora the values for volume size and amount of I/O requests represent real data obtained from CloudWatch metrics (VolumeBytesUsed for used volume space and VolumeReadIOPs+VolumeWriteIOPs for IOPs used) after the test run
  • In the case of Percona Server/RocksDB due to LZ4 compression, the database on disk is 5x smaller, so we used a half-sized io1 volume – 350GB vs 700GB for either Percona Server with InnoDB or RDS/MySQL. This still complies with the requirement for io1 volumes to deliver 50 IOPS per GB.
  • The duration set for the test run is 30 mins
Our total cost formulas

These are the formulas we used in calculating these costs:

  • EC2/gp2, EC2/io1, RDS/gp2, RDS/io1
    • total cost = server instance size cost + allocated volume size cost + requested amount of IOPS cost
  • RDS/Amazon Aurora
    • total cost = server instance size cost + allocated volume size cost + actually used amount of I/O cost
The results

Here are our calculations in chart form, you can click on the chart to enlarge it on screen:

One interesting observation here is that, as you can see from the costs structure chart, the most significant part of costs is IO provisioning – either the requested amount of IOPS (EC2/io1 or RDS/io1) or the actually used amount of IOPS (RDS/Aurora). In the former case, the cost is a function of time, and in the latter case, costs depend only on the amount of I/O requests actually issued.

Let’s check how these costs might look like if we provision EC2/io1, RDS/io1 volumes and RDS/aurora storage for one month. From the cost structure, it’s clear that in case of RDS/aurora 4xlarge – db instance performed 51M I/O requests for half an hour. So we effectively got 51000000 (I/O request) / 1800(seconds) ~= 28000 IOPs.

EC2/io1: (28000 (IOPS) * 0.065(IOPs price) * 24(hours)*30(days)/(24(hours)*30(days)) 1820$ RDS/io1: (28000 (IOPS) * 0.1(IOPs price) * 24(hours)*30(days)/(24(hours)*30(days)) 2800$ RDS/aurora: 102M(I/O per hour) * 0.2(I/O req price) * 24(hours)*30(days) 14688$

In this way, IO provisioning of 28000 IOPS for EC2/io1 costs 8x less and for RDS/io1 costs 5x less. That means that to be cost-efficient, the throughput of RDS/Aurora should be at least 5x or even 8x better than that of EC2 or RDS with io1 volume.

Conclusion: the IO provisioning factor should be taken into account during your planning of deployments with io1 volumes or RDS/aurora

Efficiency

Now it’s time to review which databases perform the most efficiently by analyzing their transaction/cost ratio:

Below you can find the minimum and maximum prices for 1000 transactions for each of the database servers in our tests, again running from cheapest to most expensive in $ terms:

Server Min $’s per 1000 TX Server Config Min $’s per 1000 TX Server Config Server test #4 EC2#Percona Server/RocksDB 0.42 4xlarge/io1 1.93 large/io1 Server test #3 EC2#Percona Server/InnoDB 1.66 4xlarge/gp2 12.11 large/io1 Server test #2 RDS#MySQL/InnoDB 2.23 4xlarge/gp2 22.3 large/io1 Server test #1 RDS#Amazon Aurora 8.29 4xlarge 13.31 xlarge Some concluding thoughts
  • EC2#Percona Server/RocksDB offers the lowest price per 1000 transactions – $0.42 on m5.4xlarge instance with 350GB io1 volume/15000 IOPs
  • RDS/MySQL looked to be the most expensive in this evaluation – $22.3 for 1000 transactions – db.m5.large with 700GB io1 volume/15000 IOPs
  • Lowest price for each database was obtained on 4xlarge instances, most expensive on large instances.
  • IO provisioning is a key factor that impacts run costs
  • For both EC2 and RDS gp2/5400GB (~16000 IOPS) is the cost wise choice
  • RDS/Aurora – the lowest price per 1000 transactions is $8.29, but that is 4x more expensive than the best price of 1000 transactions for RDS/MySQL, 5x more expensive than for EC2#Percona/InnoDB, and 20x more expensive than for EC2#Percona/RockDB. That means that despite the fact that Amazon Aurora shows very good throughput (actually the best among InnoDB-like engines), it may not be as cost-effective as other options.
One Final Note

When estimating your expenses, you will need to keep in mind that each company is different in terms of what they offer, how they build and manage those offerings, and of course, their pricing structure and cost per transaction. For AWS, you do need to be aware of the expenses of building and managing those things yourself that AWS handles for you; i.e. built into their cost. We can see, however, that in these examples, MyRocks is definitely a cost-effective solution when comparing direct costs.

SQL TOP, LIMIT Or ROWNUM Clause Example Tutorial

SQL TOP, LIMIT Or ROWNUM Clause Example Tutorial is today’s topic. SQL SELECT TOP clause is used to specify the number of records to be returned. The SELECT TOP clause is used on large tables having thousands of records because returning a very large number of records that can impact on the performance. The operation performed by TOP, LIMIT, and ROWNUM clause has almost the same functionality.

SQL TOP, LIMIT Or ROWNUM Clause

In some situations, you may not be interested in all of of the rows returned by a query, for example, if you want to retrieve the top 10 employees who have recently joined the organization and get top 3 students by score, or something like that.

If we want to handle such situations, you can use the SQL’s TOP clause in the SELECT statement. However, a TOP clause is only supported by the SQL Server and MS Access database systems. 

Not all the database systems support the SELECT TOP clause.

MySQL supports the LIMIT clause to select a limited number of records.

Oracle uses ROWNUM.

#SYNTAX: (For SQL SERVER) SELECT TOP number|percent column_name(s) FROM table_name WHERE condition; #Parameters
  1. TOP number: The numbers of records to be retrieved.
  2. TOP percent: Percentage of records to be retrieved.
  3. Table_name: Name of the table.
  4. Condition: Condition to be imposed on the Select statement.
#Syntax: (For Oracle) SELECT column_name(s) FROM table_name WHERE ROWNUM <= number;

See the syntax of MySQL Databases.

#Syntax: (For MySQL databases) SELECT&nbsp;column_name(s) FROM&nbsp;table_name WHERE&nbsp;condition LIMIT&nbsp;number;

Let’s understand all these with examples.

Consider table: CUSTOMERS

ID NAME AGE ADDRESS SALARY  1 Tom 21 Kolkata 500 2 Karan 22 Allahabad 600 3 Hardik 23 Dhanbad 700 4 Komal 24 Mumbai 800

 

QUERY: (For SQL SERVER)

Select Top 3 * From Customers; Output: ID NAME AGE ADDRESS SALARY  1 Tom 21 Kolkata 500 2 Karan 22 Allahabad 600 3 Hardik 23 Dhanbad 700

 

So, here the first three records are displayed as we have used Top number clause.

#SQL TOP PERCENT Example

The following SQL statement selects the first 50% of the records from the “Customers” table.

SELECT TOP 50 PERCENT * FROM Customers; #QUERY: (For MySQL databases) Select * from Customers where ID >= 1 LIMIT 3; #Output: ID NAME AGE ADDRESS SALARY  1 Tom 21 Kolkata 500 2 Karan 22 Allahabad 600 3 Hardik 23 Dhanbad 700

So, here, the records whose Id’s are more than 1 and is equal to 1 are displayed up to limit 3.

#QUERY: (For Oracle) SELECT * FROM Customers WHERE ROWNUM <= 3; #Output: ID NAME AGE ADDRESS SALARY  1 Tom 21 Kolkata 500 2 Karan 22 Allahabad 600 3 Hardik 23 Dhanbad 700

 

#Using LIMIT along with OFFSET

LIMIT a OFFSET b means skip the first b entries and then return the next a entries.

OFFSET can only be used with an ORDER BY clause. It cannot be used on its own. An OFFSET value must be greater than or equal to zero. It cannot be negative, else it returns the error.

See the following syntax.

SELECT expressions FROM tables [WHERE conditions] [ORDER BY expression [ ASC | DESC ]] LIMIT number_rows [ OFFSET offset_value ];

In the above query, we are using the SELECT, WHERE, ORDER BY, and LIMIT Clause.

#Parameters or Arguments
#expressions
The columns that you wish to retrieve.
#tables
The tables that you wish to retrieve the records from. There must be at least one table listed in a FROM clause.
#WHERE conditions
Optional. The conditions that must be met for the records to be returned.
#ORDER BY expression
Optional. It is used in a SELECT LIMIT statement so that you can order the results and target those that you wish to return. The ASC is ascending order and DESC which means descending order.
#LIMIT number_rows
It specifies the limited number of rows in the result set to be returned based on the number_rows. Let’s say, LIMIT 11 would return the first 11 rows matching the SELECT criteria. This is where the sorting order matters, so you need to be sure to use the ORDER BY clause appropriately.
#OFFSET offset_value
Optional. The first row returned by the LIMIT will be determined by offset_value.
#Using LIMIT ALL

The LIMIT ALL clause implies no limit. See the following syntax.

SELECT * FROM Student LIMIT ALL;

The above query returns all the entries in the table.

Finally, SQL TOP, LIMIT Or ROWNUM Clause Example Tutorial is over.

The post SQL TOP, LIMIT Or ROWNUM Clause Example Tutorial appeared first on AppDividend.

SQL Injection Example | What Is SQL Injection

SQL Injection Example | What Is SQL Injection is today’s topic. An SQL injection is a code injection technique that may lead to destroying your database. It is one of the most common web hacking techniques. It can also be defined as placement of malicious code in SQL statements from a web page input. Attackers can use the SQL Injection vulnerabilities to bypass the application security measures.

#What Is SQL Injection(S.I.)

SQL Injection (SQLi) is the type of injection attack that makes it possible to execute the malicious SQL statements. These statements control the database server behind a web application.

The SQL Injection vulnerability may affect any website or web application that uses the SQL database such as SQL Server, MySQL, Oracle, SQL Server, or others.

Cyber Criminals may use it to gain the unauthorized access to your sensitive data like customer information, personal data, trade secrets, and intellectual property, and much more top-secret information.

SQL Injection attacks are among the oldest, most prevalent, and the most dangerous web application vulnerabilities.

#SQL in Web Pages

SQL injection usually occurs when we ask for a specific thing, and something other than that is given. For example, if we ask for the username/userId and instead of that, the user provides the SQL statement that will be unknowingly running in our database.

#Example

In the following example, we will be creating the Select statement by adding a variable for selecting a string.

Userid= getRequestString(“User_Id”); txtSQL= ”Select * from Users where UserId = ”+Userid; #1=1 based S.I.

The main purpose of the code was to create the SQL statement to select a user with the userId.

The user can enter some “Smart” input of there is nothing to prevent a user from entering a “Wrong” input.

For example:

UserId: 105 OR 1=1

See the following query.

Select * from users where userId =105 or 1=1;

The above SQL statement will return All rows from the “Users” table as OR 1=1 always results in True.

By using these techniques, the hacker might get all the information of users, including its username and password by simply inserting 105 OR 1=1 into the input field.

“=” based S.I.

For example, if a web page contains the userId and password field, as shown below.

 

#Example UserName = getRequestString("Username"); Password = getRequestString("User_password");

Now, see the following code.

SQL = 'SELECT * FROM Users WHERE Name ="' + UserName + '" AND Pass ="' + Password + '"' #Result Select * from Users where Name=”Shubh” AND Pass=”Password”

This is the approach which is used on the web for logging inside the users’ database.

But, A hacker can get access to usernames and passwords in the database by simply inserting “ OR “=” into the user name or password text box.

 

#Query SELECT * FROM Users WHERE Name ="" or ""="" AND Pass ="" or ""=""

The above SQL statement will return all the rows from the “Users” table as OR “=” is always true.

#SQL Injection Based On Batch SQL Statements

Nowadays, most databases use batched SQL statements.

A Batched SQL statement is a group of SQL statements separated by a semicolon.

#Example Select * from Users; DROP TABLE Suppliers

Suppose, there is the following input.

UserId: 

105; DROP TABLE SUPPLIERS

See the following query.

#Query SELECT * FROM Users WHERE UserId = 105; DROP TABLE Suppliers; #SQL Parameters For Protection

We use Parameters for protecting a website from SQL injection.

These parameters are the values that are added to the SQL query at the time of execution.

The SQL engine checks each parameter to ensure that it is correct for its column and not as a part of the SQL to be executed.

#Example txtNam = getRequestString("CustomerName"); txtAdd = getRequestString("Address"); txtCit = getRequestString("City"); txtSQL = "INSERT INTO Customers (CustomerName,Address,City) Values(@0,@1,@2)"; db.Execute(txtSQL,txtNam,txtAdd,txtCit); #How and Why S.I. Attack Is Performed

If we want to make an SQL Injection attack, the attacker must first find the vulnerable user inputs within a web page or web application.

The web page or web application that has the SQL Injection vulnerability uses such user to input directly in the SQL query. The attacker can create an input content.

Such content is often called the malicious payload and it is a key part of the attack. After the attacker sends this content through input, malicious SQL commands are executed in that database.

SQL is the query language that was designed to manage the data stored in the relational databases. You can use it to create, read, modify, and delete data.

Many websites and web apps store all the data in SQL databases. In some cases, you can also use the SQL commands to run the operating system commands. Therefore, the successful SQL Injection attack can have the severe consequences.

  1. Attackers can use the SQL Injections to find the credentials of other users in the database. They can then impersonate these users. The impersonated user may be the database administrator with all the database privileges.
  2. SQL lets you select and output the data from a database. The SQL Injection vulnerability could allow the attacker to gain the complete access to all data in the database server.
  3. SQL also lets you alter data in the database and add new data. For example, in the financial application, the attacker could use the SQL Injection to change balances, void the transactions, or transfer the money to their account.
  4. You can use an SQL to delete the records from the database, even drop tables. Even if the administrator makes the database backups, deletion of the data could affect the application availability until that database is restored. Also, backups may not recover the most recent data.
  5. In many database servers, you can access the OS using the database server. This may be intentional or accidental. In such cases, the attacker could use an SQL Injection as the initial vector and then attack the internal network behind the firewall.
#How to Prevent an S.I.
  1. The only sure way to prevent the S.I. attacks are the input validation and parameterized queries, including the prepared statements.
  2. The application code should never use an input directly.
  3. A developer must sanitize all the inputs, not only web form inputs such as login forms. They must remove the potentially malicious code items such as single quotes.
  4. It is also a better idea to turn off the visibility of database errors on your production sites. Database errors can be used with the SQL Injection to gain information about your database.
  5. If you discover the S.I. vulnerability, for example, the vulnerability may be in an open-source code. In such cases, you can use the web application firewall to sanitize your input temporarily.

Finally, SQL Injection Example | What Is SQL Injection is over.

The post SQL Injection Example | What Is SQL Injection appeared first on AppDividend.

MySQL: Disk Space Exhaustion for Implicit Temporary Tables

I was recently faced with a real issue about completely exhausting the disk space on MySQL. This was a serious issue because of the continuous outages of the service, as the customer had to constantly restart the server and wait for the next outage.

What was happening? In this article, I’m going to explain it and propose solutions.

Implicit temporary tables

MySQL needs to create implicit temporary tables for solving some kinds of queries. The queries that require a sorting stage most of the time need to rely on a temporary table. For example, when you use GROUP BY, ORDER BY or DISTINCT.  Such queries are executed in two stages: the first is to gather the data and put them into a temporary table, the second is to execute the sorting on the temporary table.

A temporary table is also needed in case of some UNION statements evaluation, for VIEW that cannot use merge, for derived tables when using subqueries, for multiple-table UPDATE, and some other cases.

If the temporary table is small it can be created into the memory, otherwise, it’s created on the disk. Needless to say that an in-memory temporary table is faster. MySQL creates an in-memory table, and if it becomes too large it is converted to an on-disk table. The maximum size for in-memory temporary tables is defined by the tmp_table_size or max_heap_table_size value, whichever is smaller. The default size in MySQL 5.7 is 16MB. If you run queries on a large amount of data, or if you have not optimized queries, you can increase the variables. When setting the threshold, take into consideration the available RAM and the number of concurrent connections you have during your peaks. You cannot indefinitely increase the variables, as at some point you’ll need to let MySQL use on-disk temporary tables.

Note: the temporary table is created on-disk if the tables involved have TEXT or BLOB columns, even if the size is less than the configured threshold.

Temporary tables storage engine

Until MySQL 5.6, all the on-disk temporary tables are created as MyISAM. The temporary table is created in-memory or on-disk, depending on the configuration, and it’s dropped immediately at the end of the query. From MySQL 5.7, they are created as InnoDB by default. Then you can rely on the advanced features.

The new default is the best option for the overall performance and should be used in the majority of the cases.

A new configuration variable is available to set the storage engine for the temporary tables:  internal_tmp_disk_storage_engine. The variable can be set to  innodb (default if not set) or myisam.

The potential problem with InnoDB temporary tables

Although using InnoDB is the best for performance, a new potential issue could arise. In some particular cases, you can have disk exhaustion and server outage.

As any other InnoDB table in the database, the temporary tables have their own tablespace file. The new file is in the data directory together with the general tablespace, with the name ibtmp1. It stores all the tmp tables. A tablespace file cannot be shrunk, and it grows constantly as long as you don’t run a manual OPTIMIZE TABLE. The ibtmp1 makes no difference, as you cannot use OPTIMIZE. The only way to shrink ibtmp1 size to zero is to restart the server.

Fortunately, even if the file cannot shrink, after the execution of a query the temporary table is automatically dropped and the space in the tablespace can be reused for another incoming query.

Let’s think now about the following case:

  • you have non-optimized queries that require the creation of very large on-disk tmp tables
  • you have optimized queries, but they are creating very large on-disk tmp tables because you are doing in purpose computation on a very large dataset (statistics, analytics)
  • you have a lot of concurrent connections running the same queries with tmp table creation
  • you don’t have a lot of free space in your volume

In such a situation it’s easy to understand that the file ibtmp1 size can increase considerably and the file can easily exhaust the free space. This was happening several times a day, and the server had to be restarted in order to completely shrink the ibtmp1 tablespace.

It’s not mandatory that the concurrent queries are launched exactly at the same time. Since a query with a large temporary table will take several seconds or minutes to execute, it is sufficient to have queries launched at different times while the preceding ones are still running. Also, you have to consider that any connection creates its own temporary table, so the same exact query will create another exact copy of the same temporary table into the tablespace. Exhausting the disk space is very easy with non-shrinkable files!

So, what to do to avoid disk exhaustion and outages?

The trivial solution: use a larger disk

This is really trivial and can solve the problem, but it is not the optimal solution. In fact, it’s not so easy to figure out what your new disk size should be. You can guess by increasing the disk size step by step, which is quite easy to do if your environment is in the cloud or you have virtual appliances on a very large platform. But it’s not easy to do in on-premise environments.

But with this solution, you can risk having unneeded expenses, so keep that in mind.

You can also move the ibtmp1 file on a dedicated large disk, by setting the following configuration variable:

[mysqld] innodb_temp_data_file_path = ../../tmp/ibtmp1:12M:autoextend

A MySQL restart is required.

Please note that the path has to be specified as relative to the data directory.

Set an upper limit for ibtmp1 size

For example:

[mysqld] innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:10G

In this case, the file cannot grow more than 10GB. You can easily eliminate the outages, but this is a dangerous solution. When the data file reaches the maximum size, queries fail with an error indicating that the table is full. This is probably bad for your applications.

Step back to MyISAM for on-disk temporary tables

This solution seems to be counterintuitive but it could be the best way to avoid the outages in a matter of seconds and is guaranteed to use all needed temporary tables.

You can set the following variable into my.cnf:

internal_tmp_disk_storage_engine = MYISAM

Since the variable is dynamic you can set it also at runtime:

SET GLOBAL internal_tmp_disk_storage_engine = MYISAM;

Stepping back to MyISAM, you will considerably decrease the possibility of completely filling your disk space. In fact, the temporary tables will be created into different files and immediately dropped at the end of the query. No more issues about a forever increasing file.

And while there is always the possibility to see the same issue, just in case you can run the queries at the exact same time or really very close. In my real-world case, this was the solution to avoid all the outages.

Optimize your queries

This is the most important thing to do. After stepping back the storage engine to MyISAM to mitigate the outage occurrences, you have to absolutely take the time to analyze the queries.

The goal is to decrease the size of the on-disk temporary tables. It’s not the aim of this article to explain how to investigate the queries, but you can rely on the slow log, on a tool like pt-query-digest and on EXPLAIN.

Some tips:

  • create missing indexes on the tables
  • add more filters in the queries to gather less data, if you don’t really need it
  • rewrite queries to optimize the execution plan
  • if you have very large queries on purpose, you can use a queue manager in your applications to serialize their executions or to decrease the concurrency

This will be the longest activity, but hopefully, after all the optimizations, you can return to set the temporary storage engine to InnoDB for better performance.

Conclusion

Sometimes the improvements can have unexpected side effects. The InnoDB storage engine for on-disk temporary tables is a good improvement, but in some particular cases, for example, if you have non-optimized queries and little free space, you can have outages because of “disk full” error. Stepping back the tmp storage engine to MyISAM is the fastest way to avoid outages, but the optimization of the queries is the more important thing to do as soon as possible in order to return to InnoDB. And yes, even a larger or dedicated disk may help. It’s a trivial suggestion, I know, but it can definitely help a lot.

By the way, there’s a feature request about the issue: https://bugs.mysql.com/bug.php?id=82556

Further readings:
https://www.percona.com/blog/2007/01/19/tmp_table_size-and-max_heap_table_size/
https://www.percona.com/blog/2017/12/04/internal-temporary-tables-mysql-5-7/
http://mysqlserverteam.com/mysql-5-7-innodb-intrinsic-tables/
https://dev.mysql.com/doc/refman/5.7/en/internal-temporary-tables.html

 

Assessing MySQL Performance Amongst AWS Options – Part One

With such a wide range of options available for running MySQL based servers in Amazon cloud environments, how do you choose? There’s no doubt it’s a challenge. In this two-part series of blog posts, we’ll try to draw a fair and informative comparison based on well-established benchmark scenarios – at scale.

In part one we will discuss the performance of the current Amazon RDS services – Amazon Aurora and Amazon RDS for MySQL – and compare it with the performance of Percona Server with InnoDB and RocksDB engines. And in part two we will go over costs and efficiencies to look for. All this information is necessarily number-heavy, but hopefully, you’ll enjoy the experiments!

Evaluation scenario The benchmark scripts

For these evaluations, we use the sysbench/tpcc LUA test with a scale factor of 500 warehouses/10 tables. This is the equivalent of 5000 warehouses of the official TPC-C benchmark.

Amazon MySQL Environments

These are the AWS MySQL environments under analysis:

  • Amazon RDS Aurora
  • Amazon RDS for MySQL with the InnoDB storage engine
  • Percona Server for MySQL with the InnoDB storage engine on Amazon EC2
  • Percona Server for MySQL with the RocksDB storage engine on Amazon EC2
Technical Setup – Server

These general notes apply across the board:

  • AWS region us-east-1(N.Virginia) was used for all tests
  • Server and client instances were spawned in the same availability zone
  • All data for tests were prepared in advance, stored as snapshots, and restored before the test
  • Encryption was not used

And we believe that these configuration notes allow for a fair comparison of the different technologies:

  • AWS EBS optimization was enabled for EC2 instances
  • For RDS/Amazon Aurora only a primary DB instance was created and used
  • In the case of RDS/MySQL, a single AZ deployment was used for RDS/MySQL
  • EC2/Percona Server for MySQL tests were run with binary log enabled

Finally, here are the individual server configurations per environment:

Server test #1: Amazon RDS Aurora
  • Database server: Aurora MySQL 5.7
  • DB instances: r5.large, r5.xlarge, r5.2xlarge, r5.4xlarge
  • volume: used ~450GB(>15000 IOPS)
Server test #2: Amazon RDS for MySQL with InnoDB Storage Engine
  • Database server: MySQL Server 5.7.25
  • RDS instances: db.m5.large, db.m5.xlarge, db.m5.2xlarge, db.m5.4xlarge
  • volumes(allocated space):
    • gp2: 5400GB(~16000 IOPs)
    • io1: 700GB(15000 IOPs)
Server test #3: Percona Server for MySQL with InnoDB Storage Engine
  • Database server: Percona Server 5.7.25
  • EC2 instances: m5.large, m5.xlarge, m5.2xlarge, m5.4xlarge
  • volumes(allocated space):
    • gp2: 5400GB(~16000 IOPs)
    • io1: 700GB(15000 IOPs)
Server test #4: Percona Server for MySQL with RocksDB using LZ4 compression
  • Database server: Percona Server 5.7.25
  • EC2 instances: m5.large, m5.xlarge, m5.2xlarge, m5.4xlarge
  • volumes(allocated space):
    • gp2: 5400GB(~16000 IOPs)
    • io1: 350GB(15000 IOPs)
Technical Setup – Client

Common to all tests, we used an EC2 instance: m5.xlarge

So, now that we have established the setup, let’s take a look at what we found.

Disk space consumption for sysbench/tpcc 5000 warehouses

Since disk space costs money, you’d want to see how the raw data occupied space in each environment. There’s a clear “winner” there: Percona Server for MySQL with RocksDB storage engine. Of course, there are lots more factors to consider. 

The performance tests

For the performance tests, we used this set up as common to all:

  • number of threads: 128
  • duration of each test run: 30 mins

Then, per server, we worked through individual tests as follows:

Server tests Server test #1: RDS/Amazon Aurora
  • create cluster
  • restore cluster instance from db snapshot
Server test #2: RDS/MySQL
  • restore instance from db snapshot
Server tests #3 and #4: Percona Server for MySQL on EC2
  • create EC2 instance as server
  • create volume from snapshot and attach to server instance
Client test
  • create EC2 instance as client
  • start test
Throughput

First of all, then, let’s review throughput results for every tested database. In case things aren’t complicated enough, at this stage we’re also taking into account whether io1 or gp2 volume type has an effect on the outcomes. AWS offers this comparison of io1 and gp2.

Server test #1: RDS/Amazon Aurora

Aurora scales almost linearly from large to 4xlarge, notably outperforms Percona Server/InnoDB and RDS/MySQL but still behind RocksDB more than 2x times

Server test #2: RDS/MySQL
  • for all types of instances, we saw almost no difference in results between io1 and gp2 volumes
  • in these tests, RDS/MySQL shows the lowest throughput from all databases
Server test #3: Percona Server for MySQL with InnoDB 
  • up to 4xlarge instance results for gp2 volume slightly better(~10%) than for io1. On 4xlarge instance where the size of instance allows having a large enough innodb buffer pool that from one side helps to reduce the amount of reads as a notable amount of data will be already cached and from other side that helps to improve writing/flushing. As and in case of RocksDB it looks like writes operations are much more efficient(better latency) on io1 volumes and that helps to get better results with io1 volumes.
  • overall results for Percona Server/InnoDB is on 10-15% better than for RDS/MySQL but notably lower than for RDS/Aurora (in 2x-2.5x times) and RocksDB(5x times)
Server test #4: Percona Server for MySQL with RocksDB
  • large/xlarge – results are on par for gp2 and io1 volumes, starting from 2xlarge instance results with io1 volume continue scales linearly while gp2 stays almost on the same level
  • RockDB shows best results across all types of instances and outperforms Percona Server/InnoDB and RDS/MySQL in 5x times and RDS/Aurora 2-2.5 times

In the next post in this series, I will take a look at the total cost of one test run for each database, as well as review which databases perform most efficiently by analysis transaction cost ratio.

Upcoming Webinar 7/18: Learn how to connect a MySQL database with Java

Please join Percona’s Service Delivery Manager Rodrigo Trindade as he presents “Troubleshooting Java Connections to MySQL” on Thursday, July 18th, 2019 at 10:00 AM PDT (UTC-7).

Register Now

This talk will explain the steps needed to make a connection from Java to MySQL work and highlight potential issues you might encounter. It will cover all components, installation, and configuration.

If you can’t attend sign up anyways we’ll send you the slides and recording afterward.

Speaker: Rodrigo Trindade
Service Delivery Manager Rodrigo has a Master in Computer Science degree by the Rio Grande do Sul Federal University (Brazil) as well as over 10 years of experience as a CS Professor. Started as Software Developer then moved to Support Engineering working for Netscape, Sun Microsystems, and Oracle. Joined Percona in 2018 as a Service Delivery Manager. Solaris, Java, and Weblogic certified.

How to get current month in Codeigniter

In this example we will learn how to get current month data in codeigniter we can get current month data using codeigniter query builder we will use MONTH and YEAR mysql function for getting current month dataI will share with you simple example example of getting current month data in cod

Percona Kubernetes Operator for Percona XtraDB Cluster 1.1.0 Now Available

We are glad to announce the 1.1.0 release of the  Percona Kubernetes Operator for Percona XtraDB Cluster.

The Percona Kubernetes Operator for Percona XtraDB Cluster automates the lifecycle and provides a consistent Percona XtraDB Cluster instance. The Operator can be used to create a Percona XtraDB Cluster, or scale an existing Cluster, and contains the necessary Kubernetes settings.

The Operator simplifies the deployment and management of the Percona XtraDB Cluster in Kubernetes-based environments. It extends the Kubernetes API with a new custom resource for deploying, configuring and managing the application through the whole life cycle.

The Operator source code is available in our Github repository. All of Percona’s software is open-source and free.

New features and improvements
  • Now the Percona Kubernetes Operator allows upgrading Percona XtraDB Cluster to newer versions, either in semi-automatic or in manual mode.
  • Also, two modes are implemented for updating the Percona XtraDB Cluster my.cnf configuration file: in automatic configuration update mode Percona XtraDB Cluster Pods are immediately re-created to populate changed options from the Operator YAML file, while in manual mode changes are held until Percona XtraDB Cluster Pods are re-created manually.
  • A separate service account is now used by the Operator’s containers which need special privileges, and all other Pods run on default service account with limited permissions.
  • User secrets are now generated automatically if don’t exist: this feature especially helps reduce work in repeated development environment testing and reduces the chance of accidentally pushing predefined development passwords to production environments.
  • The Operator is now able to generate TLS certificates itself which removes the need in manual certificate generation.
  • The list of officially supported platforms now includes Minikube, which provides an easy way to test the Operator locally on your own machine before deploying it on a cloud.
  • Also, Google Kubernetes Engine 1.14 and OpenShift Platform 4.1 are now supported.

Percona XtraDB Cluster is an open source, cost-effective and robust clustering solution for businesses. It integrates Percona Server for MySQL with the Galera replication library to produce a highly-available and scalable MySQL® cluster complete with synchronous multi-master replication, zero data loss and automatic node provisioning using Percona XtraBackup.

Help us improve our software quality by reporting any bugs you encounter using our bug tracking system.

Slick Command-Line Tricks for a Tungsten MySQL / MariaDB Database Cluster

Overview The Skinny

Tungsten Clustering provides high availability, disaster recovery, and a host of other benefits for MySQL / MariaDB / Percona Server databases. In this blog post we will explore some of the shell aliases I use every day to administer various Tungsten Clusters.

Shell Aliases: A Quick Review Quick and Easy

A shell alias is simply a way to create a shortcut for frequently-used command sequences.

For example, I like to shorten the command clear to cls, i.e.

shell> alias cls=clear shell> cls

If you create an alias on the fly it will be lost when the shell exits.

To save aliases so they are available to all shell sessions, update your shell’s profile or rc script.

For example, add the below line to the bottom of .bashrc, save and exit:

shell> vi ~/.bashrc alias cls=clear shell> source ~/.bashrc shell> cls

Open a new terminal window and confirm that your new alias works.

Shell Aliases: My Favorites Speed and Efficiency

The aliases below are grouped together based on the functionality:

alias se='source /opt/continuent/share/env.sh' alias vini='vi /etc/tungsten/tungsten.ini' alias auto='echo set policy automatic | cctrl' alias maint='echo set policy maintenance | cctrl' alias cdl='cd /opt/continuent/service_logs' alias cds='cd /opt/continuent/software' alias cdt='cd /opt/continuent/tungsten' alias ccls='echo ls | cctrl' alias cccls='echo ls | cctrl -service global' alias chb='echo cluster heartbeat | cctrl' alias toff='trepctl offline' alias ton='trepctl online' alias ts='trepctl status' alias tsg='trepctl status | egrep stat\|appl' alias tss='trepctl services' alias tsl='trepctl services | grep serviceName | awk -F: '\''{print $2}'\''' alias tqv='tpm query version' alias tvu='tools/tpm validate-update' alias tup='tools/tpm update -i --replace-release'

You can create your own – the sky is the limit!

Summary The Wrap-Up

In this blog post we explored some of the shell aliases I use every day to administer various Tungsten Clusters.

To learn about Continuent solutions in general, check out https://www.continuent.com/solutions

The Library Please read the docs!

For more information about Tungsten Cluster recovery procedures, please visit https://docs.continuent.com/tungsten-clustering-6.0/operations-recovery-master.html

Tungsten Clustering is the most flexible, performant global database layer available today – use it underlying your SaaS offering as a strong base upon which to grow your worldwide business!

For more information, please visit https://www.continuent.com/solutions

Want to learn more or run a POC? Contact us.

Percona Live Europe 2019 Conference Updates

The Percona Live Open Source Database Conference, Europe 2019 will have a one week extension of the call for papers.  Our new close date will be Monday July 22, 2019.  Any and all topics related to open source database technologies are invited.

This year’s conference takes a new approach, organizing tracks along these key business themes:

  • Performance & Scalability
  • Public, Private, and Hybrid Clouds & Everything In Between
  • Building Large, Scalable, & Secure Database Deployments
  • Hot Topics, New Features, & Trends You Should Know About
  • Monitor, Manage, & Maintain Databases at Scale
  • How to Reduce Costs & Complexity with Open Source Databases
  • Fascinating or important talks that don’t 100% fit in the other tracks

But as always the conference will be rich in expertise about MySQL®, MongoDB®, MariaDB®, PostgreSQL, Kubernetes®, and everything else related to open source databases.

Program Committee Announced

Percona is pleased to announce the conference program committee.  We are grateful for the participation of these senior technical experts from across the open source database world:

  • Laine Campbell, Facebook
  • Colin Charles, Consultant
  • Frédéric Descamps, Oracle
  • Antonios Giannopoulos, Object Rocket
  • Giuseppe Maxia, Software Explorer
  • Valerie Parham-Thompson, Pythian
  • Nicolai Plum, Booking.com

Percona’s committee representatives are:

  • Jobin Augustine, Senior Support Engineer
  • Vinicius Grippa, Senior Support Engineer
  • Sveta Smirnova, Principal Support Escalation Specialist
  • Alkin Tezuysal, Senior Technical Manager

The conference program will be announced on a rolling basis in early August.

Registration is OPEN!

Finally,  registration is now open!! Buying your ticket early will get you the best value by far at just €525 for all three days until August 9 . We are hosting the conference at a stunning new airport venue, the Amsterdam Hilton Schiphol, allowing easy access for international travelers but just 20 minutes by train from the historic heart of Amsterdam.  This new venue provides outstanding modern facilities with room for the conference to continue to grow. Read more about the event or simply…

Register here now

 

MySQL Master Replication Crash Safety Part #5a: making things faster without reducing durability - using better hardware

This is a follow-up post in the MySQL Master Replication Crash Safety series.  In the previous posts, we explored the consequences of reducing durability on masters (different data inconsistencies after an OS crash depending on replication type) and the performance boost associated with this configuration (benchmark results done on Google Cloud Platform / GCP).  The consequences are summarised in

MySQL Master Replication Crash Safety Part #5: faster without reducing durability (under the hood)

This post is a sister post to MySQL Master Replication Crash Safety Part #5: making things faster without reducing durability.  There is no introduction or conclusion to this post, only landing sections: reading this post without its context is not not recommended. You should start with the main post and come back here for more details. And this Part #5 of the series has many sub-parts.  So far,

Monitoring MySQL and MariaDB Servers

In week 5 of our Benefits of SQL Diagnostic Manager for MySQL (formerly Monyog) blog series, we detail MySQL and MariaDB monitoring features with SQL Diagnostic Manager for MySQL, including real-time monitoring and monitoring MySQL error logs. If you missed it, you can read our previous post on understanding database performance trends.

Fast Startup Time to Start Monitoring

Database administrators can start monitoring MySQL and MariaDB servers in less than a single minute. The unique architecture and low-footprint of SQL Diagnostic Manager for MySQL enable database administrators to install and configure all of the components that are required for monitoring MySQL and MariaDB servers very quickly.

The fast startup time is in sharp contrast with other monitoring and advisory tools for MySQL and MariaDB. Before database administrators can even start monitoring MySQL and MariaDB servers, such tools require installing agents, web servers, multiple language runtimes, and more.

Real-Time Monitoring

The Real-Time feature enables database administrations to know what is happening to MySQL and MariaDB servers without delay. With a single click of a mouse button, obtain critical data (such as the top 200 SQL queries, slow SQL queries, locked and locking SQL queries, along with the most active users, hosts, databases, and tables). There is no need to enable slow query logs and general query logs. SQL Diagnostic Manager for MySQL records the data in sessions, and saves the sessions for later analysis.

Monitor Error Logs

Monitoring MySQL error logs is critical for any database administrator. SQL Diagnostic Manager for MySQL is the first monitoring tool for MySQL and MariaDB to monitor MySQL error logs. It sends notifications over simple mail transfer protocol (SMTP) and simple network management protocol (SNMP) for error log events that require attention.

Monitor Deadlocks

SQL Diagnostic Manager for MySQL monitors MySQL and MariaDB servers for deadlocks and optionally sends alerts immediately in the form of emails and simple network management protocol (SNMP) traps. In addition to detecting deadlocks, it also provides data on the latest deadlock found.

Create Custom SQL Objects

Instead of monitoring MySQL and MariaDB servers by writing SQL queries, create Custom SQL Objects. Custom SQL Objects return an array of MySQL rows. SQL Diagnostic Manager for MySQL exposes these rows as a JavaScript array, monitors it, and references it like any SQL Diagnostic Manager for MySQL object.

Read more in the full solution brief.

Find and fix MySQL performance problems on-premises and in the cloud with SQL Diagnostic Manager for MySQL.

The post Monitoring MySQL and MariaDB Servers appeared first on Monyog Blog.

MySQL Insert Statement to Add Rows

This tutorial explains the MySQL INSERT command to insert single and multiple rows in a table. Here, you’ll find some unique ways with different variations for adding records with fully working examples. 1. INSERT Statement Syntax 2. INSERT Single Row 3. INSERT Default Values 3. INSERT Date Columns 4. INSERT Multiple Rows Let’s now read and understand each of the section one by one. MySQL INSERT statement As stated initially, the INSERT command is a built-in MySQL statement which inserts the specified records into a given table. So, let’s first check the details and see how to use the INSERT

The post MySQL Insert Statement to Add Rows appeared first on Learn Programming and Software Testing.

Dynimize Quickstart Tutorial

Duration: 10 min

Level: Beginner

 

This tutorial will show you how to install Dynimize. We'll then use it to optimize a live MySQL process that's been running the Sysbench OLTP benchmark, obtaining a 46% increase in performance by applying Dynimize.

 

Part 1:  Quickstart

 

Here's a quick overview of how to use Dynimize.

To install Dynimize, run the following commands.

wget https://dynimize.com/install -O install
wget https://dynimizecloud.com/install.sha256 -O install.sha256
sha256sum -c install.sha256; if [ $? -eq 0 ]; then sudo bash ./install -d; fi

 

Use your access token to start a subscription license for your host.

$ sudo dyni -license=start -token=<your-access-token>

 

To start Dynimize, run

$ sudo dyni -start

 

To check the status of Dynimize and any optimization targets, run:

$ sudo dyni -status

 

Once a target process has been fully optimized, the above command will report:

Dynimize is running
mysqld, pid: 21091, dynimized

 

It could take anywhere from 30 to 300s of CPU intensive activity from the target process before Dynimize has completed profiling and optimizing that process.

Deciding which applications to target can be done by editing the list of supported target exes in /etc/dyni.conf under the [exeList] section.

 

To stop Dynimize, run:

$ sudo dyni -stop

 

To stop the subscription license for this host, run:

$sudo dyni -license=stop -token=<your-access-token>

 

Part 2:   Benchmarking with Dynimize

 

In the remainder of this tutorial we'll demonstrate how to benchmark MySQL using the Sysbench OLTP benchmark, and then improve on the benchmark results by installing and running Dynimize alongside MySQL. We try to keep things as simple as possible, and so by no means is this meant to demonstrate the most realistic or optimal way to benchmark MySQL with Sysbench, or accurately measure the improvements Dynimize can achieve. This tutorial can be completed with any version of MySQL, MariaDB, or Percona Server, and it is assumed that one of these is already installed.

Note that the benchmark is run using a CPU bound setup, with --oltp-read-only=on so that the IO subsystem is not involved, and making sure the workload fits into RAM. This example was run on a cloud based VPS from OVH, with 2 vCPUs (2.4 Ghz Haswell), with 8 GB ram, and a 40 GB SSD. You can recreate this exact VM on OVH's public cloud by selecting the VPS-SSD-3 server option in their Beauharnois data center. We used Ubuntu 16.04.2 LTS with MySQL 5.7.18, and Sysbench 0.4.12. Note that Sysbench 0.4 is used in this tutorial because it can easily be installed from most Linux repositories. Note that Dynimize speedups can be more pronounced when running on KVM hypervisors, as is the case here.

 

1. Install Sysbench

apt-get based install:

$ sudo apt-get install sysbench

 

OR

yum based install:

$ sudo yum install sysbench

 

2. Run sysbench

In the following commands, make sure to replace user and password with something appropriate. Here we use the database name testDB.

Lets start off with a fresh MySQL process:

$ sudo service mysql restart

 

Load a table for the Sysbench OLTP workload. Make sure to replace user and password as appropriate:

$ mysql -u user --password=password -e "DROP DATABASE testDB;"
$ mysql -u user --password=password -e "CREATE DATABASE testDB;"
$ sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=testDB --mysql-user=user --mysql-password='password' prepare

 

Perform a warm-up run:

$ sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=testDB --mysql-user=user --mysql-password='password' --max-time=120 --oltp-read-only=on --max-requests=0 --num-threads=8 run

 

Perform a measurement run (command is the same as warm-up):

$ sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=testDB --mysql-user=user --mysql-password='password' --max-time=120 --oltp-read-only=on --max-requests=0 --num-threads=8 run

 

These runs will produce results in the following format:

OLTP test statistics:
queries performed:
read: 1203062
write: 0
other: 171866
total: 1374928
transactions: 85933 (1432.12 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1203062 (20049.73 per sec.)
other operations: 171866 (2864.25 per sec.)

Test execution summary:
total time: 60.0039s
total number of events: 85933
total time taken by event execution: 479.6911
per-request statistics:
min: 1.19ms
avg: 5.58ms
max: 41.07ms
approx. 95 percentile: 7.85ms

Threads fairness:
events (avg/stddev): 10741.6250/233.97
execution time (avg/stddev): 59.9614/0.00

 

In this case we can see transactions per second of 1432.12 as highlighted above.

 

3. Repeat with Dynimize

Let's repeat that with Dynimize running. First install Dynimize and start a host license if you haven't already.

If you haven't already done so, install Dynimize:

wget https://dynimize.com/install -O install
wget https://dynimizecloud.com/install.sha256 -O install.sha256
sha256sum -c install.sha256; if [ $? -eq 0 ]; then sudo bash ./install -d; fi

 

If you haven't already started a subscription license for your host then use your access token to start one :

$sudo dyni -license=start -token=<your-access-token>

 

Now start Dynimize:

$ sudo dyni -start

 

Perform another warmup run to get the mysqld process in the "dynimized" state:

$ sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=testDB --mysql-user=user --mysql-password='password' --max-time=120 --oltp-read-only=on --max-requests=0 --num-threads=8 run

 

After the warmup make sure mysqld is dynimized:

$ sudo dyni -status
Dynimize is running
mysqld, pid: 25072, dynimized

 

If the mysqld process is not yet dynimized then repeatedly run the warm-up followed by sudo dyni -status until mysqld shows up as dynimized.

 

Now that mysqld is dynimized, let's measure its performance again:

$ sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=testDB --mysql-user=user --mysql-password='password' --max-time=120 --oltp-read-only=on --max-requests=0 --num-threads=8 run

 

Which will output the following:

OLTP test statistics:
queries performed:
read: 1759352
write: 0
other: 251336
total: 2010688
transactions: 125668 (2094.36 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1759352 (29321.06 per sec.)
other operations: 251336 (4188.72 per sec.)

Test execution summary:
total time: 60.0030s
total number of events: 125668
total time taken by event execution: 479.5530
per-request statistics:
min: 0.77ms
avg: 3.82ms
max: 492.75ms
approx. 95 percentile: 5.52ms

Threads fairness:
events (avg/stddev): 15708.5000/166.91
execution time (avg/stddev): 59.9441/0.00

 

Here we see transactions per second of 2094.36, which is a 46% speedup from the baseline of 1432.12 tps.

That concludes this tutorial. Happy Dynimizing!

Pages