6

but I have difficulties to see the added value compared to Cloud SQL instances with replication and backup. I am not very familiar in scaling heavy load systems. Galera-based clusters like Percona XtraDB Cluster support true active/active multi-master, so failover is seamless because you can actually be writing on any node at any time (tho it's ...


4

MySQL does not use multiple CPUs for any query, not even with UNION or PARTITION (the likely candidates). Multiple connections (not just multiple threads) could do things in parallel. But the overhead of making the connection and gathering the 3 sets of data may be worse. To make things worse, UNION always creates a tmp table to gather the separate ...


4

Answer originally left as a comment by Michael - sqlbot: Mostly it's a MySQL thing. The slow query log "table" -- by default -- is actually just a CSV file read by the "CSV storage engine," which emulates a table but uses a simple CSV file as its tablespace. This allows appending to the slow query "table" to be a low overhead operation, but of course a ...


2

Unfortunately this is not a simple matter. There is much black art and experience involved because you are dealing with a few different but strongly coupled matters. First you are trying to decide if your infrastructure needs updating to handle the application1, if the application needs changes to optimise it instead2, some mix of the two3, or if you need to ...


2

Number one: PostgreSQL will never be great as a key-value store if you have many UPDATEs. Workloads with many UPDATEs are just hard for PostgreSQL's architecture. Make sure that you create your table with a fillfactor way below 100, so that you can make use of HOT updates. This is the only way you can survive workloads with many UPDATEs. Make sure that ...


1

Use pt-show-grants utility to generate a "logical dump" of your accounts and grants. You can do the same thing manually if you just run show grants for for each user, but this percona-toolkit utility will make it even easier. Unless you have special configuration or authentication plugins, this should mostly work (unlike trying to dump and restore all user ...


1

As you mentioned you are trying to get 80% of the rows of your table and it is normal that postgreSQL optimizer decide to scan the table rather than get data from the index. Unfortunately postgres does not support queries with index hints like MS SQL server, so it is not possible to compare the results of index scan vs sequential scan but I am absolutely ...


1

Your theory is correct about your cloudSQL since it is a Google managed service. If you would like to have full access over your mysql database, you can use it through a Compute Engine instance in which you will have super user privileges. Nevertheless, about your current issue please fill a bug here which is under a private component and you can share ...


1

It's all documented here and here. Relevant part: To make your general or slow query logs available, enable the corresponding flag and set the log_output flag to FILE. This makes the log output available using the Logs Viewer in the Google Cloud Platform Console. Note that Stackdriver logging charges apply. If log_output is set to NONE, you ...


1

Are your instances 1st or 2nd generation? The activation policy configuration for 1st generation instances can cause your instance to automatically shut itself off after 15 minutes of inactivity. Regarding your bottom-line question: yes, instances are set to automatically shutdown (both generations) for maintenance. For second generation instances, you can ...


1

There isn't anything you can do to improve the failover time, but you can add a read replica to have read access to your data while your instance is recovering. If you aren't locked down to MySQL, you should also try the Postgres version of Cloud SQL - it uses a faster method for recovery and from my personal experience is generally down for ~30-60 seconds....


1

raster2pgsql This a helper to make the job easy. It's not strictly speaking required. raster2pgsql is a raster loader executable that loads GDAL supported raster formats into sql suitable for loading into a PostGIS raster table. It is capable of loading folders of raster files as well as creating overviews of rasters. You can just redirect the output ...


1

Indicators of something needed: If you are more than 2 versions behind the latest, it is time to upgrade. (When you get too many versions behind, the upgrading becomes more difficult.) For MySQL, you should be on 5.6, 5.7, or 8.0. As software grows, and the data grows, and the expectations of the 'users' grow, you may find that there is demand for better ...


1

The subquery is evaluated only once because it has no dependency on the row to update. You may add one, just to force the engine to evaluate the subquery for each row. For instance if dest_col is a non-null number: UPDATE dest SET dest_col = (SELECT s.src_col FROM source s WHERE s.name = 'abc' ORDER BY random()+dest_col ...


1

By design (it's hardcoded) tsearch_data directory is in SHAREDIR. If you want to change SHAREDIR you should use your own compiled version of PostgreSQL. If you are interested, you can read the documentations' Installation from Source Code section and If you still want to change tsearch_data, well PostgreSQL is opensource, you can change Makefile and ts_utils....


1

I have seen this happens a lot in Amazon RDS. This occurs with row-based replication when a Master get hammered with lots of writes and they get serialized to one I/O thread. What you should do is dynamically change the following: SET GLOBAL innodb_flush_log_at_trx_commit = 2; Problem is you need the SUPER privilege, which neither Amazon RDS nor Google ...


1

Short Summary You have to run an REASSIGN OWNED BY "$VAULT_USERNAME" TO "cloudsqlsuperuser" after the table/sequence/function creation process to reassign back to a the common role. Longer Answer Postgres 9.6 CREATE TABLE will by default create the table under the user's role. Because the vault user is designed to be a temporary credential, we need to ...


1

Found the issue, Google App Engine runs the query for monitoring


1

You may not find any such information for GCP because Aurora is a storage engine native to Amazon. It can be used with MySQL or PostgreSQL. You only have three(3) options here: OPTION #1 You need to read the Amazon Documentation on Aurora and try to locate the same storage options and features in CloudSQL. Thoroughly scour the GCP Documentation for any ...


1

Do you really need compressed? A multi-terabyte datasets may happily live on 100G-RAM machine. What is your data like? Text? Blobs? Lots of rows? Care to share the CREATE TABLEs? I don't have any details on Cloud SQL. But... Compression is usually not the first place to look when thinking of Scaling. It has overhead (usually CPU, sometimes complexity). ...


1

I had a similar issue. I believe there are two ways to resolve it (though only one worked for me). The first way (that did not work for me) would be to create a client certificate via the Cloud Platform -> SQL -> [Instance] -> Access Control tab -> SSL tab -> Create Client Certificate button. It will give you instructions for downloading the client ...


Only top voted, non community-wiki answers of a minimum length are eligible