Useful Tools from the Web

These are my favourites, used for every day common jobs. This is not a complete review of them (you can find it anywhere…) but just a list with the reason why I choose them.

SQL Script Utility
(http://sqlsrvscrpt.codeplex.com/wikipage?title=DBScript%20Utility)

Management Studio is an incredible management tool…but if you need to script massivly indexes it can not help you. I looked for a tool like Script Utility years ago to support a replica scenario. Sometimes the replica had to be re-initialized so the tables and their indexes (created only on the subscriber) where dropped. I needed a good and fast way to script indexes on the subscriber to re-create them after every replica re-initialize.
This tool (completely FREE from ms codeplex) was a good solution to solve this problem.

 scriptutility

Classic Shell
(http://www.classicshell.net)

After the upgrade to Win 8 of my pc I tried for months to work without the start button. No way!  I can not work without it.
It’s not just a link box: it’s the complete list of evertything I have installed on my pc and a fast shortcut to everything (control panel, computer management, common used apps, last opened docs and so on…). Win 8.1 will re-introduce it but with different purposes (just a rapid link to start screen…).

Classic Shell for me was the final solution. Well done, fast, easy to install, no (or just a little…) configuration needed. A great tool for a simple but important job. And it’s FREE.

Note: If you want you can customize the start button  with custom logos or skins. My suggestion is http://www.enkeria.se/download/ClassicShell/Classic_Shell_Buttons_by_Enkeria.zip . It’s a good download to have a mswin-like logo (from release 4 Ms Win logo is not available any more in the product) and other funny alternatives.

 classicshell
Pubblicità

Queries to see rapidly what your MySql is doing NOW

Those queries are useful to admin MySQL instances using mysql client.

1) Check active processes/connections running

mysql> show processlist;

the previous statement doesn’t show all queries text running. To see the complete statements running use:

mysql> show processlist;
--or--
mysql> show full processlist G;

the running queries statements could bee quite long. Use the “G” modifier to see processes in different output paragraphs

2) Check UPTIME
MySql store main infos under metadata views querable using “SHOW STATUS” command. Those are some of the most quick and useful.

mysql> show status like '%uptime%';
+---------------------------+--------+
| Variable_name             | Value  |
+---------------------------+--------+
| Uptime                    | 880329 |
| Uptime_since_flush_status | 8347   |
+---------------------------+--------+
2 rows in set (0.01 sec)

3) Check Connections

mysql> show status like '%conn%';
+-----------------------------------------------+---------+
| Variable_name                                 | Value   |
+-----------------------------------------------+---------+
| Aborted_connects                              | 0       |
| Connection_errors_accept                      | 0       |
| Connection_errors_internal                    | 0       |
| Connection_errors_max_connections             | 0       |
| Connection_errors_peer_address                | 0       |
| Connection_errors_select                      | 0       |
| Connection_errors_tcpwrap                     | 0       |
| Connections                                   | 4094917 |
| Max_used_connections                          | 222     |
| Performance_schema_session_connect_attrs_lost | 0       |
| Ssl_client_connects                           | 0       |
| Ssl_connect_renegotiates                      | 0       |
| Ssl_finished_connects                         | 0       |
| Threads_connected                             | 120     |
+-----------------------------------------------+---------+
14 rows in set (0.00 sec)

4) Check Caching Status
Caching mechanism are managed using startup parameters. First you have to check if caching is enabled:

mysql> show variables like '%query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      |
| query_cache_limit            | 2097152  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 33554432 |
| query_cache_type             | ON       |
+------------------------------+----------+
5 rows in set (0.00 sec)

If query caching is enabled caching metadata infos are visible simply in this way.

mysql> show status like 'QCache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 957      |
| Qcache_free_memory      | 15523704 |
| Qcache_hits             | 1978478  |
| Qcache_inserts          | 132298   |
| Qcache_lowmem_prunes    | 6037     |
| Qcache_not_cached       | 5969     |
| Qcache_queries_in_cache | 3389     |
| Qcache_total_blocks     | 8396     |
+-------------------------+----------+
8 rows in set (0.00 sec)

5) Check Instance Wait Time using PERFORMANCE_SCHEMA
Starting with release 5.5 MySql introduce the new performance_schema. Now performance critical conditions can be analized quering a group of system tables realtime dinamically updated.
First we’ll check if performance_schema is turned on. This option can be turned on using startup parameter file.

mysql> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+

If performance_schema is started there are many tables available to check your instance status (use SHOW TABLES under performance schema to see the complete list). Here we’ll start with one of the most simple, used to analize instance wait conditions in realtime:

mysql> SELECT * FROM events_waits_currentG
*************************** 1. row ***************************
            THREAD_ID: 0
             EVENT_ID: 5523
           EVENT_NAME: wait/synch/mutex/mysys/THR_LOCK::mutex
               SOURCE: thr_lock.c:525
          TIMER_START: 201663444489586
            TIMER_END: 201660494576112
           TIMER_WAIT: 86526
                SPINS: NULL
        OBJECT_SCHEMA: NULL
          OBJECT_NAME: NULL
          OBJECT_TYPE: NULL
OBJECT_INSTANCE_BEGIN: 142270668
     NESTING_EVENT_ID: NULL
            OPERATION: lock
      NUMBER_OF_BYTES: NULL
                FLAGS: 0
...