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

[wizzy420]

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

 

[wizzy420]

For those interested:

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

Steve

 

[wizzy420]

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

 

[wizzy420]

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.

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)

 

[wizzy420]

CPU Usage Example

Our next example show CPU usage by MySQL user.

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)

 

[d_t]

Very interesting topic!

It will be great if cPanel will include this patch and create a nice frontend in WHM in order to see what really happen on mysql server (instead of existing irrelevant "CPU/Memory/MySQL Usage")

 

[panayot]

Great tutorial! I installed the percona binaries on a new server. I plan to install it on all other servers soon as well.

Do you know if the statistics data is written to a table on disk or it is inmemory table? Are statistics being reset on mysql restart?

 

[arp]

+1 we always do it manually.

 

[DomineauX]

I would love to see Percona MySQL as an integrated option in cPanel as that would enable much better user accounting and could lead to wonderful automated handling of suspending an account for ridiculous usage.

 

[Doobla]

Out of curiosity, have you done any work to determine how much overhead this generates on the server?

 

[panayot]

Out of curiosity, have you done any work to determine how much overhead this generates on the server?

The overhead is only in cpu and a little in ram. On most web servers cpu is usually idle and most load comes from disks. userstats shows which users use most intensively mysql (and thus disk) so I am sure gain is much more. And on our servers we have plenty of ram. I believe overhead is negligible.

By the way we have switched to MariaDB:Download:MariaDB 5.2.0-beta - Askmonty.org which also has users-stats built in.

My recomendation is to install it on a fresh hosting server, because upgrading older mysql versions could cause some problems. (but we actually upgraded two production servers 5.0 and 5.1 to 5.2 without problems - just run mysql_upgrade after that, and dump/reload databases that cannot be auto-converted)

We take database cpu usage and combine it with psacct php usage (php must run as cgi, or mod_fcgid) and we get a total cpu minutes per user. Anyone above our cpu limit gets a warning email We are quite happy with the system and manage to keep servers calm even with more than 1000 accounts on them.

 

[fujipadam]

Can I ask why you switched to MariaDB?

Also do you know if going frmo Mysql 5.1 to Percona 5.5 is going to be an issue and any tips?

 

[eva2000]

MariaDB 5.2.x just is faster and scales better for both MyISAM and InnoDB usage from my MySQL comparison benchmarks.

No issues going from MySQL 5.0.x / 5.1 to MySQL 5.5, Percona 5.5 or MariaDB 5.2.x. Note WHM 11.32 will have MySQL 5.5 http://forums.cpanel.net/f145/mysql-5-5-ga-178651.html#post903352

 

[fujipadam]

thanks! yes, I have been waiting for mysql 5.5 support in cpanel but I am seriously considering percona + xtradb - its either that or get another server which is not palatable to my wallet.Since almost all my tables are innodb, I think I am going to pick percona over mariaDB for now.

The only issue was that I couldn't find a good guide on moving to percona in cpanel env. I even suggested that in the percona forums with no response. But with thanks to your tutorial above I should be taking hte plunge next weekend.

Btw I have enjoyed reading your blog posts and had refered to it for varnish plugin sometime back too. Great job and thanks for sharing your knowledge!

fuji

 

[eva2000]

fuji, glad my posts have been helpful