Re: Dramatic mysqld slowness when running compiles in parallel

[Sverre Froyen <sverre%viewmark.com@localhost>]

On Tuesday 07 October 2008, Andrew Doran wrote:
> On Mon, Oct 06, 2008 at 04:42:58PM -0600, Sverre Froyen wrote:
> > On Monday 06 October 2008, Andrew Doran wrote:
> > > What happens with 'skip-thread-priority' in my.cnf?
> >
> > With this option, the slowdown is almost eliminated.  I can still observe
> > occasional delays but they are of the order of a second not ten seconds
> > or more.  I probably would never have noticed the issue if I had used
> > this option earlier.
>
> It's a bug in MySQL. Look at the PRI column in the top output and notice
> that many of the MySQL threads have very low priority, meaning that they'll
> only run when the system is ~idle. I think someone was going to file a bug
> report with the MySQL people but I don't know if that ever happened. We
> should at least disable this in pkgsrc.

I wonder if it would be worth it to modify the priority values that mysql 
uses.  Currently, the are:

#define INTERRUPT_PRIOR 10
#define CONNECT_PRIOR   9
#define WAIT_PRIOR      8
#define QUERY_PRIOR     6

It's the QUERY_PRIOR value that is used for the thread that handles my insert 
queries so slowly.

I notice that Linux, FreeBSD, and NetBSD are all using different ranges for 
thread priority settings.  I'm not sure what the MySQL priorities are 
targeted for, but assuming they refer to the (negative of)nice values as used 
by Linux, I applied the attached patch to both mysql5-client and 
mysql5-server.  It may be an overkill but it certainly removes any effect of 
the compiles on the execution time for the database inserts.


>       http://bugs.mysql.com/bug.php?id=18526
>
> You won't notice this problem with NetBSD 4.0 or revivesa because neither
> is realtime capable.

Makes sense.

Thanks again!

Sverre


--- sql/mysql_priv.h.orig       2008-10-07 14:03:26.000000000 -0600
+++ sql/mysql_priv.h    2008-10-07 14:04:19.000000000 -0600
@@ -282,10 +282,10 @@
 #define WAIT_PRIOR     0
 #define QUERY_PRIOR    2
 #else
-#define INTERRUPT_PRIOR 10
-#define CONNECT_PRIOR  9
-#define WAIT_PRIOR     8
-#define QUERY_PRIOR    6
+#define INTERRUPT_PRIOR 53
+#define CONNECT_PRIOR  52
+#define WAIT_PRIOR     51
+#define QUERY_PRIOR    49
 #endif /* __WIN92__ */
 
        /* Bits from testflag */