Questions tagged [online-operations]
Performing physical (e.g. index or DDL) operations with increased concurrency (reduced locking).
30
questions
3
votes
1
answer
206
views
During an online piecemeal restore, is it possible to restore to a point-in-time?
I have a database (full recovery model) with multiple filegroups, that each have full and diff backups. I am able to replicate an online piecemeal restore (SQL Server 2019 Enterprise Edition) as it is ...
- 83
1
vote
1
answer
532
views
Run pt-online-schema-change with multiple ALTER queries synchronously
I want to run 3 ALTER queries with pt-online-schema-change tool:
--alter "ADD INDEX userid_sid_ts_fid (user_id, scorecard_id, timestamp, factor_id), DROP INDEX uidts, RENAME INDEX ...
- 113
1
vote
1
answer
60
views
Index creation on partitioned table is not completely offline in Standard Edition
We have SQL Server 2019 Standard Edition
Microsoft SQL Server 2019 (RTM-CU10) (KB5001090) - 15.0.4123.1 (X64)
Mar 22 2021 18:10:24 Copyright (C) 2019 Microsoft Corporation
Standard Edition (64-bit)...
- 10.6k
1
vote
1
answer
2k
views
Alter Table Drop Column INPLACE NoLock - until which point is the column being dropped accessible?
I am trying to run MySQL Alter INPLACE command to drop few columns from a very large 90GB table. While the Alter is running I am able to run the Select statement on the same table to ensure that the ...
- 195
1
vote
1
answer
536
views
Table is full even with innodb_file_per_table
I am trying to create an index to my table using alter query.
My my.cnf file
innodb_data_home_dir = /usr/local/mysql5/data
innodb_data_file_path = ibdata1:60021538816;ibdata2:300M;ibdata3:30000M;...
- 61
2
votes
2
answers
942
views
Convert postgres varchar[] to text[] without downtime
As I discovered in researching this question, converting a column from varchar(100)[] to text[] requires a table rewrite and rebuilding indexes.
Is there a way to manage this conversion without the ...
- 283
3
votes
1
answer
1k
views
Postgres varchar(100)[] -> text[] column conversion
I need to change a column type from varchar(100)[] to text[]. The table has >100M rows and heavy write load, so I'd like to avoid taking an access exclusive lock and rewriting the index. I saw in ...
- 283
0
votes
1
answer
262
views
Online schema changes for cloudsql DB
I'm new to cloudsql for mysql. I have a table with trigger. I need to do some ddl changes on that table. However, I can't take any downtime.
What I have tried so far:
I tried with pt-online-schema-...
- 348
7
votes
3
answers
1k
views
Online Index Rebuild of large table takes exclusive lock
I'm trying to rebuild the clustered index of a large table (77GB) on Azure SQL Database. There is high concurrent transactional activity on the table so I'm using the ONLINE=ON option.
This has worked ...
- 486
0
votes
0
answers
890
views
Are online index REBUILD operation possible on every Azure SQL database?
I am trying to find informations about the possibility to operate online index REORGANIZE/REBUILD on a database.
What I actually found in the Microsoft documentation is that:
Reorganizing an index ...
- 101
0
votes
1
answer
339
views
How to determine if an ALTER statement will be a metadata-only operation O(1) or a size of data operation O(n)?
We are dealing with very large tables in Sql Azure.
Size-of-data operations can lock tables for a very long time so we must be careful to run these only on week ends.
Metadata operations are quick ...
- 486
0
votes
1
answer
564
views
Optimize table on large table online DDL Feature
I am running optimize table on MySQL table size 340GB in size and storage engine is InnoDB.
mysql> show variables like '%tmp%';
+----------------------------+------------------+
| Variable_name ...
- 133
1
vote
0
answers
547
views
Online Index Rebuild shows self blocking
I've been doing some testing on rebuilding some larger clustered indexes (with online + sort_in_tempdb on) and I'm observing something that I can't immediately explain. I have a hunch that this is ...
- 3,804
2
votes
2
answers
442
views
How to make DDL changes to large tables and cause minimum server and object contention?
I work with a lot of large tables (billions to tens of billions of records in each) in a database I recently inherited. I see a few clear DDL changes that would benefit the use-cases of the database ...
- 35.2k
2
votes
0
answers
908
views
ALTER TABLE ... ADD COLUMN ... NOT NULL is not instant in PostgreSQL 11?
I have read the documentation and it states that adding a column with a non-null and non-volatile default value should not require a table rewrite. However, this does require a rewrite and blocks all ...
- 308
3
votes
1
answer
708
views
Adding column into MySQL table size 150GB
I am trying to add column into MySQL table
Table size 150GB
Table storage engine= innodb
MySQL version=5.6.34
Table have already created primary key(cluster index).
More than 2 hours completed and ...
- 133
19
votes
1
answer
2k
views
What exactly is the 'online' in OLAP and OLTP?
I'm a bit confused because I'm questioning the definition of 'online' in OLTP and OLAP. I used to think that 'online' here means that we want our answer in bounded time and based on the data available ...
- 467
1
vote
0
answers
64
views
Azure SQL - Modifying Indexed Views without downtime
We have a SQL Azure database with a large indexed view that supports a website with high traffic during business hours. One of our goals from a DevOps perspective to be able to ship software (...
- 111
8
votes
0
answers
3k
views
How do I rename a table with minimal downtime in a production postgres database?
I understand that renaming a table in postgres boils down to a simple catalog update. However, it also requires an ACCESS EXCLUSIVE lock to ensure transactions are safe.
What would be the ideal way ...
- 181
7
votes
2
answers
10k
views
SQL Server 2017 Enterprise vs Standard - Online Indexing and Online Schema Change
I read through Microsoft's "Editions and supported features of SQL Server 2017" doc to compare the feature differences between Enterprise and Standard edition.
Two things not available in Standard ...
- 35.2k
14
votes
1
answer
24k
views
Changing foreign key to ON DELETE CASCADE with least amount of impact
I have an existing foreign key that has ON DELETE NO ACTION defined. I need to change this foreign key to ON DELETE CASCADE. I can do this within a transaction:
begin;
alter table posts drop ...
- 317
8
votes
1
answer
916
views
Does RE-BUILDing a HEAP cause downtime?
This is a bit of an embarrassing question, and I can't believe I've missed this for so many years.
I have a vendor third-party database that has 401 heap tables. I've recently got into using Brent ...
- 352
28
votes
3
answers
29k
views
Why would I ever prefer ALGORITHM=COPY to ALGORITHM=INPLACE?
Since MySQL 5.6 introduced online DDL, the ALTER TABLE command can optionally have either ALGORITHM=INPLACE or ALGORITHM=COPY specified. The overview of online DDL notes that, by default, INPLACE is ...
- 1,075
8
votes
0
answers
907
views
How to minimize access exclusive table locks while reducing bloat or ALTER (ing) a table?
In some circumstances, I've been told not to do a VACUUM FULL (or CLUSTER) of a table that is in production, because that will exclusively lock it for longer than wished. The same might apply to ...
- 13.3k
3
votes
2
answers
842
views
MySQL Online DDL Index Creation
I have a table with 25 million records and 10 columns on which I am trying to create indexes. According to the following documentation no table copy should occur but when I monitor disk usage it is ...
- 71
1
vote
0
answers
85
views
When are UPDATE / INSERT operations processed in Online DDL in MySQL 5.6?
MySQL 5.6 includes functionality called Online DDL which allows ALTER operations to proceed on tables without applying a LOCK to the table (technically referred to as allowing Concurrent DML).
https:/...
- 111
7
votes
1
answer
80
views
Update an Indexed View "Online"
Say I have an indexed view that looks like this:
ALTER VIEW dbo.MyIndexedView WITH SCHEMABINDING
AS
SELECT ord.SomeColumn, COUNT_BIG(*) AS Count
FROM dbo.Ordered ord
WHERE ord....
- 2,484
2
votes
1
answer
129
views
onsite and offsite backup question
We have an sql database of 165GB (includes filestreaming) and we do an onsite full backup every evening using veritas. We would like to also have an online backup in place just so that we don't need ...
- 21
1
vote
1
answer
828
views
Order of DROP_EXISTING and ONLINE for CREATE INDEX
If I call a CREATE NONCLUSTERED INDEX using DROP_EXISTING = ON and ONLINE = ON what order are things done it?
What I want is:
New index is made in the TempDB
Existing Index is dropped
New index is ...
- 2,484
7
votes
2
answers
2k
views
Different results rebuilding an index online and offline
I have a non-clustered, non-unique index on a foreign key column of type bigint. When I rebuild the index online, the average fragmentation drops to 3%, with 2 fragments, and 30 pages.
When I run ...
- 173