Tag Info

New answers tagged

1

The problem had to do with a kernel feature called Transparent Huge Pages (THP.) You can diagnose this with perf top: 59.73% postmaster [kernel.kallsyms] [k] compaction_alloc 1.31% postmaster [kernel.kallsyms] [k] _spin_lock 0.94% postmaster [kernel.kallsyms] [k] __reset_isolation_suitable 0.78% postmaster ...


2

A dump-restore cycle removes all bloat and dead tuples from your tables and restores with the minimum possible size - except if you have a fillfactor setting below 100 that reserves some wiggle-room per data page. Immediately after the migration, you get a lot more "extensions" (added pages at the physical end of the table (the file on disk). The ...


2

Simplified scenario: CREATE TABLE IF NOT EXISTS `test`.`revision` ( `idrevision` INT UNSIGNED NOT NULL, `iduser` INT UNSIGNED NOT NULL, `idpost` INT UNSIGNED NOT NULL, `content` VARCHAR(4000) NOT NULL, UNIQUE INDEX `index1` (`idpost`, `idrevision`), PRIMARY KEY (`idrevision`, `iduser`), INDEX `user1_idx` (`iduser`)); INSERT INTO ...


2

According to the documentation located at http://www.postgresql.org/docs/9.4/static/explicit-locking.html#LOCKING-DEADLOCKS (emphasis mine): PostgreSQL automatically detects deadlock situations and resolves them by aborting one of the transactions involved, allowing the other(s) to complete. (Exactly which transaction will be aborted is difficult to ...


0

I ended up doing this, to do it in batches of 1krow (which takes about .2s per batch if there's nothing to update, 2s otherwise; basically it has the same total speed per row if it's in batches of 10k, 5k or 1k): SET SESSION sql_mode = 'strict_all_tables'; SET SESSION sql_warnings = 1; \W DELIMITER // DROP PROCEDURE IF EXISTS update_dmf_lifespan// CREATE ...


1

You can find all tables locks in an Oracle database by running the following query select c.owner, c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine from v$locked_object a , v$session b, dba_objects c where b.sid = a.session_id and a.object_id = c.object_id;



Top 50 recent answers are included