1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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

Discussion in 'General Discussion' started by wizzy420, Jan 6, 2009.

  1. wizzy420

    wizzy420 Member

    Joined:
    Nov 13, 2007
    Messages:
    123
    Likes Received:
    1
    Trophy Points:
    18
    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
     
    #1 wizzy420, Jan 6, 2009
    Last edited: Jan 9, 2009
  2. wizzy420

    wizzy420 Member

    Joined:
    Nov 13, 2007
    Messages:
    123
    Likes Received:
    1
    Trophy Points:
    18
  3. wizzy420

    wizzy420 Member

    Joined:
    Nov 13, 2007
    Messages:
    123
    Likes Received:
    1
    Trophy Points:
    18
    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
     
    DomineauX likes this.
  4. wizzy420

    wizzy420 Member

    Joined:
    Nov 13, 2007
    Messages:
    123
    Likes Received:
    1
    Trophy Points:
    18
    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. wizzy420

    wizzy420 Member

    Joined:
    Nov 13, 2007
    Messages:
    123
    Likes Received:
    1
    Trophy Points:
    18
    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. d_t

    d_t Member

    Joined:
    Sep 20, 2003
    Messages:
    243
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Bucharest
    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. panayot

    panayot Member

    Joined:
    Nov 18, 2004
    Messages:
    126
    Likes Received:
    0
    Trophy Points:
    16
    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. arp

    arp New Member

    Joined:
    Jun 4, 2010
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    +1 we always do it manually.
     
  9. DomineauX

    DomineauX Member

    Joined:
    Apr 12, 2003
    Messages:
    404
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Houston, TX
    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. Doobla

    Doobla New Member

    Joined:
    Jan 2, 2003
    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    1
    Out of curiosity, have you done any work to determine how much overhead this generates on the server?
     
  11. panayot

    panayot Member

    Joined:
    Nov 18, 2004
    Messages:
    126
    Likes Received:
    0
    Trophy Points:
    16
    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.
     
    #11 panayot, Jun 4, 2010
    Last edited: Jun 4, 2010
  12. fujipadam

    fujipadam New Member

    Joined:
    Jun 25, 2009
    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    1
    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. eva2000

    eva2000 Member

    Joined:
    Aug 14, 2001
    Messages:
    298
    Likes Received:
    4
    Trophy Points:
    18
    Location:
    Brisbane, Australia
  14. fujipadam

    fujipadam New Member

    Joined:
    Jun 25, 2009
    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    1
    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. eva2000

    eva2000 Member

    Joined:
    Aug 14, 2001
    Messages:
    298
    Likes Received:
    4
    Trophy Points:
    18
    Location:
    Brisbane, Australia
    fuji, glad my posts have been helpful :)
     

Share This Page