In this blog, I will provide answers to the Q & A for the Basic Internal Troubleshooting Tools for MySQL Server webinar.
First, I want to thank everybody for attending my February 15, 2018, webinar on troubleshooting tools for MySQL. The recording and slides for the webinar are available here. Below is the list of your questions that I was unable to answer fully during the webinar.
Q: How do we prevent the schema prefix from appearing in the show create view. This is causing issue with restore on another server with a different DB. See the issue here and reproducible test case: https://gist.github.com/anonymous/30cb138b598fec46be762789397796b6
A: I shortened the example in order to fit it in this blog:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | mysql> create table t1(f1 int); Query OK, 0 rows affected (3.47 sec) mysql> create view v1 as select * from t1; Query OK, 0 rows affected (0.21 sec) mysql> show create view v1G *************************** 1. row *************************** View: v1 Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`f1` AS `f1` from `t1` character_set_client: utf8 collation_connection: utf8_general_ci 1 row in set (0.00 sec) mysql> select * from information_schema.views where table_schema='test'G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: v1 VIEW_DEFINITION: select `test`.`t1`.`f1` AS `f1` from `test`.`t1` CHECK_OPTION: NONE IS_UPDATABLE: YES DEFINER: root@localhost SECURITY_TYPE: DEFINER CHARACTER_SET_CLIENT: utf8 COLLATION_CONNECTION: utf8_general_ci 1 row in set (0.00 sec) |
The issue you experienced happened because even if you created a view as SELECT foo FROM table1;, it is stored as SELECT foo FROM your_schema.table1;. You can see it if you query the *.frm file for the view:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | sveta@Thinkie:~/build/ps-5.7/mysql-test$ cat var/mysqld.1/data/test/v1.frm TYPE=VIEW query=select `test`.`t1`.`f1` AS `f1` from `test`.`t1` md5=5840f59d1287385629fcb9b948e53d96 updatable=1 algorithm=0 definer_user=root definer_host=localhost suid=2 with_check_option=0 timestamp=2018-02-24 10:27:45 create-version=1 source=select * from t1 client_cs_name=utf8 connection_cl_name=utf8_general_ci view_body_utf8=select `test`.`t1`.`f1` AS `f1` from `test`.`t1` |
You cannot prevent the schema prefix from being stored. If you restore the view on a different server with a different database name, you should edit the view definition manually. If you already restored the view that points to a non-existent schema, just recreate it. VIEW is metadata only and does not hold any data, so this operation is non-blocking and will run momentarily.
Q: What is thread/sql/compress_gtid_table in performance_schema.threads?
A: thread/sql/compress_gtid_table is name of the instrument. You can read this and other instruments as below:
- thread/ is a group of instruments. In this case, it is the instruments that are visible in the THREADS table.
- thread/sql/ is the group of instruments that are part of the server kernel code. If you are not familiar with MySQL source tree, download the source code tarball and check its content. The main components are:
- sql – server kernel
- storage – where storage engines code located ( storage/innobase is InnoDB code, storage/myisam is MyISAM code and so on)
- vio – input-output functions
- mysys – code, shared between all parts of the server
- client – client library and utilities
- strings – functions to work with strings
This is not full list. For more information consult MySQL Internals Manual
- thread/sql/compress_gtid_table is the name of the particular instrument.
Unfortunately, there is no link to source code for instrumented threads in the table THREADS, but we can easily find them in the sql directory. The function compress_gtid_table is defined in sql/rpl_gtid_persist.cc and we can check comments and find what it is doing:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | /** The main function of the compression thread. - compress the gtid_executed table when get a compression signal. @param p_thd Thread requesting to compress the table @return @retval 0 OK. always, the compression thread will swallow any error for going to wait for next compression signal until it is terminated. */ extern "C" { static void *compress_gtid_table(void *p_thd) { |
You can also find the description of mysql.gtid_executed compression in the User Reference Manual.
You can follow the same actions to find out what other MySQL threads are doing.
Q: How does a novice on MySQL learn the core basics about MySQL. The documentation can be very vast which surpasses my understanding right now. Are there any good intro books you can recommend for a System Admin?
A: I learned MySQL a long time ago, and a book that I can recommend written for version 5.0. This is “MySQL 5.0 Certification Study Guide” by Paul DuBois, Stefan Hinz and Carsten Pedersen. The book is in two parts: one is devoted to SQL developers and explains how to run and tune queries. The second part is for DBAs and describes how to tune MySQL server. I asked my colleagues to suggest more modern books for you, and this one is still on the list for many. This is in all cases an awesome book for beginners, just note that MySQL has changed a lot since 5.0 and you need to deepen your knowledge after you finish reading this book.
Another book that was recommended is “MySQL” by Paul DuBois. It is written for beginners and has plenty of content. Paul DuBois has been working on (and continues to work on) the official MySQL documentation for many years, and knows MySQL in great detail.
Another book is “Murach’s MySQL” by Joel Murach, which is used as a course book in many colleges for “Introduction into Databases” type classes.
For System Administrators, you can read “Systems Performance: Enterprise and the Cloud” by Brendan Gregg. This book talks about how to tune operating systems for performance. This is one of the consistent tasks we have to do when administering MySQL. I also recommend that you study Brendan Gregg’s website, which is a great source of information for everyone who is interested in operating system performance tuning.
After you finish the books for novices, you can check out “High Performance MySQL, 3rd Edition” by Peter Zaitsev, Vadim Tkachenko, Baron Schwartz and “MySQL Troubleshooting” by Sveta Smirnova (yours truly =) ). These two books require at least basic MySQL knowledge, however.
Q: Does the database migration goes on same way? Do these tools work for migration as well?
A: The tools I discussed in this webinar are available for any version of MySQL/Percona/MariaDB server. You may use them for migration. For example, it is always useful to compare configuration ( SHOW GLOBAL VARIABLES) on both “old” and “new” servers. It helps if you observe performance drops on the “new” server. Or you can check table definitions before and after migration. There are many more uses for these tools during the migration process.
Q: How can we take backup of a single schema from a MySQL AWS instance without affecting the performance of applications. An AWS RDS instance to be more clear. mysqldump we cannot use in RDS instance in the current scenario.
A: You can connect to your RDS instance with mysqldump from your local machine, exactly like your MySQL clients connect to it. Then you can collect a dump of a single database, table or even specify the option –where to limit the resulting set to only a portion of the table. Note, by default mysqldump is blocking, but if you backup solely transactional tables (InnoDB, TokuDB, MyRocks) you can run mysqldump with the option --single-transaction, which starts the transaction at the beginning of the backup job.
Alternatively, you can use AWS Database Migration Service, which allows you to replicate your databases. Then you can take a backup of a single schema using whatever method you like.
Q: Why do some sites suggest to turn off information and performance schema? Is it important to keep it on or turn it off?
A: You cannot turn off Information Schema. It is always available.
Performance Schema in earlier versions (before 5.6.14) was resource-consuming, even if it was idle while enabled. These limitations were fixed a long time ago, and you don’t need to keep it off. At least unless you hit some new bug.
Q: How do we handle storage level threshold if a data file size grows and reaches max threshold when unnoticed? Can you please help on this question?
A: Do you mean what will happen if the data file grows until filesystem has no space? In this case, clients receive the error "OS error code 28: No space left on device" until space is freed and mysqld can start functioning normally again. If it can write into error log file (for example, if it is located on different disk), you will see messages about error 28 in the error log file too.
Q: What are the performance bottlenecks when enabling performance_schema. Is there any benchmark we can have?
A: Just enabling Performance Schema in version 5.6 and up does not cause any performance issue. With version 5.7, it can also start with almost zero allocated memory, so it won’t affect your other buffers. The Performance Schema causes impact when you enable particular instruments. Most of them are instruments that start with the name events_waits_*. I performed benchmarks on effects of particular Performance Schema instruments and published them in this post.
Q: Suggest us some tips about creating a real-time dashboards for the same as we have some replication environment? it would be great if you can help us here for building business level dashboards
A: This is topic for yet another webinar or, better still, a tutorial. For starters, I recommend you to check out the “MySQL Replication” dashboard in PMM and extend it using the metrics that you need.
Thanks for attending the webinar on internal troubleshooting tools for MySQL.
Troubleshoot if MySQL Server has left through Cognegic’s MySQL Enterprise Backup
Ensure if constantly your MySQL server has left at that point may be conceivable this happens if server coordinated out or if there is wrong or too expansive packets. Clearly it appears to be exceptionally repulsive in light of the fact that client doesn’t see how to understand it. For the brisk and quick arrangement of this issue, you need to make an immediate association with MySQL Remote Support or MySQL Remote Service. We at Cognegic give profundity arrangement in regards to MySQL issues. So contact to MySQL Server 5.0 Support and fix it rapidly.
For More Info: https://cognegicsystems.com/
Contact Number: 1-800-450-8670
Email Address- [email protected]
Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801