Questions tagged [google-cloud-sql]
Google Cloud SQL is a service that allows you to create, configure, and use relational databases (currently MySQL and PostgreSQL) that live in Google's cloud. It is a fully-managed service that maintains, manages, and administers your databases, allowing you to focus on your applications and services.
82
questions
6
votes
2
answers
4k
views
Mysql query works in 200ms with 33594 parameters in SELECT IN (xx,xxx,xx) but runs hours with 33595 or more parameters, why?
We had a small malfunction which generated unexpected amount of data for a device. Our code ends up creating a select query along the lines of
SELEXT * from XXX WHERE yyy IN (xxx,xxx,xxx....,xxxx)
...
- 183
6
votes
1
answer
7k
views
ERROR: role "user1" cannot be dropped because some objects depend on it
On my Google Cloud SQL database instance (Postgres 9.6), I am unable to drop a user/role via the GCP Console that was created via the GCP Console. When I try, the Console shows a notification that ...
- 161
4
votes
2
answers
10k
views
Efficient key value store in Postgres
I want to use Postgres(11 in CloudSQL) as an efficient key value store. I have about 200GB of dictionaries (average size is 10kB, the structure can be different and nested). I'm thinking about ...
- 141
4
votes
1
answer
6k
views
Set column to randomly chosen value from another table
Consider this:
UPDATE dest
SET dest_col = (SELECT s.src_col FROM source s
WHERE s.name = 'abc'
ORDER BY random() LIMIT 1 OFFSET 0);
My goal: Set dest.dest_col in ...
- 143
4
votes
1
answer
1k
views
Galera cluster into Google cloud platform
We have a galera cluster with 3 nodes, on 3 different physical machines but all located in the same datacenter.
From what I understood, the reason they deployed this in the past was to increase ...
- 173
4
votes
0
answers
513
views
Does Google CloudSQL Export function lock tables?
In order to create more 'data recovery' points than the standard automatic backup policy allows, I'm going to run a cron which will export sql dumps out of my CloudSQL instances at intervals ...
- 1,774
3
votes
1
answer
3k
views
MySQL slow_log table: querying takes forever
I'm running a query on the slow_log:
SELECT start_time FROM mysql.slow_log WHERE start_time < TIMESTAMP '2016-01-31 00:00:00'
Without a WHERE clause (using a LIMIT), the selects are fast.
...
- 191
3
votes
2
answers
2k
views
IMPORT FOREIGN SCHEMA fails with Password error for 127.0.0.1 but not for remote IP (of same server)
I have a Postgres server in Google SQL Cloud with multiple databases. I want to create a FOREIGN DATA WRAPPER between them. If I use the server's remote IP it works:
CREATE SERVER "some_db_fdw&...
- 133
3
votes
1
answer
367
views
Half of the partitions are empty on a KEY partition
We are trying to move data from one table that currently has over 1B rows to a partitioned table.
But as we started to do so we notice that half of the partitions are empty, why is that?
Table is ...
- 185
2
votes
1
answer
4k
views
UNION Queries run in Parallel form or not
I have three tables and I want to select data from these three tables in parallel form to increase the performance.
I'm running a query like this.
SELECT * FROM table1
UNION ALL
SELECT * FROM table2
...
- 73
2
votes
1
answer
4k
views
PgAdmin 4.14 connection error in cloudSQL : could not send data to server, socket is not connected, could not send ssl negotiation packet
I am trying to connect cloud sql with pgadmin but it throws error when I try to execute query.
"Could not send Data to server, socket is not connected, could not
sent SSL negotiation pocket"
...
- 23
2
votes
1
answer
186
views
Why does the page cleaner in MySQL 8 increase the number of active connections to the database?
I have noticed that when the page cleaner is triggered in my MySQL 8 database, it causes a temporary increase in the number of active connections. This can result in increased wait time for new ...
BadProgrammer
2
votes
1
answer
4k
views
GCP MySQL query logging
I have a managed MySQL instance on GCP. Where would I find a query log? Is this something I need to enable first somewhere? I have read their documentation ( https://cloud.google.com/sql/docs/mysql/...
- 133
2
votes
1
answer
3k
views
ERROR 2003 (HY000): Can't connect to MySQL server on 'xxx.xxx.xx.xx' (10060)
I'm trying to connect my local MySQL workbench to a 2nd generation MySQL instance on Google SQL platform using the following command on my windows laptop.
mysql --host=130.xxx.xx.xx --port=3306 --...
- 2,591
2
votes
1
answer
2k
views
Creating manual superuser in cloudsql for postgres
I’m using google managed cloudsql database and I’m trying to create my own superuser manually and I’m getting below message
Note: I’m logged in via existing superuser only(mycurrentuser_dev).
...
- 348
2
votes
1
answer
187
views
Durability of storage in second generation google cloud sql
How durable should I expect data for a Google Cloud SQL instance to be? Just a single instance, with the high-availability option disabled.
AWS explicitly mention that Amazon Aurora data is stored in ...
2
votes
1
answer
720
views
Postgresql sudden storage increase at regular interval?
I am running Postgresql 11 in production and observing a weird step function kind of thing in storage as follow:
The above graph is for a single day and it is happening everyday at random times. I ...
- 93
2
votes
0
answers
340
views
Google CloudSQL - cannot delete created role
I created user observer using only postgres user on my PostgreSQL 11 instance and I don't need it anymore. But after all I can't delete it - pgAdmin showing me that my user have connect privileges ...
2
votes
0
answers
125
views
taking a gcloud snapshot of a postgres database
Given a postgres database on a Google cloud virtual machine instance, are we guaranteed snapshot consistency if we call pg_start_backup(), sync(), then take a snapshot of the instance?
https://cloud....
- 21
1
vote
1
answer
642
views
How do I see where postgres privileges are coming from for a role?
Im using google cloudsql but this is a more generic postgres question.
I have a role (that is already created and managed by cloudsql) called cloudsqliamserviceaccount.
The cloudsqliamserviceaccount ...
- 123
1
vote
2
answers
90
views
How do you know when it's time to upgrade a RDBMS/SQL database infrastructure? What metrics to follow? [closed]
In my specific case, I have just deployed my side project to AWS and using AWS MySQL Tier 2 Micro instance, which is within AWS' Free Tier;
https://aws.amazon.com/ec2/instance-types/t2/
It has: 20 ...
- 111
1
vote
1
answer
233
views
Postgres allows to UPDATE however only SELECT is granted
Owner of table x is user dave.
Only SELECT is granted to cloudsqlsuperuser, however user anton is able to update data in table x. How it's possible?
=> \du
...
- 13
1
vote
1
answer
1k
views
UPDATE incredibly slow on small PostgreSQL query - EXPLAIN ANALYZE causes it to hang
I'm trying to debug a very slow UPDATE query in one of my tables.
The table name is beepers. It has 2486 rows and weighs 1204 MB.
It has 18 columns and two indices: one on id and the other on ...
- 11
1
vote
1
answer
980
views
Installing pglogical: Find postgres include folder, add all include folders to C_INCLUDE_PATH
I am trying to install the pglogical extension in my postgres database, which I installed on my macOS with homebrew. I am following instructions provided on the GCP blog as I am preparing to migrate ...
1
vote
1
answer
246
views
migrate users from mariadb10.1 to mysql5.7
My company is moving our databases from self-hosted mariadb 10.1 to Google CloudSQL (which is essentially MySQL 5.7).
Dumping / restoring our application schemas looks straightforward. However I'm ...
- 122
1
vote
1
answer
435
views
Speed up 180M+ rows returned by simple SELECT with PostgreSQL
I have performance issues on a SELECT statement which returns 180+ out of 220+ of millions of rows on my managed PostgreSQL. The table structure is the following:
CREATE TABLE members (
id ...
- 11
1
vote
2
answers
82
views
Improve speed of GCP SQL Failover
I've been trying out GCPs SQL high availability options. Is there a way to improve the fail over speed?
Attempting manual failovers through the UI takes literally over 5 minutes where mysql is ...
- 5,253
1
vote
1
answer
256
views
Google Cloud SQL lacks raster2pgsql
Google Cloud SQL supports the PostGIS extension, but I haven't seen any documentation regarding how to work with raster data on it. To load raster data into a table, we have to use raster2pgsql driver....
- 159
1
vote
3
answers
3k
views
Google Cloud SQL Replication Delay
We have a project which does write data on a second generation MySQL 5.6
The master's write behaviour:
Average 70 write operations per second.
The thing is 2 times a day, application writes data ...
- 63
1
vote
1
answer
1k
views
How to setup vault and Postgres in Google cloud to have the correct permissions?
The Problem
I am trying to setup Hashicorp vault and Postgres in Google Cloud.
I am using liquibase to manage the schema and when that does a migration, it retrieves a username/password from vault ...
- 193
1
vote
1
answer
115
views
Information_schema appearing in slow_log
I am finding that my information_schema keeps running this query
SELECT engine, COUNT(*) as n FROM information_schema.tables WHERE engine is not null GROUP BY engine
Which seems to always appear in ...
- 133
1
vote
0
answers
17
views
How to Transfer 100GB database from GCP to AWS
I have a 100GB database hosted in GCP's Cloud SQL offering in us-east1
I'd like to transfer this to AWS lightsail databases and hosted in AP-SOUTHEAST-2.
Within AWS I'm using AWS Lightsail Databases ...
- 111
1
vote
0
answers
90
views
How to recover stopped replication on MySQL?
Several times now my Google Cloud MySQL read replica just stopped replication of the master. Visible symptoms are:
Replication lags rises
on the slave.
The Relay_Log_Pos value of show slave status is ...
- 111
1
vote
0
answers
116
views
What is the best way to migrate a multi-TB MySQL database from on-prem to gcloud Cloud SQL?
I have several multi-TB on-prem MySQL databases I need to migrate into Google Cloud's managed MySQL offering, Cloud SQL. I have migrated two of size ~1TB so far with mysqldump but this method is far ...
- 133
1
vote
0
answers
376
views
Query that normally takes 100ms, occasionally takes 30+s. Need help analyzing query plan
I am using Google Cloud SQL. I am using a g1-small. I have a table that looks like this:
+-----------+-------------------+-------------+
| Column | Type | Modifiers |
|-----------+---...
1
vote
0
answers
251
views
How to run pg_xlogdump or pg_waldump in Google CloudSQL? Or use Query Insights to get similar results?
Does anyone know how to look up the transaction that’s associated with an LSN on a CloudSQL Postgres instance?
For example, let’s say I’m trying to find the transaction associated with LSN 842/...
- 141
1
vote
0
answers
138
views
GCP Cloudsql Mysql Replica unresponsive after mass delete on Master
We have a Master/Replica configuration for Mysql innodb(5.7.32) databases in Cloud SQL... We have a single Table (let's call it Master table) partitioned on Two keys having both primary and non-...
- 11
1
vote
1
answer
53
views
Constant writing operations on google cloud database
I have a MySQL 8.0 cluster database and so far I'm using it to test a couple of things, so I only write from time to time. Problem is that I have realised that there are write operations all the time. ...
- 13
1
vote
1
answer
559
views
download mysql slow query log on google cloud sql
I'm having a difficult time locating the log file to download. When running
show global variables like '%slow%';
I can see it is located
/mysql/logs/slow.log
When I log into the instance though ...
- 111
1
vote
0
answers
486
views
Keeping the same order of selected records while inserting to another table
I am running a below query in big query
insert into `test-project.temp_test_dataset.cluster_records`
(pm,col1,col2,col3,total)
select pm,col1,col2,col3,total from (
select 'ingest' as pm,col1,col2,...
- 279
1
vote
2
answers
579
views
can any tweaks improve full table scan time in Postgres/Cloud SQL?
I have a table with 150 million rows in a Postgres database hosted in Cloud SQL (in Google Cloud Platform).
Typically I obviously try to avoid scanning the table, but occasionally I will manually want ...
- 213
1
vote
0
answers
126
views
Restore Stored Procedure from Google Cloud MYSQL?
I accidentally overwrote a MYSQL stored procedure through the Workbench client connected to a GCP MYSQL instance. I tried restoring a previous backup to recover it, but that only restored the tables - ...
- 111
1
vote
0
answers
37
views
Move Our Google Cloud SQL Instance to Compute Engine
We are running several Cloud SQL Instances on GCP and plan to move all of them to some Compute Engine instances, depending on the applications that use it. Our main concern is cost of the server.
We ...
- 111
1
vote
1
answer
162
views
Google Managed PGSQL DB migration to DigitalOcean Managed PGSQL DB
We are migrating from Google-Cloud PGSQL managed database to a managed PGSQL server on Digital ocean.
The dilemma we are facing is that both Google and Digital ocean are managed so we have no access ...
- 211
1
vote
1
answer
2k
views
How to sync easily between a master database from Google Cloud SQL and a master database from Amazon RDS
I've created a master - slave (replication) in Google Cloud SQL.
I've also created a master - slave (replication) in Amazon RDS.
I want to sync two master MysQL from Google Cloud SQL and Amazon RDS....
- 151
1
vote
0
answers
241
views
Dumping Postgres schema from Google cloud SQL
Now this may look trivial but it is a managed service so I don't have the superuser rights. And various schema/tables/sequences have different roles/users as their owners so I can't just use pg_dump ...
- 211
1
vote
0
answers
62
views
Why use Failover replica on Google Cloud RDS?
we are thinking of migrating our MySQL database from our dedicated server to a Google Cloud RDS instance. However I would like to clarify the below:
1) What is the purpose of creating a failover ...
- 41
1
vote
1
answer
768
views
What database to choose for 1TB of time series? [closed]
I'm saving about 3GB per day of financial orderbook data to a database (currently Mysql). In the coming weeks, I'm going to save trade and quote (bids/asks) data too.
Data is structured, but I'm ...
- 111
1
vote
1
answer
98
views
Adding columns upped my RAM usage significantly
I have a Google Cloud Platform MySQL 2nd Gen 5.7 instance with two databases, one for testing and one for production.
There are about 20 or so tables. One of the tables accounts for 99% of the ...
- 225
1
vote
0
answers
119
views
X-devAPI not working inside of Google cloud SQL
I am tryng to use X-devAPI inside of Google cloud SQL but getting the following error from MySQL client
comp@user:~$ sudo mysqlsh -u root -h XX.XXX.XX.XXX --mysql --dba enableXProtocol
[sudo] ...
- 111