Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-775

LP:1018694 - CPU_TIME counter from USER_STATISTICS gets wrong after some uptime

Details

    Description

      Hi there

      We collect user statistics and run "SELECT * FROM INFORMATION_SCHEMA.USER_STATISTICS; FLUSH USER_STATISTICS" every 5 minutes and then put received values to rrd databases. We noticed that after some uptime CPU_TIME counter goes mad and starts to show incredibly high usage values. After restarting MariaDB process it goes back to normal for some time. Please see attached graphs as examples. One can notice a huge drop down of CPU_TIME counter on both graphs. These are graphs for a random customer from two different MariaDB servers.

      Platform used: Debian Squeeze amd64, MariaDB 5.2.12-MariaDB-mariadb115~squeeze-log from official package.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-673 LP:706991 - incorrect Bytes_received in user_statistics table

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-773 LP:611878 - user_statistics.busy_time is unreliable

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-4578 information_schema.processlist reports incorrect value for Time (2147483647)

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            View 6 older comments
            elenst Elena Stepanova added a comment -

            Re: CPU_TIME counter from USER_STATISTICS gets wrong after some uptime
            Hi Anton,

            Just to clarify, it started showing the same wrong values for all clients simultaneously, right?

            elenst Elena Stepanova added a comment - Re: CPU_TIME counter from USER_STATISTICS gets wrong after some uptime Hi Anton, Just to clarify, it started showing the same wrong values for all clients simultaneously, right?
            elenst Elena Stepanova added a comment -

            Re: CPU_TIME counter from USER_STATISTICS gets wrong after some uptime
            I suppose it's one of the bunch of bugs filed in regard to broken user stats:

            https://bugs.launchpad.net/percona-server/+bug/608027
            https://bugs.launchpad.net/percona-server/+bug/924872
            https://bugs.launchpad.net/percona-server/+bug/728082

            Some of them say that a fix is committed in 5.1, so maybe there is something useful to port.
            Assigning to Sergei to decide.

            elenst Elena Stepanova added a comment - Re: CPU_TIME counter from USER_STATISTICS gets wrong after some uptime I suppose it's one of the bunch of bugs filed in regard to broken user stats: https://bugs.launchpad.net/percona-server/+bug/608027 https://bugs.launchpad.net/percona-server/+bug/924872 https://bugs.launchpad.net/percona-server/+bug/728082 Some of them say that a fix is committed in 5.1, so maybe there is something useful to port. Assigning to Sergei to decide.
            ratzpo Rasmus Johansson (Inactive) added a comment -

            Launchpad bug id: 1018694

            ratzpo Rasmus Johansson (Inactive) added a comment - Launchpad bug id: 1018694
            antonkhalikov Anton Khalikov (Inactive) added a comment -

            Re: CPU_TIME counter from USER_STATISTICS gets wrong after some uptime
            Hi Elena,

            Yes, the values started to be counted wrong for all clients at the same moment (simultaneously). But no, the values are not the same for everyone if you meant that. It looks like MariaDB at some moment started to multiply real CPU_TIME by a factor but this factor is different for every single client. So those who used to have high cpu usage values before the problem occured became to have incredibly high cpu usage values (take a look at second graph, the maximum there is about 14000% which is just impossible), but not every client had so high values.

            If you want to know how we calculate these percents, it's simple math. We flush counters every 5 minutes which means 300 seconds. So if a customer's cpu_time within this 5 minutes interval equals to 300 seconds, it's 100%. If cpu_time within 5 minutes only counted to 30 seconds, its 10%. And so on. If a customer has 600 seconds cpu_time within 5 minutes interval it's 200% and means customer's processes used 2 processor cores at 100% each.

            Hope this helped.

            antonkhalikov Anton Khalikov (Inactive) added a comment - Re: CPU_TIME counter from USER_STATISTICS gets wrong after some uptime Hi Elena, Yes, the values started to be counted wrong for all clients at the same moment (simultaneously). But no, the values are not the same for everyone if you meant that. It looks like MariaDB at some moment started to multiply real CPU_TIME by a factor but this factor is different for every single client. So those who used to have high cpu usage values before the problem occured became to have incredibly high cpu usage values (take a look at second graph, the maximum there is about 14000% which is just impossible), but not every client had so high values. If you want to know how we calculate these percents, it's simple math. We flush counters every 5 minutes which means 300 seconds. So if a customer's cpu_time within this 5 minutes interval equals to 300 seconds, it's 100%. If cpu_time within 5 minutes only counted to 30 seconds, its 10%. And so on. If a customer has 600 seconds cpu_time within 5 minutes interval it's 200% and means customer's processes used 2 processor cores at 100% each. Hope this helped.
            elenst Elena Stepanova added a comment - - edited

            See also MDEV-773, MDEV-673. There have been and still are several bugs related to user_statistics, some of them fixed, some still open.

            elenst Elena Stepanova added a comment - - edited See also MDEV-773 , MDEV-673 . There have been and still are several bugs related to user_statistics, some of them fixed, some still open.

            People

              Unassigned Unassigned
              antonkhalikov Anton Khalikov (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.