tel. 883 59 39 89 l mail: 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 -
  • Combining tiered and leveled compaction
    There are simple optimization problems for LSM tuning. For example use leveled compaction to minimize space amplification and use tiered to minimize write amplification. But there are interesting problems that are harder to solve: maximize throughput given a constraint on write and/or space amplification minimize space and/or write amplification given a constraint on read amplification To solve the first problem use leveled compaction if it can satisfy the write amp constraint, else use tiered compaction if it can satisfy the space amp constraint, otherwise there is no solution. The lack of a solution might mean the constraints are unreasonable but it can also mean we need to enhance LSM implementations to support more diversity in LSM tree shapes. Even when there is a solution using leveled or tiered compaction there are solutions that would do much better were an LSM to support more varieties of tiered+leveled and leveled-N. When I mention solved above I leave out that there is more work to find a solution even when tiered or leveled compaction is used. For both there are decisions about the number of levels and per-level fanout. If minimizing write amp is the goal then that is a solved problem. But there are usually more things to consider. Tiered+leveled I defined tiered+leveled and leveled-N in a previous post. They occupy the middle ground between tiered and leveled compaction with better read efficiency than tiered and better write efficiency than leveled. They are not supported today by popular LSM implementations but I think they can and should be supported.  While we tend to explain compaction as a property of an LSM tree (all tiered or all leveled) it is really a property of a level of an LSM tree and RocksDB already supports hybrids, combinations of tiered and leveled. For tiered compaction in RocksDB all levels except the largest use tiered. The largest level is usually configured to use leveled to reduce space amp. For leveled compaction in RocksDB all levels except the smallest use leveled and the smallest (L0) uses tiered. So tiered+leveled isn't new but I think we need more flexibility. When a string of T and L is created from the per-level compaction choices then the regex for the strings that RocksDB supports is T+L or TL+. I want to support T+L+. I don't want to support cases where leveled is used for a smaller level and tiered for a larger level. So I like TTLL but not LTTL. My reasons for not supporting LTTL are: The benefit from tiered is less write amp and is independent of the level on which it is used. The reduction in write amp is the same whether tiered is used for L1, L2 or L3. The cost from tiered is more read and space amp and that is dependent on the level on which it is used. The cost is larger for larger levels. When space amp is 2 more space is wasted on larger levels than smaller levels. More IO read amp is worse for larger levels because they have a lower hit rate than smaller levels and more IO will be done. More IO implies more CPU cost from decompression and the CPU overhead of performing IO. From above the benefit from using T is the same for all levels but the cost increases for larger levels so when T and L are both used then T (tiered) should be used on the smaller levels and L (leveled) on the larger levels. Leveled-N I defined leveled-N in a previous post. Since then a co-worker, Maysam Yabandeh, explained to me that a level that uses leveled-N can also be described as two levels where the smaller uses leveled and the larger uses tiered. So leveled-N might be syntactic sugar in the LSM tree configuration language. For example with an LSM defined using the triple syntax from here as (compaction type, fanout, runs-per-level) then this is valid: (T,1,8) (T,8,2) (L,8,2) (L,8,1) and has total fanout of 512 (8 * 8 * 8). The third level (L,8,2) uses leveled-N with N=2. Assuming we allow LSM trees where T follows L then the leveled-N level can be replaced with two levels: (L,8,1) (T,1,8). Then the LSM tree is defined as (T,1,8) (T,8,2) (L,8,1) (T,1,8) (L,8,1). These LSM trees have the same total fanout and total read/write/space amp. Compaction from (L,8,1) to (T,1,8) is special. It has zero write amp because it is done by a file move rather than merging/writing data so all that must be updated is LSM metadata to record the move. So in general I don't support T after L but I do support it in the special case. Of course we can pretend the special case doesn't exist if we use the syntactic sugar provided by leveled-N. But I appreciate that Maysam discovered this.

  • Percona Live Europe Presents: The Latest MySQL Replication Features
    Considering the modern world of technology, where distributed system play a key role, replication in MySQL® is at the very heart of that change. It is very exciting to deliver this presentation and to be able to show everyone the greatest and the latest features that MySQL brings in order to continue the success that it has always been in the past. The talk is suitable for anyone that’s interested in knowing what Oracle is doing with MySQL replication. Old acquaintances will get familiarized about new features already delivered and being considered and newcomers to the MySQL ecosystem will see how great MySQL Replication has grown to be and how it fits in their business.. What I’m most looking forward to at Percona Live Europe… We are always eager to get feedback about the product. Moreover, MySQL being MySQL has a very large user base and, as such, is deployed and used in many different ways. It is very appealing and useful to continuously learn how our customers and users are making the most out of the product. Especially when it comes to replication, since MySQL replication infrastructure is anenabler for advanced and complex setups, making it a powerful and indispensable tool in virtually any setup nowadays. The post Percona Live Europe Presents: The Latest MySQL Replication Features appeared first on Percona Community Blog.

  • Effective Monitoring of MySQL with SCUMM Dashboards Part 1
    We added a number of new dashboards for MySQL in our latest release of ClusterControl 1.7.0. - and in our previous blog, we showed you How to Monitor Your ProxySQL with Prometheus and ClusterControl. In this blog, we will look at the MySQL Overview dashboard. So, we have enabled the Agent Based Monitoring under the Dashboard tab to start collecting metrics to the nodes. Take note that when enabling the Agent Based Monitoring, you have the options to set the “Scrape Interval (seconds)” and “Data retention (days)”. Scraping Interval is where you want to set how aggressively Prometheus will harvest data from the target and Data Retention is how long you want to keep your data collected by Prometheus before it’s deleted. When enabled, you can identify which cluster has agents and which one has agentless monitoring. Compared to the agentless approach, the granularity of your data in graphs will be higher with agents. The MySQL Graphs The latest version of ClusterControl 1.7.0 (which you can download for free - ClusterControl Community) has the following MySQL Dashboards for which you can gather information for your MySQL servers. These are MySQL Overview, MySQL InnoDB Metrics, MySQL Performance Schema, and MySQL Replication. We’ll cover in details the graphs available in the MySQL Overview dashboard. MySQL Overview Dashboard This dashboard contains the usual important variables or information regarding the health of your MySQL node. The graphs contained on this dashboard are specific to the node selected upon viewing the dashboards as seen below: It consists of 26 graphs, but you might not need all of these when diagnosing problems. However, these graphs provides a vital representation of the overall metrics for your MySQL servers. Let’s go over the basic ones, as these are probably the most common things that a DBA will routinely look at. The first four graphs shown above along with the MySQL’s uptime, query per-seconds, and buffer pool information are the most basic pointers we might need. From the graphs displayed above, here are their representations: MySQL Connections This is where you want to check your total client connections thus far allocated in a specific period of time. MySQL Client Thread Activity There are times that your MySQL server could be very busy. For example, it might be expected to receive surge in traffic at a specific time, and you want to monitor your running threads activity. This graph is really important to look at. There can be times your query performance could go south if, for example, a large update causes other threads to wait to acquire lock. This would lead to an increased number of your running threads. The cache miss rate is calculated as Threads_created/Connections. MySQL Questions These are the queries running in a specific period of time. A thread might be a transaction composed of multiple queries and this can be a good graph to look at. MySQL Thread Cache This graph shows the thread_cache_size value, threads that are cached (threads that are reused), and threads that are created (new threads). You can check on this graph for such instances like you need to tune your read queries when noticing a high number of incoming connections and your threads created increases rapidly. For example, if your Threads_running / thread_cache_size > 2 then increasing your thread_cache_size may give a performance boost to your server. Take note that creation and destruction of threads are expensive. However, in the recent versions of MySQL (>=5.6.8), this variable has autosizing by default which you might consider it untouched. The next four graphs are MySQL Temporary Objects, MySQL Select Types, MySQL Sorts, and MySQL Slow Queries. These graphs are related to each other specially if you are diagnosing long running queries and large queries that needs optimization. MySQL Temporary Objects This graph would be a good source to rely upon if you want to monitor long running queries that would end up using disk instead of temporary tables or files going in-memory. It’s a good place to start looking for periodical occurrence of queries that could add up to create disk space issues especially during odd times. MySQL Select Types One source of bad performance is queries that are using full joins, table scans, select range that is not using any indexes. This graph would show how your query performs and what amongst the list from full joins, to full range joins, select range, table scans has the highest trends. MySQL Sorts Diagnosing those queries that are using sorting, and the ones that take much time to finish. MySQL Slow Queries Trends of your slow queries are collected here on this graph. This is very useful especially on diagnosing how often your queries are slow. What are things that need to be tuned? It could be too small buffer pool, tables that lack indexes and goes a full-table scan, logical backups running on unexpected schedule, etc. Using our Query Monitor in ClusterControl along with this graph is beneficial, as it helps determine slow queries. The next graphs we have cover is more of the network activity, table locks, and the underlying internal memory that MySQL is consuming during the MySQL’s activity. MySQL Aborted Connections The number of aborted connections will render on this graph. This covers the aborted clients such as where the network was closed abruptly or where the internet connection was down or interrupted. It also records the aborted connects or attempts such as wrong passwords or bad packets upon establishing a connection from the client. MySQL Table Locks Trends for tables that request for a table lock that has been granted immediately and for tables that request for a lock that has not been acquired immediately. For example, if you have table-level locks on MyISAM tables and incoming requests of the same table, these cannot be granted immediately. MySQL Network Traffic This graph shows the trends of the inbound and outbound network activity in the MySQL server. “Inbound” is the data received by the MySQL server while “Outbound” is the data sent or transferred by the server from the MySQL server.This graph is best to check upon if you want to monitor your network traffic especially when diagnosing if your traffic is moderate but you’re wondering why it has a very high outbound transferred data, like for example, BLOB data. MySQL Network Usage Hourly Same as the network traffic which shows the Received and Sent data. Take note that it’s based on ‘per hour’ and labeled with ‘last day’ which will not follow the period of time you selected in the date picker. MySQL Internal Memory Overview This graph is familiar for a seasoned MySQL DBA. Each of these legends in the bar graph are very important especially if you want to monitor your memory usage, your buffer pool usage, or your adaptive hash index size. The following graphs show the counters that a DBA can rely upon such as checking the statistics for example, the statistics for selects, inserts, updates, the number of master status that has been executed, the number of SHOW VARIABLES that has been executed, check if you have bad queries doing table scans or tables not using indexes by looking over the read_* counters, etc. Top Command Counters (Hourly) These are the graphs you would likely have to check whenever you would like to see the statistics for your inserts, deletes, updates, executed commands such as gathering the processlist, slave status, show status (health statistics of the MySQL server), and many more. This is a good place if you want to check what kind of MySQL command counters are topmost and if some performance tuning or query optimization is needed. It might also allow you to identify which commands are running aggressively while not needing it. MySQL Handlers Oftentimes, a DBA would go over these handlers and check how the queries are performing in your MySQL server. Basically, this graph covers the counters from the Handler API of MySQL. Most common handler counters for a DBA for the storage API in MySQL are Handler_read_first, Handler_read_key, Handler_read_last, Handler_read_next, Handler_read_prev, Handler_read_rnd, and Handler_read_rnd_next. There are lots of MySQL Handlers to check upon. You can read about them in the documentation here. MySQL Transaction Handlers If your MySQL server is using XA transactions, SAVEPOINT, ROLLBACK TO SAVEPOINT statements. Then this graph is a good reference to look at. You can also use this graph to monitor all your server’s internal commits. Take note that the counter for Handler_commit does increment even for SELECT statements but differs against insert/update/delete statements which goes to the binary log during a call to COMMIT statement. The next graph will show trends about process states and their hourly usage. There are lots of key points here in the bar graph legend that a DBA would check. Encountering disk space issues, connection issues and see if your connection pool is working as expected, high disk I/O, network issues, etc. Process States/Top Process States Hourly This graph is where you can monitor the top thread states of your queries running in the processlist. This is very informative and helpful for such DBA tasks where you can examine here any outstanding statuses that need resolution. For example, opening tables state is very high and its minimum value is almost near to the maximum value. This could indicate that you need to adjust the table_open_cache. If the statistics is high and you’re noticing a slow down of your server, this could indicate that your server is disk-bound and you might need to consider increasing your buffer pool. If you have a high number of creating tmp table then you might have to check your slow log and optimize the offending queries. You can checkout the manual for the complete list of MySQL thread states here. The next graph we’ll be checking is about query cache, MySQL table definition cache, how often MySQL opens system files. Related resources  Introducing SCUMM: the agent-based database monitoring infrastructure in ClusterControl  How to Monitor MySQL or MariaDB Galera Cluster with Prometheus Using SCUMM  Download ClusterControl MySQL Query Cache Memory/Activity These graphs are related to each other. If you have query_cache_size <> 0 and query_cache_type <> 0, then this graph can be of help. However, in the newer versions of MySQL, the query cache has been marked as deprecated as the MySQL query cache is known to cause performance issues. You might not need this in the future. The most recent version of MySQL 8.0 has drastic improvements; it tends to increase performance as it comes with several strategies to handle cache information in the memory buffers. MySQL File Openings This graph shows the trend for the opened files since the MySQL server’s uptime but it excludes files such as sockets or pipes. It does also not include files that are opened by the storage engine since they have their own counter that is Innodb_num_open_files. MySQL Open Files This graph is where you want to check your InnoDB files currently held open, the current MySQL open files, and your open_files_limit variable. MySQL Table Open Cache Status If you have very low table_open_cache set here, this graph will tell you about those tables that fail the cache (newly opened tables) or miss due to overflow. If you encounter a high number or too much “Opening tables” status in your processlist, this graph will serve as your reference to determine this. This will tell you if there’s a need to increase your table_open_cache variable. MySQL Open Tables Relative to MySQL Table Open Cache Status, this graph is useful in certain occasions like you want to identify if there’s a need to increase of your table_open_cache or lower it down if you notice a high increase of open tables or Open_tables status variable. Note that table_open_cache could take a large amount of memory space so you have to set this with care especially in production systems. MySQL Table Definition Cache If you want to check the number of your Open_table_definitions and Opened_table_definitions status variables, then this graph is what you need. For newer versions of MySQL (>=5.6.8), you might not need to change the value of this variable and use the default value since it has autoresizing feature. Conclusion The SCUMM addition in the latest version of ClusterControl 1.7.0 provides significant new benefits for a number of key DBA tasks. The new graphs can help easily pinpoint the cause of issues that DBAs or sysadmins would typically have to deal with and help find appropriate solutions faster. We would love to hear your experience and thoughts on using ClusterControl 1.7.0 with SCUMM (which you can download for free - ClusterControl Community). In part 2 of this blog, I will discuss Effective Monitoring of MySQL Replication with SCUMM Dashboards. Tags:  MySQL monitoring dashboards clustercontrol scumm

  • ProxySQL 1.4.11 and Updated proxysql-admin Tool Now in the Percona Repository
    ProxySQL 1.4.11, released by ProxySQL, is now available for download in the Percona Repository along with an updated version of Percona’s proxysql-admin tool. ProxySQL is a high-performance proxy, currently for MySQL and its forks (like Percona Server for MySQL and MariaDB). It acts as an intermediary for client requests seeking resources from the database. René Cannaò created ProxySQL for DBAs as a means of solving complex replication topology issues. The ProxySQL 1.4.11 source and binary packages available at include ProxySQL Admin – a tool, developed by Percona to configure Percona XtraDB Cluster nodes into ProxySQL. Docker images for release 1.4.11 are available as well: You can download the original ProxySQL from The documentation is hosted on GitHub in the wiki format. Improvements mysql_query_rules_fast_routing is enabled in ProxySQL Cluster. For more information, see #1674 at GitHub. In this release, rmpdb checksum error is ignored when building ProxySQL in Docker. By default, the permissions for proxysql.cnf are set to 600 (only the owner of the file can read it or make changes to it). Bugs Fixed Fixed the bug that could cause crashing of ProxySQL if IPv6 listening was enabled. For more information, see #1646 at GitHub. ProxySQL is available under Open Source license GPLv3.

  • Where you can find MySQL in October - December 2018 - part 2.
    As a continue of the previous blog announcement posted on Oct 16, please find below a list of conferences & events where you can find MySQL team &/or MySQL Community during the period of Oct-Dec 2018: November 2018 - cont. PerconaLive, Frankfrurt Germany, November 5-7, 2018 We are happy to inform that MySQL is going to be Silver sponsor of PerconaLive Europe 2018! You will be able to find us on the MySQL booth in the expo hall as well as attend multiple MySQL talks given by our colleagues. The talks are scheduled as follows: Tutorial on: "MySQL InnoDB Cluster in a Nutshell : The Saga Continues with 8.0" by Frédéric Descamps, the MySQL Community Manager, scheduled for Monday, @9:00am-12:00pm. "MySQL 8.0 Performance: Scalability & Benchmarks" by Dimitri KRAVTCHUK, the Performance Architect, talk is scheduled for Tuesday @12:20-13:10. "MySQL Group Replication: the magic explained" by Frédéric Descamps, the MySQL Community Manager, scheduled for Tuesday @14:20-15:10. "Upgrading to MySQL 8.0 and a More Automated Experience" by Dmitry Lenev, the Senior Software Developer, scheduled for Tuesday @ 15:20-16:10. "More SQL in MySQL 8.0" by Norvald Ryeng, the Senior Developer Manager - Optimizer Team, scheduled for Tuesday 17:25-17:50. "The Latest MySQL Replication Features" by Jorge Tiago, the Senior Software Developer, scheduled for Wednesday @14:20-15:10. "Developing Applications with Node.js and the MySQL Document Store" by Johannes Schlüter, the Principal Software Developer, scheduled for Wednesday @17:00-17:25. Come to listen MySQL sessions & stop at our booth to talk to our staff at PerconaLive 2018! PHP.RUHR, Dortmund, Germany, November 8, 2018 MySQL team is going to be part of this technical/PHP show. You can come to listen a MySQL talk in the Mainstage: Developer as: "MySQL 8.0 - The new Developer API" given by Mario Beck, the MySQL Sales Consulting Manger for EMEA. There also will be a Q&A session in the expo area about MySQL, where you can come to ask questions. Highload++, Moscow, Russia, November 8-9, 2018 MySQL team is going to be part of this technical conference in Moscow. You can find us on our MySQL booth in the expo area as well as you should be able to find MySQL talk in the schedule (still not heard about its acceptance). We are looking forward to talking to you @ Highload++ this year!!! SeaGL, Seattle, US, November 9-10, 2018 MySQL community team supports this GNU/Linux conference in Seattle.  PHP[World], Washington DC, US, November 14-15, 2018  MySQL is a Workshop sponsor this year. Unfortunately no talk this year, but we are in the waiting list for lightening talk. Please watch the organizers' website. BGOUG, Pravets, Bulgaria, November 14-15, 2018 As a tradition MySQL is a conference sponsor of this Bulgarian Oracle User Group regular conference. This time with a following talk: "Data Masking in MySQL Enterprise 5.7 and 8" given by Georgi Kodinov, the Senior Software Developer Manager for MySQL.  Please come to listen Georgi's talk as well as ask him questions. DOAG, Nuremberg, Germany, November 20-23, 2018 Same as in previous years we are going to be part of this conference organized by German Oracle User Group. You can find MySQL staff at the Oracle booth (Place 320) as well as attend several MySQL talks. You can find the talks here. Feira do Conhecimento, Fortaleza, Brazil, November 21-24, 2018 The Science, Technology and Higher Education Secretariat (Secitece) will hold the second edition of the Knowledge Fair - Science, Technology, Innovation and Business at the Ceará Event Center (East Pavilion) and we are happy to be part of this event!! A Local MySQL Sales representative will be there to answer all MySQL related questions as well as a MySQL talk on "Innovation, Business & Technology" is approved. Please watch the organizers' website for further updates. PyCon HK, Hong Kong, November 23-24, 2018 MySQL is a Bronze sponsor of this Python show and again this year without booth, but with a MySQL talk on "NoSQL Development for MySQL Document Store using Python" by Ivan Ma, the MySQL Principal Sales Consultant. December 2018 Tech18, UKOUG, Liverpool, UK, December 3-5, 2018 As a tradition MySQL will be part of this Oracle User Group Conference in the UK. You will be able to find our staff at the Oracle booth in the expo area. IT.Tage 2018, Frankfurt, Germany, December 10-13, 2018 Our pleasure to announce that this year MySQL is again part of the IT.Tage event. This time together with Oracle Developer & Linux team. You will have an opportunity to find us all at the shared Oracle booth as well as attend several Oracle's talks.  For MySQL do not miss the opportunity to listen what is new in MySQL 8 during following session: "MySQL 8 - MySQL as a Document Store Database" given by Carsten Thalheimer, the MySQL Principal Sales Consultant, scheduled for December 12, 2018 @ 14:30-15:15 in the Database track.  We are looking forward to talking to you there! OpenSource Conference Fukuoka, Fukuoka, Japan (December 8, 2018) MySQL is Gold sponsor here. You will be able to find us at MySQL booth in expo area as well as find a MySQL talk on "State of Dolphin" given by Yoshiaki Yamasaki, the MySQL Senior Sales Consultant Asia Pacific and Japan region, during the talk general product updates will be covered. OpenSource Enterprise, Tokyo, Japan, (December 14, 2018) Again, MySQL is a Gold sponsor with MySQL booth & talk on the same topic: "State of Dolphin" by Yoshiaki Yamasaki.  We will continue updating you about upcoming events & conferences where you can find MySQL team at.