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.

            Gliffy Diagrams

              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
                  5 older comments
                  Hide
                  Permalink
                  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?

                  Show
                  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?
                  Hide
                  Permalink
                  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.

                  Show
                  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.
                  Hide
                  Permalink
                  antonkhalikov Anton Khalikov 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.

                  Show
                  antonkhalikov Anton Khalikov 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.
                  Hide
                  Permalink
                  ratzpo Rasmus Johansson added a comment -

                  Launchpad bug id: 1018694

                  Show
                  ratzpo Rasmus Johansson added a comment - Launchpad bug id: 1018694
                  Hide
                  Permalink
                  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.

                  Show
                  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

                    • Assignee:
                      Unassigned
                      Reporter:
                      antonkhalikov Anton Khalikov
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      1 Start watching this issue

                      Dates

                      • Created:
                        Updated: