Emergency root@localhost db connection and service
In cloud due to lack of the db engine to allocate resources fairly, we run into lots of situations of running out of connection handles.
During situations when max_connections is saturated, an Admin or a DBA is powerless to kill offending queries.
mysqladmin commands will also fail in such scenarios with a "too many connections".
Logging into the mysql client also yields the same error.
Usually what happens is that end users/customers queries that are long running or slow will start stacking up which will put more load on the server in turn slowing it for another (usually next slowest) customer queries which also start stacking up.
Eventually all connections are maxed out and we have no option but to recycle mysql service or kill the mysqld process which not desirable if it can be avoided.
I'd like to see if we can get Drizzle to implement a way of saving one extra connection at all times for "root@localhost" if no other "root@localhost" connections are already in place.
Sort of a super root@localhost user that has the edge for administrative use.
Blueprint information
- Status:
- Complete
- Approver:
- None
- Priority:
- High
- Drafter:
- None
- Direction:
- Approved
- Assignee:
- Andrew Hutchings
- Definition:
- Approved
- Series goal:
- Accepted for 7.0
- Implementation:
-
Implemented
- Milestone target:
-
2010-12-20
- Started by
- Andrew Hutchings
- Completed by
- Andrew Hutchings
Whiteboard
Rackspace request
So initial thoughts on this is an administration plugin server side separate from the mysql/drizzle protocols. A new administration client tool (maybe a re-written resurrection of drizzleadmin) would connect to this to do things like kill connections/queries bypassing the max_connections limitations.
After discussions with the other developers the above appears to be the best way to go.
Work items:
Add max_connections: DONE
Design plugin/client specifications: DONE
Fix libdrizzle connect error handling: DONE
Modify connection code: DONE
Create client: DONE
DTR test cases: DONE
Documentation: DONE
Implementation (subject to change):
1. Drizzle no longer has a max_connections limiter. This will be added back in, per-protocol.
2. We will check for certain conditions, connection from an admin IP, a special flag (given by admin client) and user/pass.
2a. This connection will only be allowed to do admin command (eg. PING/KILL/
3. A new utility will be created based on the Drizzle client to connect to this socket so the user can administer the server.
How it has actually been implemented so far:
1. Added per protocol max-connections option (also modified connection counters to be per-protocol)
2. Added an admin option to the protocol
3. Added a drizzleadmin client tool which is a modified drizzle client
4. Added options for mysql and drizzle protocols to have an ip filter for admin connections
5. Added a test case for this
So, now you can connect via. drizzleadmin to the drizzle server using "root" user and an ip address listed in --mysql-
Kept things like SELECT for drizzleadmin after all since users may need to use data_dictionary. Eventually new command sequences can be added which will only be allowed in drizzleadmin (such as catalog manipulation)
