Re: Why could different data in a table be processed with different performance?
> Does your LVM have readahead > ramped up ? Try lvchange -r 65536 data/postgres (or similar). Changed this from 256 to 65536. If it is supposed to take effect immediately (no server reboot or other changes), then I've got no changes in performance. No at all. Vlad
Re: Why could different data in a table be processed with different performance?
> You will have lesser > slots in the cache, but the total available cache will indeed be > unchanged (half the blocks of double the size). But we have many other tables, queries to which may suffer from smaller number of blocks in buffer cache. > To change block size is a > painful thing, because IIRC you do that at db initialization time My research shows that I can only change it in compile time. https://www.postgresql.org/docs/10/static/install-procedure.html And then initdb a new cluster... Moreover, this table/schema is not the only in the database, there is a bunch of other schemas. And we will need to dump-restore everything... So this is super-painful. > It could affect space storage, for the smaller blocks. But at which extent? As I understand it is not something about "alignment" to block size for rows? Is it only low-level IO thing with datafiles? > But before going through all this, I would first try to reload the data > with dump+restore into a new machine, and see how it behaves. Yes, this is the plan, I'll be back once I find enough disk space for my further experiments. Vlad
Re: To keep indexes in memory, is large enough effective_cache_size enough?
On 28 September 2018 at 16:45, Sam R. wrote: > That was what I was suspecting a little. Double buffering may not matter in > our case, because the whole server is meant for PostgreSQL only. > > In our case, we can e.g. reserve almost "all memory" for PostgreSQL (shared > buffers etc.). > > Please correct me if I am wrong. You mentioned above: > RAM: 64 GB > Data: 500 GB - 1.5 TB, for example. If most of that data just sits on disk and is never read then you might be right, but if the working set of the data is larger than RAM then you might find you get better performance from smaller shared buffers. I think the best thing you can go and do is to go and test this. Write some code that mocks up a realistic production workload and see where you get the best performance. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: Why could different data in a table be processed with different performance?
On 28/09/18 11:56, Vladimir Ryabtsev wrote:
>
> > It could affect space storage, for the smaller blocks.
> But at which extent? As I understand it is not something about "alignment" to
> block size for rows? Is it only low-level IO thing with datafiles?
>
Maybe 'for the smaller blocks' was not very meaningful.
What i mean is 'in terms of wasted disk space: '
In an example:
create table test_space (i int);
empty table:
select pg_total_relation_size('test_space');
pg_total_relation_size
0
(1 row)
insert one single record:
insert into test_space values (1);
select pg_total_relation_size('test_space');
pg_total_relation_size
8192
select pg_relation_filepath('test_space');
pg_relation_filepath
--
base/16384/179329
ls -alh base/16384/179329
-rw--- 1 postgres postgres 8.0K Sep 28 16:09 base/16384/179329
That means, if your block size was bigger, then you would have bigger space
allocated for one single record.
regards,
fabio aprdi
Re: Why could different data in a table be processed with different performance?
> That means, if your block size was bigger, then you would have bigger space allocated for one single record. But if I INSERT second, third ... hundredth record in the table, the size remains 8K. So my point is that if one decides to increase block size, increasing storage space is not so significant, because it does not set minimum storage unit for a row. vlad
