Обсуждение: Problem creating index
Hi to all ....
On my Postgresql 9.1 instance I had a problem with an index.
Using index I get less tuples than expected.
I try to remove index and the query works fine but obviosly the query is slow so I try to recreate the index.
I run the create index statement but after a lot of time I get this error message:
ERROR: unexpected end of tape
I try to look in postgresql and syslog log files but I find nothing.
Any suggestion?
Create index statement that I use is:
CREATE INDEX dati_impianto_id_tipo_dato_id_data_misurazione_idx
ON dati
USING btree
(impianto_id , tipo_dato_id , data_misurazione DESC);
On 08/26/2013 04:27 PM, Torello Querci wrote: > Create index statement that I use is: > > CREATE INDEX dati_impianto_id_tipo_dato_id_data_misurazione_idx > ON dati > USING btree > (impianto_id , tipo_dato_id , data_misurazione DESC); What are the data types of these columns? -- Florian Weimer / Red Hat Product Security Team
2013/8/26 Florian Weimer <fweimer@redhat.com>
On 08/26/2013 04:27 PM, Torello Querci wrote:What are the data types of these columns?Create index statement that I use is:
CREATE INDEX dati_impianto_id_tipo_dato_id_data_misurazione_idx
ON dati
USING btree
(impianto_id , tipo_dato_id , data_misurazione DESC);
impianto_id -> integer
tipo_dato_id -> integer
data_misurazione -> date
The index was present in the database before I drop it since one about year and half.
On Mon, Aug 26, 2013 at 4:27 PM, Torello Querci <tquerci@gmail.com> wrote: > ERROR: unexpected end of tape Really strange, if I get it right something went wrong while sorting tuples. Is it possible to test with an incremented work_mem value? Luca
2013/8/26 Luca Ferrari <fluca1978@infinito.it>
On Mon, Aug 26, 2013 at 4:27 PM, Torello Querci <tquerci@gmail.com> wrote:Really strange, if I get it right something went wrong while sorting tuples.
> ERROR: unexpected end of tape
Is it possible to test with an incremented work_mem value?
Actually I use the default work_set value (1MB).
Of course it is possible increase work_set value to any values.
Now I'll try with 10MB (shared_buffers is set to 412MB).
If you think that 10MB is a very low value for a table with million of records I can try to increase work_set value to higher value.
Best Regards, Torello
Torello Querci <tquerci@gmail.com> writes: > 2013/8/26 Luca Ferrari <fluca1978@infinito.it> >> Is it possible to test with an incremented work_mem value? > Actually I use the default work_set value (1MB). maintenance_work_mem is what would be used for CREATE INDEX. FWIW, though, the combination of this weird error and the fact that you had a corrupt index to begin with makes me suspicious that there's some low-level problem. You might be well advised to do some memory testing on that machine, for example. regards, tom lane
Torello Querci escribió: > 2013/8/26 Luca Ferrari <fluca1978@infinito.it> > > > On Mon, Aug 26, 2013 at 4:27 PM, Torello Querci <tquerci@gmail.com> wrote: > > > ERROR: unexpected end of tape > > > > Really strange, if I get it right something went wrong while sorting > > tuples. > > Is it possible to test with an incremented work_mem value? > > > > Actually I use the default work_set value (1MB). > Of course it is possible increase work_set value to any values. > Now I'll try with 10MB (shared_buffers is set to 412MB). > If you think that 10MB is a very low value for a table with million of > records I can try to increase work_set value to higher value. Note that index creation uses maintenance_work_mem to limit memory used, not work_mem. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
2013/8/26 Tom Lane <tgl@sss.pgh.pa.us>
Torello Querci <tquerci@gmail.com> writes:> 2013/8/26 Luca Ferrari <fluca1978@infinito.it>maintenance_work_mem is what would be used for CREATE INDEX.
>> Is it possible to test with an incremented work_mem value?
> Actually I use the default work_set value (1MB).
Ok .... thanks
FWIW, though, the combination of this weird error and the fact that you
had a corrupt index to begin with makes me suspicious that there's some
low-level problem. You might be well advised to do some memory testing
on that machine, for example.
I check for ecc memory but unfortunally the machine use non ecc memory.
This machine is installed on a remote site so I should to try to use e memory tester in normal linux shell, so I can't use memtest at boot level.
In this moment I get this error while executing the restore of the big table in a different database on the same machine:
psql:dump_ess_2013_08_26.sql:271177424: SSL error: sslv3 alert unexpected message
psql:dump_ess_2013_08_26.sql:271177424: connection to server was lost
I was connected with psql -h localhost.
Any other suggestion?
Best Regards
Ok,
now create index is finished using maintenance_work_mem=100MB.
Thanks to all.
I suppose that an error message more clear can help.
Best Regards, Torello
2013/8/26 Torello Querci <tquerci@gmail.com>
2013/8/26 Tom Lane <tgl@sss.pgh.pa.us>Torello Querci <tquerci@gmail.com> writes:> 2013/8/26 Luca Ferrari <fluca1978@infinito.it>maintenance_work_mem is what would be used for CREATE INDEX.
>> Is it possible to test with an incremented work_mem value?
> Actually I use the default work_set value (1MB).Ok .... thanksFWIW, though, the combination of this weird error and the fact that you
had a corrupt index to begin with makes me suspicious that there's some
low-level problem. You might be well advised to do some memory testing
on that machine, for example.I check for ecc memory but unfortunally the machine use non ecc memory.This machine is installed on a remote site so I should to try to use e memory tester in normal linux shell, so I can't use memtest at boot level.In this moment I get this error while executing the restore of the big table in a different database on the same machine:psql:dump_ess_2013_08_26.sql:271177424: SSL error: sslv3 alert unexpected messagepsql:dump_ess_2013_08_26.sql:271177424: connection to server was lostI was connected with psql -h localhost.Any other suggestion?Best Regards
On 08/26/2013 06:37 PM, Torello Querci wrote: > > > In this moment I get this error while executing the restore of the big > table in a different database on the same machine: > > psql:dump_ess_2013_08_26.sql:271177424: SSL error: sslv3 alert > unexpected message > psql:dump_ess_2013_08_26.sql:271177424: connection to server was lost > Hello This error has probably nothing to do with your index problem. How big is the restore file you are restoring? More than 512MB? We had a similar error in our RHEL6 servers and the problem was that the server could not SSL renegotiate. Some systems has SSL libraries that are incapable of SSL renegotiation as a safeguard against (old) bugs in the protocol. If you are doing a restore of a database/table bigger than 512MB and your system can not do SSL renegotiation, you have to change the parameter ssl_renegotiation_limit to 0 in your postgresql.conf and restart postgreSQL. regards, -- Rafael Martinez Guerrero Center for Information Technology University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/
On Mon, Aug 26, 2013 at 10:01 AM, Torello Querci <tquerci@gmail.com> wrote: > Ok, > > now create index is finished using maintenance_work_mem=100MB. > > Thanks to all. > > I suppose that an error message more clear can help. Unfortunately, since no one knows what the real problem is, we can't make the message more clear. Something that is never supposed to happen has happened. One thing you could do is set log_error_verbosity to verbose. It seems like the most likely cause is flaky hardware, either memory or hard-drive. In which case, your database is in serious danger of irrecoverable corruption. Is it reproducible that if you lower the maintenance_work_mem you get the error again, and if you raise it the error does not occur? Cheers, Jeff
Yes,
the table is bigger than 512MB.
Thank got your tips.
Best Regard, Torello
2013/8/26 Rafael Martinez Guerrero <r.m.guerrero@usit.uio.no>
On 08/26/2013 06:37 PM, Torello Querci wrote:Hello
>
>
> In this moment I get this error while executing the restore of the big
> table in a different database on the same machine:
>
> psql:dump_ess_2013_08_26.sql:271177424: SSL error: sslv3 alert
> unexpected message
> psql:dump_ess_2013_08_26.sql:271177424: connection to server was lost
>
This error has probably nothing to do with your index problem.
How big is the restore file you are restoring? More than 512MB?
We had a similar error in our RHEL6 servers and the problem was that the
server could not SSL renegotiate. Some systems has SSL libraries that
are incapable of SSL renegotiation as a safeguard against (old) bugs in
the protocol.
If you are doing a restore of a database/table bigger than 512MB and
your system can not do SSL renegotiation, you have to change the
parameter ssl_renegotiation_limit to 0 in your postgresql.conf and
restart postgreSQL.
regards,
--
Rafael Martinez Guerrero
Center for Information Technology
University of Oslo, Norway
PGP Public Key: http://folk.uio.no/rafael/
2013/8/26 Jeff Janes <jeff.janes@gmail.com>
On Mon, Aug 26, 2013 at 10:01 AM, Torello Querci <tquerci@gmail.com> wrote:Unfortunately, since no one knows what the real problem is, we can't
> Ok,
>
> now create index is finished using maintenance_work_mem=100MB.
>
> Thanks to all.
>
> I suppose that an error message more clear can help.
make the message more clear. Something that is never supposed to
happen has happened.
One thing you could do is set log_error_verbosity to verbose.
It seems like the most likely cause is flaky hardware, either memory
or hard-drive. In which case, your database is in serious danger of
irrecoverable corruption.
Is it reproducible that if you lower the maintenance_work_mem you get
the error again, and if you raise it the error does not occur?
I'll try to restore the database on the same hw but different DB using differente maintenance_work_mem end verbosity and I'll posted the result here, if can help to improve the error message.
Cheers, Torello
Interesting .....
while trying to restore the database on the same machine as different database I get this error message:
ERROR: date/time field value out of range: "20016009:50:37.927936"
Since I get this data from a database dump obtained with "pg_dump" on the same hardware I suppose that can to be two possibility:
- postgresql bug somewhere
- hardware problem that caused data corruption
Since the dump file is 11G is not so easy to handle ....
I think that this is not related with create index problem since this field is not used by this index and increase maintenance memory had worked.
I'll fix it and go ahead in maintenance_work_mem test for index creating.
Best Regards
2013/8/27 Torello Querci <tquerci@gmail.com>
2013/8/26 Jeff Janes <jeff.janes@gmail.com>On Mon, Aug 26, 2013 at 10:01 AM, Torello Querci <tquerci@gmail.com> wrote:Unfortunately, since no one knows what the real problem is, we can't
> Ok,
>
> now create index is finished using maintenance_work_mem=100MB.
>
> Thanks to all.
>
> I suppose that an error message more clear can help.
make the message more clear. Something that is never supposed to
happen has happened.
One thing you could do is set log_error_verbosity to verbose.
It seems like the most likely cause is flaky hardware, either memory
or hard-drive. In which case, your database is in serious danger of
irrecoverable corruption.
Is it reproducible that if you lower the maintenance_work_mem you get
the error again, and if you raise it the error does not occur?I'll try to restore the database on the same hw but different DB using differente maintenance_work_mem end verbosity and I'll posted the result here, if can help to improve the error message.Cheers, Torello
2013/8/28 Dan Langille <dan.langille@gmail.com>
Same version of DB for dump & restore? If not, was the dump done via the pg_dump from the newer version. If not, please do that.
I'm using the same version. I make this test on the same machine.
Moreover I try to remove this line using pgadmin and I get the same error (this field is part of primary key).
To remove this line I need to not use primary key but give a where condition that return only this tuple.
Again, is very strange that this data is in the database .... moreover this data came from a import procedure and this data is not present in the source import file.
Really, I think that I get some kind of data corruption
Best Regards
On Wed, Aug 28, 2013 at 1:08 PM, Torello Querci <tquerci@gmail.com> wrote: > Again, is very strange that this data is in the database .... moreover this > data came from a import procedure and this data is not present in the source > import file. > Really, I think that I get some kind of data corruption I'm sure you got some kind of data corruption because the date is invalid and it was in a primary key (if I get it right). Luca
2013/8/28 Luca Ferrari <fluca1978@infinito.it>
On Wed, Aug 28, 2013 at 1:08 PM, Torello Querci <tquerci@gmail.com> wrote:I'm sure you got some kind of data corruption because the date is
> Again, is very strange that this data is in the database .... moreover this
> data came from a import procedure and this data is not present in the source
> import file.
> Really, I think that I get some kind of data corruption
invalid and it was in a primary key (if I get it right).
You get it right.
At this point I think that a full server check is needed ....
Same version of DB for dump & restore? If not, was the dump done via the pg_dump from the newer version. If not, please do that.
--
--
Dan Langille
Interesting .....while trying to restore the database on the same machine as different database I get this error message:ERROR: date/time field value out of range: "20016009:50:37.927936"Since I get this data from a database dump obtained with "pg_dump" on the same hardware I suppose that can to be two possibility:- postgresql bug somewhere- hardware problem that caused data corruptionSince the dump file is 11G is not so easy to handle ....I think that this is not related with create index problem since this field is not used by this index and increase maintenance memory had worked.I'll fix it and go ahead in maintenance_work_mem test for index creating.Best Regards2013/8/27 Torello Querci <tquerci@gmail.com>2013/8/26 Jeff Janes <jeff.janes@gmail.com>On Mon, Aug 26, 2013 at 10:01 AM, Torello Querci <tquerci@gmail.com> wrote:Unfortunately, since no one knows what the real problem is, we can't
> Ok,
>
> now create index is finished using maintenance_work_mem=100MB.
>
> Thanks to all.
>
> I suppose that an error message more clear can help.
make the message more clear. Something that is never supposed to
happen has happened.
One thing you could do is set log_error_verbosity to verbose.
It seems like the most likely cause is flaky hardware, either memory
or hard-drive. In which case, your database is in serious danger of
irrecoverable corruption.
Is it reproducible that if you lower the maintenance_work_mem you get
the error again, and if you raise it the error does not occur?I'll try to restore the database on the same hw but different DB using differente maintenance_work_mem end verbosity and I'll posted the result here, if can help to improve the error message.Cheers, Torello