8
votes
Accepted
Set column to randomly chosen value from another table
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 ...
- 32.9k
7
votes
Efficient key value store in Postgres
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 ...
- 62.6k
5
votes
Mysql query works in 200ms with 33594 parameters in SELECT IN (xx,xxx,xx) but runs hours with 33595 or more parameters, why?
I'm more curious about why this happens to understand what's the limit...
The why is because it's an overly complicated predicate. The IN clause is syntactical sugar for a bunch of OR clauses. Too ...
- 41.1k
4
votes
PgAdmin 4.14 connection error in cloudSQL : could not send data to server, socket is not connected, could not send ssl negotiation packet
Switching from localhost to 127.0.0.1 allowed the connection successfully.
- 141
4
votes
Accepted
UNION Queries run in Parallel form or not
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 ...
- 80.8k
4
votes
Accepted
IMPORT FOREIGN SCHEMA fails with Password error for 127.0.0.1 but not for remote IP (of same server)
The problem is that when you connect locally, PostgreSQL does not demand a password. So while the user mapping might define a password, that password can't be used as it is never asked for. If it ...
- 42.5k
3
votes
ERROR: role "user1" cannot be dropped because some objects depend on it
06:42:36.347 UTC [199]: [2-1] db=cloudsqladmin,user=cloudsqlagent
DETAIL: privileges for database mydb 24 objects in database mydb
Crossdatabase queries in PostgreSQL are not implemented. PostgreSQL ...
- 3,982
3
votes
Accepted
How do I see where postgres privileges are coming from for a role?
The permissions are stored on the individual objects, and there is no central view to see them all.
You could use the access privilege inquiry functions for each type of object to get that information....
- 62.6k
3
votes
Can a new transaction claim an older sequence id?
Is it expected that this can happen. A newer transaction claiming a lower seq value?
Yes.
The formulation "claiming a lower seq value" is fuzzy. You seem to be thinking "writing a ...
- 187k
2
votes
Accepted
GCP MySQL query logging
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 ...
- 32k
2
votes
How do you know when it's time to upgrade a RDBMS/SQL database infrastructure? What metrics to follow?
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 ...
- 32.7k
2
votes
Accepted
Half of the partitions are empty on a KEY partition
You have 100 partitions. So it's likely that your hash values modulus 100 are all even numbers.
This is a general risk of hashing. If the hashing function produces only values with a pattern like this,...
- 17.3k
2
votes
Efficient key value store in Postgres
Your benchmarking program creates two hash indexes, one to support the constraint, and another free standing one which seems to serve no purpose. Also, 100 iterations is by no means sufficient to ...
- 42.5k
2
votes
UPDATE incredibly slow on small PostgreSQL query - EXPLAIN ANALYZE causes it to hang
1+GB size for a table with 2k rows means either it has huge text columns or it is highly bloated.
Why don't you try running vacuum (analyze, full) against the database in question (if you haven't ...
- 312
2
votes
Accepted
Postgres allows to UPDATE however only SELECT is granted
=arwdDxt/dave means all privileges are granted to the pseudo-role PUBLIC, of which anton is obviously a member. The documentation states that
any particular role will have the sum of privileges ...
- 28.8k
2
votes
Why does the page cleaner in MySQL 8 increase the number of active connections to the database?
This depends on what the OS has configured
In the MySQL 8.0 Documentation on innodb_page_cleaners, it says in part
If your workload is write-IO bound when flushing dirty pages from buffer pool ...
- 186k
2
votes
Half a million rows deleted still showing same disk size as before in phpMyAdmin
How is this being calculated - is there some place where the deleted rows exists which is still being counted as 450MB ?
Acquiring space from the disk is a resource intensive task. Most database ...
- 41.1k
2
votes
Half a million rows deleted still showing same disk size as before in phpMyAdmin
MySQL fails to shrink disk usage when deleting rows. Instead, it leaves the free space in the file for future INSERTs.
This SQL may shrink the table for you: OPTIMIZE TABLE tablename. (I say "...
- 80.8k
1
vote
Creating manual superuser in cloudsql for postgres
Yeah, cloudsqlsuperuser is not a real superuser. If you want your new role to be as super as it gets, then grant it cloudsqlsuperuser.
- 42.5k
1
vote
can't backup postgres 13 with pg_dump
For supplying credentials to any PostgreSQL utility, I would strongly recommend using the Postgres Password File instead of trying to pass passwords around in variables.
Why? You have to get those ...
- 9,937
1
vote
Online schema changes for cloudsql DB
ALTER TABLE t MODIFY COLUMN c ENUM(...) ...
If that is only adding a new option on the end and not going past 255 options, that should be a "schema-only" change that happens instantly. (...
- 80.8k
1
vote
IMPORT FOREIGN SCHEMA fails with Password error for 127.0.0.1 but not for remote IP (of same server)
Building on top of jjanes's answer—for those still looking for an answer in an environment where you have control, you need to disable local trust for all users by modifying the following line in your ...
- 111
1
vote
can any tweaks improve full table scan time in Postgres/Cloud SQL?
Counting all rows is notoriously slow. Only fast storage and RAM can help.
But the task at hand can be much faster, as you obviously don't need a full count:
to see if I had set any values yet
...
- 187k
1
vote
can any tweaks improve full table scan time in Postgres/Cloud SQL?
Obviously faster storage and more RAM for caching can improve performance of a sequential scan.
Other than that, you can increase max_parallel_workers_per_gather on recent PostgreSQL versions to have ...
- 62.6k
1
vote
Postgresql sudden storage increase at regular interval?
Since you have no filesystem access you won't get the answer but some guesses only, so here comes mine: if there are no bulk INSERTs, this might be some temporary space created by SELECT, for example ...
- 181
1
vote
Accepted
migrate users from mariadb10.1 to mysql5.7
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 ...
- 15.1k
1
vote
Speed up 180M+ rows returned by simple SELECT with PostgreSQL
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 ...
- 1,533
1
vote
Removing MySQL Orphan Tables in Google's Managed MySQL
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 ...
- 111
Only top scored, non community-wiki answers of a minimum length are eligible