Personal blog of Yzmir Ramirez

It’s National Video Game Day! Whether you love the thrill of high action, immersion into an alternative world, or the challenge of mastering a skill, there’s a game out there for everyone. In gaming, it’s all about a seamless user experience, but behind the game is a complex infrastructure that relies on databases to keep data flowing and applications humming. Is your database ready for game day?

As a leading provider of unbiased open source database solutions, Percona supports gaming companies by optimizing their databases through their entire launch, from design and testing to implementation and scaling. Plus, at Percona, our team of open source database experts help to ensure that your applications stay agile, highly available, and performant, so you can deliver enhancements quickly. 

For example, Percona partnered with Peak, a mobile gaming technology company, to maximize the effectiveness of their database environment. In addition to using Percona Server for MySQL, Peak works with Percona to get expert advice and practical assistance, which has resulted in more efficient queries, faster more stable rollouts, and lower costs.

We can help you too!

In our recent gaming webinar series, Percona Experts share their insights about the entire gaming application lifecycle and how to design and optimize your databases to boost your game performance.

Here’s what the series includes:

Available on demand:

Maximize Your Database Performance to Enhance Your Player Experience

Databases are crucial to providing the smooth gaming experience users demand. How you design your database environment before you start building applications impacts every other decision down the line. That’s why it’s so important to get it right. 

In this webinar, we’ll look at database design considerations, talk through design issues that can affect performance and scalability, and offer guidance on how to avoid having to fix poor design decisions later. 

Behind the Scenes: Test Your Application Performance Before Setting Your Game in Motion

Some things should not be exciting, like a plane landing or a doctor’s visit. Same goes for launching a new game or application. Prior to a release, you want to feel confident that the process will be uneventful. Design is an important piece of that but so is ensuring that all the components work well together. 

In this webinar, we’ll discuss the importance of testing your components as well as the tools you need to quickly find and fix problems within complex application stacks. We’ll also talk about how to prepare for the launch, what to watch for during rollout, and how to keep your environment running smoothly post release. 

Scale in and Scale Out Your Database: Five Pointers From Percona Experts for Launch Day

Disruptions, breaches, and other failures are often hard to prevent, but outright catastrophes can be avoided. Gaming companies face big challenges around adapting to traffic as it waxes and wanes over the lifetime of a game. 

Tune in to hear Percona Experts provide advice on how to efficiently scale in and out of your database on launch day and beyond. 

Deep Dive Into Game Analytics and Batch Data

Maintaining high application performance after your launch is critical to delighting your users and protecting your reputation for reliability. But how do you keep things running smoothly? How do you ensure that you’re not constantly in reactive mode?

In this webinar, we’ll take a deep dive into a variety of best practices that allow you to proactively uncover issues before they impact your users. 

Optimize Database Efficiency and Reduce the Risk of Downtime

In this webinar, Krisztian Banhidy, DevOps engineer from Peak, offers a deep dive into the lifecycle of his company’s game launches. He shares how Peak uses automated and manual testing to vet components before launch, what his team monitors for during pre-production and production, their approach to achieving high availability, how they handle challenges around scale, and insights into common database problems. 

Coming soon:

Ensure Uptime, Reliability, and High Availability for Your Database

Hear what customers have to say about their best practices for keeping gaming environments highly available. Date to be announced. 

Sign up for our gaming webinar series to get your team on a path to a successful launch today!

First I want to thank everyone who attended my November 16, 2021 webinar “MySQL Performance for DevOps“. Recording and slides are available on the webinar page.

Here are answers to the questions from participants which I was not able to provide during the webinar.

Q: Hi! We have troubles with DELETE queries. We have to remove some data periodically (like, hourly, daily) and we have short-term server stalls during these DELETEs. Server is running on modern NVMe’s so we wonder why do we have this situation. Those DELETE’s are not so large, like 10 000 – 15 000 records, but tables on which DELETE’s are performed update frequently.

A: I would test if a similar

DELETE

  statement is slow when you run it on the development server in an isolated environment while no other session is connected to the MySQL server instance.  If it is slow in this case too, check if MySQL uses indexes to resolve the condition

WHERE

  for the

DELETE

  statement. You can use

EXPLAIN

  statement for

DELETE

  or convert

DELETE

  into a similar

SELECT

  query and experiment.

If the

DELETE

  statement is running fast when called in the isolated environment, check how parallel sessions affect its performance. If the tables you are deleting from are updated frequently,

DELETE

  statements could cause and be affected by locking conflicts. To resolve this situation study how MySQL works with locks. Great presentation about InnoDB locks “InnoDB Locking Explained with Stick Figures” could be found at https://www.slideshare.net/billkarwin/innodb-locking-explained-with-stick-figures Then you need to optimize

DELETE

  and

UPDATE

  statements, so they finish faster. Alternatively, you can separate them in time, so they have less effect on each other. You may also split

DELETE

  statements, so they update fewer records at a time.

Q: Question 2. We have innodb_buffer_size set around 260Gb on the dedicated server with about 320Gb of total RAM. Still, we have 99.9% memory full and there are no other large memory consumers, only MySQL (Percona 8.0.23). The server starts and around 3 hours it takes all available memory regardless of the innodb_buffer_size setting. We never had something like this with 5.7. Do you have any ideas?

A: MySQL uses memory not only for the InnoDB buffer pool but for other data, such as session-based and operation-based buffers. For example, if you have 100 connections that use underlying temporary tables to resolve queries and set the size of the internal temporary table to 100MB you will use around 10G additional memory for these tables. Query memory digest tables in Performance Schema and views on these tables in the

sys

 schema to find the operations that allocate memory in your MySQL server.

Q: Can we get a copy of this presentation?

A: You should have received a copy of the slides. If you did not, they are attached to this blog post: DevOps_Perf_202111

Q: buffer_pool_size should be what percentage of the host RAM?

A: The percentage of the host RAM is a very rough estimation of the ideal amount of memory you need to allocate for the InnoDB buffer pool. For example, the MySQL user manual in past had recommendations for having InnoDB buffer pool size up to 80% of the available RAM. But 80% of RAM is very different if the host has, say, 8G, or 1024G. In the former case, 80% is 6.4G and the host will have 1.6G for other MySQL buffers and the operating system that could be not enough. In the latter case, 80% is 819.2G and the host will have 204.8G for other needs. Depending on your workload it could be a huge waste of resources. I recommend you to read this blog post: https://www.percona.com/blog/2015/06/02/80-ram-tune-innodb_buffer_pool_size/ and follow the links in the end, then choose the size, appropriate for your data set and workload.

Q: How we can fitting RAM size vs data size?

Example: if I have 1G of data, how many RAM I need for get 100 QPS, and if I have 100G of data how many RAM I need for get 100 QPS?

A: RAM size, dataset size, and the number of queries per second that your server can handle are not directly related. You need to test your queries and follow how they are executed. For example, if you select everything from the InnoDB table and your table holds either 1G or 100G of data, and you do not access any other table on the server, the very first run will be slower than following because InnoDB will read data into the buffer pool. Then performance and the number of queries per second will be limited only by network speed and bandwidth between your client and server having you can allocate about 100G for your buffer pool. But cached size will stay almost the same as the table size no matter how many connections you have. Your MySQL server will only use a small amount of memory for new connections buffers.

In another case, however, you may have a comparatively small table that you will access by a quite complicated query. For example, if you try to repeat the test case for still valid https://bugs.mysql.com/bug.php?id=29423, a single query on the 184M table would run for a much longer time than you expect. In this case number of queries per second will be also very low.

Q: Do you have a recommendation parameter list for MySQL RDS on AWS?

A: It is the same as for the dedicated MySQL server but you may have not been able to change some of the options.

Q: If you know you have SSD’s, but ROTA = 1, what has to be configured to make use of the SSDs?

A: For SSD ROTA should be 0. If you are sure you have SSDs but they are shown as rotational disks this means that your storage is configured incorrectly. Depending on the configuration you may still have the same performance as if the disks were recognized properly. If this is not the case, check your storage, RAID controller, and system configuration.

MySQL just sends system commands for reading, writing, and syncing data. It does not care if the disk is rotational or not. For MySQL performance value of ROTA does not really matter.

Q: If you believed you tuned both master and slave for the best performance, but seconds behind master continues to increase, you decide to split the shard, but xtrabackup fails with log wrap.  But even if you were to get a good backup, once it is online, the slave will never catch up.  The Kobayashi Maru, a no win situation – have you been there?  What did you do?

A: First make sure if you configured a multi-threaded replica. If you use parallel type

LOGICAL_CLOCK

, study option binlog_transaction_dependency_tracking. Practically how it works when set to

WRITESET

  or to

WRITESET_SESSION

 . For avoiding log wrap during backup increase redo log file size. If you can stop the source server, stop it and set up a replica by copying datadir: it is faster than using XtraBackup, because you would not need to copy changes in the redo log files while the backup is running.

Q: In MySQL 5.7, the tmp tablespace is now InnoDB, how can you tune tmp to take advantage of RAM and not use disk?

A: The tablespace file on disk is used only when the in-memory table is converted into a disk-based table. Otherwise, temporary tables continue using memory.

Q: What are the top 6 variables to get the best performance, how can you verify how effective their setting are, looking at the global status, when can you know when those variables can be increased to get the best utilization from CPUs/RAM/Disk/Network.

A: While I showed variables that can improve performance in most cases on my “Conclusion” slides I recommend you to start from the issue you are trying to solve and start adjusting variables only when you understand what you are doing.

Some of such variables could be measured for effectiveness. For example, if the number of free buffers in the output of

SHOW ENGINE INNODB STATUS

  is small and the buffer pool hit rate shows that a number of disk access is consistently greater than the number of the buffer pool hits, it indicates that the buffer pool size may be too small for you your workload and data.

Regarding CPU, if the number of active threads is high, and you see performance drop when concurrency increases while the operating system shows low CPU usage, it may be a symptom that either:

– you limited the upper limit of the number of active engine threads

– disk does not support so many parallel operations and active threads are waiting for IO

Another issue with CPU performance could happen if the upper limit of the number of active engine threads is not set or too high and threads are spending time doing nothing while waiting in the priority queue.

The only option that directly limits IO activity is

innod_io_capacity

  that limits the speed of background InnoDB operations. If set too low InnoDB may underuse your fast disk and if set too high InnoDB could start writing too fast, so each write request will waste time waiting in its queue.

Q: What was the last InnoDB setting, the one which should up to no of CPU cores?

A: This is

innodb_thread_concurrency

  that limits the number of InnoDB threads that could run in parallel. You should set it either to 0 or to the number of CPU cores.

Q: Which is more secure and faster community MySQL or Percona MySQL or aws rds?

A: Percona MySQL has performance, diagnostic improvements, as well as Enterprise-level features, available as open source. AWS RDS supports hardware scaling on demand and physical replication that uses InnoDB redo log files instead of binary logs. However, it does not allow you to have the same control on the server as for your own physical instance. Community MySQL works on a higher number of platforms, thus uses function calls that work on all of them where Percona MySQL or AWS RDS may use optimized variants. So each of them has its own advantages and disadvantages.

Q: In case with open tables >>> open_files (and cannot change open_files) how to set table_open_cache? “as big as possible”?

A: Status variable

Open_files

  is “the number of files that are open. This count includes regular files opened by the server. It does not include other types of files such as sockets or pipes. Also, the count does not include files that storage engines open using their own internal functions rather than asking the server level to do so.” (https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html#statvar_Open_files) The status variable

Open_tables

  is “the number of tables that are open”. They are not related to each other. You need to watch that value of 

Opened_tables

  (“the number of tables that have been opened”) is not greater than

Open_tables

 .

There is an operating system option “open files” that is visible if you run the command

ulimit -n

. This option should be greater than the maximum number of files that your MySQL instance can simultaneously open. Speaking about

Open_tables

 : you cannot have this value set to a number that is larger than the operating system option “open files” unless your tables are stored in the shared or general tablespace.

Q: How to tell if we should tune join_buffer_size? wait events anywhere?

A: If you use

JOIN

  queries that do not use indexes and they perform slowly because of this. Start from regular query tuning using slow query log, Performance Schema, and Query Analyzer in PMM to find queries that require optimization. In Query Analyzer add a column “Full Join” to your query list. In the Performance Schema search for statements where the value of

SELECT_FULL_JOIN

  is greater than 0 in the

events_statements_*

  tables.

Check also my “Introduction to MySQL Query Tuning for Dev[Op]s” webinar.

Q: How to measure memory consumption of table_open_cache? 15K/table? FRM-related? some way to estimate?

A: This is event “

memory/sql/TABLE_SHARE::mem_root

” Check also this blog post.

Q: Hello guys!

Do we need to prepare different optimization depends on MySQL engine e.g. XtraDB, InnoDB? If yes, could you please explain differences?

Best regards,

Oleg Stelmach

A: XtraDB is an enhanced version of InnoDB in the Percona Server: https://www.percona.com/doc/percona-server/8.0/percona_xtradb.html. So differences are added features in the Percona Server. Namely, the options that exist in the Percona server and do not exist in the upstream Community MySQL.

Q: Regarding threads. Do better to use hyperthreading\multithreading for MySQL instance or we need to turn off this function?

Best regards,

Oleg Stelmach

A: You do not have to turn this option off but you may see that MySQL performance is not linearly predictable in high concurrent workloads. I recommend you to check this blog post with hyperthreading benchmarks on MySQL and comments on it for a better understanding of how hyperthreading can affect MySQL performance.

Q: Besides from setting os swap-pines correctly. would also recommend to enable memlock in my.cnf?

A: Normally you do not need it.

First, I want to thank everyone who attended my November 9, 2021 webinar “Introduction to MySQL Query Tuning for Dev[Op]s“. Recording and slides are available on our webinars page.

Here are answers to the questions from participants which I was not able to provide during the webinar due to a technical issue we experienced at the end.

Q: If a large/complex SELECT is run from inside a Stored Procedure, will it use/not use the same optimizations as it would if run as a direct query? Is there anything to bear in mind when writing Stored Procedures that need to run large/complex SELECTs from within them?

Just wanted to clarify, using a stored procedure does not utilize any of the indexes on the corresponding tables?

A: Any query inside a stored procedure will be optimized the same way as if it was called outside of the routine. We cannot run

EXPLAIN

  on stored procedures but we can prove it with the help of Performance Schema.

For example, let’s take a standard test database employees and perform a quite ineffective query on it:

mysql> SELECT emp_no, first_name, last_name, title, salary FROM employees 
    -> JOIN titles USING(emp_no) JOIN salaries USING(emp_no) 
    -> WHERE salary = (SELECT MAX(salary) FROM salaries) 
    -> OR salary = (SELECT MIN(salary) FROM salaries);
+--------+------------+-----------+------------------+--------+
| emp_no | first_name | last_name | title            | salary |
+--------+------------+-----------+------------------+--------+
|  43624 | Tokuyasu   | Pesch     | Senior Staff     | 158220 |
|  43624 | Tokuyasu   | Pesch     | Staff            | 158220 |
| 253406 | Olivera    | Baek      | Technique Leader |  38623 |
+--------+------------+-----------+------------------+--------+
3 rows in set (4,38 sec)

Then let’s create a stored routine that uses this query:

mysql> \d |

mysql> CREATE PROCEDURE my_test()
    -> BEGIN
    ->     SELECT emp_no, first_name, last_name, title, salary 
    ->     FROM employees JOIN titles USING(emp_no)
    ->     JOIN salaries USING(emp_no) 
    ->     WHERE salary = (SELECT MAX(salary) FROM salaries)
    ->     OR salary = (SELECT MIN(salary) FROM salaries);
    -> END
    -> |
Query OK, 0 rows affected (0,02 sec)

This routine call takes about the same time on my laptop as if I run the query separately:

mysql> CALL my_test();
+--------+------------+-----------+------------------+--------+
| emp_no | first_name | last_name | title            | salary |
+--------+------------+-----------+------------------+--------+
|  43624 | Tokuyasu   | Pesch     | Senior Staff     | 158220 |
|  43624 | Tokuyasu   | Pesch     | Staff            | 158220 |
| 253406 | Olivera    | Baek      | Technique Leader |  38623 |
+--------+------------+-----------+------------------+--------+
3 rows in set (4,35 sec)
Query OK, 0 rows affected (4,35 sec)

If I then enable statement instrumentation in Performance Schema and run the query again I would have the following output:

mysql> USE performance_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> UPDATE setup_instruments SET enabled='yes' WHERE name LIKE 'statement/%';
Query OK, 0 rows affected (0,00 sec)
Rows matched: 213  Changed: 0  Warnings: 0

mysql> UPDATE setup_consumers SET enabled='yes' WHERE name LIKE '%statement%';
Query OK, 1 row affected (0,00 sec)
Rows matched: 4  Changed: 1  Warnings: 0

mysql> USE employees;
Database changed

mysql> TRUNCATE performance_schema.events_statements_history;
Query OK, 0 rows affected (0,01 sec)

mysql> SELECT emp_no, first_name, last_name, title, salary FROM employees 
    -> JOIN titles USING(emp_no) JOIN salaries USING(emp_no)
    -> WHERE salary = (SELECT MAX(salary) FROM salaries)
    -> OR salary = (SELECT MIN(salary) FROM salaries);
+--------+------------+-----------+------------------+--------+
| emp_no | first_name | last_name | title            | salary |
+--------+------------+-----------+------------------+--------+
|  43624 | Tokuyasu   | Pesch     | Senior Staff     | 158220 |
|  43624 | Tokuyasu   | Pesch     | Staff            | 158220 |
| 253406 | Olivera    | Baek      | Technique Leader |  38623 |
+--------+------------+-----------+------------------+--------+
3 rows in set (4,41 sec)

mysql> SELECT * FROM performance_schema.events_statements_history 
    -> WHERE thread_id=(SELECT thread_id FROM performance_schema.threads WHERE processlist_id=@@pseudo_thread_id) 
    -> AND event_name != 'statement/sql/truncate'\G
*************************** 1. row ***************************
              THREAD_ID: 100414
               EVENT_ID: 24392721
           END_EVENT_ID: 26360924
             EVENT_NAME: statement/sql/select
                 SOURCE: init_net_server_extension.cc:93
            TIMER_START: 271361154464092000
              TIMER_END: 271365562450546000
             TIMER_WAIT: 4407986454000
              LOCK_TIME: 325000000
               SQL_TEXT: SELECT emp_no, first_name, last_name, title, salary FROM employees JOIN titles USING(emp_no) 
                         JOIN salaries USING(emp_no) WHERE salary = (SELECT MAX(salary) FROM  salaries) OR salary = (SELECT MIN(salary) FROM salaries)
                 DIGEST: 4fe5ea4a8a96539aa9e837031c7c4ea59c1b772272a83c1db5e2833170c2d563
            DIGEST_TEXT: SELECT `emp_no` , `first_name` , `last_name` , `title` , `salary` FROM `employees` JOIN `titles` USING ( `emp_no` ) 
                         JOIN `salaries` USING ( `emp_no` ) WHERE `salary` = ( SELECT MAX ( `salary` ) 
                         FROM `salaries` ) OR `salary` = ( SELECT MIN ( `salary` ) FROM `salaries` )
         CURRENT_SCHEMA: employees
            OBJECT_TYPE: NULL
          OBJECT_SCHEMA: NULL
            OBJECT_NAME: NULL
  OBJECT_INSTANCE_BEGIN: NULL
            MYSQL_ERRNO: 0
      RETURNED_SQLSTATE: NULL
           MESSAGE_TEXT: NULL
                 ERRORS: 0
               WARNINGS: 0
          ROWS_AFFECTED: 0
              ROWS_SENT: 3
          ROWS_EXAMINED: 11069933
CREATED_TMP_DISK_TABLES: 0
     CREATED_TMP_TABLES: 0
       SELECT_FULL_JOIN: 0
 SELECT_FULL_RANGE_JOIN: 0
           SELECT_RANGE: 0
     SELECT_RANGE_CHECK: 0
            SELECT_SCAN: 3
      SORT_MERGE_PASSES: 0
             SORT_RANGE: 0
              SORT_ROWS: 0
              SORT_SCAN: 0
          NO_INDEX_USED: 1
     NO_GOOD_INDEX_USED: 0
       NESTING_EVENT_ID: NULL
     NESTING_EVENT_TYPE: NULL
    NESTING_EVENT_LEVEL: 0
           STATEMENT_ID: 428707
1 row in set (0,00 sec)

The interesting part of the output is the performance fields with non-zero values. Namely,

ROWS_SENT: 3
ROWS_EXAMINED: 11069933
  SELECT_SCAN: 3
NO_INDEX_USED: 1

Now let’s call the stored procedure and compare these values.

mysql> TRUNCATE performance_schema.events_statements_history;
Query OK, 0 rows affected (0,00 sec)

mysql> CALL my_test();
+--------+------------+-----------+------------------+--------+
| emp_no | first_name | last_name | title            | salary |
+--------+------------+-----------+------------------+--------+
|  43624 | Tokuyasu   | Pesch     | Senior Staff     | 158220 |
|  43624 | Tokuyasu   | Pesch     | Staff            | 158220 |
| 253406 | Olivera    | Baek      | Technique Leader |  38623 |
+--------+------------+-----------+------------------+--------+
3 rows in set (4,55 sec)
Query OK, 0 rows affected (4,55 sec)

mysql> SELECT * FROM performance_schema.events_statements_history 
    -> WHERE thread_id=(SELECT thread_id FROM performance_schema.threads WHERE processlist_id=@@pseudo_thread_id) 
    -> AND event_name != 'statement/sql/truncate'\G
*************************** 1. row ***************************
              THREAD_ID: 100414
               EVENT_ID: 28336202
           END_EVENT_ID: 30292242
             EVENT_NAME: statement/sp/stmt
                 SOURCE: sp_head.cc:2210
            TIMER_START: 271549571279270000
              TIMER_END: 271554128099300000
             TIMER_WAIT: 4556820030000
              LOCK_TIME: 139000000
               SQL_TEXT: SELECT emp_no, first_name, last_name, title, salary FROM employees JOIN titles USING(emp_no) 
                         JOIN salaries USING(emp_no) WHERE salary = (SELECT MAX(salary) FROM salaries) OR salary = (SELECT MIN(salary) FROM salaries)
                 DIGEST: NULL
            DIGEST_TEXT: NULL
         CURRENT_SCHEMA: employees
            OBJECT_TYPE: PROCEDURE
          OBJECT_SCHEMA: employees
            OBJECT_NAME: my_test
  OBJECT_INSTANCE_BEGIN: NULL
            MYSQL_ERRNO: 0
      RETURNED_SQLSTATE: NULL
           MESSAGE_TEXT: NULL
                 ERRORS: 0
               WARNINGS: 0
          ROWS_AFFECTED: 0
              ROWS_SENT: 3
          ROWS_EXAMINED: 11069933
CREATED_TMP_DISK_TABLES: 0
     CREATED_TMP_TABLES: 0
       SELECT_FULL_JOIN: 0
 SELECT_FULL_RANGE_JOIN: 0
           SELECT_RANGE: 0
     SELECT_RANGE_CHECK: 0
            SELECT_SCAN: 3
      SORT_MERGE_PASSES: 0
             SORT_RANGE: 0
              SORT_ROWS: 0
              SORT_SCAN: 0
          NO_INDEX_USED: 1
     NO_GOOD_INDEX_USED: 0
       NESTING_EVENT_ID: 28336201
     NESTING_EVENT_TYPE: STATEMENT
    NESTING_EVENT_LEVEL: 1
           STATEMENT_ID: 429597
*************************** 2. row ***************************
              THREAD_ID: 100414
               EVENT_ID: 28336201
           END_EVENT_ID: 30292243
             EVENT_NAME: statement/sql/call_procedure
                 SOURCE: init_net_server_extension.cc:93
            TIMER_START: 271549571185646000
              TIMER_END: 271554128135521000
             TIMER_WAIT: 4556949875000
              LOCK_TIME: 0
               SQL_TEXT: CALL my_test()
                 DIGEST: 070cb7d4d2eba9690a3a993ec61700fb6d6c3e46166e329408d7284e45fd58c2
            DIGEST_TEXT: CALL `my_test` ( )
         CURRENT_SCHEMA: employees
            OBJECT_TYPE: NULL
          OBJECT_SCHEMA: NULL
            OBJECT_NAME: NULL
  OBJECT_INSTANCE_BEGIN: NULL
            MYSQL_ERRNO: 0
      RETURNED_SQLSTATE: 00000
           MESSAGE_TEXT: NULL
                 ERRORS: 0
               WARNINGS: 0
          ROWS_AFFECTED: 0
              ROWS_SENT: 0
          ROWS_EXAMINED: 0
CREATED_TMP_DISK_TABLES: 0
     CREATED_TMP_TABLES: 0
       SELECT_FULL_JOIN: 0
 SELECT_FULL_RANGE_JOIN: 0
           SELECT_RANGE: 0
     SELECT_RANGE_CHECK: 0
            SELECT_SCAN: 0
      SORT_MERGE_PASSES: 0
             SORT_RANGE: 0
              SORT_ROWS: 0
              SORT_SCAN: 0
          NO_INDEX_USED: 0
     NO_GOOD_INDEX_USED: 0
       NESTING_EVENT_ID: NULL
     NESTING_EVENT_TYPE: NULL
    NESTING_EVENT_LEVEL: 0
           STATEMENT_ID: 429596
2 rows in set (0,00 sec)

This time, the select from the performance_schema.events_statements_history returned two rows: one for the

SELECT

  statement and one for the

CALL

  command. We are interested in the data for the

SELECT

  statement. It is:

ROWS_SENT: 3
ROWS_EXAMINED: 11069933
  SELECT_SCAN: 3
NO_INDEX_USED: 1

So performance metrics are absolutely the same as for the original query.

OK, this was for the slow, not effective query. But what about the one that uses indexes?

Let’s craft another example. Consider a query:

SELECT COUNT(*), title FROM titles GROUP BY title;

And the stored procedure:

mysql> CREATE PROCEDURE my_second_test()
    -> BEGIN
    ->     SELECT COUNT(*), title FROM titles GROUP BY title;
    -> END
    -> |
Query OK, 0 rows affected (0,02 sec)

Now let’s call the query and examine data in the Performance Schema:

mysql> TRUNCATE performance_schema.events_statements_history;
Query OK, 0 rows affected (0,00 sec)

mysql> SELECT COUNT(*), title FROM titles GROUP BY title;
+----------+--------------------+
| COUNT(*) | title              |
+----------+--------------------+
|    97750 | Senior Engineer    |
|   107391 | Staff              |
|   115003 | Engineer           |
|    92853 | Senior Staff       |
|    15128 | Assistant Engineer |
|    15159 | Technique Leader   |
|       24 | Manager            |
+----------+--------------------+
7 rows in set (0,28 sec)

mysql> SELECT * FROM performance_schema.events_statements_history 
    -> WHERE thread_id=(SELECT thread_id FROM performance_schema.threads WHERE processlist_id=@@pseudo_thread_id)
    -> AND event_name != 'statement/sql/truncate'\G
*************************** 1. row ***************************
              THREAD_ID: 100414
               EVENT_ID: 30340666
           END_EVENT_ID: 30346280
             EVENT_NAME: statement/sql/select
                 SOURCE: init_net_server_extension.cc:93
            TIMER_START: 271987423077801000
              TIMER_END: 271987697673114000
             TIMER_WAIT: 274595313000
              LOCK_TIME: 137000000
               SQL_TEXT: SELECT COUNT(*), title FROM titles GROUP BY title
                 DIGEST: 3b63daf99fa8586784dcbc73f82b79e777e4fc074c37de3ebd25753f0d684a46
            DIGEST_TEXT: SELECT COUNT ( * ) , `title` FROM `titles` GROUP BY `title`
         CURRENT_SCHEMA: employees
            OBJECT_TYPE: NULL
          OBJECT_SCHEMA: NULL
            OBJECT_NAME: NULL
  OBJECT_INSTANCE_BEGIN: NULL
            MYSQL_ERRNO: 0
      RETURNED_SQLSTATE: NULL
           MESSAGE_TEXT: NULL
                 ERRORS: 0
               WARNINGS: 0
          ROWS_AFFECTED: 0
              ROWS_SENT: 7
          ROWS_EXAMINED: 443308
CREATED_TMP_DISK_TABLES: 0
     CREATED_TMP_TABLES: 1
       SELECT_FULL_JOIN: 0
 SELECT_FULL_RANGE_JOIN: 0
           SELECT_RANGE: 0
     SELECT_RANGE_CHECK: 0
            SELECT_SCAN: 1
      SORT_MERGE_PASSES: 0
             SORT_RANGE: 0
              SORT_ROWS: 0
              SORT_SCAN: 0
          NO_INDEX_USED: 0
     NO_GOOD_INDEX_USED: 0
       NESTING_EVENT_ID: NULL
     NESTING_EVENT_TYPE: NULL
    NESTING_EVENT_LEVEL: 0
           STATEMENT_ID: 431634
1 row in set (0,00 sec)

In this case, the picture is different:

ROWS_SENT: 7
     ROWS_EXAMINED: 443308
CREATED_TMP_TABLES: 1
       SELECT_SCAN: 1
     NO_INDEX_USED: 0
NO_GOOD_INDEX_USED: 0

This is still not a very effective query: it uses an index scan but it certainly uses the index.

Let’s check data for the stored procedure:

mysql> TRUNCATE performance_schema.events_statements_history;
Query OK, 0 rows affected (0,00 sec)

mysql> CALL my_second_test();
+----------+--------------------+
| COUNT(*) | title              |
+----------+--------------------+
|    97750 | Senior Engineer    |
|   107391 | Staff              |
|   115003 | Engineer           |
|    92853 | Senior Staff       |
|    15128 | Assistant Engineer |
|    15159 | Technique Leader   |
|       24 | Manager            |
+----------+--------------------+
7 rows in set (0,28 sec)
Query OK, 0 rows affected (0,28 sec)

mysql> SELECT * FROM performance_schema.events_statements_history 
    -> WHERE thread_id=(SELECT thread_id FROM performance_schema.threads WHERE processlist_id=@@pseudo_thread_id)
    -> AND event_name != 'statement/sql/truncate'\G
*************************** 1. row ***************************
              THREAD_ID: 100414
               EVENT_ID: 30354940
           END_EVENT_ID: 30360553
             EVENT_NAME: statement/sp/stmt
                 SOURCE: sp_head.cc:2210
            TIMER_START: 272124909174908000
              TIMER_END: 272125181823179000
             TIMER_WAIT: 272648271000
              LOCK_TIME: 222000000
               SQL_TEXT: SELECT COUNT(*), title FROM titles GROUP BY title
                 DIGEST: NULL
            DIGEST_TEXT: NULL
         CURRENT_SCHEMA: employees
            OBJECT_TYPE: PROCEDURE
          OBJECT_SCHEMA: employees
            OBJECT_NAME: my_second_test
  OBJECT_INSTANCE_BEGIN: NULL
            MYSQL_ERRNO: 0
      RETURNED_SQLSTATE: NULL
           MESSAGE_TEXT: NULL
                 ERRORS: 0
               WARNINGS: 0
          ROWS_AFFECTED: 0
              ROWS_SENT: 7
          ROWS_EXAMINED: 443308
CREATED_TMP_DISK_TABLES: 0
     CREATED_TMP_TABLES: 1
       SELECT_FULL_JOIN: 0
 SELECT_FULL_RANGE_JOIN: 0
           SELECT_RANGE: 0
     SELECT_RANGE_CHECK: 0
            SELECT_SCAN: 1
      SORT_MERGE_PASSES: 0
             SORT_RANGE: 0
              SORT_ROWS: 0
              SORT_SCAN: 0
          NO_INDEX_USED: 0
     NO_GOOD_INDEX_USED: 0
       NESTING_EVENT_ID: 30354939
     NESTING_EVENT_TYPE: STATEMENT
    NESTING_EVENT_LEVEL: 1
           STATEMENT_ID: 432318
*************************** 2. row ***************************
              THREAD_ID: 100414
               EVENT_ID: 30354939
           END_EVENT_ID: 30360554
             EVENT_NAME: statement/sql/call_procedure
<I omitted data for the CALL statement>

Counters again have exactly the same values as for the standalone statement:

ROWS_SENT: 7
     ROWS_EXAMINED: 443308
CREATED_TMP_TABLES: 1
       SELECT_SCAN: 1
     NO_INDEX_USED: 0
NO_GOOD_INDEX_USED: 0

So we have proof that the optimizer creates the same query plan no matter if the query was called inside the stored procedure or not.

Q: A few times when I was building a query for a table with multiple indexes, the EXPLAIN command shows me an index that is not clearly the best choice and I had to use FORCE INDEX in the query. I never understand why sometimes this happens, is it possible that sometimes the engine makes that mistake?

A: The engine can make a mistake for sure. If you want to understand more about such mistakes I recommend you to try

EXPLAIN FORMAT=JSON

  as described in these series as well as studying the Optimizer Trace. While the different formats of the

EXPLAIN

do not change the query plan,

EXPLAIN FORMAT=JSON

  provides more information on the optimizations that are used to resolve the query.

Q: I have a question about statuses. It’s about the Handler_read you mentioned. It keeps on increasing when queries are being executed. If the server has an uptime of more than 14 days, having 1000qps, the handler will be in the range of millions. Once we do a query tuning workshop, I would like to reset the Handlers counter. How to perform that? (just to see: for the same time range, whether the Handler_read decreases)

A: To reset counters for the session use the

FLUSH STATUS

  statement. Then you can run the original statement and examine which job needs to be done inside the engine to resolve it.

Here is the pseudo-code, showing how to do it:

FLUSH STATUS;
<RUN YOUR QUERY>
SHOW STATUS LIKE ‘Handler%’;

Q: Can you talk a bit on explain extended? How can we use that to further help us in tuning queries? Does it show the full query that the server/optimizer will execute in the correct format?

A:

EXPLAIN EXTENDED

  is included in the standard output of the

EXPLAIN

  command since version 5.7. If you started using MySQL earlier and preferred to run

EXPLAIN

  command you would now notice two differences.

First, this is the

“Filtered”

  column. This column shows how many rows the optimizer expects to be discarded from the output. For example, for the query:

mysql> EXPLAIN SELECT salary FROM salaries WHERE salary BETWEEN 158000 AND 159000\G
*************************** 1. row ***************************
id: 1
  select_type: SIMPLE
        table: salaries
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2838426
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0,00 sec)

It expects that the engine will read 2838426 rows but only 11.11% of them would be used to get the final result. This usually indicates that the query is not effective.

On the other way around, the query:

mysql> EXPLAIN SELECT DISTINCT(title) FROM titles\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: titles
   partitions: NULL
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 209
          ref: NULL
         rows: 442189
     filtered: 100.00
        Extra: Using index; Using temporary
1 row in set, 1 warning (0,00 sec)

Would use all 442189 retrieved rows to create the final result set (filtered: 100.00).

Another feature of the

EXPLAIN EXTENDED

  before version 5.7 and regular

EXPLAIN

  since version 5.7 is that it returns a query as it was rewritten by the optimizer in its diagnostic area, accessible by the

SHOW WARNINGS

  command.

For example, let’s take a query:

SELECT emp_no, first_name, last_name FROM employees 
WHERE emp_no IN (SELECT emp_no FROM salaries WHERE salary = (SELECT MAX(salary) FROM salaries));

And then run

EXPLAIN

  on it, followed by the

SHOW WARNINGS

 :

mysql> EXPLAIN SELECT emp_no, first_name, last_name FROM employees 
    -> WHERE emp_no IN (SELECT emp_no FROM salaries WHERE salary = (SELECT MAX(salary) FROM salaries))\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: employees
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 299113
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: <subquery2>
   partitions: NULL
         type: eq_ref
possible_keys: <auto_distinct_key>
          key: <auto_distinct_key>
      key_len: 4
          ref: employees.employees.emp_no
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 3. row ***************************
           id: 2
  select_type: MATERIALIZED
        table: salaries
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2838426
     filtered: 10.00
        Extra: Using where
*************************** 4. row ***************************
           id: 3
  select_type: SUBQUERY
        table: salaries
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2838426
     filtered: 100.00
        Extra: NULL
4 rows in set, 1 warning (0,00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `employees`.`employees`.`emp_no` AS `emp_no`,`employees`.`employees`.`first_name` AS `first_name`,
`employees`.`employees`.`last_name` AS `last_name` from `employees`.`employees` semi join (`employees`.`salaries`) 
where ((`<subquery2>`.`emp_no` = `employees`.`employees`.`emp_no`) 
and (`employees`.`salaries`.`salary` = (/* select#3 */ select max(`employees`.`salaries`.`salary`) from `employees`.`salaries`)))
1 row in set (0,00 sec)

In the

SHOW WARNINGS

  output, you see that the optimizer used semi-join optimization for the query: it practically converted one of the subqueries into a

JOIN

. The same query could be written as:

SELECT emp_no, first_name, last_name FROM employees JOIN salaries USING(emp_no) WHERE salary = (SELECT MAX(salary) FROM salaries);

If we turn semi-join optimization off we will see a different warning:

mysql> SET optimizer_switch='semijoin=off';
Query OK, 0 rows affected (0,00 sec)

mysql> EXPLAIN SELECT emp_no, first_name, last_name FROM employees WHERE emp_no IN (SELECT emp_no FROM salaries WHERE salary = (SELECT MAX(salary) FROM salaries))\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: employees
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 299113
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: SUBQUERY
        table: salaries
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2838426
     filtered: 10.00
        Extra: Using where
*************************** 3. row ***************************
           id: 3
  select_type: SUBQUERY
        table: salaries
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2838426
     filtered: 100.00
        Extra: NULL
3 rows in set, 1 warning (0,00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `employees`.`employees`.`emp_no` AS `emp_no`,`employees`.`employees`.`first_name` AS `first_name`,
`employees`.`employees`.`last_name` AS `last_name` from `employees`.`employees` 
where <in_optimizer>(`employees`.`employees`.`emp_no`,`employees`.`employees`.`emp_no` 
in ( <materialize> (/* select#2 */ select `employees`.`salaries`.`emp_no` from `employees`.`salaries` 
where (`employees`.`salaries`.`salary` = (/* select#3 */ select max(`employees`.`salaries`.`salary`) from `employees`.`salaries`)) ),
 <primary_index_lookup>(`employees`.`employees`.`emp_no` in <temporary table> on <auto_distinct_key> 
where ((`employees`.`employees`.`emp_no` = `<materialized_subquery>`.`emp_no`)))))
1 row in set (0,00 sec)

This feature could help to understand why one or particular optimization was used.

Q: Have you ever used the mysqltuner perl script and if so, would you suggest it as a short-term option?

A: Do you mean https://github.com/major/MySQLTuner-perl ?

I just run it on my laptop and here are the recommendations I got:

——– Recommendations —————————————————————————

General recommendations:

Reduce or eliminate unclosed connections and network issues

Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1

This is, probably, OK, unless you want to provide access to your MySQL server from the outside.

    We will suggest raising the ‘join_buffer_size’ until JOINs not using indexes are found.

See https://dev.mysql.com/doc/internals/en/join-buffer-size.html

(specially the conclusions at the bottom of the page).

I do not understand why to raise

join_buffer_size

  if it is not required by queries I use. It also could be dangerous if the number of connections increases.

    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this:

It is always good to read the user manual before changing options. I recommend you to do it every time you follow performance tuning advice. Even mine.

Variables to adjust:

join_buffer_size (> 256.0K, or always use indexes with JOINs)

Again, this could not be suggested without examining the queries.

    innodb_buffer_pool_size (>= 713.2M) if possible.

This conclusion is based on my data size and this is sane for my laptop with 32G RAM. But if the amount of data is larger than the amount of RAM on the machine this advice would not help you to identify the ideal InnoDB buffer pool size. In this case, I recommend you to start from this blog post and follow the links at the end of it.

innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

InnoDB redo log file size should hold as much data, so InnoDB can flush the data from the buffer pool and reuse log space and does not have to perform aggressive flushing.

If you have a small active data set but you write a lot you may have InnoDB’s total log files size greater than 25% of the buffer pool size. Or, if you have a large dataset, but your workload is almost read, you may have very small redo log files. This advice does not make any sense by itself.

In conclusion, I can say that MySQL Tuner is a product that performs static analysis of your MySQL or MariaDB instance and makes suggestions, based on what its authors think the best practices are.

Unfortunately, it is not possible to tune MySQL the same way for all use cases. InnoDB redo log file size above is just one example. There are other options that should be tuned differently depending on which workload you have.

I suggest you study the product you use and understand what and why you are changing. And it is better to start from the question: “Which problem do I need to solve?” instead of modifying random options.

For a general-purpose server, running on the dedicated machine, you can use option –innodb-dedicated-server. Though, in my opinion, it is far from the ideal too.

Q: Mixed based?

A: Is this the question for the slide “Asynchronous Replica”, explaining how binary logging format affects replication performance? Mixed-based binary log format instructs MySQL to log everything in the

STATEMENT

  format by default and automatically switch to the

ROW

  format when a user issues commands that are not safe for the

STATEMENT

  format. This means that safe queries that were slow on the source server and replicated in the

STATEMENT

  format, will experience the same performance issues on the replica too. Unsafe queries are not affected by this behavior, but tables that they modify have to have

PRIMARY KEY

  defined. Otherwise, the replica would have to do a full table scan for each row updated.

Have a burning question about MongoDB?

Database experts will be leading an open forum discussion based on attendees’ interests. Are you ahead of the curve? Just trying to keep up? Get the best of MongoDB.

Live Stream: October 13 at 11:30 am CT (30 min)

Watch this upcoming Open Mic on Open Source to learn the latest from the experts. Will Fromme, Percona Solutions Engineer, and Michal Nosek, Percona Enterprise Architect, will share their insights into what’s facing MongoDB admins right now.

Will and Michal will bring you up to speed on:

• How to convert a standalone MongoDB Community Edition to Percona Server for MongoDB
• How to convert an entire replica set running MongoDB Community Edition to Percona Server for MongoDB
• How to run Kubernetes Operators with Percona Server for MongoDB

Percona’s pro pairing will bring together in-depth operational knowledge of MongoDB, Percona’s open source tools, and a fully-supported MongoDB Community distribution. They’ll also remind you how to best scale a cluster, use the self-healing feature, backup and restore your database, and modify parameters.

Hear what other open source enthusiasts want to know about!

This is an open forum for a reason. If you get stumped sometimes, don’t worry; you’re not alone! Our hosts will take your questions in REAL-TIME. And you can remain anonymous even if you want to ask a question.

Register to Attend!

When you register, our hosts will make sure they provide a webinar worth the half-hour!

Space is limited. Secure your spot now and we’ll see you on October 13 at 11:30 am.

Thanks to everyone who attended last week’s webinar on Using Open Source Software to Optimize and Troubleshoot Your MySQL Environment; hopefully you’ve found the time we spent in Percona Monitoring and Management (PMM) useful.

We had a record-breaking number of questions during the talk and unfortunately weren’t able to answer them all live, so we decided to answer them separately. Also, there were several requests for best practices around installation and configuration. This is something we are considering for the next webinar in this series, so stay tuned!

If you weren’t able to attend, the recording is available for viewing. But now, without further ado, here are the questions that we didn’t have time to cover during the presentation.

Q: Can PMM also be used for a web hosting server (Cpanel, Directadminetc)?

PMM by default can monitor a node to provide vital statistics on the health of the host.  From there, you can use external exporters to monitor other applications and send the data to PMM to visualize and create alerts.

Q: Does it provide any query optimization suggestions if my query is bad? 

Not at present…that’s planned for the future query advisor

Q: How soon we will be able to use the alerting manager in production?

We are looking at late Sept to early Oct. When it’s ready, you will hear about it!

Q: Capturing Queries Data for performance checking can be costly and some monitoring systems capture data every few seconds. At what level of data is captured here and analyzed…live systems with lots of database traffic? What percentage (all of it,  2 seconds, 1 second, etc.)?

We adhere to ‘do no harm’ so the impact of PMM  is typically 1-4% of the busiest systems.  We offer custom resolutions to adjust the scrape frequency to balance the need for information with the need for performance.

Q: Are long-running queries captured that potentially slow down the system over time & shown as graph/alert? Also, is there potentially more than one instance of these types running over again by a user.?

This is something we are going to include in our Alerting capabilities (coming soon, see above).

Q: Can more than one of the metrics be compared against each other to gain more insight into a problem in graphical form? Can you in effect play with these graphs?

Yes, you can, this is in fact how most of the dashboards are designed, where we connect different metric series together to drive graphs that explain system performance.  While you may be able to edit the existing graphs, Percona recommends that you instead make a copy of the dashboard you’d like to modify and make your changes on the copy.  The reason for this is if you modify a dashboard distributed by PMM, it will be overwritten on the next upgrade, and you’ll lose your changes.

Q: Could you list what can be monitored using PMM? And explain what recommended plugins are available and what they are used for? 

Natively, any Linux system and pretty much all flavors of MySQL, MariaDB, MongoDB, and PostgreSQL. You can use external exporters to gather even more data than default and using Grafana as the basis for visualization of PMM allows you to create custom dashboards and a wealth of community plugins.

Q: Can you choose to monitor a particular set of users? Set of queries? Set of schema? 

You can filter it down to view based on username, particular schema, and then filter those results by particular query strings.  We can monitor as much or as little about your database as the user you define to pull data.

Q: How can we work on optimization when using cloud-based services like RDS where we have limited access?

PMM can monitor RDS instances and has simplified the connection and selection process of its remote monitoring capabilities.  We can provide nearly the same data as an on-prem database however we don’t have access to the node level statistics.

Q: For Oracle MySQL 5.7.29, if you have many tables/objects in the database, will the PMM query information_schema and load the DB?

We have a predefined limit of 1000 tables that will disable polling information schema but you can configure this to your liking both with the client and with remote monitoring. This CAN have a more significant impact on your system though especially with large table and row counts.

Q: At what point do I know I’ve done enough optimization? 

HA! It’s a never-ending game of cat and mouse considering the sheer volume of variables in play. It’s these times where monitoring data for before and after become vital.

Q: Can a database monitoring package be the source of database performance issues? In particular, mysqld_exporter is installed as a docker container, as I’m seeing “out of resources” on a trace on mysqld_exporter.

Of course, there are plenty of ways to generate database performance issues and it’s possible monitoring can result in some overhead. For an extreme example, here’s one way to replicate some overhead: start the pmm-client on a MySQL database and restore a blank DB from mysqldump. A few million rows at a time should generate LOTS of chaos and load between QAN and exporters. Our pmm client runs the exporter natively so no need to use a container.

Q: Is the query analytics somehow slowing down the database server as well? Or is it save to enable/use it without further impact?

The impact is minimal.  Most of the Query Analytics processing is done at the PMM server, the only impact to the client is retrieving the queries from slowlog or performance schema so this can have a bigger impact for the most extremely active DB’s but still should remain below 5% CPU hit.

Q: Did I understand correctly that PMM is not for RDS users and that AWS tools are available?

PMM certainly is for RDS! Since RDS is managed by AWS, PMM cannot collect CPU/Disk/Memory metrics but all MySQL metrics are still available even in RDS.

Q: Do you have any instructions/steps to install PMM to monitor MySQL RDS? 

• Gear icon ? PMM Inventory ? Add Instance
• Choose AWS/RDS Add Remote Instance
• Use your AWS credentials to view your available RDS & Aurora nodes
• Ensure that performance_schema is enabled

Watch the Recording

Fiserv, a Percona customer, will discuss the business case and results they achieved working with Percona to run MongoDB.  Register for a Fireside Chat taking place on Tuesday, July 27, 1 PM EDT!

If you use MongoDB to build business applications that need to scale quickly, the chances are that your company is paying dearly for enterprise licenses. In fact, proprietary license fees are continuing to go up and up. In addition, MongoDB may be pushing you towards their cloud service, Atlas.

If this sounds familiar, you should join our Fireside chat with Kim Thomas, Database Architect at Fiserv, on July 27, 2021, at 1:00 PM EDT.

Kim will be sharing Fiserv’s journey from MongoDB to Percona Software, Services, and Support. During this event you’ll discover:

• Why Fiserv used Percona to run MongoDB
• How Fiserv and Percona are building innovative solutions together
• How using open source databases gives Fiserv a competitive edge

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

Register for Webinar

About the Speaker 

Kim Thomas

Kim Thomas is a Database Architect at Fiserv, primarily responsible for delivering open source database solutions across the Enterprise. Knowledgeable in DBaaS, Relational, Columnar, Big Data, OLAP, OLTP, NoSQL, and DB Operator technologies, he has been working with various database technologies for the past 25 years.

Emily Lockhart, Percona’s Director of Customer Success will lead the conversation.

Join Percona experts Michal Nosek and Dimitri Vanoverbeke as they walk through the task of migrating a MongoDB database to Percona Server for MongoDB (PSMDB).

BY THE END OF THIS HANDS-ON WORKSHOP YOU WILL BE ABLE TO:

– Uninstall MongoDB and install Percona Server for MongoDB

– Convert a stand-alone MongoDB server to Percona Server for MongoDB

– Add Percona Server for MongoDB to an existing MongoDB replica set

– Convert a MongoDB replica set to a Percona Server for MongoDB replica set

Please join Michal Nosek and Dimitri Vanoverbeke on July 6, 2021, at 1:00 PM BST for their webinar Converting MongoDB to Percona Server for MongoDB.

Register for Webinar

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

Join our expert team for a live demo of the Percona Monitoring and Management (PMM) platform and witness the power of Percona support! Bring your questions to the table in a live, open forum discussion.  Brian Walters, Sr. Director of Solution Engineering, and Iwo Panowicz, Technical Support Engineer, will highlight features and pain points of Percona Monitoring and Management in an interactive demo of the product.  In this unique virtual event, we want to hear from you!

PMM is a free, best-of-breed, open source database monitoring solution. It helps you:

1. Reduce complexity
2. Save costs
3. Optimize performance
4. Improve the security of your business-critical database environments on-premise, in the cloud, or hybrid.

Please join Brian Walters, Sr. Director of Solution Engineering, and Iwo Panowicz, Technical Support Engineer, on June 17, 2021, at 11:00 CST for their webinar Open Mic on Open Source: Percona Monitoring and Management.

Register for Webinar

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

Do You Know How to Choose the Right MongoDB Shard Key for Your Business?

In our upcoming panel, Percona MongoDB experts Mike Grayson, Kimberly Wilkins, and Vinicius Grippa will discuss the complex issue of MongoDB shard key selection and offer advice on the measures to take if things go wrong.

Selecting the right shard key is one of the most important MongoDB design decisions you make, as it impacts performance and data management. Choosing the wrong shard key can have a disastrous effect on both.

MongoDB 5.0 is due to be released this summer, and it is likely to include another big change around sharding. Following last year’s 4.4 release that included refinable shard keys, we expect to see a new feature that allows for fully changeable shard keys for the first time.

But, even with refinable and changeable options, shard key selection will continue to be a crucial MongoDB task.

Join us for the panel, where Mike, Kimberly, and Vinicius will highlight some of the perils and pitfalls to avoid, as well as offering shard key best practices such as:

* Factors to consider when selecting your shard key

* Hidden “gotcha’s” around shard selection and architecture

* Examples of the worst shard keys our experts have seen

* What happens when you have a busted shard key, and how you can mitigate the impact

* What’s happening “under MongoDB’s hood” with all the changes?

* The future of shard keys for MongoDB

Please join Percona Technical Experts Mike Grayson, Kimberly Wilkins, and Vinicius Grippa on June 29, 2021, at 1 pm EDT for their webinar Unlocking the Mystery of MongoDB Shard Key Selection.

Register for Webinar

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

Considering a move to the cloud, but unsure which provider is right for you?

According to the results of our 2020 Open Source Data Management Software Survey, AWS continues to dominate the public cloud provider market, with 50% of respondents using its cloud platform in 2020, with Microsoft Azure quickly gaining market share with 23% adoption and Google Cloud not far behind at 18%.  With the “big three” players all in the space, the decision is anything but cut and dry.

Join Percona open source database experts Stephen Thorne and Barrett Chambers for an informative webinar on the good, the bad, and the ugly of each provider. They will discuss how you can make the best cloud database vendor decision for your business by navigating potential pitfalls, avoiding spiraling infrastructure costs, and truly understanding what ‘fully managed’ means in the cloud.

Our experts will address a number of key issues and questions, including:

1. The pros and cons of AWS, Azure, and GCP for database hosting
2. Known customer issues and restrictions of each provider
3. DBaaS cost to scale by cloud provider
4. What fully managed really means

By the end of this webinar, attendees will have a deeper knowledge of the options available for each provider as well as considerations, limitations, and costs associated with deploying cloud DBaaS solutions at scale.

Please join Stephen Thorne and Barrett Chambers, Percona Technical Experts, on Tuesday, June 8, 2021, at 11 am EDT for their webinar The Pros and Cons of AWS, Azure, and Google Cloud – Which Cloud Provider is Right for Your Business?

Register for Webinar

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