[MDEV-4705] PROCESS - Add process priority in PROCESSLIST, add SQL interface to renice query priority - JIRA

Details

Type: Task
Status: Open
Priority: Minor
Resolution: Unresolved
Fix Version/s: None
Component/s: None
Labels:
None

Description

implement a interface that change query process priority (nice value in linux), and add a column to PROCESSLIST

–
maybe we could implement a new token inside queries?

SELECT /* NICE_VALUE=-20 */ * FROM table WHERE ....
UPDATE /* NICE_VALUE=-20 */ table SET xxxx .....

and a process control interface

RENICE <query id> <new nice value>

–

after query done (end of query), the server should go back to a normal priority (windows) or a nice value (linux)
set a global variable @@system_thread_default_priority (default = 0) to set default process priority

in windows we have this priorities:
IDLE_PRIORITY_CLASS
BELOW_NORMAL_PRIORITY_CLASS
NORMAL_PRIORITY_CLASS
ABOVE_NORMAL_PRIORITY_CLASS
HIGH_PRIORITY_CLASS
REALTIME_PRIORITY_CLASS
http://msdn.microsoft.com/en-us/library/windows/desktop/ms685100(v=vs.85).aspx

in linux we have a value from -20 (high priority) to 20 (low priority), it's the nice value, we can renice via console or execute mysql with nice -n 999 mysqld .... command

i think that mysqld should not use realtime priority but i don't know if we can use it, it's something that automation control systems use, control a GPIO for example, not a general process schedule feature

the point is... when using windows we have a priority value, when using linux (posix) we have another priority value, maybe we should have a linux nice value/windows class priority interface ? maybe:

linux	windows
from -20 to -11	HIGH_PRIORITY_CLASS
from -10 to -1	ABOVE_NORMAL_PRIORITY_CLASS
0	NORMAL_PRIORITY_CLASS
from 1 to 10	BELOW_NORMAL_PRIORITY_CLASS
from 11 to 20	IDLE_PRIORITY_CLASS

maybe use the microsoft 'nice values' (base priority) starting from 1 to 31
or use the cygwin version of nice_to_winprio and winprio_to_nice:
http://cygwin.com/cgi-bin/cvsweb.cgi/src/winsup/cygwin/miscfuncs.cc?rev=1.98&content-type=text/x-cvsweb-markup&cvsroot=src

/* Get a default value for the nice factor.  When changing these values,
   have a look into the below function nice_to_winprio.  The values must
   match the layout of the static "priority" array. */
int
winprio_to_nice (DWORD prio)
{
  switch (prio)
    {
      case REALTIME_PRIORITY_CLASS:
        return -20;
      case HIGH_PRIORITY_CLASS:
        return -16;
      case ABOVE_NORMAL_PRIORITY_CLASS:
        return -8;
      case NORMAL_PRIORITY_CLASS:
        return 0;
      case BELOW_NORMAL_PRIORITY_CLASS:
        return 8;
      case IDLE_PRIORITY_CLASS:
        return 16;
    }
  return 0;
}

/* Get a Win32 priority matching the incoming nice factor.  The incoming
   nice is limited to the interval [-NZERO,NZERO-1]. */
DWORD
nice_to_winprio (int &nice)
{
  static const DWORD priority[] =
    {
      REALTIME_PRIORITY_CLASS,          /*  0 */
      HIGH_PRIORITY_CLASS,              /*  1 */
      HIGH_PRIORITY_CLASS,
      HIGH_PRIORITY_CLASS,
      HIGH_PRIORITY_CLASS,
      HIGH_PRIORITY_CLASS,
      HIGH_PRIORITY_CLASS,
      HIGH_PRIORITY_CLASS,              /*  7 */
      ABOVE_NORMAL_PRIORITY_CLASS,      /*  8 */
      ABOVE_NORMAL_PRIORITY_CLASS,
      ABOVE_NORMAL_PRIORITY_CLASS,
      ABOVE_NORMAL_PRIORITY_CLASS,
      ABOVE_NORMAL_PRIORITY_CLASS,
      ABOVE_NORMAL_PRIORITY_CLASS,
      ABOVE_NORMAL_PRIORITY_CLASS,
      ABOVE_NORMAL_PRIORITY_CLASS,      /* 15 */
      NORMAL_PRIORITY_CLASS,            /* 16 */
      NORMAL_PRIORITY_CLASS,
      NORMAL_PRIORITY_CLASS,
      NORMAL_PRIORITY_CLASS,
      NORMAL_PRIORITY_CLASS,
      NORMAL_PRIORITY_CLASS,
      NORMAL_PRIORITY_CLASS,
      NORMAL_PRIORITY_CLASS,            /* 23 */
      BELOW_NORMAL_PRIORITY_CLASS,      /* 24 */
      BELOW_NORMAL_PRIORITY_CLASS,
      BELOW_NORMAL_PRIORITY_CLASS,
      BELOW_NORMAL_PRIORITY_CLASS,
      BELOW_NORMAL_PRIORITY_CLASS,
      BELOW_NORMAL_PRIORITY_CLASS,
      BELOW_NORMAL_PRIORITY_CLASS,
      BELOW_NORMAL_PRIORITY_CLASS,      /* 31 */
      IDLE_PRIORITY_CLASS,              /* 32 */
      IDLE_PRIORITY_CLASS,
      IDLE_PRIORITY_CLASS,
      IDLE_PRIORITY_CLASS,
      IDLE_PRIORITY_CLASS,
      IDLE_PRIORITY_CLASS,
      IDLE_PRIORITY_CLASS,
      IDLE_PRIORITY_CLASS               /* 39 */
    };
  if (nice < -NZERO)
    nice = -NZERO;
  else if (nice > NZERO - 1)
    nice = NZERO - 1;
  DWORD prio = priority[nice + NZERO];
  return prio;
}

Gliffy Diagrams

Attachments

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Sergei Golubchik added a comment - 2015-03-05 20:49

nice() is per-process, we'd need to use pthread_setschedparam() on linux and SetThreadPriority() on Windows.

But MySQL used to set thread priorities in the past. They were removed in 5.5.

Show

Sergei Golubchik added a comment - 2015-03-05 20:49 nice() is per-process, we'd need to use pthread_setschedparam() on linux and SetThreadPriority() on Windows. But MySQL used to set thread priorities in the past. They were removed in 5.5.

Hide

Permalink

roberto spadim added a comment - 2015-03-05 21:20

hum, any idea why they removed?

Show

roberto spadim added a comment - 2015-03-05 21:20 hum, any idea why they removed?

PROCESS - Add process priority in PROCESSLIST, add SQL interface to renice query priority

Details

Description

Gliffy Diagrams

Attachments

Activity

People

Dates

Agile