Hi Steve,
You go to APIs and Services > Credentials in the console and you can either take one of the keys that are already there or you can create a new one with the "Create Credentials" button. This is the only way to create API keys, but they can be reused any amount of times. There is a feature request to create them with a gcloud command, however. You can follow it here if you're interested. https://issuetracker.google.com/76227920
Let me know if you have any other questions or concerns and I'll be happy to help.
Cheers!
Nicholas Google Cloud Platform Support, Montreal Created By: Steve Coffman (8/7/20, 2:46 PM - 5 days ago) Thanks! How do I generate the API key from the commandline again? -Steve Created By: Nicholas E (8/7/20, 1:18 PM - 5 days ago)Was this helpful?YesNo Hi Steve,
Thanks for getting back to me,
For a request to get time series from across that metric you would have something looking like this:
curl
'https://monitoring.googleapis.com/v3/projects/[PROJECT]/timeSeries?filter=metric.type%20%3D%20%22cloudsql.googleapis.com%2Fdatabase%2Fpostgresql%2Fnum_backends%22&interval.endTime=[YEAR-MONTH-DAY]T[HOUR]%3A[MINUTE]%3A[SECOND]Z&interval.startTime=[YEAR-MONTH-DAY]T[HOUR]%3A[MINUTE]%3A[SECOND]Z&view=FULL&key=[YOUR_API_KEY]'
--header 'Authorization: Bearer [YOUR_ACCESS_TOKEN]'
--header 'Accept: application/json'
--compressed
Note that time will have to be in Zulu time, hence the "Z" at the end of the time string. This will give you a full output of the given connections to all of the databases on your project. The important information in this output will be a INT64 value looking something like this:
Indicates the database: "metric": { "labels": { "database": "cloudsqladmin"
and this, indicating the connections active at that time: "value": { "int64Value": "3"
You can further fine tune your query using the examples in this doc [1] and combining them with the fields that the API method can use [2].
I hope this answers your question! If you have any other questions or concerns please feel free to reach out and I'll be happy to help.
Sincerely,
Nicholas Google Cloud Platform Support, Montreal
[1] https://cloud.google.com/monitoring/api/v3/filters [2] https://cloud.google.com/monitoring/api/ref_v3/rest/v3/projects.timeSeries/list Created By: Steve Coffman (8/6/20, 5:18 PM - 6 days ago) Thanks for confirming this. Can you give me an example curl command that would return MQL time series results for our database? Created By: Nicholas E (8/6/20, 2:52 PM - 6 days ago)Was this helpful?YesNo Hi Steve,
Thank you for your patience,
So far it doesn't seem like there is any way of querying only the most recent value for a monitored metric, and from my research it seems like the only API methods available in either the Monitoring API or in any of the Cloud SQL APIs would be the time series list method you described earlier. Would including a shorter time series interval work for your use case? And from my understanding data is displayed most recent to latest, would it be feasible to run a slightly wider time series interval and grab the first value that appears?
Let me know what you think. If you have any other questions or concerns please do not hesitate to reach out and I’ll be happy to help.
Sincerely,
Nicholas Google Cloud Platform Support, Montreal
[1] https://cloud.google.com/monitoring/api/ref_v3/rest [2] https://cloud.google.com/monitoring/api/ref_v3/rpc [3] https://cloud.google.com/monitoring/api/ref_v3/rest/v3/projects.timeSeries/query [4] https://cloud.google.com/monitoring/api/ref_v3/rest/v3/projects.timeSeries/list [5] https://cloud.google.com/sql/docs/postgres/admin-api/rest Created By: Nicholas E (8/5/20, 4:31 PM - 7 days ago)Was this helpful?YesNo Hi Steve,
Good to hear we're making progress!
I'm going to continue looking into your questions and allow you some time to experiment and ruminate on our current solution, so you can expect to hear back from me in the next 24 hours at the latest.
If you have any other questions or concerns in the meantime please do not hesitate to reach out and I’ll be happy to help.
Cheers,
Nicholas Google Cloud Platform Support, Montreal Created By: Steve Coffman (8/5/20, 2:54 PM - 7 days ago) I'm still interested in pursuing the metric API path, but if we were going to use a database query to monitor the database connections, I think the postgres equivalent to the MySQL command you gave is to:
SELECT count(distinct(numbackends)) FROM pg_stat_database; If you need to figure out where the connections are going, you can break down the connections by database.
SELECT datname, numbackends FROM pg_stat_database; To further investigate connections to a specific database, query pg_stat_activity.
SELECT * FROM pg_stat_activity WHERE datname='mycooldb'; Created By: Steve Coffman (8/5/20, 2:50 PM - 7 days ago) Thanks! This is very helpful.
Our jobs are generally run on a nightly or hourly basis, so they can afford to wait around for a few minutes until the database connections are not congested. If the monitoring data is 210 to 270 seconds old, it's possible multiple concurrent jobs will exceed the max connections without realizing it, but it seems unlikely. Is there an API to get just the latest metric value, rather than the time series?
By the way this is a postgres database, and we would like to avoid making a database connection to check the number of database connections. We could have a single polling application that would write the current value to datastore for other jobs to access, but this would also consume a database connection and have some potential for lag.
Best Regards, -Steve Created By: Nicholas E (8/5/20, 1:44 PM - 7 days ago)Was this helpful?YesNo Hi Steve,
Thank you for your patience,
During my research I looked into the possibility of using the Monitoring metrics to query connections to the database, but it seems like both “database/network/connections” and “database/postgresql/num_backends” metrics sample data every 60 seconds and that data is only available after 210 seconds. I’m unsure if your workloads are time sensitive and this may be a blocker if they are, so I explored other options.
The Cloud SQL and MySQL REST API includes a field “connection name”, but this is the name of the Cloud SQL instance used in connection strings. And as you said earlier you were able to get similar results using the Monitoring API but could not replicate exactly.
Then I came across the SHOW PROCESSLIST; command[1] in MySQL. This shows all active connections currently running on the database, which, with a bit of code to be run every x minutes to read the current connections, sounds like exactly what you want to accomplish in your use case.
Does this solution sound like what you’re looking for? I’ll be awaiting your reply in order to proceed. If you have any other questions or concerns please do not hesitate to reach out and I’ll be happy to help.
Sincerely,
Nicholas Google Cloud Platform Support, Montreal
[1] https://cloud.google.com/sql/docs/mysql/diagnose-issues#processlist Created By: Nicholas E (8/5/20, 11:55 AM - 7 days ago)Was this helpful?YesNo Hi Steve,
Thank you for your in depth explaination,
I believe I understand what you're going for here now. At the moment I don't have any suggestions as to how best to get the metric num_backends in the way you're looking for, but now that I understand what you need I can start looking what we can do.
I'll keep looking into this today and provide you with an update by end of day. If you have any other questions or concerns in the meantime please don't hesitate to reach out and I'll be happy to help.
Best regards,
Nicholas Google Cloud Platform Support, Montreal Created By: Steve Coffman (8/4/20, 8:49 PM - 8 days ago) Thank you very much for your patience. I'm sorry I was not more clear before.
We have many different uncoordinated jobs (in both Dataflow and GKE) which connect to our database and can consume all of the available database connections. We would like the jobs to gracefully delay (exponential backoff w/jitter) when the database connections are too close to the maximum number. In order to achieve this, they need to be able to interrogate the current number of database connections without consuming a database connection to find out this information. Google's monitoring API has the num_backends information, as I can see it in some dashboards, so I was trying to craft a request to get that current num_backends. If additional information such as previous values are also retrieved, this is not at all required, but also not a problem if there is no more convenient method.
It appears that when I inspect the dashboard, the actual REST requests is (omitting headers and API key url parameter):
curl -v -H "Authorization: Bearer $(gcloud --project 'khan-academy' auth print-access-token)" |
--header "Content-Type: application/json"
'https://monitoring.clients6.google.com/v3/projects/khan-academy/timeSeries?filter=metric.type%3D%22cloudsql.googleapis.com%2Fdatabase%2Fpostgresql%2Fnum_backends%22%20AND%20(resource.label.database_id%3D%22khan-academy%3Akhan-production%22)&aggregation.groupByFields=metric.label.database&aggregation.crossSeriesReducer=REDUCE_SUM&aggregation.perSeriesAligner=ALIGN_MEAN&clientRequestId=97EC31A7-A6D1-4783-B1B5-658D3757E009&interval.startTime=2020-08-04T19%3A30%3A00Z&interval.endTime=2020-08-04T20%3A41%3A00Z&aggregation.alignmentPeriod=%2B60s'
url decoding the parameters gives:
filter=metric.type="cloudsql.googleapis.com/database/postgresql/num_backends" AND (resource.label.database_id="khan-academy:khan-production") aggregation.groupByFields=metric.label.database aggregation.crossSeriesReducer=REDUCE_SUM aggregation.perSeriesAligner=ALIGN_MEAN interval.startTime=2020-08-04T19:23:00Z interval.endTime=2020-08-04T20:34:00Z aggregation.alignmentPeriod=+60s (key appears optional anyway)
This seems similar to the API explorer MQL query, and appears to provides similar results to what I saw in both dashboards. Unfortunately, I can no longer recall where I viewed the source of that MQL. However, I'm not also able to use curl to replicate the MQL results I see in the browser.
I find if I use: curl -v -d @query.json -H "Authorization: Bearer $(gcloud auth --project 'khan-academy' print-access-token)" --header "Content-Type: application/json" -X POST 'https://monitoring.googleapis.com/v3/projects/khan-academy/timeSeries:query'
with the query.json file contents as:
{"query":"fetch cloudsql_database\n| metric 'cloudsql.googleapis.com/database/postgresql/num_backends'\n| filter (resource.database_id == 'khan-academy:khan-production')\n| group_by 1m, [value_num_backends_mean: mean(value.num_backends)]\n| every 1m","queryTransformationSpec":{"timingState":{"absoluteWindow":{"startTime":"2020-08-04T22:59:00.000Z","endTime":"2020-08-05T00:00:00.000Z"},"graphPeriod":"60s","queryPeriod":"60s"}},"reportPeriodicStats":false,"reportQueryPlan":false}
I get results like: { "timeSeriesDescriptor": {} }
I get the same results I replace the escaped newlines with spaces in the query.json file contents:
{
"query": "fetch cloudsql_database | metric 'cloudsql.googleapis.com/database/postgresql/num_backends' | filter (resource.database_id == 'khan-academy:khan-production') | group_by 1m, [value_num_backends_mean: mean(value.num_backends)] | every 1m"
}
I'm not sure why this is. If you have any alternative guidance as to how best to get the metric num_backends, I am not wedded to any of these queries or endpoints.
Best Regards, Steve Created By: Nicholas E (8/4/20, 5:53 PM - 8 days ago)Was this helpful?YesNo Hi Steve,
Thanks for getting back to me.
Apologies, what I meant is I would like to know where is it that you saw the following query:
fetch cloudsql_database | metric 'cloudsql.googleapis.com/database/postgresql/num_backends' | filter (resource.database_id == 'khan-academy:khan-production') | group_by 1m, [value_num_backends_mean: mean(value.num_backends)] | every 1m
Does the API still throw errors when you remove the pipes?
Would you be able to go into more detail about your use case/what exactly you would like to accomplish? At this time I’m unsure if it’s possible to be updated on a metric for every new value that comes in without some backend application coding, so I’m curious exactly what you need to get done to see if I can come up with a solution for you.
I will be awaiting your reply in order to proceed. If you have any other questions or concerns about your issue, please do not hesitate to contact me by replying to this message. I will be more than happy to help at that time.
Sincerely,
Nicholas Google Cloud Platform Support, Montreal Created By: Steve Coffman (8/4/20, 4:30 PM - 8 days ago) If you go to: https://cloud.google.com/sql/docs/postgres/monitor-instance#instance_monitoring_in_the_console And click on the "Go to the Cloud SQL Instances page" then select the khan-production instance. The monitoring will default to "CPU utilization", which if you select as a drop down, you can pick "Active connections" This is the dashboard which I'm looking at.
I don't really need the data over time, just the most recent value. Created By: Nicholas E (8/4/20, 2:10 PM - 8 days ago)Was this helpful?YesNo Hi Steve,
Thank you for your patience,
You mention in the original desciption that the query that backs the dashboard is
fetch cloudsql_database | metric 'cloudsql.googleapis.com/database/postgresql/num_backends' | filter (resource.database_id == 'khan-academy:khan-production') | group_by 1m, [value_num_backends_mean: mean(value.num_backends)] | every 1m
Could you tell me where you went to get this information? This will allow me to attempt to reproduce the issue if necessary. I would like to know if the same query gives what you're looking for if you were to remove the pipes "|" from the query, as this is what's currently causing an error.
You also mention in your last message you were able to successfully get a 200 response with the query format you mentioned. Did this provide the information that you wanted or is the response that you posted with the query the entirety of the response that was given?
I will be awaiting your reply in order to proceed. If you have any other questions or concerns about your issue, please do not hesitate to contact me by replying to this message. I will be more than happy to help at that time.
Sincerely,
Nicholas Google Cloud Platform Support, Montreal Created By: Nicholas E (8/4/20, 1:31 PM - 8 days ago)Was this helpful?YesNo Hi Steve,
Thanks for getting back to us, I see you've escalated this case so I will be working with you for the time being.
Please allow me some time to go over the details of this case in the meantime and I'll get back to you in the next 2 hours. If you have any extra information that you'd like to share or think I should know while working on this issue please let me know and I'll be happy to listen.
If you have any questions or concerns in the meantime please feel free to reach out and I'll be happy to help.
Sincerely,
Nicholas Google Cloud Platform Support, Montreal Created By: Steve Coffman (8/3/20, 10:56 PM - 9 days ago) It looks like if I omit the newline characters and save a text file named query.json:
{
"query": "fetch cloudsql_database | metric 'cloudsql.googleapis.com/database/postgresql/num_backends' | filter (resource.database_id == 'khan-academy:khan-production') | group_by 1m, [value_num_backends_mean: mean(value.num_backends)] | every 1m"
}
If I then make a curl request:
curl -v -d @query.json -H "Authorization: Bearer $TOKEN" --header "Content-Type: application/json" -X POST https://monitoring.googleapis.com/v3/projects/${PROJECT_ID}/timeSeries:query
Then I get a 200 HTTP response:
{
"timeSeriesDescriptor": {}
}
Created By: Steve Coffman (8/2/20, 11:47 PM - 10 days ago) Hello Mahtab, you understand correctly that I want to monitor the postgres concurrent connections through that API.
The API I referenced suggests that the query parameter is required and the query is in the monitoring query language format. However, when I "Try this API" and attempt to set the query to what I sent to you, I get an error that "Unexpected character encountered" for the | character. How can I properly format the query in a way to get the result I am looking for? Or do you suggest a different API for this same query, or a different query for this same API to get the current number of postgres concurrent connections? Thank you for your help!
Best Regards, -Steve Created By: Mahtab M (8/2/20, 8:08 PM - 10 days ago)Was this helpful?YesNo Hello Steve,
Thank you for contacting Google Cloud Platform Support. I understand that you have queries about monitoring postgres concurrent connections through API, please correct me if I am wrong.
To move forward, could you please provide more details about the issue along with reproduction steps? This will help me to better understand your use case and do further research.
I will be awaiting your response. If you have any queries or concerns, do not hesitate to contact me back and I will be more than happy to assist you.
We want our GKE jobs to introspectively scale our concurrent connections to our postgres database without consuming a connection to the database to find out how many connections are already in use.
We can see the connections in use on the console. It appears the query that backs this view dashboard is:
fetch cloudsql_database | metric 'cloudsql.googleapis.com/database/postgresql/num_backends' | filter (resource.database_id == 'khan-academy:khan-production') | group_by 1m, [value_num_backends_mean: mean(value.num_backends)] | every 1m
We suspect that this query (or something like it) can be used via this API :
https://cloud.google.com/monitoring/api/ref_v3/rest/v3/projects.timeSeries/query
Any help or guidance would be greatly appreciated.Thanks!
The time/timezone when issue occurred:
Logs or screenshot(for UI related issues):
Hi! I had this same issue, and it seems to be a block by the frequency of the requests to GCP. The limit seems to be 4 minutes.