We have a Windows Server 2022 with **56Gb** RAM. This server is dedicated to MySQL DB and during idle it has ~**50.9Gb** available memory. But while bulk inserting rows (200k rows) into a table from an application I'm facing the following error. Unfortunately, application does not support batch inserts yet.

Message: MySql.Data.MySqlClient.MySqlException (0x80004005): Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space

Initially, I tried with default configuration then changed configuration after searching online for the issue and still facing the same error. As insertion query is executed, memory usage increases from ~**4Gb** to **55Gb**+ with MySQL service consuming ~**53Gb**. Following are the configurations set in my.ini

[mysqld]

default-storage-engine=INNODB

sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

max_connections=200

thread_stack=100M

table_open_cache=10000

temptable_max_ram=5G

tmp_table_size=5G

internal_tmp_mem_storage_engine=TempTable

myisam_max_sort_file_size=2146435072

myisam_sort_buffer_size=6G

key_buffer_size=2048M

read_buffer_size=256M

read_rnd_buffer_size=512M

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=256M

innodb_buffer_pool_size=35G

innodb_redo_log_capacity=100M

innodb_thread_concurrency=13

innodb_autoextend_increment=64

innodb_buffer_pool_instances=16

innodb_concurrency_tickets=5000

innodb_old_blocks_time=1000

innodb_stats_on_metadata=0

innodb_file_per_table=1

innodb_checksum_algorithm=0

innodb_lock_wait_timeout=50

innodb_flush_method=normal

flush_time=300

join_buffer_size=256K

max_allowed_packet=1G

max_connect_errors=100

open_files_limit=10000

sort_buffer_size=4G

binlog_row_event_max_size=8K

sync_source_info=10000

sync_relay_log=10000

mysqlx_port=33060

upon checking online for solution, I increased innodb_buffer_pool_size and key_buffer_size . I also increased thread_stack size. Please suggest If I'm missing any configuration or what configurations need to be adjusted to not get the error when bulk insert (~1M+ rows). Thanks.

I am troubleshooting an issue with our marketing platform as it has become sluggish and slow, I've requested the top 50 heavy/resource intensive queries executed on our sql server to correlate with our marketing workflows. Here is an example or some of the top queries, based on the average I/O would you say these queries are consuming too much resources? what is a normal acceptable IO for a query? If I take the first query execution plan, it will be as following. Plan Link https://www.brentozar.com/PasteThePlan/?id=HJtGXHxUc

I am facing high memory utilization issue on RDS MySQL server. We are having other issues too, can someone give me a help? My database has 120GB, including indexes. Can anyone help me out to understand, RDS memory consumption and freeing behavior? How we can able to know what causing high memory utilization? How we can avoid it? RDS Configuration:- db.m5.4xlarge (vCPU = 16 and RAM = 64 GB) Here are some variables:

bulk_insert_buffer_size - 8388608
innodb_buffer_pool_chunk_size - 134217728
innodb_buffer_pool_dump_at_shutdown - ON
innodb_buffer_pool_dump_now - OFF
innodb_buffer_pool_dump_pct - 25
innodb_buffer_pool_filename - ib_buffer_pool
innodb_buffer_pool_instances8
innodb_buffer_pool_load_abort - OFF
innodb_buffer_pool_load_at_startupON
innodb_buffer_pool_load_now - OFF
innodb_buffer_pool_size - 47244640256
innodb_change_buffer_max_size - 25
innodb_change_buffering - all
innodb_log_buffer_size16777216
innodb_sort_buffer_size - 2097152
join_buffer_size262144
key_buffer_size - 16777216
myisam_sort_buffer_size - 8388608
net_buffer_length - 16384
preload_buffer_size - 32768
read_buffer_size131072
read_rnd_buffer_size262144
sort_buffer_size262144
sql_buffer_result - OFF
max_tmp_tables32
tmp_table_size6316621824
have_query_cacheYES
query_cache_limit - 0
query_cache_min_res_unit4096
query_cache_size0
query_cache_typeON
query_cache_wlock_invalidateOFF
innodb_page_cleaners4
max_connections - 2500
max_user_connections0
thread_stack196608
binlog_cache_size - 32768
binlog_stmt_cache_size32768
have_query_cacheYES
host_cache_size - 728
innodb_disable_sort_file_cacheOFF
innodb_ft_cache_size8000000
innodb_ft_result_cache_limit2000000000
innodb_ft_total_cache_size640000000
key_cache_age_threshold - 300
key_cache_block_size1024
key_cache_division_limit100
max_binlog_cache_size - 18446744073709547520
max_binlog_stmt_cache_size18446744073709547520
metadata_locks_cache_size - 1024
query_cache_limit - 0
query_cache_min_res_unit4096
query_cache_size0
query_cache_typeON
query_cache_wlock_invalidateOFF
stored_program_cache256
table_definition_cache2000
table_open_cache31512
table_open_cache_instances16
thread_cache_size - 33
connect_timeout - 60
delayed_insert_timeout300
have_statement_timeoutYES
innodb_flush_log_at_timeout - 1
innodb_lock_wait_timeout50
innodb_rollback_on_timeoutOFF
interactive_timeout - 30
lock_wait_timeout - 31536000
net_read_timeout30
net_write_timeout - 60
rpl_stop_slave_timeout31536000
slave_net_timeout - 60
wait_timeout10
+-------------------------------------+----------------------+

**Part of Innodb Status**

-------------------------------------+
InnoDB
=====================================
2019-10-02 14:21:22 0x2b94c2f40700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 33 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 63575 srv_active, 0 srv_shutdown, 11740 srv_idle
srv_master_thread log flush and writes: 75314
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 55391770
OS WAIT ARRAY INFO: signal count 26184149
RW-shared spins 0, rounds 86600920, OS waits 42401698
RW-excl spins 0, rounds 462913252, OS waits 12003058
RW-sx spins 23109, rounds 530146, OS waits 16312
Spin rounds per wait: 86600920.00 RW-shared, 462913252.00 RW-excl, 22.94 RW-sx
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 1; buffer pool: 0
32457638 OS file reads, 4421652 OS file writes, 2294794 OS fsyncs
2 pending preads, 0 pending pwrites
782.52 reads/s, 16384 avg bytes/read, 82.57 writes/s, 69.24 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 13566, seg size 13568, 371078 merges
merged operations:
insert 454633, delete mark 124835, delete 12938
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 10093739, node heap has 4264 buffer(s)
Hash table size 10093739, node heap has 135474 buffer(s)
Hash table size 10093739, node heap has 1471 buffer(s)
Hash table size 10093739, node heap has 59948 buffer(s)
Hash table size 10093739, node heap has 19650 buffer(s)
Hash table size 10093739, node heap has 1081 buffer(s)
Hash table size 10093739, node heap has 7542 buffer(s)
Hash table size 10093739, node heap has 47145 buffer(s)
168373.38 hash searches/s, 14089.63 non-hash searches/s
---
LOG
---
Log sequence number 804344096126
Log flushed up to 804344095294
Pages flushed up to 803851900229
Last checkpoint at803851900229
1 pending log flushes, 0 pending chkp writes
1251611 log i/o's done, 38.88 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 48375005184
Dictionary memory allocated 2461293
Buffer pool size 2883584
Free buffers 7704
Database pages 2599305
Old database pages 959347
Modified db pages104790
Pending reads2
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 215190608, not young 1218564644
3804.95 youngs/s, 45715.22 non-youngs/s
Pages read 32426535, created 1349697, written 2650097
783.43 reads/s, 2.97 creates/s, 33.67 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 2 / 1000 not 32 / 1000
Pages read ahead 97.03/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 2599305, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 360448
Free buffers 923
Database pages 324971
Old database pages 119939
Modified db pages13414
Pending reads0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 26775448, not young 149344221
483.59 youngs/s, 5762.25 non-youngs/s
Pages read 4020738, created 170517, written 337016
103.39 reads/s, 0.67 creates/s, 2.18 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 2 / 1000 not 32 / 1000
Pages read ahead 12.88/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 324971, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 1
Buffer pool size 360448
Free buffers 961
Database pages 324942
Old database pages 119929
Modified db pages13177
Pending reads0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 25415387, not young 141982022
425.65 youngs/s, 4656.50 non-youngs/s
Pages read 3816124, created 167340, written 316273
79.30 reads/s, 0.03 creates/s, 4.48 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 2 / 1000 not 32 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 324942, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 2
Buffer pool size 360448
Free buffers 927
Database pages 324869
Old database pages 119903
Modified db pages12944
Pending reads1
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 27297719, not young 150838953
481.65 youngs/s, 6947.94 non-youngs/s
Pages read 4077648, created 168214, written 330297
101.91 reads/s, 0.09 creates/s, 5.15 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 2 / 1000 not 31 / 1000
Pages read ahead 15.73/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 324869, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 3
Buffer pool size 360448
Free buffers 957
Database pages 324872
Old database pages 119903
Modified db pages13846
Pending reads0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 27241158, not young 152596490
508.68 youngs/s, 5102.88 non-youngs/s
Pages read 4079040, created 165287, written 332920
101.63 reads/s, 0.06 creates/s, 4.33 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 1 / 1000 not 16 / 1000
Pages read ahead 10.12/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 324872, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 4
Buffer pool size 360448
Free buffers 970
Database pages 324930
Old database pages 119924
Modified db pages13306
Pending reads0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 27071975, not young 156302791
495.53 youngs/s, 4981.52 non-youngs/s
Pages read 4202662, created 168923, written 335712
106.24 reads/s, 0.61 creates/s, 4.76 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 3 / 1000 not 36 / 1000
Pages read ahead 12.76/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 324930, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 5
Buffer pool size 360448
Free buffers 1000
Database pages 324927
Old database pages 119923
Modified db pages12453
Pending reads0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 29017652, not young 165174087
520.92 youngs/s, 6319.20 non-youngs/s
Pages read 4381293, created 171171, written 331047
104.78 reads/s, 0.58 creates/s, 7.24 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 3 / 1000 not 46 / 1000
Pages read ahead 15.91/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 324927, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 6
Buffer pool size 360448
Free buffers 973
Database pages 324822
Old database pages 119884
Modified db pages13399
Pending reads0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 25561540, not young 148607714
404.81 youngs/s, 5283.75 non-youngs/s
Pages read 3835345, created 169647, written 334254
89.85 reads/s, 0.42 creates/s, 3.21 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 2 / 1000 not 34 / 1000
Pages read ahead 11.21/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 324822, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
---BUFFER POOL 7
Buffer pool size 360448
Free buffers 993
Database pages 324972
Old database pages 119942
Modified db pages12251
Pending reads1
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 26809729, not young 153718366
484.11 youngs/s, 6661.19 non-youngs/s
Pages read 4013685, created 168598, written 332578
96.33 reads/s, 0.52 creates/s, 2.30 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 3 / 1000 not 47 / 1000
Pages read ahead 18.42/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 324972, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
4 read views open inside InnoDB
Process ID=14412, Main thread ID=47944612980480, state: sleeping
Number of rows inserted 76778123, updated 1282408, deleted 102505, read 277730269604
1496.74 inserts/s, 47.06 updates/s, 1.30 deletes/s, 3574606.95 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

We are in the process of upgrading our Oracle database server at work, from SUSE Linux Enterprise Server 10.4 / Oracle 10g / 4GB RAM (Intel 32-bit) to Scientific Linux 6.4 / Oracle 12c / 8GB RAM (Intel 64-bit). As the first step, the additional RAM has been installed, but due to some application-related issue we had to pend the rest of the planned upgrade. ***To clarify**, the server is still running Oracle 10g and SLES 10.4. No change has been made on the software side* Since the installation of the additional RAM, however, the only database instance on that machine has been running out of shared memory (with the error in the title) two afternoons in a row. The depletion was severe enough that the only way to recover was using `SHUTDOWN ABORT`. No configuration change has been made, e.g. to the memory settings of the database: NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 1632M sga_target big integer 1632M pga_aggregate_target big integer 384M db_16k_cache_size big integer 0 db_2k_cache_size big integer 0 db_32k_cache_size big integer 0 db_4k_cache_size big integer 0 db_8k_cache_size big integer 0 db_cache_advice string ON db_cache_size big integer 960M It seems that the extra RAM actually cause the Oracle database software to have less RAM available to itself than before, but how exactly is this happening?

I'm not a DB Administrator but am looking for advice on tuning our MySQL database. It's a small storefront that is running Prestashop, and lags out for several minutes when saving changes to a product pack. That is, a product that is made up of 2 or more other products. I just ran MySQLTuner-Perl and it looks alright for the most part, but there are several points flagged for review. [!!] Aborted connections: 6.83% (337899/4948004) [!!] CPanel and Flex system skip-name-resolve should be on [!!] Joins performed without indexes: 10213707 I suspect the 10M Joins performed without indexes is the likely culprit here, but not entirely sure what that means or how it should be addressed. The server has tons of spare memory so I could increase the allocation 2-3x as a temporary bandaid, but I imagine this won't be resolved until I figure out the indexing issue. Any advice or clarity on these results would be greatly appreciated.

I am tuning a PG installation with 32GB of RAM. The question is: which one of the following sentences is true? 1. effective_cache_size + shared_buffers ~= 30GB (PG knows that it has shared buffer plus the effective_cache_size) 2. effective_cache_size ~= 30GB (effective_cache_size includes shared_buffers)

We run SQL Server 2000 and we get a few of these errors every night. Could not continue scan with NOLOCK due to data movement The query that throws this error is a large complex query that joins over a dozen tables. Our underlying data can be updated frequently. The cultural 'best-practice' is that, in the past, introduction of NOLOCK hints increased performance and improved concurrency. This query doesn't need to be 100% accurate, i.e. we will tolerate dirty reads etc. However, we are struggling to understand why the database is throwing this error, even though we have all these locking hints. Can anyone shed some light on this - be gentle, I'm actually a programmer, not a DBA :) PS: We have applied the fix mentioned below previously: http://support.microsoft.com/kb/815008

I came across a situation where I need expert opinion. Our application ingests data into table A in aurora mysql 3.02 every 5 seconds. This is a write intensive process. During this time, I am using DMS to replicate data for table B from a different source into the same aurora db where table A is located. After few seconds the application queue piles up with many files which are waiting to be written into table A. We have bumped up the instance from 2x - 8x and there is no luck. I cannot relate how writing data in table B affects data ingesting in table A and how do I approach this issue?

I am trying to grasp the different concepts of Database Partitioning and this is what I understood of it: Horizontal Partitioning/Sharding: Splitting a table into different tables that will contain a subset of the rows that were in the initial table (an example that I have seen a lot if splitting a Users table by Continent, like a sub table for North America, another one for Europe, etc...). Each partition being in a different physical location (understand 'machine'). As I understood it, Horizontal Partitioning and Sharding are the exact same thing(?). Vertical Partitioning: From what I understood (http://technet.microsoft.com/en-us/library/ms178148%28v=sql.105%29.aspx ), there are 2 sorts of Vertical Partitioning: - Normalization (which consists of removing redundancies from a the database by splitting tables and linking them with a foreign key). - Row Splitting, here is what I don't understand, what is the difference between Normalization and Row Splitting? In what those 2 techniques differ from each other? I have also read in this post (https://stackoverflow.com/questions/11707879/difference-between-scaling-horizontally-and-vertically-for-databases ) that the difference between Horizontal Partitioning and Vertical Partitioning is that in the first you scale by adding more machines, while in the second one you scale by adding more power (CPU, RAM) to your existing machine, is that a correct definition? I thought that the core difference between those 2 techniques resides in the way you split your tables. This answer does make sense according to MongoDB's definition of Vertical Partitioning: http://docs.mongodb.org/manual/core/sharding-introduction/ but it goes in contradiction with other answers or articles I have come across: https://stackoverflow.com/questions/18302773/what-are-horizontal-and-vertical-partitions-in-database-and-what-is-the-differen http://technet.microsoft.com/en-us/library/ms178148%28v=sql.105%29.aspx http://building.wanelo.com/post/42361472646/the-case-for-vertical-sharding I am sorry for the load of questions but I am a bit confused as a lot of different websites that I have came across say different things. Any help clarifying would be greatly appreciated. Any link to a clear and simple demonstration with a few tables would also be very helpful.

Our DB design presently has a interval column which will only be storing days (no other interval type) so it is making sense to use INT2 (smallint) instead of interval. Reference to documentation. Advantage: 2 bytes instead of 12 bytes (we have many such columns). Is this line of thinking ok or am I overlooking something?

I have 4 development SQL Server 2016 instances on a virtual server (128 GB, 12 virtual processors). Each instance is configured the same (16 GB memory, automatic processor and I/O affinity masks). All instance parameters are the same. Each instance has a database called "operations" with a table called "ErrLog" with a clustered PK index; each instance's ErrLog currently has 600-1000 records. Each operations database has one datafile on E: drive, one tx log file on F: drive. Querying the ErrLog table in instances (b), (c), and (d) is sub-second. In instance (a), it consistently takes 2-4 seconds. I do typical maintenance (Ola Hallengren's index and statistic maintenance once a week), but this is consistently done in each instance. I have used sp_BlitzFirst and sp_Blitz, which return issues, but consistent issues between the instances. And so far I'm not noticing any issues returned by various checkdb operations. Can anyone suggest other troubleshooting steps to determine why instance (a) is having different performance than others? (Querying ErrLog is just a proxy for honing in on the differences; other queries are also performing differently.) Also, I restored a copy of a.Operations into b.Operations_test, and query b.Operations_test.ErrLog also takes 2-4 seconds. I restored a copy of b.Operations into a.Operations_test, and querying a.Operations_test is sub-second. EDIT: Thanks for your replies... Here is the structure of the table (same in each of the instances); just the one, clustered primary key index:

CREATE TABLE [dbo].[ErrLog](
	[ErrorID] [int] IDENTITY(1,1) NOT NULL,
	[TimestampCreate] [dbo].[TimestampCreate] NOT NULL,
	[ErrorNumber] [int] NOT NULL,
	[ErrType] [int] NOT NULL,
	[ErrLocation] [varchar](2000) NULL,
	[ErrContext] [text] NULL,
	[ErrSeverity] [int] NOT NULL,
	[MessageLong] [varchar](1024) NOT NULL,
	[ServerName] [dbo].[DescriptionLong] NULL,
 CONSTRAINT [PK_ErrLog] PRIMARY KEY CLUSTERED 
(
	[ErrorID] ASC
)

PasteAPlan results: [NP02](https://www.brentozar.com/pastetheplan/?id=Skapt4E2j) [NP03](https://www.brentozar.com/pastetheplan/?id=H1d9iN42s) This might be a clue to the different performances: I find that in NP02, some of the records have much longer values in the ErrContext text field: NP02: 668 rows; maximum length of "ErrContext" text field is 3,090,039 NP03: 648 rows; maximum length of "ErrContext" text field is 154,013 NP04: 411 rows; max length of "ErrContext" text field is 28,519 NP05: 46 rows; max length of "ErrContext" text field is 55,445 Top 10 Wait Statistics (pulled using Pinal Dave's query): NP02: |Wait_Type|Wait_Time_Seconds|Waiting_Tasks_Count|Percentage_WaitTime| |---------|-----------------|-------------------|-------------------| |OLEDB|17951.078|1006510364|34.22289012| |BACKUPBUFFER|5775.054|6016680|11.00987018| |ASYNC_IO_COMPLETION|5491.216|3688|10.46874632| |BACKUPIO|5441.588|5592972|10.37413286| |IO_COMPLETION|3451.687|1192286|6.58047973| |SOS_SCHEDULER_YIELD|3271.515|6502869|6.236990244| |PAGEIOLATCH_SH|3191.304|2482244|6.084071726| |BACKUPTHREAD|2224.763|229830|4.241406543| |TRACEWRITE|1680.446|864|3.203691656| NP03: |Wait_Type|Wait_Time_Seconds|Waiting_Tasks_Count|Percentage_WaitTime| |---------|-----------------|-------------------|-------------------| |CXPACKET|60954.838000|24948490|20.506848003464389| |BACKUPBUFFER|58801.396000|27540692|19.782372158277558| |ASYNC_IO_COMPLETION|48562.138000|1528|16.337610194112273| |BACKUPIO|41613.771000|28454440|13.999992531322523| |OLEDB|27122.218000|1397826908|9.124644085557670| |BACKUPTHREAD|22593.140000|1078602|7.600940353594105| |ASYNC_NETWORK_IO|12203.635000|1545004|4.105631254975333| |PAGEIOLATCH_SH|8875.883000|5384558|2.986085921145972| |SOS_SCHEDULER_YIELD|5326.097000|43211882|1.791842374032848| |IO_COMPLETION|3153.986000|1708478|1.061085774800265|

I know that an INSERT on a SQL table can be slow for any number of reasons: * Existence of INSERT TRIGGERs on the table * Lots of enforced constraints that have to be checked (usually foreign keys) * Page splits in the clustered index when a row is inserted in the middle of the table * Updating all the related non-clustered indexes * Blocking from other activity on the table * Poor IO write response time * ... anything I missed? How can I tell which is responsible in my specific case? How can I measure the impact of page splits vs non-clustered index updates vs everything else? I have a stored proc that inserts about 10,000 rows at a time (from a temp table), which takes about 90 seconds per 10k rows. That's unacceptably slow, as it causes other spids to time out. I've looked at the execution plan, and I see the INSERT CLUSTERED INDEX task and all the INDEX SEEKS from the FK lookups, but it still doesn't tell me for sure why it takes so long. No triggers, but the table does have a handful of FKeys (that appear to be properly indexed). This is a SQL 2000 database.

AWS RDS Postgresql 12.10 According to https://www.enterprisedb.com/postgres-tutorials/comprehensive-guide-how-tune-database-parameters-and-configuration-postgresql , > max_worker_processes **Set this to the number of CPUs you want to share > for PostgreSQL exclusively**. This is the number of background processes > the database engine can use. Setting this parameter will require a > server restart. **The default is 8.** The default is also 8 in AWS RDS Postgresql, no matter the Instance type (and thus number of vCPUs). Am I cheating myself by paying for a db.r5.12xlarge (48 CPUs) while using the default max_worker_processes value?

is it possible if min_wal_size bigger than max_wal_size ? What happen if it does ? What are the circumstances when min_wal is bigger than max_wal ? I am using PG 12. Thanks

I have a dedicated server to run a WordPress website. I use Nignx + php fpm + MySql 8.0. Php processes are set to run for a maximum of 60 seconds, and mysql is used just to run this website. I checked the value of wait_timeout and is set to 28800 (8 hours). WordPress doesn't use persistent connections. Given the php process duration can I lower wait_timeout value? I'm tempted to lower this value to 10 seconds but I'm quite newbie and I'm unsure about consequences of doing this. Can you help me understand better the implications?

I am currently reading [SQL Perfrmance Explained](https://sql-performance-explained.com/) by Marcus Winand. I would like to follow along with the queries and examples he used to explain database tuning. It would greatly allow me to understand by doing things hands on and experiment with my own queries. I can set up a Docker container with an Oracle database and simply copy over the dataset(s) (perhaps in the form of CSV files?) into tables in the database. I just need the files themselves. **Question: Anyone know where to find the dataset(s) Marcus Winand used in his book [SQL Perfrmance Explained](https://sql-performance-explained.com/)?**

I am migrating from a 4 year old setup to a brand new high performance server and am experiencing slower performance than on the old machine. The old setup is a Ubuntu 16.04 Server on bare metal Intel i7 64G RAM and MySQL 5.7 and is under medium load with many services running. The new setup is an AMD Ryzen™ 9 5950X with 128G RAM, no load and only MySQL 8 running on Ubuntu 20.04. Both are bare metal machines and I am the only one who can access it. Now the database I am testing against holds a table with 100M rows. The new one holds an exact copy, imported after a mysqldump. I am running the exact same simple query without any join, just from one table. **The old server returns the result in about 120ms the new one returns in about 200ms.** > 10 rows retrieved starting from 1 in 192 ms (execution: 184 ms, > fetching: 8 ms) I am using DataGrip for the query (connected via SSH). If I connect via SSH terminal directly and use MySQL on the console the result is returned in 0.00s How is that even possible? I did run a few tests via mysqltuner, but found nothing that might be of help. Here is part of the the my.cnf from the new server: [mysqld] default_authentication_plugin= mysql_native_password innodb_buffer_pool_size = 100G innodb_buffer_pool_instances = 64 innodb_buffer_pool_chunk_size = 134217728 innodb_log_file_size = 12G collation_server = utf8_unicode_ci character_set_server = utf8 This is mysqltuner output: mysqltuner >> MySQLTuner 1.7.13 - Major Hayden >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering [--] Skipped version check for MySQLTuner script Please enter your MySQL administrative login: root Please enter your MySQL administrative password: [!!] Currently running unsupported MySQL version 8.0.25-0ubuntu0.20.04.1 [OK] Operating on 64-bit architecture -------- Log file Recommendations ------------------------------------------------------------------ [--] Log file: /mnt/mysql/data/leo.err(0B) [!!] Log file /mnt/mysql/data/leo.err doesn't exist [!!] Log file /mnt/mysql/data/leo.err isn't readable. -------- Storage Engine Statistics ----------------------------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA [--] Data in InnoDB tables: 16.8G (Tables: 39) [OK] Total fragmented tables: 0 -------- Analysis Performance Metrics -------------------------------------------------------------- [--] innodb_stats_on_metadata: OFF [OK] No stat updates during querying INFORMATION_SCHEMA. -------- Security Recommendations ------------------------------------------------------------------ [--] Skipped due to unsupported feature for MySQL 8 -------- CVE Security Recommendations -------------------------------------------------------------- [OK] NO SECURITY CVE FOUND FOR YOUR VERSION -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 1m 47s (419 q [3.916 qps], 45 conn, TX: 402K, RX: 35K) [--] Reads / Writes: 100% / 0% [--] Binary logging is enabled (GTID MODE: OFF) [--] Physical Memory : 125.8G [--] Max MySQL memory : 104.2G [--] Other process memory: 136.7M [--] Total buffers: 104.0G global + 1.1M per thread (151 max threads) [--] P_S Max memory usage: 72B [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 104.0G (82.72% of installed RAM) [OK] Maximum possible memory usage: 104.2G (82.85% of installed RAM) [OK] Overall possible memory usage with other process is compatible with memory available [OK] Slow queries: 0% (0/419) [OK] Highest usage of available connections: 1% (2/151) [!!] Aborted connections: 4.44% (2/45) [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance [--] Query cache have been removed in MySQL 8 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 19 sorts) [!!] Joins performed without indexes: 18 [OK] Temporary tables created on disk: 0% (0 on disk / 23 total) [OK] Thread cache hit rate: 95% (2 created / 45 connections) [OK] Table cache hit rate: 80% (327 open / 408 opened) [OK] Open file limit used: 0% (2/10K) [OK] Table locks acquired immediately: 100% (9 immediate / 9 locks) [OK] Binlog cache memory access: 0% (0 Memory / 0 Total) -------- Performance schema ------------------------------------------------------------------------ [--] Memory used by P_S: 72B [--] Sys schema is installed. -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is disabled. -------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Key buffer used: 18.2% (3M used / 16M cache) [!!] Cannot calculate MyISAM index size - re-run script as root user -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 0 [OK] InnoDB File per table is activated [OK] InnoDB buffer pool / data size: 104.0G/16.8G [OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 12.0G * 2/104.0G should be equal 25% [OK] InnoDB buffer pool instances: 64 [--] Number of InnoDB Buffer Pool Chunk : 832 for 64 Buffer Pool Instance(s) [OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances [!!] InnoDB Read buffer efficiency: 13.96% (5858 hits/ 41974 total) [OK] InnoDB Write log efficiency: 98.44% (630 hits/ 640 total) [OK] InnoDB log waits: 0.00% (0 waits / 10 writes) -------- AriaDB Metrics ---------------------------------------------------------------------------- [--] AriaDB is disabled. -------- TokuDB Metrics ---------------------------------------------------------------------------- [--] TokuDB is disabled. -------- XtraDB Metrics ---------------------------------------------------------------------------- [--] XtraDB is disabled. -------- Galera Metrics ---------------------------------------------------------------------------- [--] Galera is disabled. -------- Replication Metrics ----------------------------------------------------------------------- [--] Galera Synchronous replication: NO [--] No replication slave(s) for this server. [--] Binlog format: ROW [--] XA support enabled: ON [--] Semi synchronous replication Master: Not Activated [--] Semi synchronous replication Slave: Not Activated [--] This is a standalone server -------- Recommendations --------------------------------------------------------------------------- General recommendations: MySQL was started within the last 24 hours - recommendations may be inaccurate Reduce or eliminate unclosed connections and network issues Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1 Adjust your join queries to always utilize indexes Variables to adjust: join_buffer_size (> 256.0K, or always use indexes with JOINs) What is a recommended aproach to narrow down the problem?

How do I determine the right value for innodb_sync_array_size in MySQL? We have version 5.7 deployed on AWS RDS with 32 vCPUs, and it sees about 26 concurrent threads on average. When I measure waits using:

show engine innodb mutex

, the total number of waits increases by about 143k / hour. The MySQL documentation does say that a higher number will help with high concurrency and wait situation, but I cannot find any documentation on what to set this number and how to determine whether this will indeed help me.

I'm working on my first project that makes heavy use of a MySQL database and am struggling to understand performance issues I'm having. Most of the time, responses are returned quickly from the server, but when I have certain scripts that make a lot of queries (both reading and writing data) running in the background, I'm finding that other requests are slow to receive responses, or are timing out entirely. As an example, yesterday when a PHP script that makes a lot database queries was running, requests to the server for pages that made a number of queries before returning a response were timing out -- I couldn't even connect to the database with Navicat, as it timed out as well. In the above example, I ran top/htop in an SSH console and found that there was very low CPU usage and memory usage was only at about 50%. Because of this, I think the problem lies with the database, but I'm having trouble understanding what is causing the issues, such as whether they might be due to: - Slow queries - Too many connections - Too many queries per second - Other potential issues I'm not aware of I know about the slow query log, but none of my queries are very slow. What methods or tools can I use to determine what is causing a MySQL database to become slow, sometimes with requests even timing out?

Suppose I have the following table **data(partitioned_key_index, some_dummy_measure)** Assume that partitions are of an equal large size. with Oracle 11g. The end result should be like this select partitioned_key_index, sum(some_dummy_measure) from data group by partitioned_key_index Each partition will be grouped independently, the optimizer should be clever enough to come up with a plan in which each partition will be aggregated then a simple 'union all' to get the desired output. What I want to do is something close to this select 1 as partitioned_key_index, sum(some_dummy_measure) from data where partitioned_key_index = 1 Union All select 2 as partitioned_key_index, sum(some_dummy_measure) from data where partitioned_key_index = 2 Union All . . . select i as partitioned_key_index, sum(some_dummy_measure) from data where partitioned_key_index = i My intuition with the above method is to to serialize the hash group operation thus each partition will be moved from the disk to the buffer cache with the hope of not spilling into disk for the group by operation. Any ideas how to tune this kind of queries?