A SQL query is a definition of what data to return. There is a component of the database called the optimizer which determines how to get that data - which indexes to use, if any, in which sequence, which join algorithm to use etc. The output of that component is a query plan, the equivalent of a procedural program in the DBMS's internal execution language. ...
The trick to getting good performance out of PostgreSQL for inserts using SQLAlchemy is, as @LaurenzAlb hinted at, better management of transactions. SQLAlchemy can, but doesn't by default, use psycopg2's executemany helpers for PostgreSQL.
Turning these on transforms performance for multiple inserts into the same table. Here's the modified code for the ...
You need to add indexes:
image (person_id, image_id)
has (image_id, tag_id) - you can remove id column from this table and make this index a primary key
It is not the best idea to use "text" data type for person.name column. It would be better to use varchar or nvarchar. For example, varchar(100) should be enough. Then create index on this column ...
16 GB of RAM -- Is this correct?
Uptime = 1d 02:44:27
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.
The More Important Issues:
How many tables do you have? Apparently table_open_cache = 2000 is not high enough. Set it to ...
I was bitten by the same problem today and your question was the only one that showed up in a rather exhaustive (and exhausing) Google search.
Anyway, here's what I found.
If you execute two or more different query strings as prepared statements on the same cursor, it will be very slow.
In your benchmark script, you are executing a DELETE query and then ...
KEY brand_id etc (brand_id,shopid,last_seen,price,saleprice)
This index does not support this query well.
The first field in the index is brand_id, but you are only filtering by "> 0".
Since I doubt you have many brands with negative id's, this probably isn't going to help very much.
The next field in the index is shopid, which doesn't appear ...
Scenarios that may explain these symptoms include:
The application code has started a transaction and not committed
The application SET IMPLICIT_TRANSACTIONS ON
The stored procedure includes SET IMPLICIT_TRANSACTIONS ON
It is a good practice to include SET XACT_ABORT ON in stored procedures with explict transactions to ensure transactions are rolled back,...
Laurenz Albe answered this question on a similar post
this is the answer:
The table has to be rewritten, and you have to wait.
If you have several columns whose data type you want to change, you can use several ALTER COLUMN clauses in a single ALTER TABLE statement and save time that way.
An alternative idea would be to use logical replication: set up an ...
I suppose if you try to run "EXPLAIN SELECT *" instead of "SELECT id, cookie_id" then server will prefer to use table scan too because execution plan with index seek will require a lot (millions) of key lookups. The same consideration works for DELETE statement. So delete with table scan should be the fastest non-partitioned solution. If ...
I made tables and queries from your situation. Even though they are not the same as yours, it will be helpful to solve the issue.
create table students
create table student_point
create index idx_student_point_02 on student_point(exam_seq,...
Rate Per Second = RPS
Suggestions to consider for your RDS t3.large Parameter Group
innodb_lru_scan_depth=100 # from 1024 to conserve 90% of CPU cycles used for function
innodb_flush_neighbors=2 # from 1 to clear innodb_buffer_pool_pages_dirty of 59,407 faster
read_rnd_buffer_size=64K # from 512K to reduce handler_read_rnd_next RPS of 12,411
Refactor didn't work.
I decided to move to UNION ALL which gives the same results as use index or force index. I choose that approach since it will not require any code change if the index gets dropped or renamed.
I have full access to the server and could manipulate the .idb files directly ...
No, you really couldn't.
Database != File
All you would achieve is completely breaking your database.
All DBMSs, not just MySQL, store their data files in proprietary, binary formats that mean absolutely nothing to the likes of you or me.
Let the database to worry about its ...
Changing 1.7M rows in a table requires making copies of all the rows that are changed. (This is in case there is a crash or ROLLBACK.) The system is not designed to handle huge changes like that.
It may be faster to do the UPDATE in chunks, COMMITting after each chunk. This way the undo stuff is not huge.
I discuss techniques for walking through a table ...
Significant gains? No, I shouldn't think so.
SQL Server's indexes are B-Trees. These have a hierarchical nature. To read a row the server starts at the root node of the index then steps through one or more intermediate nodes before reading the row's values via the leaf node. (Details vary between clustered & non-clustered indexes, and heaps.) The speed-...
Realistically, the right answer varies depending on datasize of "megabytes" versus "gigabytes" versus "terabytes". "Petabytes" would literally take a ton of disk drives. And thousands of servers. And lots of load balancers, routers, etc. Once you add HA, double everything (at least). What's your budget like? Do ...
what is your bottleneck
database speed / computation power
transfer speed / bandwitdth
application computation power
application main memory
which indices are present
are the joins supported by them
are the selects supported by them
how is the data structured and what data does the application need of that data
do you need all the data ...
In most cases, one select will be faster. 5 selects mean 5 times the overhead, as @RickJames said.
If your database is not on the same server your query is running on, this also means 5 times the network latency, which can add a lot to your overall time. (I've seen this a few times recently, when employees had an application that talks to the database ...
Look at it this way... There is a lot of overhead in each SELECT:
Round trip between client and server
Allocate a thread to work on the query
Parse the SQL
Perform it (The meat!)
Send the results back
In some experiments, I have seen the "overhead" be 90% of the work. 5 queries takes nearly 5 times the overhead.
Adding OR clauses makes it more difficult to estimate how well the index will filter. One solution is to add a generated always column that calculates whether the predicates for magic and itemId are satisfied, and index that:
CREATE TABLE tblExample (
id int(11) unsigned NOT NULL AUTO_INCREMENT,
fTS timestamp NULL DEFAULT CURRENT_TIMESTAMP,
About PRIMARY KEYs:
Each table must have a PRIMARY KEY.
The PK (in MySQL's InnoDB engine) is always "clustered"
Indexes other than the PK are non-clustered.
The PK is "unique". It uniquely identifies each row.
In your question:
One INSERT per minute is very slow. (So, no performance concerns here, regardless of the indexes on the ...
I think it might be some kind of bug in PostgreSQL. We're running PostgreSQL 12 with 3 redundant servers (one master + 2 standby servers) each having 64 GB of RAM.
Originally we configured the system to have shared_buffers = 24 GB and work_mem = 128 MB. The system seemed to eat memory until OOM Killer finally took over when the system run out of memory.
I think it may be the main problem of query, because there is no condition with sutdent_id from student tables when selecting data from intervention_table: I mean IN ( SELECT MAX.... ) <-- In this query block,
external condition student_id can't come in and join: To get lastest intervention_date, seq scan is operated. Besides, in explain plan,
this table ...
Until our maintenance job will update statistics again with fullscan
next time, the auto statistics update runs and resets the ‘fullscan’
This is the side effect of updating statistics with fullscan on a weekly basis, it becomes more of a guessing game when your statistics are going to be automatically updated.
As mentioned in the comments and since ...
If an average of the current rows size is wanted, you could use pg_column_size :
SELECT SUM(pg_column_size(table_name.*))/COUNT(*) FROM tablename;
Using it per column :
SELECT SUM(pg_column_size(table_name.column_name))/COUNT(*) FROM tablename;
A partial index typically doesn't need the columns from the WHERE clause in the index as well. You should put other columns that you retrieve in the queries that make use of that index in the column. e.g. the PK or something else.
So if you e.g. frequently use this query:
select pk_column, other_column
where num_col = 0
and bool_col = false;
That's a system session.
DBCC INPUTBUFFER(37); might give a clue what it's doing, or CROSS APPLY with sys.dm_exec_input_buffer(), but you might not be able to understand any more about what it's doing. Since it's a background task, it is unlikely to be a cause for any other perceived slowness but, being internal, I'm not sure what you could possibly do to ...
Determining indexes for tables is not easy. I can offer some general guidelines that will help, but the specifics are going to be up to you to determine through testing on your systems with your queries.
The first, and most important, index you set is your clustered index. You only get one, so you want to make it right. Further, the key on your clustered ...
A clustered index on a timestamp column (a temporal data type such as datetime, not rowversion) is often a good choice for audit tables. This will help avoid full scans when date range criteria are specified in queries and the incremental value helps improve buffer efficiency during inserts.
An additional non-clustered index on UserName will help avoid a ...
From your question, if you filter on Username you should add a non-clustered index on username.
Make sure you also use the include statement for all the columns you are selecting or joining on.
If the user query is also order by timestamp, you can create the index like this:
CREATE NONCLUSTERED INDEX IX_USER_AUDIT_Username
ON Username(Timestamp DESC, ...
There are lots of pitfalls.
Simply adding PARTITIONing will not improve performance.
Change most of the indexes if you add PARTITIONing.
Don't use unless you have a least a million rows
Only BY RANGE() is useful.
It is essentially useless to PARTITION BY RANGE(the-primary-key)
There are only 4 use case for partitioning.
A PARTITIONed table is likely to be ...
First of all thanks to @rois and @piotr for their help. The solution (in our case) was a combination of things and thanks to their help we were able to look in the right direction.
These config settings/changes provide us a lot more throughput than before. Just an important note upfront: Since we can bare a little data loss (till a backup point), we use the &...
According to microsoft SQLNCLI is deprecated and MSOLEDBSQL should be preferred.
But I had a case where I had to use SQLNCLI in SSIS:
I unexpectedly got some 'The OLE DB provider used by the OLE DB adapter cannot convert between types "DT_DBTIMESTAMPOFFSET" and "DT_WSTR" for ...'
messages in SSIS dataflows.
After some debugging I found ...
Can't be done.
To achieve the goal, you would need a 2-dimensional index. Such does not exist.
category_id = 30
AND created_at > 1592862179
That much is handled nicely with INDEX(category_id, created_at). But adding anything onto the end of the index definition will not benefit you
The only way to get the index to be also useful for the ...
How can I effectively filter a huge table using another big list?
Just JOIN the big table to list.
Just make sure your list is a table format:
Global Temporary Table
APEX Collection (for APEX apps)
an SQL collection (variable)
PL/SQL collection (18c+)
Since the problem are bad generic plans (symptom: execution becomes slower from the sixth execution on) and you are using PostgreSQL v12, the solution is simple:
ALTER FUNCTION xy SET plan_cache_mode = force_custom_plan;
Although Erwin's masterful-as-always answer has incredible performance, it seems to be dependent on the number of prefix characters that need to be matched, which could be tricky to maintain; in telco, e164 prefixes are effectively arbitrary, and can be remarkably long, particularly if you are going to local geographic prefixes in a country with an already ...
I must say I'm not sure what is the problem you are facing. I can only give some ideas what could be wrong.
I speculated it could be tempdb. But in this case IO_COMPLETION waits would be much higher. Still I find the IO on tempdb and database puzzling.
First the CXPACKET waits. As you note you changed maxdop. If you haven't restarted the instance in the ...
Your innodb_buffer_pool_size is almost absurdly small for a server of that size.
innodb_flush_log_at_trx_commit=2 is dangerous.
innodb_file_per_table=1 you definitely don't want commended out. Rebuild any tables that aren't in separate tablespaces.
query_cache_limit=1G makes no sense with query cache disabled.
Various buffer sizes other than ...
A basic database principle is to avoid duplication of data. Option 2 has 3 rows with essentially the same basic data. So, a vote for Option 1.
Option 1 is smaller overall, so another vote for it.
If you get much past 3 sorts, the problem gets messier.
You are doing SELECT id. Was that a simplification for this discussion? If you really are selecting only ...
The first problem goes away if you solve the second with a Signed Stored Procedure installed in Master. If there are any version differences required, you can have different versions of the procedure for different SQL Server versions.
Then the script is a just a call to the procedure.
We had a similar issue upgrading from SQL Server 2008 R2 to SQL Server 2019(compatibility level 150).
Some of our nightly update jobs suddenly took 6-7 times as long to run(from 4 min to 39 min, and from 1 hour to 6 hours).
Setting Legacy Cardinality setting to ON brought us back to our usual update speed.
This is what we did(source: https://blog....
The documentation on query hints says
DISABLE_PARAMETER_SNIFFING ... is equivalent to trace flag 4136.
That trace flag was documented in KB980653 which tells us
Enabling trace flag 4136 disables parameter sniffing, which is equivalent to adding an OPTIMIZE FOR UNKNOWN hint to each query which references a parameter.
The documentation on supported trace ...
Max Server Memory for SQL Server 2019 = Buffer Pool Memory + Non-Buffer Pool Memory.
No, some part of memory allocation for SQL Server 2019 can be done outside of max server memory like ( I would quote from Memory Management Architecture Guide)
Memory for thread stacks1, CLR2, extended procedure .dll files, the OLE DB providers referenced by distributed ...