Re: [SQL] Index of a table is not used (in any case)
In article <[EMAIL PROTECTED]>, Josh Berkus wrote: > Reinier, > > For future notice, [SQL] is the correct list for this kind of inquiry. > Please do not post it to [HACKERS]. And please don't cross-post ... it > results in a lot of needless duplication of effort. > >> I have defined a table and the necessary indices. > >> Is the order of index creation relevant? I.e., should I create the >> indices before inserting >> entries or the other way around? > > Ummm ... not to be obvious, or anything, but did you VACCUUM ANALYZE > after populating your table? > > There's also some special steps to take if you are regularly deleting > large numbers of records. Could you tell me what those steps are or where to find them? I have a db that I delete about 1 million records a day from in a batch job. The only special thing I do is every few days I reindex the table involved to reclame the space burned by the indexes not reclaiming space on deletion of rows. What other good and useful things could I do? Thanks marc > > -Josh > > __AGLIO DATABASE SOLUTIONS___ >Josh Berkus > Complete information technology [EMAIL PROTECTED] >and data management solutions (415) 565-7293 > for law firms, small businessesfax 621-2533 > and non-profit organizations. San Francisco > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Slow SELECT -> Growing Database
On Thu, Jun 27, 2002 at 04:24:04PM +0100, Marcos Garcia wrote:
> On Mon, 2002-06-24 at 22:43, Stephan Szabo wrote:
> > On 24 Jun 2002, Marcos Garcia wrote:
> >
> > Hmm, that should only happen if you're doing alot of updates or deletes
> > I would guess (updates would cause it to slow down as well since it's
> > similar to a delete and insert under MVCC). And frequent normal vacuums
> > should do that as well unless the table has high turnover.
> >
> > Well, if you haven't yet, you might try upping the sort_mem and
> > shared_buffers amounts, although I think the former would only
> > potentially cut down the difference between 32s and 59s and the
> > latter would probably only help on a later use of the call if the
> > buffer is big enough to hold a significant portion of the pages.
> >
>
>
> The problem isn't in the select.
>
> I realize that my database is growing and growing.
>
> I've two tables that have, lets say, 120.000 records each, and:
> - delete about 30.000 records a day from each table
> - insert about 30.000 records a day on each table
> - update each record at least 4 four times
>
if you have a lot of change going on in your db do not forget
to reindex your tables every so often. Index space is not
reclamed by vacuum, the first time can take a long time. Also
in 7.2+ vacuum got a new keyword 'full'. "vacuum full' reclaims
disk space like in 7.1, the devault vacuum just marks rows that
were deleted as reusable.
lookin at your numbers you have 60,000 definate index entries
created each day, per index. And you have 120,000 x 4(min)=
480,000 endex entries created per index, if I remember correctly
update is handled by inserting a new row and deleteing the old
row. So it looks like 540,000 index entries changed per day.
good luck
marc
> I've two other ones, that were mentioned in my previous emails, that
> have 12.000 records each, and:
> - insert 48 records a day in each table
> - =~ 120.000 updates in the last inserted records.
>
> Track the problem:
>
> # df -h /var/lib/pgsql-> 7.8 GB (I create this database 4 month's ago)
>
> # pg_dump dbnane > dbname.dump
>
> # dropdb dbname
>
> # createdb dbname
>
> # psql dbaname < dbname.dump
>
> # df -h /var/lib/pgsql-> 140 M
>
> I don't understand why the database is growing
> And is still growing.
> I make a vacuum -z -d dbname everyday (I checked if it really runs).
>
> The only reason, for the growing of the database space, that i can see
> for now, is described in the following lines.
>
> I've some perl programs that are concurrent in the access to the
> database, so i've have to make "SELECT FOR UPDATE". The algorithm of the
> program is:
>
> Autocommit = 0;
> eval {
> select id from table where state=1 for update limit 10;
> update table set locked = true where id in (?);
> };
> if (ERROR){
> ROLLBACK;
> }else{
> COMMIT;
> }
> Autocommit = 1;
>
>
> What are the major reasons for the growing of the database disk space?
>
> Maybe the following information is important:
>
> dbname> select relname, relpages,reltuples from pg_class order by relpages desc
>limit 10;
>
> relname | relpages | reltuples
> -+--+---
> sms_recv|30911 | 46801
> sms_send| 7026 | 49119
> sms_recv_unique_idclimsgidclien | 4561 | 46801
> sms_recv_pkey | 3647 | 46801
> sms_recv_msgidclient_idx| 3615 | 46801
> recv_total | 1864 | 8120
> send_total | 1378 | 12315
> sms_send_pkey | 991 | 49119
> sms_send_idclient_idx | 913 | 49119
> recv_total_idclismsdate | 686 | 8120
> (10 rows)
>
>
>
> I really appreciate your help,
>
> thanks,
>
> M.P.Garcia
>
>
> --
> M.P.Garcia
> PT Inovação, SA
> Serviços e Redes Móveis
> Rua José Ferreira Pinto Basto - 3810 Aveiro
> Tel: 234 403 253 - Fax: 234 424 160
> E-mail: [EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] XML to Postgres conversion
On Thu, Jul 11, 2002 at 09:23:39AM -0500, [EMAIL PROTECTED] wrote: > Look at contrib/xml at > http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/xml/. I never > used this, but it might be useful. > > George Essig > > > Hello. > > > > I am trying to figure out how to import xml documents into a postgres > > database. I am running PostgreSql 7.1.3 on Red Hat 7.2 at the moment. > > I have several dynamic xml documents that I want imported into the > > database on a regular basis. From my research so far, I know that > > there is middleware available to perform this, but am having some > > difficulty in finding the actual applications. I am trying to stay > > with open source applications, if possible. Can anyone give me any > > suggestions or resources to pull from? > > > > Thanks, > > > > N. Hill tDOM and nstcl might ne what you need, and a little coding. marc > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Editor for pgsql
On Mon, Jul 22, 2002 at 04:09:21PM -0400, James Orr wrote: > On Monday 22 July 2002 12:27 pm, Josh Berkus wrote: > > > Me, I use Kate, an MDI text editor from the KDE crew, and CVS for > > version control. Thanks, KDE guys!But, after 3 years of Postgres, > > I'm pretty fluent in PL/pgSQL. I even double-quote without thinking > > about it. > > How do you use CVS on your database? I recently started doing this, and i'm > wondering how other people handle it. > > Basically I create a sql folder with three sub-folders tables, views > and functions. I have a file for each table in tables, each view in > views and for each trigger and/or function in functions. > > For the actual editing? I'm a vi fan myself :). If i'm using the graphical > vim I can even do CVS operations with a custom menu. > > - James James, That sounds very ugly, I will usually have 1-4 files per db. Either everything goes into 1 file, drops at the front then creates. Or 2 files, 1 for ddl( create/drop table) and another for plpgsql procedures and triggers. Sometimes I will split each of those into a create and drop file. But that is about as complex as I want it to get. marc ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] 1 milion data insertion
On Fri, Jul 26, 2002 at 03:34:12PM -0300, Elielson Fontanezi wrote: > Hi fellows! > > I have tried to insert 1.000.000 of record to the following table; > > -- > zakal=# \d teste; > codigo | bigint | not null > nome | character varying(100) | > -- > > and I got these errors: > > -- > zakal$ psql -c "copy teste from 'teste.dat' using delimeters '|'" > ERROR: parser: parse error at or near "delimeters" > ERROR: parser: parse error at or near "delimeters" > zakal$ psql -c "copy teste from 'teste.dat' using delimiters '|'" > ERROR: COPY command, running in backend with effective uid 504, could not > open > file 'teste.dat' for reading. Errno = No such file or directory (2). > ERROR: COPY command, running in backend with effective uid 504, could not > open > file 'teste.dat' for reading. Errno = No such file or directory (2). > zakal$ pwd > /home/zakal/tmp > zakal$ psql -c "copy teste from '`pwd`/teste.dat' using delimiters '|'" > DEBUG: copy: line 27536, XLogWrite: new log file created - consider > increasing > WAL_FILES > DEBUG: copy: line 93146, XLogWrite: new log file created - consider > increasing > WAL_FILES > DEBUG: recycled transaction log file > > > ERROR: copy: line 164723, Bad int8 external representation "16722" > ERROR: copy: line 164723, Bad int8 external representation "16722" > zakal$ > zakal$ > zakal$ DEBUG: recycled transaction log file 0001 > -- > > the log has overflowed. > > Ok, this was a test. I'd like to know what would be happen. > But, from you, great PostGres DBA's, what is the best way to > insert a large number of data? > Is there a way to turn off the log? > Is there a way to commit each 100 records? > > regards, in relativly small chuncks, do 100 10,000 record transactions and you should be fine. marc > > .. > A Question... > Since before your sun burned hot in space and before your race was born, I > have awaited a question. > > Elielson Fontanezi > DBA Technical Support - PRODAM > +55 11 5080 9493 > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] slowing down too fast - why ?
On Sun, Aug 11, 2002 at 02:10:34PM -0400, [EMAIL PROTECTED] wrote: > > I've even launched the backend with "-F" and removed BEGIN/COMMIT and > LOCK TABLE and FOR UPDATE, but I still get slow response. > > only when count(*) from file is 16000, I get about 2-3 rows / second on > average. When count(*) from file was 100, I get about 20-30 rows / second. > > Help ! > > Thanx, > > John > some qustions in no particular order Have you tried 7.2.1? Have you looked at the disk io performance? Have you considdered reindexing every night? How fast is a count on the tables primary key vs the count(*)? You are using a foreign key in table file that can make things slow in 7.1.x, I think it is fixed in 7.2+ Another thing is in table am you are using 1 char fields to represent boolean values, if they are heavily used you might want to switch to pg's native boolean type. It is probably faster. And please stop top posting, it makes it harder to figure out what is going on(read the thread) so it is less likely that you will get the help you want. I looked at your pl script it is not a good test for select speed, it does other stuff. Try something like this: echo 'select now() ; select count(*) from yourtable; select now()'|psql dbname to try to localize the problem. good luck marc > On Sun, 11 Aug 2002 [EMAIL PROTECTED] wrote: > > > > > and I forgot to mention that my stats are available at: > > http://John.Vicherek.com/slow/times.query.txt > > > > John > > > > On Sun, 11 Aug 2002 [EMAIL PROTECTED] wrote: > > > > > > > > > > > Hi, > > > > > > I must be doing something silly. I have a 900MHz, 384MB RAM, and > > > this thing is slow. (Postgresql-7.1.2). > > > > > > And growing exponencially slower. > > > > > > SQL: http://John.Vicherek.com/slow/schema.sql (save to /tmp/schema.sql) > > > am.dat: http://John.Vicherek.com/slow/am.dat (save to /tmp/am.dat ) > > > perl: http://John.Vicherek.com/slow/rpm2filerian.pl (save to >/tmp/rpm2filerian.pl) > > > > > > when I do : > > > > > > createdb filerian > > > psql -d filerian -f /tmp/schema.sql > > > echo 'create table times (the_moment datetime, the_number int4);' | psql -d >filerian > > > cd /tmp/mdk/8.2/i586/Mandrake/RPMS # lots of RPMs here > > > while sleep 10 ; do echo 'insert into times values( now(), count(file.id));' | >psql -d filerian ; done 2>&1 >/dev/null & > > > for i in *.rpm ; do echo $i ; perl /tmp/rpm2filerian.pl 0 $i ; done > > > > > > > > > Why are the times so bad ? Why is it slowing so fast ? > > > > > > Am I missing any useful indeces ? > > > > > > This shows the slowage: > > > select the_number,min(the_moment) from times group by the_number; > > > > > > PS: if you look in the perl code for "exec", immediatelly above will you > > > find the query it is doing. > > > > > >Thanx, > > > > > > John > > > > > > > > > > > > > > > > > > > > > -- > -- Gospel of Jesus is the saving power of God for all who believe -- >## To some, nothing is impossible. ## > http://Honza.Vicherek.com/ > > > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
