Installing Percona MySQL to track queries per user and table access volumes

Installing Percona MySQL - Track SQL CPU, SQL queries, table access volumes, by user

[ SCROLL TO POST #4 TO SEE WHAT USEFUL STUFF YOU CAN DO ]

[ Question below answered, instruction in Post #3 ]

Hey!

I want to install the Percona MySQL server so I can track user stats. This version has been built with the Google userstatsv2.patch so you can do things like look at queries per table to see what users are doing per table.

I've used it before but had it overwritten when 5.0.67 update came out.

Would I be correct that all I need to do to prevent cPanel from updates is going to server config -> updates and set MySQL update to Never?

Word of warning for those who get the idea to try this. You will need to be sure you preserve dbs, make sure all old MySQL packages are removed, be sure you install devel libraries, and also will need to recompile (Easy Apache) apache/php/etc to be sure they are compiled with the correct libraries.

W

 

For those interested:

http://www.percona.com/docs/wiki/release:start
http://www.percona.com/percona-lab.html

Steve

 

Instructions

Here is my tutorial on installing the Percona server.

create a work directory in /root - such as /root/percona

Go to download area
http://www.percona.com/docs/wiki/release:start

Get latest 5.0 (assuming when you read this cPanel is still using 5.0.x) RPMs for your distro

download client, devel, server, shared, test -- NOTE - binaries are x86_64, NOT i386

make a backup copy of /etc/init.d/mysql

make a backup of your /var/lib/mysql directory
(check for sufficient free space)
(this is just a precaution, removing MySQL does not delete the database files)
rsync -a -v -S -H --progress /var/lib/mysql/ /root/mysql-backup

stop all services
/etc/init.d/httpd stop ; /etc/init.d/exim stop ; /etc/init.d/pure-ftpd stop ; /etc/init.d/dovecot stop ; /etc/init.d/cpanel stop ; /etc/init.d/lfd stop ; /etc/init.d/munin-node stop ; /etc/init.d/crond stop ; /etc/init.d/mysql stop ; /etc/init.d/exim stop ; /etc/init.d/portsentry stop

Re-run the rsync so your mysql backup is complete and updated

do a ps to make sure all stopped

rpm -qa | grep -i mysql

yum remove (all mysql installed)

rpm -iv (the percona files)

Note that server and client have a dep on Perl::DBI which is installed but may not be in package management, so for server and client do a --nodeps *after* checking to make sure Perl::DBI is the only thing it is asking for

Once all installed, check /etc/init.d/mysql to be sure it is still there.

reboot server. Check all started, MySQL started. Start mysql command line and poke on it to be sure it is there.

run easyapache and recompile apache/php/etc

Go to Server Configuration -> Update Config and set MySQL to "never", save, double check it has been done

Test new Apache to be sure server is working

 

Now that that is done, here we have a useful example. With statistics we can, for exmaple, see which table is getting slammed and who the user is.

I have, of course, obfuscated some information.

Here we see one user who, with one table, has almost 20x the reads of even the next highest use table.

Code:

mysql> SELECT * FROM information_schema.table_STATISTICS ORDER BY ROWS_READ;
+-------------------------+----------------------------------+-----------+--------------+------------------------+
| TABLE_SCHEMA            | TABLE_NAME                       | ROWS_READ | ROWS_CHANGED | ROWS_CHANGED_X_INDEXES |
+-------------------------+----------------------------------+-----------+--------------+------------------------+
| XXXXXXXX_writeQQQ       | ZZZZZZlog                        |         0 |           59 |                    118 |
| XXXXXXXX_YYYYYYnowriter | ZZZZZZlog                        |         0 |           99 |                    198 |
| XXXXXXXX_YYYYYYnowriter | history                          |         0 |            1 |                      1 |
| XXXXXXXX_XXXXXXXX       | XXXXXXXX_LLLLLL_page             |         1 |            0 |                      0 |
| XXXXXXXX_wri111it       | profile_values                   |         1 |            0 |                      0 |
| XXXXXXXX_pi111111hie    | users_roles                      |         1 |            0 |                      0 |
| XXXXXXXX_cogling        | url_alias                        |         1 |            0 |                      0 |
| XXXXXXXX_wri111it       | links_1232                       |         1 |            0 |                      0 |
| XXXXXXXX_plk            | QQQQQk_i18n_1232                 |        50 |            0 |                      0 |
| XXXXXXXX_pi1111111ie    | poll_choices                     |        52 |            0 |                      0 |
| XXXXXXXX_pi11111lm      | sessions                         |        93 |            9 |                     27 |
| XXXXXXXX_heretic        | wp_links                         |        96 |            0 |                      0 |
| XXXXXXXX_heretic        | wp_terms                         |        96 |            0 |                      0 |
| XXXXXXXX_dlnet          | biblio_contributor               |        96 |            0 |                      0 |
| XXXXXXXX_dlnet          | vocabulary                       |        99 |            0 |                      0 |
| XXXXXXXX_writelit       | vocabulary                       |       100 |            0 |                      0 |
| XXXXXXXX_heretic        | wp_term_taxonomy                 |       102 |            0 |                      0 |
| XXXXXXXX_writelit       | cache_views                      |       104 |            0 |                      0 |
| XXXXXXXX_pi111111hie    | file_revisions                   |       108 |            0 |                      0 |
| XXXXXXXX_XXXXXXXX       | XXXXXXXX_search_dataset          |       111 |            0 |                      0 |
| XXXXXXXX_writelit       | cache_content                    |       114 |            0 |                      0 |
| XXXXXXXX_writelit       | users                            |       114 |            0 |                      0 |
| XXXXXXXX_YYYYYYnowriter | 1232_ZZZZZZ                      |       117 |            0 |                      0 |
| XXXXXXXX_dlnet          | 1232_revisions                   |       117 |            0 |                      0 |
| XXXXXXXX_plk            | QQQQQk_1232_revisions            |       122 |            0 |                      0 |
| XXXXXXXX_plk            | QQQQQk_users                     |       122 |            0 |                      0 |
| XXXXXXXX_XXXXXXXX       | XXXXXXXX_links                   |       301 |            0 |                      0 |
| XXXXXXXX_plk            | QQQQQk_1232                      |       304 |            0 |                      0 |
| XXXXXXXX_plk            | QQQQQk_term_data                 |       315 |            0 |                      0 |
| XXXXXXXX_cadaad         | cache                            |       981 |            0 |                      0 |

| XXXXXXXX_XXXXXXXX       | XXXXXXXX_cache                   |      1080 |          214 |                    428 |
| XXXXXXXX_pi111111m      | term_1232                        |      1429 |            0 |                      0 |
| XXXXXXXX_metaphor       | system                           |      1449 |            0 |                      0 |
| XXXXXXXX_dlnet          | biblio                           |      1477 |            0 |                      0 |
| XXXXXXXX_YYYYYYnowriter | term_1232                        |     10860 |            0 |                      0 |
| XXXXXXXX_YYYYYYnowriter | users                            |     11881 |            1 |                      3 |
| XXXXXXXX_dlnet          | menu_links                       |     24270 |            0 |                      0 |
| XXXXXXXX_wr111lit       | term_data                        |     24821 |            0 |                      0 |
| XXXXXXXX_YYYYYYnowriter | variable                         |     88165 |          174 |                    174 |
| XXXXXXXX_YYYYYYnowriter | system                           |     91715 |         1218 |                   2436 |
| XXXXXXXX_XXXXXXXX       | XXXXXXXX_system                  |    159094 |            0 |                      0 |
| XXXXXXXX_YYYYYYnowriter | 1232                             |    235106 |            0 |                      0 |
| XXXXXXXX_XXXXXXXX       | XXXXXXXX_amazon1232              |   3550771 |            0 |                      0 |
+-------------------------+----------------------------------+-----------+--------------+------------------------+
356 rows in set (0.00 sec)

 

CPU Usage Example

Our next example show CPU usage by MySQL user.

Code:

mysql> SELECT USER,CPU_TIME FROM information_schema.user_statistics ORDER BY CPU_TIME\G
*************************** 1. row ***************************
    USER: XXXXXXXX_noid3
CPU_TIME: 0
*************************** 2. row ***************************
    USER: root
CPU_TIME: 0
*************************** 3. row ***************************
    USER: YYYYYYYY_micval
CPU_TIME: 0
*************************** 4. row ***************************
    USER: eximstats
CPU_TIME: 0
*************************** 5. row ***************************
    USER: XXXXXXXX_noid5
CPU_TIME: 1
*************************** 6. row ***************************
    USER: XXXXXXXX_noid4
CPU_TIME: 1
*************************** 7. row ***************************
    USER: XXXXXXXX_noid2
CPU_TIME: 15
*************************** 8. row ***************************
    USER: YYYYYYYY_1234567
CPU_TIME: 20
8 rows in set (0.00 sec)