ERROR: out of overflow pages in hash index
Anyone know if this can be remedied? ERROR: out of overflow pages in hash index This occurred while waiting for a hash index to be created on a 1,580 GB table (Postgres 11.7). We have > 30 TB of space free. Here's the line where I think it gets triggered. https://github.com/postgres/postgres/blob/7559d8ebfa11d98728e816f6b655582ce41150f3/src/backend/access/hash/hashovfl.c#L283 And I found that #define HASH_MAX_BITMAPS Min(BLCKSZ / 8, 1024) So there looks like a maximum size here, but I couldn't find anything in the documentation. What is the maximum size? I am guessing partitioning the table someway might be a workaround, but it doesn't lend itself very well to partitioning (i.e. there's no year column, or category or some such). Help is appreciated!
Re: Modifying database schema without losing data
What if a person is a member of more than one Org? Consider a person_org table. I see mention of a site in the person table. It may also be the case that you need a site table. Often, you want a table for the Person and a Contact (or Address) table separately. This allows for having more than one contact for a Person. Org(id, ) Person(id, person_org_id, person_site_id, ... ) Person_Org(id, org_id, person_id, ) Contact(id, person_id, address, city, state, zip , email, ) Site(id, name, address, ) Person_Site(id, person_id, site_id, ... ) This way a person can be a member of more than one org, at one or more sites, and have one or more contacts. On Mon, Sep 28, 2020 at 10:15 AM Rich Shepard wrote: > I've been developing a business tracking application for my own use and > it's > worked well up to now. But, I need to modify it by adding a table with > attributes from two other tables. I've not drawn a E-R diagram so I show > the > two existing tables here: > > CREATE TABLE Organizations ( >org_id serial PRIMARY KEY, >org_name varchar(64) DEFAULT '??' NOT NULL, >org_addr1 varchar(64), >org_addr2 varchar(64), >org_city varchar(16), >state_code char(2), >org_postcode varchar(10), >org_country char(2) DEFAULT 'US' NOT NULL, >main_phone varchar(16), >org_fax varchar(12), >org_url varchar(64), >industry varchar(24) DEFAULT 'Other' NOT NULL > REFERENCES industries(ind_name) > ON UPDATE CASCADE > ON DELETE RESTRICT, >status varchar(20) DEFAULT 'Opportunity' NOT NULL > REFERENCES statusTypes(stat_name) > ON UPDATE CASCADE > ON DELETE RESTRICT, >comment text > ); > > CREATE TABLE People ( >person_id serial PRIMARY KEY, >lname varchar(15) NOT NULL, >fname varchar(15) NOT NULL, >job_title varchar(32), >org_id int DEFAULT '0' NOT NULL >REFERENCES Organizations(org_id) >ON UPDATE CASCADE >ON DELETE RESTRICT, >site_name varchar(64), >site_addr varchar(32), >site_city varchar(16), >state_code char(2), >site_postcode varchar(10), >site_country char(2) DEFAULT 'US' NOT NULL, >direct_phone varchar(15), >direct_fax varchar(15), >cell_phone varchar(15), >site_phone varchar(15), >ext varchar(6), >email varchar(64), >active boolean DEFAULT TRUE NOT NULL, >comment text > ); > > What I should have noticed when I designed this tool is that addresses and > phone/e-mail addresses can be duplicated when there's only a single > location. Now I have some prospective clients with multiple locations but I > have no names of individuals. So, I want to add a Location table with > addresses and contact information. Each row in that table will have a > serial PK > and will use a FK to reference the Organization table. People will now > reference the Locations table rather than the Organization table. > > There are data in each of these tables and my research in my books and on > the web have not provided any insights on how to modify the existing schema > and get date into their new appropriate table homes. > > I think the long way is to dump the database and manually move rows (using > emacs) from their current table to the new one, as appropriate, but > there're > probably much better ways to do this and I'm eager to learn. > > Regards, > > Rich > > >
Re: Improving performance of select query
select count(*) from is probably not using the index that your insert/select would, so I would not use that as a test for performance. If customer_backup has an index, the insert-select will be performance-limited by updating that index. If you can do a *create table customer_backup* as *select * from customer where customer_id in (,,..); * I expect it to run quite fast as long as customer_id is indexed and analyzed. On Mon, Dec 14, 2020 at 9:37 AM Muhammad Bilal Jamil wrote: > I think you can also increase the query performance by creating indexes? > > On Mon, 14 Dec 2020 at 11:36, Rob Sargent wrote: > >> >> >> > On Dec 14, 2020, at 4:47 AM, Thomas Kellerer wrote: >> > >> > Karthik Shivashankar schrieb am 14.12.2020 um 12:38: >> >> I have a postgres(v9.5) table named customer holding 1 billion rows. >> >> It is not partitioned but it has an index against the primary key >> >> (integer). I need to keep a very few records (say, about 10k rows) >> >> and remove everything else. >> >> >> >> /insert into customer_backup select * from customer where customer_id >> in (,,..); / >> >> >> >> >> >> >> >> If I go for something like above I'm afraid the insert-select may take >> a very long time as when I ran >> >> >> >> /select count(*) from customer;/ >> >> >> >> it is taking about 45 minutes to return the count. >> > >> > Well, you need to compare the time with the same condition you use in >> your >> > CREATE TABLE .. AS SELECT statement, >> > >> > e.g.: >> > >> > select count(*) >> > from customer >> > where id in (); >> > >> > Or: >> > >> > explain (analyze) >> > select * >> > from customer >> > where id in (); >> > >> > >> > Regards >> > Thomas >> > >> As for the actually copy of the specific records, I would ‘where exists’ >> (even possibly with a temp table of ids) rather than in(id1..id1) >> >> > >> >> >> >>
Re: weird issue with occasional stuck queries
If you get a chance, showing the `top` output might be useful as well. For instance if you are low on memory, it can slow down the allocation of buffers. Another thing to look at is `iostat -x -y` and look at disk util %. This is an indicator, but not definitive, of how much disk access is going on. It may be your drives are just saturated although your IOWait looks ok in your attachment. That wait event according to documentation is "Waiting to access the multixact member SLRU cache." SLRU = segmented least recently used cache Do you have a query that is a "select for update" running somewhere? If your disk is low on space `df -h` that might explain the issue. Is there an ERROR: multixact something in your postgres log? Adam On Fri, Apr 1, 2022 at 6:28 AM spiral wrote: > Hey, > > I'm having a weird issue where a few times a day, any query that hits a > specific index (specifically a `unique` column index) gets stuck for > anywhere between 1 and 15 minutes on a LWLock (mostly > MultiXactOffsetSLRU - not sure what that is, I couldn't find anything > about it except for a pgsql-hackers list thread that I didn't really > understand). > Checking netdata history, these stuck queries coincide with massive > disk read; we average ~2MiB/s disk read and it got to 40MiB/s earlier > today. > > These queries used to get stuck for ~15 minutes at worst, but I turned > down the query timeout. I assume the numbers above would be worse if I > let the queries run for as long as they need, but I don't have any logs > from before that change and I don't really want to try that again as it > would impact production. > > I asked on the IRC a few days ago and got the suggestion to increase > shared_buffers, but that doesn't seem to have helped at all. I also > tried deleting and recreating the index, but that seems to have changed > nothing as well. > > Any suggestions are appreciated since I'm really not sure how to debug > this further. I'm also attaching a couple screenshots that might be > useful. > > spiral >
Re: weird issue with occasional stuck queries
The logs were helpful. You may want to see the statements around the errors, as more detail may be there such as the SQL statement associated with the error. Deadlocks are an indicator that the client code needs to be examined for improvement. See https://www.cybertec-postgresql.com/en/postgresql-understanding-deadlocks/ about deadlocks. They will slow things down and could cause a queue of SQL statements eventually bogging down the system. It definitely looks like locking issues which is why you don't see high CPU. IIRC you might see high system CPU usage, as opposed to userspace CPU, where the kernel is getting overloaded. The `top` command will help to show that. The disks could be saturated by the write ahead log (WAL) handling of all the transactions. More about WAL here: https://www.postgresql.org/docs/10/wal-internals.html You could consider moving that directory somewhere else using a symbolic link (conf. the link) Anyway, these are the things I would look at. Adam On Sat, Apr 2, 2022 at 5:23 AM spiral wrote: > Hey, > > > That wait event according to documentation is "Waiting to access the > > multixact member SLRU cache." SLRU = segmented least recently used > > cache > > I see, thanks! > > > if you are low on memory, it can slow down the allocation of > > buffers. Do you have a query that is a "select for update" running > > somewhere? If your disk is low on space `df -h` that might explain > > the issue. > > - There aren't any queries that are running for longer than the selects > shown earlier; definitely not "select for update" since I don't ever > use that in my code. > - Both disk and RAM utilization is relatively low. > > > Is there an ERROR: multixact something in your postgres log? > > There isn't, but while checking I saw some other concerning errors > including "deadlock detected", "could not map dynamic shared memory > segment" and "could not attach to dynamic shared area". > (full logs here: > https://paste.sr.ht/blob/9ced99b119c3fce1ecfd71e8554946e7845a44dd ) > > > Another thing to look at is `iostat -x -y` and look at disk util %. > > This is an indicator, but not definitive, of how much disk access is > > going on. It may be your drives are just saturated although your > > IOWait looks ok in your attachment. > > I didn't specifically look at that, but I did notice *very* high disk > utilization in at least one instance of the stuck queries, as I > mentioned previously. Why would the disks be getting saturated? The > query count isn't noticeably higher than average, and the database > is not autovacuuming, so not sure what could cause that. > > spiral >
Re: Directly embedding a psql SET variable inside another string?
Do you mean like this? postgres=# \set v_embed %:v_ssn% postgres=# \echo :v_embed %345% postgres=# SELECT * FROM employee WHERE ssn LIKE :'v_embed'; ssn| name ---+-- 123456789 | John Doe (1 row) On Fri, Jan 13, 2023 at 8:12 AM Ron wrote: > > Pg 12 > > I need to pass a \set variable (in this example named v_ssn) into a LIKE > string. A two-stage process (building v_like from v_ssn, and then using > v_like in the LIKE string) works, and is fine when executing an sql script, > but not so good is there any way to *directly* embed v_ssn in another > string? > > test=# \set v_ssn 345 > test=# \echo :v_ssn > 345 > test=# \set v_like %:v_ssn% > test=# \echo :v_like > %345% > > test=# SELECT * FROM employee WHERE ssn LIKE :'v_like'; > ssn| name | ssn_int > ---+--+--- > 123456789 | John Doe | 123456789 > (1 row) > > As expected, this fails: > > postgres=# SELECT * FROM employee WHERE ssn LIKE :'%v_ssn%'; > ERROR: syntax error at or near ":" > LINE 1: SELECT * FROM employee WHERE ssn LIKE :'%v_ssn%'; > > > -- > Born in Arizona, moved to Babylonia. >
Re: order by
Check the index creation has NULLS FIRST (or LAST) on both indexes that are used. Use explain to see what indexes are used See docs for create index: https://www.postgresql.org/docs/current/sql-createindex.html On Thu, May 11, 2023, 7:30 AM Marc Millas wrote: > Hi, > > I keep on investigating on the "death postgres" subject > but open a new thread as I don't know if it's related to my pb. > > I have 2 different clusters, on 2 different machines, one is prod, the > second test. > Same data volumes. > > On prod if I do > select col_a, count(col_a) from table_a group by col_a order by col_a > desc, > I get the numbers of NULL on top. > To get the number of NULL on top on the test db, I have to > select col_a, count(col_a) from table_a group by col_a order by col_a asc. > > so, it looks like there is something different within the b-tree operator > class of varchar (?!?) > between those 2 clusters. > > What can I check to to explain this difference as, to my understanding, > it's not a postgresql.conf parameter. > > thanks > > Marc MILLAS > Senior Architect > +33607850334 > www.mokadb.com > >
Re: pb with big volumes
I think your concern is that 20 min + 30 min does not equal 3 hours. It might be natural to think the contention would, at max, be 50 min x 2 (1 hr 40 min). So what's going on? It seems disk I/O is a primary suspect since you hint for an iostat replacement inside of Postgres. If it is due to disk I/O the resolution will be to add RAID 0 SSDs at best. Consider looking at io stats on the container's persistent volumes. What is the pipe connecting the database server to the disks? If it's NAS, well that would explain it. HTH, Adam On Thu, Aug 10, 2023 at 2:37 PM Marc Millas wrote: > Hi, > > I have a 15 TB db on postgres 14 (soon 15). > shared buffers is 32 GB. > > It's a db with max 15 users and often less, and currently 1 or 2. > the biggest table have 133 partitions of 150M to 200M+ rows each. > lots of request access explicitly one of those. > > When I, alone, run a query "reading" 15M buffers, it takes 20 minutes > (+-5minutes). inside the query there are 2 group by on a 200M rows > partition, with all the rows in each group by. > When a colleague run the same kind of request (not the same request, but > something reading roughly the same volume ) , on a different set of data, > his request is completed in less than half an hour. > If we run our requests simultaneously... my request take hours. around 3 > hours. > > I am making a supposition that its some kind of "pumping" effect in the > cache. > > I cannot have access to the underlying OS. I can, for sure, do some copy > xx from program 'some command', but its a container with very limited > possibilities, not even 'ps'. > So I would like to monitor from inside the db (so without iostat and the > same) the volumes of read that postgres do to the OS. > I did activate track_io_timing, but the volumes I get in the explain > analyze buffer are roughly the same alone or not alone. (the 15M buffers > told ) > to my understanding, the volumes that are shown in pg_stat_database are > the useful ones ie. even if the db as to read it from disk more than once. > true ? or false ? > > So.. either my supposition is not correct, and I will read with a lot of > interest other ideas > either its correct and I would like to know how to monitor this (in the > current context, installing a dedicated extension is not impossible, but is > a very boring process) > > Thanks for your help :-) > > regards, > > PS: I know that providing the complete data model and the exact requests > can be considered mandatory, but when I change the request I get the very > same behaviour... > > > > Marc MILLAS > Senior Architect > +33607850334 > www.mokadb.com > >
