3 ms·

Ive always wondered where pushes for database "performance" tuning come from, and i've suspected it might be an Oracle thing due to the applications nature as s

Ive always wondered where pushes for database "performance" tuning come from, and i've suspected it might be an Oracle thing due to the applications nature as somewhat of a black box with a prohibition in the license on any public performance testing against other products. MySQL and Postgres are generally good enough out of the box to handle most workloads. Tuning efforts should best be spent at things like query optimization and normalization, or identifying unintentionally inefficient nested queries that might have cropped up through the life of the database. things like old 'select *' reports that managers of long ago may have mandated, or rogue cron jobs that run meaningless reporting. Identifying records to truncate or creating new databases entirely for different types of data instead of packing it all into one giant database as some companies tend to do, is also worthwhile.

ahachete 7y ago
I beg to disagree ;) Postgres performance with the default configuration could be significantly slower (30-40%, sometimes more than 100%) than a properly tuned configuration. It is quite important and one of the main recommendations and jobs we do on our daily work. For example, if you leave random_page_cost at the default value and have fast SSDs, it is very likely that the fancy indexes you created may not be used and seq scans may be used instead. No amount of query tuning may fix that. A classical one is shared_buffers, whose default size is 128MB of RAM. Unless you are running PG on an AWS Lambda ^___^ or a Raspberri Pi, this is typically a very low number.
- Scarbutt 7y ago
  True but many setups can get away with just tuning 5-10 parameters.
  - ahachete 7y ago
    Absolutely. But we found over many years that know which 5-10 (out of the 300) and what values are good is a hard job for most. Despite that, we end up tuning 30-40 for most of the customer environments we work with. For instance, logging (for appropriate logging or troubleshooting) is like a dozen. Autovacuum takes its fair share too, and if you have a heavy traffic db, it is a must.
unnouinceput 7y ago
+1 Also, throwing some hardware like a bigger SSD or memory increase, both being quite cheap these days, will increase performance when dealing with an out of the box server as well. Example: Year 2017, client started to have bigger traffic and users were started seeing timeouts. Server was 16 GB RAM. Mind you, it was the developer server as well, so lots of stuff were running there. Bought dedicated server, upped the memory to 64 GB and in one night we did the switch together. Downtime of only 30 minutes and to this day the client is good to go.
derefr 7y ago
I use PG as a place to put OLAP cubes generated from primary-source event-sourcing data. In that use-case, PG really doesn't need any kind of durability guarantees, because it's not the canonical store of the data it's holding (i.e. the same data could just be repopulated into a new PG instance in the event of a hardware failure.) PG's default tuning is very sub-optimal for this use-case. :) (Actually, question for the audience, anyone with experience in heavily-OLAP workloads: what kind of DBMS would you load an OLAP cube into, if it were still too large after projection/dimension-reduction to fit into memory, and you needed to run CPU-intensive, non-embarrassingly-parallel queries on it?)

ahachete 7y ago

I beg to disagree ;) Postgres performance with the default configuration could be significantly slower (30-40%, sometimes more than 100%) than a properly tuned configuration. It is quite important and one of the main recommendations and jobs we do on our daily work. For example, if you leave random_page_cost at the default value and have fast SSDs, it is very likely that the fancy indexes you created may not be used and seq scans may be used instead. No amount of query tuning may fix that. A classical one is shared_buffers, whose default size is 128MB of RAM. Unless you are running PG on an AWS Lambda ^___^ or a Raspberri Pi, this is typically a very low number.

Scarbutt 7y ago
True but many setups can get away with just tuning 5-10 parameters.
- ahachete 7y ago
  Absolutely. But we found over many years that know which 5-10 (out of the 300) and what values are good is a hard job for most. Despite that, we end up tuning 30-40 for most of the customer environments we work with. For instance, logging (for appropriate logging or troubleshooting) is like a dozen. Autovacuum takes its fair share too, and if you have a heavy traffic db, it is a must.

unnouinceput 7y ago

+1 Also, throwing some hardware like a bigger SSD or memory increase, both being quite cheap these days, will increase performance when dealing with an out of the box server as well. Example: Year 2017, client started to have bigger traffic and users were started seeing timeouts. Server was 16 GB RAM. Mind you, it was the developer server as well, so lots of stuff were running there. Bought dedicated server, upped the memory to 64 GB and in one night we did the switch together. Downtime of only 30 minutes and to this day the client is good to go.

derefr 7y ago

I use PG as a place to put OLAP cubes generated from primary-source event-sourcing data. In that use-case, PG really doesn't need any kind of durability guarantees, because it's not the canonical store of the data it's holding (i.e. the same data could just be repopulated into a new PG instance in the event of a hardware failure.) PG's default tuning is very sub-optimal for this use-case. :) (Actually, question for the audience, anyone with experience in heavily-OLAP workloads: what kind of DBMS would you load an OLAP cube into, if it were still too large after projection/dimension-reduction to fit into memory, and you needed to run CPU-intensive, non-embarrassingly-parallel queries on it?)