tel. 883 59 39 89 l mail: kontakt@najlepszerolety.pl l CZYNNE PN-PT 9-17

NajlepszeRolety.PL - rolety - żaluzje - moskitiery

TEL. 883 59 39 89 / UL. MONIUSZKI 54 - MYSŁOWICE

Wieści RSS

Planet MySQL

Planet MySQL - https://planet.mysql.com
  • Descending index in MySQL 8.0
    MySQL 8.0 has come with a list of new features for DBA’s ,we will discuss the new feature in MySQL 8.0 which supports Descending index.Prior to MySQL 8.0 (i.e MySQL 5.6 and 5.7) creating desc index syntax was supported but desc keyword was ignored, Now in MySQL 8.0 release descending index is extended are supported. What is index? Indexes play an important role in performance optimization  and they are used frequently to speed up access to particular data and reduce disk I/O operations . To understand index easily you can imagine a book,every book has an index with content referring to a page number.If you want to search something in a book you first refer to the index and get the page number and then get the information in the page,like this the indexes in MySQL will tell you the row with matching data. InnoDB uses a B+Tree structure internally for  indexes. A B+Tree is particularly efficient when data doesn’t fit in memory and must be read from the disk, as it ensures that a fixed maximum number of reads would be required to access any data requested, based only on the depth of the tree, So before working with indexes, it is important to understand how indexes work behind the scene and what is the data structure that is used to store these indexes. B+tree Index: Indexes are stored on disk in the form of a data structure known as B+tree. B+tree is in many ways similar to a binary search tree. B+tree follows on the same structure as of a binary search tree, in that each key in a node has all key values less than the key as its left children, and all key values more than the key as its right children. But there are some very important differences, B+tree can have more than 1 keys in a node, in fact thousands of keys is seen typically stored in a node and hence, the branching factor of a B+tree is very large and that allows the B+trees to be a lot shallower as compared to their binary search tree counterparts. B+trees have all the key values in their leaf nodes. All the leaf nodes of a B+tree are at the same height, which implies that every index lookup will take same number of B+tree lookups to find a value (equisearch) Within a B+tree all leaf nodes are linked together in a linked-listed, left to right, and since the values at the leaf nodes are sorted, so range lookups are very efficient.   B+ tree example Image courtesy :wikipedia What is Descending indexes? A descending index is an index in which the InnoDB stores the entries in descending order and and the optimizer will choose this index  when descending order is requested in the query ,which is more efficient for queries with ORDER BY clauses and it need not use a filesort operation. Descending indexes are supported only for the InnoDB storage engine. By default the indexes are stored in the Ascending order in a B+Tree. How to add a descending index on a table ? The keyword DESC is used along with the common index creation syntax ( Alter/Create ) Alter table table_name add desc_idx(col1_name desc,col2_name asc); Create index desc_idx on table_name(col1_name desc,col2_name asc); The index (col1_name desc,col2_name asc) satisfies two conditions: Order by col1_name desc,col2_name asc : Forward Scan Order by col1_name asc,col2_name desc : Backward Scan Now let us see the query performance based on handlers count for the query with and without indexes Here i am using employees table for testing purpose mysql> select @@version; +-----------+ | @@version | +-----------+ | 8.0.11    | +-----------+ mysql> show create table employees\G *************************** 1. row ***************************       Table: employees Create Table: CREATE TABLE `employees` (  `emp_no` int(11) NOT NULL,  `birth_date` date NOT NULL,  `first_name` varchar(14) NOT NULL,  `last_name` varchar(16) NOT NULL,  `gender` enum('M','F') NOT NULL,  `hire_date` date NOT NULL,  PRIMARY KEY (`emp_no`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> select count(*) from employees; +----------+ | count(*) | +----------+ |  300025 | +----------+ Now let us consider an example of the below query with ordering on descending and ascending conditions. No index were created initially. mysql> explain select * from employees order by hire_date desc,first_name asc limit 10\G; *************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: employees   partitions: NULL         type: ALL possible_keys: NULL         key: NULL      key_len: NULL          ref: NULL         rows: 299157     filtered: 100.00        Extra: Using filesort 1 row in set, 1 warning (0.01 sec)   mysql> select * from employees order by hire_date desc,first_name asc limit 10; +--------+------------+------------+------------+--------+------------+ | emp_no | birth_date | first_name | last_name  | gender | hire_date | +--------+------------+------------+------------+--------+------------+ | 463807 | 1964-06-12 | Bikash     | Covnot | M | 2000-01-28 | | 428377 | 1957-05-09 | Yucai      | Gerlach | M | 2000-01-23 | | 499553 | 1954-05-06 | Hideyuki   | Delgrande | F | 2000-01-22 | | 222965 | 1959-08-07 | Volkmar    | Perko | F | 2000-01-13 | |  47291 | 1960-09-09 | Ulf        | Flexer | M | 2000-01-12 | | 422990 | 1953-04-09 | Jaana      | Verspoor | F | 2000-01-11 | | 227544 | 1954-11-17 | Shahab     | Demeyer | M | 2000-01-08 | | 205048 | 1960-09-12 | Ennio      | Alblas | F | 2000-01-06 | | 226633 | 1958-06-10 | Xuejun     | Benzmuller | F | 2000-01-04 | | 424445 | 1953-04-27 | Jeong      | Boreale | M | 2000-01-03 | +--------+------------+------------+------------+--------+------------+ 10 rows in set (1.30 sec) The query took 1.30 secs for limit of 10 records,so from above handler Handler_read_rnd_next count we can see ,the query is doing full table scan which is equal to total number of records in table. mysql> show status like 'hand_%'; +----------------------------+--------+ | Variable_name              | Value | +----------------------------+--------+ | Handler_commit             |1 | | Handler_read_first         |1 | | Handler_read_key           |1 | | Handler_read_rnd_next      |300025 | Let us create index according to the order in the query for the columns hire_date and first_name. mysql> create index desc_idx on employees(hire_date desc,first_name); Query OK, 0 rows affected (2.15 sec) Records: 0  Duplicates: 0  Warnings: 0 mysql> explain select * from employees order by hire_date desc,first_name asc limit 10\G *************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: employees   partitions: NULL         type: index possible_keys: NULL          key: desc_idx      key_len: 61          ref: NULL         rows: 10     filtered: 100.00        Extra: Using index 1 row in set, 1 warning (0.00 sec) Now the query executes much faster. mysql> select * from employees order by hire_date desc,first_name asc limit 10; +--------+------------+------------+------------+--------+------------+ | emp_no | birth_date | first_name | last_name  | gender | hire_date | +--------+------------+------------+------------+--------+------------+ | 463807 | 1964-06-12 | Bikash     | Covnot | M | 2000-01-28 | | 428377 | 1957-05-09 | Yucai      | Gerlach | M | 2000-01-23 | | 499553 | 1954-05-06 | Hideyuki   | Delgrande | F | 2000-01-22 | | 222965 | 1959-08-07 | Volkmar    | Perko | F | 2000-01-13 | |  47291 | 1960-09-09 | Ulf        | Flexer | M | 2000-01-12 | | 422990 | 1953-04-09 | Jaana      | Verspoor | F | 2000-01-11 | | 227544 | 1954-11-17 | Shahab     | Demeyer | M | 2000-01-08 | | 205048 | 1960-09-12 | Ennio      | Alblas | F | 2000-01-06 | | 226633 | 1958-06-10 | Xuejun     | Benzmuller | F | 2000-01-04 | | 424445 | 1953-04-27 | Jeong      | Boreale | M | 2000-01-03 | +--------+------------+------------+------------+--------+------------+ 10 rows in set (0.00 sec) We can see from Handler_read_next count,the query is using the index to read the next row in key order and here Handler_read_first suggests that the number of times the first entry in an index was read. mysql> show status like 'hand_%'; +----------------------------+-------+ | Variable_name              | Value | +----------------------------+-------+ | Handler_commit             |1 | | Handler_read_first      |1 | | Handler_read_next       |9 | Lets the run the query by changing order to hire_date asc and first_name desc. mysql> explain select * from employees order by hire_date asc,first_name desc limit 10\G *************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: employees   partitions: NULL         type: index possible_keys: NULL          key: desc_idx      key_len: 61          ref: NULL         rows: 10     filtered: 100.00        Extra: Backward index scan 1 row in set, 1 warning (0.00 sec) mysql>  select * from employees order by hire_date asc,first_name desc limit 10; +--------+------------+-------------+--------------+--------+------------+ | emp_no | birth_date | first_name  | last_name | gender | hire_date | +--------+------------+-------------+--------------+--------+------------+ | 111692 | 1954-10-05 | Tonny       | Butterworth | F | 1985-01-01 | | 110183 | 1953-06-24 | Shirish     | Ossenbruggen | F | 1985-01-01 | | 111035 | 1962-02-24 | Przemyslawa | Kaelbling    | M | 1985-01-01 | | 110725 | 1961-03-14 | Peternela   | Onuegbe | F | 1985-01-01 | | 110022 | 1956-09-12 | Margareta   | Markovitch | M | 1985-01-01 | | 110303 | 1956-06-08 | Krassimir   | Wegerle | F | 1985-01-01 | | 110085 | 1959-10-28 | Ebru        | Alpin | M | 1985-01-01 | | 110511 | 1957-07-08 | DeForest    | Hagimont | M | 1985-01-01 | | 111400 | 1959-11-09 | Arie        | Staelin | M | 1985-01-01 | | 110114 | 1957-03-28 | Isamu       | Legleitner | F | 1985-01-14 | +--------+------------+-------------+--------------+--------+------------+ 10 rows in set (0.00 sec) mysql> show status like 'hand_%'; +----------------------------+-------+ | Variable_name              | Value | +----------------------------+-------+ | Handler_commit             | 1 | | Handler_read_last       | 1 | | Handler_read_prev       | 9 | As we can see from handler Handler_read_last and Handler_read_prev the index scan is backward way. Conclusion: In MySQL 8.0 it is a great new feature to avoid filesorting for the queries with Order by desc and asc clause. More can be read on this official work log WL#1074 Image Courtesy : Photo by Verne Ho on Unsplash

  • Protected: MySQL 8.0 InnoDB Cluster – Three-server installation of MySQL InnoDB Cluster; MySQL Shell, Router and Group Replication
    This post is password protected. You must visit the website and enter the password to continue reading. Advertisements

  • How to Install Nextcloud with Nginx on Ubuntu 18.04 LTS
    In this tutorial, we will show you how to install and configure the latest Nextcloud 13.0.2 release on an Ubuntu 18.04 server. We will run Nextcloud with a Nginx web server and PHP7.1-FPM and use MySQL server as the database system. Nextcloud is a free (Open Source) Dropbox-like software, a fork of the ownCloud project.

  • Mastering Continuent Clustering series: Tuning for High-Latency Links
    What if I want the cluster to be less sensitive to network, especially WAN latency? Continuent Clustering supports having clusters at multiple sites with active-active replication meshing them together. This is extraordinarily powerful, yet at times high network latency can make it harder for messaging between the sites to arrive in a timely manner. This is evidenced by seeing the following in the Manager log files named tmsvc.log: 2018/07/08 16:51:05 | db3 | INFO [Rule_0604$u58$_DETECT_UNREACHABLE_REMOTE_SERVICE1555959201] - CONSEQUENCE: [Sun Jul 08 16:51:04 UTC 2018] CLUSTER global/omega(state=UNREACHABLE) ... 2018/07/08 16:51:42 | db3 | INFO [Rule_2025$u58$_REPORT_COMPONENT_STATE_TRANSITIONS1542395297] - CLUSTER 'omega@global' STATE TRANSITION UNREACHABLE => ONLINE The delta is 37 seconds in the above example between state=UNREACHABLE and UNREACHABLE => ONLINE The default timeout is 60 seconds. If the delay above were longer than 60 seconds, one site would shun the other, and traffic would be blocked by the Connector proxy to the remote site. This timeout may be tuned to be longer, however. This is the policy.remote.service.shun.threshold setting, and the default value is 6. Whatever this property is set to is multiplied by 10 seconds to come up with the final interval, so 60 seconds by default. Find all gaps shown in the logs, figure out the time differences and then take the peak value in seconds, add 10 seconds as a buffer and then divide by 10. Take the INT of that and you have your new value. Add property=policy.remote.service.shun.threshold={new_value} to your tungsten.ini and update your clusters! This tuning will provide better cluster stability via insulation from high latency network link timeouts. Questions? Contact us.

  • On MySQL and Intel Optane performance
    Recently, Dimitri published the results of measuring MySQL 8.0 on Intel Optane storage device. In this blog post, I wanted to look at this in more detail and explore the performance of MySQL 8, MySQL 5.7 and Percona Server for MySQL using a similar set up. The Intel Optane is a very capable device, so I was puzzled that Dimitri chose MySQL options that are either not safe or not recommended for production workloads. Since we have an Intel Optane in our labs, I wanted to run a similar benchmark, but using settings that we would recommend our customers to use, namely: use innodb_checksum use innodb_doublewrite use binary logs with sync_binlog=1 enable (by default) Performance Schema I still used charset=latin1  (even though the default is utf8mb4 in MySQL 8) and I set a total size of InnoDB log files to 30GB (as in Dimitri’s benchmark). This setting allocates big InnoDB log files to ensure there is no pressure from adaptive flushing. Though I have concerns about how it works in MySQL 8, this is a topic for another research. So let’s see how MySQL 8.0 performed with these settings, and compare it with MySQL 5.7 and Percona Server for MySQL 5.7. I used an Intel Optane SSD 905P 960GB device on the server with 2 socket Intel(R) Xeon(R) CPU E5-2680 v3 @ 2.50GHz CPUs. To highlight the performance difference I wanted to show, I used a single case: sysbench 8 tables 50M rows each (which is about ~120GB of data) and buffer pool 32GB. I ran sysbench oltp_read_write in 128 threads. First, let’s review the results for MySQL 8 vs MySQL 5.7 After achieving a steady state – we can see that MySQL 8 does not have ANY performance improvements over MySQL 5.7. Let’s compare this with Percona Server for MySQL 5.7 Percona Server for MySQL 5.7 shows about 60% performance improvement over both MySQL 5.7 and MySQL 8. How did we achieve this? All our improvements are described here: https://www.percona.com/doc/percona-server/LATEST/performance/xtradb_performance_improvements_for_io-bound_highly-concurrent_workloads.html. In short: Parallel doublewrite.  In both MySQL 5.7 and MySQL 8 writes are serialized by writing to doublewrite. Multi-threaded LRU flusher. We reported and proposed a solution here https://bugs.mysql.com/bug.php?id=70500. However, Oracle have not incorporated the solution upstream. Single page eviction. This is another problematic area in MySQL’s flushing algorithm. The bug https://bugs.mysql.com/bug.php?id=81376 was reported over 2 years ago, but unfortunately it’s still overlooked. Summarizing performance findings: For Percona Server for MySQL during this workload, I observed 1.4 GB/sec  reads and 815 MB/sec  writes For MySQL 5.7 and MySQL 8 the numbers are 824 MB/sec reads and  530 MB/sec writes. My opinion is that Oracle focused on addressing the wrong performance problems in MySQL 8 and did not address the real issues. In this benchmark, using real production settings, MySQL 8 does not show any significant performance benefits over MySQL 5.7 for workloads characterized by heavy IO writes. With this, I should admit that Intel Optane is a very performant storage. By comparison, on Intel 3600 SSD under the same workload, for Percona Server I am able to achieve only 2000 tps, which is 2.5x times slower than with Intel Optane. Drawing some conclusions So there are a few outcomes I can highlight: Intel Optane is a very capable drive, it is easily the fastest of those we’ve tested so far MySQL 8 is not able to utilize all the power of Intel Optane, unless you use unsafe settings (which to me is the equivalent of driving 200 MPH on a highway without working brakes) Oracle has focused on addressing the wrong IO bottlenecks and has overlooked the real ones To get all the benefits of Intel Optane performance, use a proper server—Percona Server for MySQL—which is able to utilize more IOPS from the device. The post On MySQL and Intel Optane performance appeared first on Percona Database Performance Blog.