Questions tagged [online-operations]

Performing physical (e.g. index or DDL) operations with increased concurrency (reduced locking).

Filter by
Sorted by
Tagged with
1
vote
1answer
52 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 ...
1
vote
1answer
88 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;...
2
votes
2answers
85 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 ...
3
votes
1answer
83 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 ...
0
votes
1answer
68 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-...
6
votes
3answers
551 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 ...
0
votes
0answers
404 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 ...
0
votes
1answer
40 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 ...
0
votes
1answer
232 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 ...
1
vote
0answers
184 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 ...
2
votes
2answers
190 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 ...
1
vote
0answers
316 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 ...
1
vote
1answer
151 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 ...
15
votes
1answer
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 ...
1
vote
0answers
48 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 (...
5
votes
0answers
2k 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 ...
7
votes
2answers
8k 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 ...
11
votes
1answer
14k 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 ...
8
votes
1answer
809 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 ...
27
votes
3answers
19k 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 ...
7
votes
0answers
711 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 ...
3
votes
2answers
610 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 ...
1
vote
0answers
79 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:/...
7
votes
1answer
77 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
votes
1answer
121 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 ...
1
vote
1answer
611 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 ...
7
votes
2answers
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 ...