mysql autorization, initialization problem

MySQL & MariaDB Other MySQL® Questions
1

Hi

Of course we have heavy loaded servers as everyone :slight_smile:
But last time i saw that servers start to work in really stragne way
Every hour for about 5 minutes they got “authoriztaion and Initialization problem”
In fact i dont really know what the problem is but in processlist i can see lot of:

  • Connect: login
  • init DB
    connections
    Much more that connections like “Query” or “Sleep”

Can you say me how to diagnose what is wrong ?

Server version: 5.0.92-50-log Percona SQL Server,

2

Hi,

Have you check error log? are you seeing any specific error for connections, authorizations etc? can you provide here if possible? are you seeing those connect and init status for long time in show processlist? do you have replication ? are there any network issue between master and slave?

3

Nothing interesting in logs, yes we got replica. Network issue not possible the same switch client <-> server

Little investigatione and seems that innodb was a problem especially var innodb_io_capacity
Change it to 500 then 2000 and problem go away
We use 4 x ssd raid 10 disk so i think 2000 suit well

I think that problem growing constantlly because innodb is going to be more and more popullar in lot of cms, shops engine
Of course if you have any suggestion post it please

4

Before change array utilization was always less that 2%
After is little higher 2-5% but sometimes goes to 10-20%

5

Szym, 5.0.92 - very old MySQL/InnoDB…
Yes, InnoDB is the most popular engine for MySQL now, also a default one since 5.5.
Check your transaction logs size. See this presentation for more hints: [url]Percona Live - Open Source Database Conference

6

Unfortunately problem appear from time to time
Im tring to see what is wrong with pt-stalk (trigger is --variable=Command --function=processlist --match=Connect --threshold 10 --cycles=3)
but cant see anything interesting, of course it can not be hardware related probem - cpu less than 30%, 16 G RAM - 10G cached, array less than 10 %

Can you suggest which status or variables should i watch much more than others ?

7

Lot of tests and bingo.
Problem is caused by too much … “flush privileges”
Maybe helps someone