Rds Through Monyog

[tom.bodley]

Hi,

We've recently migrated to AWS and are utilising their database service RDS. We use MONyog to monitor our RDS instances, but as RDS doesn't provide a MySQL user with super privileges it is impossible to kill processes which aren't owned by the current user (i.e. you cannot kill processes using MONyog).

Amazon provide a number of stored procedures to manage this elsewhere (mysql.rds_kill(PROCESS_ID) and mysql.rds_kill_query(PROCESS_ID). What we require is a way to notify MONyog that the database it is monitoring is an RDS instance, so it knows to 'CALL mysql.rds_kill(PROCESS_ID)' instead of 'KILL PROCESS_ID'

Let me know if you have any questions regarding this.

Thanks,

Tom.

[peterlaursen]

You cannot do that with MONyog.

RDS is a 'Relational Database Service' (only). It is not a virtualized OS and you dont have shell access. With RDS you can monitor MySQL (whatever can be done from a MySQL client) but not what requires a shell. Also MONyog cannot access the Amazon RDS API.

Besides you are using a confusing terminology IMO. You cannot have *AWS and RDS*. You have either *AWS or RDS*. They are two different services from Amaxon. AWS is a full-flegded (virtualized) OS with shell access and RDS is not. it is a 'Database Service' (only).

We have some blogs about same:

http://blog.webyog.c…eginners-guide/

http://blog.webyog.c…out-amazon-rds/

[peterlaursen]

Also a user connected to MySQL on RDS is not MySQL root/superuser. And then she will only be able to kill his own threads from inside the MySQL connection.

But I think we have not checked details with RDS for more than a year. We will check next week if they have added some options since then.

[tom.bodley]

Hi Peter,

Thanks for your replies.

AWS is a collection of services Amazon have put together to create their cloud solution (e.g. RDS, Route53, S3, VPC). The fully-fledged virtualized OS you are referring to is EC2 not AWS.

I understand we'll never have shell access to RDS, but the feature I was requesting doesn't require shell access, it requries a user with the correct privileges to call mysql.rds_kill(PROCESS_ID) or mysql.rds_kill_query(PROCESS_ID).

Thanks,

Tom

[peterlaursen]

You are right both about the terminology and that this can be done by a Stored Procedure call as you described it.

There is one (internal for MONyog) problem however. We detect RDS instances by parsing the hostname in Javascript and disable some options accordingly when saving server details. But we do not store information that “here we have an RDS instance”. So after saving the server details to the connection.data database in MONyog's database we don't know anymore that this is an RDS instance.

So to support this request we would need a field/column/flag in connection.data like “is_RDS 0|1” so that we can execute different KILL commands when a server is an RDS instance or not when user selects this option. We also need to check a few more details, such as privileges required to CALL this Procedure (is execute privilege to it enough and does a user have this privilege as default?).

We will reply later this week if we are able to include KILL option for RDS soon.

Also BTW: do you know if there is a similar option to FLUSH [GLOBAL] STATUS on an RDS instance?

[tom.bodley]

Hi Peter,

Execute privilege is enough to use mysql.rds_kill(PROCESS_ID), by default the RDS root user has global execute privileges so can call this command.

The stored procedures baked-in to RDS by Amazon are as follows (SELECT `name` FROM mysql.proc WHERE `name` LIKE 'rds_%'):

• rds_kill – Terminate user session
• rds_kill_query – Terminate query
• rds_collect_global_status_history – Takes a snapshot of global status on demand
• rds_disable_gsh_collector – Disables global status snapshots
• rds_disable_gsh_rotation – Disables global status table rotation (mysql.rds_global_status_history & mysql.rds_global_status_history_old)
• rds_enable_gsh_collector – Enables snapshots of global status at intervals specified by rds_set_gsh_collector
• rds_enable_gsh_rotation – Enabled global status table rotation (mysql.rds_global_status_history & mysql.rds_global_status_history_old) at intervals set by rds_set_gsh_rotation
• rds_rotate_general_log – Copies mysql.general_log to mysql.general_log_backup and clears down mysql.general_log
• rds_rotate_global_status_history – Copies mysql.rds_global_status_history to mysql.rds_global_status_history and clears down mysql.rds_global_status_history
• rds_rotate_slow_log- Copies mysql.slow_log to mysql.slow_log_backup and clears down mysql.slow_log
• rds_set_gsh_collector – Interval used by rds_enable_gsh_collector
• rds_set_gsh_rotation – Interval used by rds_enable_gsh_rotation
• rds_skip_repl_error – Skips a replication error

'FLUSH STATUS' works in RDS, but will only clear down the local variables. 'FLUSH GLOBAL STATUS' doesn't appear to be a valid command (We're running 5.5.27, is it available in a newer release?).

If you are using the RDS provided GSH table (mysql.rds_global_status_history) then 'FLUSH GLOBAL STATUS' can be imitated by executing 'CALL mysql.rds_rotate_global_status_history'

Thanks,

Tom

[peterlaursen]

You are right about FLUSH GLOBAL STATUS. I should have checked docs (http://dev.mysql.com/doc/refman/5.5/en/flush.html) before writing.

I actually blogged about the issue myself long time ago here http://blog.webyog.com/2011/11/27/flush-status-surprise/. Since MySQL 5.0 there is no equivalent of what FLUSH STATUS did before 5.0. And that is what FLUSH GLOBAL STATUS should do IMO! But again: you are right: it is not there.

The culprit with MONyog is that FLUSH STATUs will reset the status variable 'uptime_since_flush_status' to “0” (docs: “Some global variables may be reset to zero as well”). And this is one of the ways we have to discard information collected by MONyog before some specific time (the other one is to define a 'base_time' setting in MONyog). FLUSH STATUS is irrevocable – MONyog's own 'base_time' is not.

We will check the options with RDS.

[tom.bodley]

Hi Peter,

Just wondering whether there has been any progress on this request?

Let me know if you have any more questions.

Thanks,

Tom

[peterlaursen]

There is not and there will not be before 5.5.7 has been released as a GA. The developer team is struggling with some nastey issues. We hope to have this solved or at least identified in a couple of days, but it is quite unpredictable if it will take one day or one week..

The only thing I can promise now, is that we will discuss on next review meeting in team (planned for tomorrow) and update here.

[peterlaursen]

We have discussed. We will implement this after finishing releases in the 5.5x tree (that are all about stability and performance). Expected timeframe is 4-6 weeks.

[tom.bodley]

Thanks Peter, that sounds great. Could you keep me updated with any progress and/or changes to the expected timefreame.

Thanks again,

Tom

[Author]

Posts