Hi, Thanks for your help Alban !
Alexis. 2014-08-09 18:46 GMT+02:00 Alban Hertroys <haram...@gmail.com>: > On 09 Aug 2014, at 13:24, Alexis Bernard <ale...@bernard.io> wrote: > > > Primary key definition: "tracks_pkey" PRIMARY KEY, btree (id) > > select version(): PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled > by gcc (Ubuntu 4.8.2-16ubuntu6) 4.8.2, 64-bit > > Ubuntu trusty with kernel 3.13.0-29-generic. > > > > I have a autovacuum process running and configuration is by default: ps > aux | grep vacuum | grep -v grep > > postgres 587 0.0 0.0 1174304 3720 ? Ss juil.30 0:05 > postgres: autovacuum launcher process > > > > I tried to vacuum manualy. > > What’s the output of: VACUUM VERBOSE tracks; > Anything that looks abnormal? > > INFO: vacuuming "public.tracks" INFO: scanned index "tracks_pkey" to remove 25777 row versions DETAIL: CPU 0.03s/0.12u sec elapsed 0.87 sec. INFO: scanned index "index_tracks_on_permalink" to remove 25777 row versions DETAIL: CPU 0.07s/0.40u sec elapsed 5.30 sec. INFO: "tracks": removed 25777 row versions in 5374 pages DETAIL: CPU 0.00s/0.14u sec elapsed 0.66 sec. INFO: index "tracks_pkey" now contains 1657345 row versions in 4594 pages DETAIL: 17739 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "index_tracks_on_permalink" now contains 1657345 row versions in 6143 pages DETAIL: 25715 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "tracks": found 17560 removable, 1657349 nonremovable row versions in 35586 out of 35586 pages DETAIL: 0 dead row versions cannot be removed yet. There were 64749 unused item pointers. 0 pages are entirely empty. CPU 0.14s/0.86u sec elapsed 7.30 sec. INFO: vacuuming "pg_toast.pg_toast_17228" INFO: index "pg_toast_17228_index" now contains 0 row versions in 1 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pg_toast_17228": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM > What do below queries return? They’re from the manual at: > http://www.postgresql.org/docs/9.3/static/routine-vacuuming.html > > SELECT c.oid::regclass as table_name, > greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age > FROM pg_class c > LEFT JOIN pg_class t ON c.reltoastrelid = t.oid > WHERE c.relkind IN ('r', 'm'); > > table_name | age --------------------------------------------+---------- pg_statistic | 37049759 pg_type | 37049759 artists | 37042311 pg_authid | 37049759 pg_attribute | 37049759 pg_proc | 37049759 pg_class | 37049759 pg_user_mapping | 37049759 pg_constraint | 37049759 pg_inherits | 37049759 pg_index | 37049759 pg_operator | 37049759 pg_opfamily | 37049759 pg_opclass | 37049759 pg_am | 37049759 pg_amop | 37049759 pg_amproc | 37049759 pg_language | 37049759 pg_largeobject_metadata | 37049759 pg_database | 37049759 pg_rewrite | 37049759 pg_trigger | 37049759 pg_event_trigger | 37049759 pg_description | 37049759 pg_cast | 37049759 pg_enum | 37049759 pg_namespace | 37049759 pg_conversion | 37049759 pg_depend | 37049759 explorers | 36847481 pg_db_role_setting | 37049759 pg_tablespace | 37049759 pg_pltemplate | 37049759 pg_auth_members | 37049759 pg_shdepend | 37049759 pg_shdescription | 37049759 pg_ts_config | 37049759 pg_ts_config_map | 37049759 pg_ts_dict | 37049759 pg_ts_parser | 37049759 pg_ts_template | 37049759 pg_extension | 37049759 pg_foreign_data_wrapper | 37049759 pg_foreign_server | 37049759 pg_foreign_table | 37049759 pg_default_acl | 37049759 pg_seclabel | 37049759 pg_shseclabel | 37049759 pg_range | 37049759 pg_largeobject | 37049759 information_schema.sql_implementation_info | 37049759 information_schema.sql_languages | 37049759 information_schema.sql_packages | 37049759 information_schema.sql_sizing | 37049759 information_schema.sql_sizing_profiles | 37049759 delayed_jobs | 37042298 pg_attrdef | 37049759 pg_aggregate | 37049759 invitation_requests | 37042286 imports | 37042289 information_schema.sql_features | 37049759 pg_collation | 37049759 information_schema.sql_parts | 37049759 schema_migrations | 37042277 tracks | 37042273 invitations | 37042283 users | 37042270 playlists | 37042280 sources | 37042276 absorbed_tracks | 37042314 countries | 37042302 bridge_errors | 37042308 bridges | 37042305 countries_sources | 37042299 favorites | 37042295 import_entries | 37042292 > SELECT datname, age(datfrozenxid) FROM pg_database; > > datname | age ------------------+---------- template1 | 37049756 template0 | 37049756 postgres | 37049756 disco_production | 37049756 > I’m wondering if perhaps autovacuum doesn’t run to completion, the above > should determine that. > > To get out of this pickle, you might want to try VACUUM FULL, but that > puts an exclusive lock on the table and creates a new copy of it while it’s > running, so you better have enough disk space to contain that table twice. > It does rewrite the table though, which is what I hope fixes your issue. > The normal VACUUM should have updated the frozenid’s appropriately though, > a VACUUM FULL should not actually be necessary. > > > I tried reindex: > > > > => reindex index tracks_pkey; > > ERROR: failed to find parent tuple for heap-only tuple at (38802,116) > in table “tracks > > The problem is probably not in the index then, or it wouldn’t have trouble > re-creating it... > > > Here are more information about storage (raid 1): > > Do I read that as disks 0 to 2 are part of an LSI SAS? Are you really > using raid-1 then, as if I’m reading that correctly only 1 of the 3 disks > is actually attached to the raid array (namely the Dell disk) and the > others are "not claimed" (i.e. spares)? > Not sure, but I understood that the Dell RAID 1 controller contains 2 Seagate disks. > > Anyway, from this it looks like you are using reliable storage that > properly honours fsync commands. Or is this a hardware raid-controller with > no battery backup unit and caching set to write-back instead of write-thru? > > No idea. I can ask to my hosting provider if needed. > Oh, one last thing, the convention in this mailing list is to reply inline > instead of top-posting. > > Got it :-) > > lshw -class disk -class storage > > *-storage > > description: Serial Attached SCSI controller > > produit: SAS2008 PCI-Express Fusion-MPT SAS-2 [Falcon] > > fabriquant: LSI Logic / Symbios Logic > > identifiant matériel: 0 > > information bus: pci@0000:01:00.0 > > nom logique: scsi0 > > version: 03 > > bits: 64 bits > > horloge: 33MHz > > fonctionnalités: storage pm pciexpress vpd msi msix bus_master > cap_list rom > > configuration: driver=mpt2sas latency=0 > > ressources: irq:16 portE/S:2000(taille=256) > mémoire:c5140000-c514ffff mémoire:c5100000-c513ffff > mémoire:c5400000-c54fffff > > *-disk:0 NON-RÉCLAMÉ > > description: ATA Disk > > produit: ST1000NM0033-9ZM > > fabriquant: Seagate > > identifiant matériel: 0.0.0 > > information bus: scsi@0:0.0.0 > > version: GA04 > > numéro de série: Z1W0HGB9 > > configuration: ansiversion=5 > > *-disk:1 > > description: SCSI Disk > > produit: Virtual Disk > > fabriquant: Dell > > identifiant matériel: 1.0.0 > > information bus: scsi@0:1.0.0 > > nom logique: /dev/sda > > version: 1028 > > taille: 931GiB (999GB) > > capacité: 931GiB (999GB) > > fonctionnalités: 15000rpm partitioned partitioned:dos > > configuration: ansiversion=6 sectorsize=512 signature=000e59a7 > > *-disk:2 NON-RÉCLAMÉ > > description: ATA Disk > > produit: ST1000NM0033-9ZM > > fabriquant: Seagate > > identifiant matériel: 0.1.0 > > information bus: scsi@0:0.1.0 > > version: GA04 > > numéro de série: Z1W0HFYZ > > configuration: ansiversion=5 > > *-storage > > description: SATA controller > > produit: 6 Series/C200 Series Chipset Family SATA AHCI Controller > > fabriquant: Intel Corporation > > identifiant matériel: 1f.2 > > information bus: pci@0000:00:1f.2 > > version: 04 > > bits: 32 bits > > horloge: 66MHz > > fonctionnalités: storage msi pm ahci_1.0 bus_master cap_list > > configuration: driver=ahci latency=0 > > ressources: irq:43 portE/S:3048(taille=8) portE/S:3054(taille=4) > portE/S:3040(taille=8) portE/S:3050(taille=4) portE/S:3020(taille=32) > mémoire:c5204000-c52047ff > > > > Cheers, > > Alexis. > > > > > > 2014-08-09 12:35 GMT+02:00 Alban Hertroys <haram...@gmail.com>: > > On 09 Aug 2014, at 11:38, Alexis Bernard <ale...@bernard.io> wrote: > > > > > Hi, > > > > > > I am having regurlarly duplicated rows with the same primary key. > > > > > > => select id, created_at, updated_at from tracks where created_at = > '2014-07-03 15:09:16.336488'; > > > id | created_at | updated_at > > > --------+----------------------------+---------------------------- > > > 331804 | 2014-07-03 15:09:16.336488 | 2014-07-03 15:37:55.253729 > > > 331804 | 2014-07-03 15:09:16.336488 | 2014-07-03 15:37:55.253801 > > > > > > => select id from tracks where id = 331804; > > > id > > > ---- > > > (0 rows) > > > > > > > First of all, what is the definition of that primary key? > > What exact version of PG are you using?: select version(); > > What exact OS is this on? What kind of storage? > > To me it looks like you may have run into transaction wrap-around or a > corrupted index. > > > > Before you do anything, make a backup. > > > > Theorising that the issue here indeed is transaction wrap-around, what > you’re seeing may be data from older transactions that has become newer > because your current transaction txid is lower (due to the wraparound) than > the txid of the transactions those rows belong(ed) to. If those > transactions were committed, then you’re possibly seeing deleted or updated > rows that are still around. TX wraparound can occur if you do not vacuum > frequently enough and another thing that vacuum does is mark old rows > obsolete so that the DB can reclaim the space they use. Seeing data from > rows that are no longer there or that has been modified since seems to fit > the bill here. > > > > Hence the question: When did you last (auto-)vacuum this table? Did you > perhaps turn autovacuum off? Did it fall behind? > > > > If you have been vacuuming and the issue is a corrupt index: Does it > help to reindex that table? You said that you have fsync on; what kind of > storage is this database on? Something allowed that index to get corrupted. > It is more likely that it’s caused by something in the underlying storage > system (including the OS) than that it is a bug in PG. > > > > > => delete from tracks where created_at = '2014-07-03 15:09:16.336488' > and updated_at = '2014-07-03 15:37:55.253801'; > > > ERROR: update or delete on table "tracks" violates foreign key > constraint "fk_sources_on_track_id" on table "sources" > > > DETAIL: Key (id)=(331804) is still referenced from table "sources”. > > > > Apparently there is a row from another table referencing this one. So > either the referenced row does actually exist (corrupt index theory) or it > doesn’t and the referencing row is from an older transaction as well (TX > wraparound theory). > > > > Considering that you’re seeing this regularly, my bet is on TX > wraparound. > > > > Alban Hertroys > > -- > > If you can't see the forest for the trees, > > cut the trees and you'll find there is no forest. > > > > > > Alban Hertroys > -- > If you can't see the forest for the trees, > cut the trees and you'll find there is no forest. > >
