Hi Kristian, If you look for explain analyze results for delete, you will see that 99% of time query spent on the foreign key triggers checks. In the same time the database have indexes on foreign key side in place.
I recommend try this: \timing on BEGIN; delete from zabbix.events where eventid = [some testing id]; select * from pg_stat_xact_user_tables where seq_scan>0 or idx_scan>0 order by seq_scan+idx_scan desc; ABORT; And provide result of the last query and how long delete runs. It might help us understand whats going on. Currently I have 3 ideas: 1)very very slow and overloaded IO subsystem 2)a lot of stuff being delete by ON DELETE CASCADE 3)some locking prevent foreign key checks run fast On Wed, Jul 24, 2019 at 11:12 AM Kristian Ejvind <kristian.ejv...@resurs.se> wrote: > Hi. > > Well, the events table has both a primary key and foreign keys referencing > it, which is not possible > on a partitioned table in postgresql 10. How did you work around this > issue? > > On the other hand, if we can get the deletion of rows from the events > table run at normal speed, I > can't imagine we would have a problem with it in a long time. After all, > although our Zabbix installation > definitely is larger than "small", it's still far from "large". > > I think I would need assistance with debugging why postgresql behaves like > it does. > Is there a defect with deleting data from a table that has multiple > foreign keys referencing it from a certain table? > Is there a problem with the query optimizer that chooses the wrong plan > when working on the foreign key constraints? > How do I inspect how the db works on the deletion of rows from the > referencing tables? > > Regards > Kristian > > > > ?On 2019-07-23, 16:33, "Kenneth Marshall" <k...@rice.edu> wrote: > > On Tue, Jul 23, 2019 at 01:41:53PM +0000, Kristian Ejvind wrote: > > Thanks Kenneth. In fact we've already partitioned the largest > history* and trends* tables > > and that has been running fine for a year. Performance was vastly > improved. But since you > > can't have a unique index on a partitioned table in postgres 10, we > haven't worked on that. > > > > Regards > > Kristian > > Hi Kristian, > > Why are you not partitioning the events and alerts tables as well? That > would eliminate this problem and you already have the infrastructure in > place to support the management since you are using it for the history > and trends tables. > > Regards, > Ken > > > > > > > Resurs Bank AB > Kristian Ejvind > Linux System Administrator > IT Operations | Technical Operations > > Ekslingan 8 > Box 222 09, SE-25467 Helsingborg > > Direkt Tfn: > Mobil: +46 728571483 > Vxl: +46 42 382000 > Fax: > E-post: kristian.ejv...@resurs.se > Webb: http://www.resursbank.se > > > > -- Maxim Boguk Senior Postgresql DBA https://dataegret.com/ Phone RU: +7 985 433 0000 Phone UA: +380 99 143 0000 Phone AU: +61 45 218 5678 LinkedIn: http://www.linkedin.com/pub/maksym-boguk/80/b99/b1b Skype: maxim.boguk "Доктор, вы мне советовали так не делать, но почему мне по-прежнему больно когда я так делаю ещё раз?"
