Results 1 to 15 of 15

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

  1. #1
    Registered Member
    Join Date
    Nov 2007
    Posts
    124

    Default 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
    Last edited by wizzy420; 01-09-2009 at 12:31 PM.

  2. #2
    Registered Member
    Join Date
    Nov 2007
    Posts
    124

  3. #3
    Registered Member
    Join Date
    Nov 2007
    Posts
    124

    Default 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:BI which is installed but may not be in package management, so for server and client do a --nodeps *after* checking to make sure Perl:BI 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

  4. #4
    Registered Member
    Join Date
    Nov 2007
    Posts
    124

    Default

    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)

  5. #5
    Registered Member
    Join Date
    Nov 2007
    Posts
    124

    Default 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)

  6. #6
    d_t
    d_t is offline
    Registered Member
    Join Date
    Sep 2003
    Location
    Bucharest
    Posts
    241

    Default

    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")

  7. #7
    Registered Member
    Join Date
    Nov 2004
    Posts
    127

    Default

    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?

  8. #8
    arp
    arp is offline
    Registered User cPanel Partner NOC Badge
    Join Date
    Jun 2010
    Posts
    2

    Default

    +1 we always do it manually.

  9. #9
    Registered Member cPanel Partner NOC Badge
    Join Date
    Apr 2003
    Location
    Houston, TX
    Posts
    405
    cPanel Access Level

    Root Administrator

    Default

    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.

  10. #10
    Registered Member cPanel Partner NOC Badge
    Join Date
    Jan 2003
    Posts
    17

    Default

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

  11. #11
    Registered Member
    Join Date
    Nov 2004
    Posts
    127

    Default

    Quote Originally Posted by Doobla View Post
    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 MariaDBownload: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.
    Last edited by panayot; 06-04-2010 at 07:02 PM.

  12. #12
    Registered Member
    Join Date
    Jun 2009
    Posts
    21

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

    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?

  13. #13
    Registered Member
    Join Date
    Aug 2001
    Location
    Brisbane, Australia
    Posts
    280

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

    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-...tml#post903352

  14. #14
    Registered Member
    Join Date
    Jun 2009
    Posts
    21

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

    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

  15. #15
    Registered Member
    Join Date
    Aug 2001
    Location
    Brisbane, Australia
    Posts
    280

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

    fuji, glad my posts have been helpful

Similar Threads

  1. cPanel DNSONLY - Track Queries Per Domain
    By sajanNOPPIX in forum Bind / DNS / Nameserver Issues
    Replies: 0
    Last Post: 07-05-2012, 05:52 PM
  2. mysql.user table lost
    By mystikzen in forum Database Discussions
    Replies: 1
    Last Post: 12-19-2011, 09:34 AM
  3. CPU / MySQL queries limitations per user
    By mihai.aldea in forum General Discussion
    Replies: 3
    Last Post: 12-20-2010, 04:31 AM
  4. mysql.user table is not updated
    By taalaibek in forum General Discussion
    Replies: 3
    Last Post: 05-09-2006, 08:34 AM
  5. Installing MySQL with Transactional Table Support?
    By brianteeter in forum General Discussion
    Replies: 0
    Last Post: 06-13-2002, 02:09 AM
bargain