So we're about to start. It's almost 11 o'clock. Okay, 11 o'clock now. So it's my pleasure
to introduce Karen from Karen Jeecks, from Crenci-Data, talking about the practical side
of the former tuning. Our very first presentation this morning was more the history and
the theory, but Karen is focusing on the hands-on stuff like the software stuff as I myself
as a DBA need to know. So I was Karen, thank you very much.
Thanks Dave.
Hi, hello everybody. So yes, as Dave says, I'm going to be talking today about optimizing
your database for analytics. So just to see your production database is probably optimized
for your day-to-day OLTP application activity. I'll wait for that to do it.
I'm not fighting that.
Okay, I think we're all right. Okay, so your database is probably optimized for your day-to-day OLTP application
activity. But what if you then need to run analytics queries against your application data?
So how can you do that without compromising the performance of your application?
This is me. I wear several hats today. I'm talking to you in my senior solutions architect
at Crenci-Data hat. But I'm also on the PostgresQL, your board PGEU, and I'm leading
the PGEU diversity task force.
Okay, so I'm going to do a quick recap of different types of database activity.
A lot of you will already know this. So you know, feel free to just have a little
snooze until we get to the rest. But just make sure everyone's on the same page.
So you've probably got at least one database.
So at least one application that's got a database back end and you've got an application
that that's being constantly queried inserted into and updated.
So it might be something like a traditional payroll app. It might be an online store.
It could be all sorts of different applications.
And that type of workload is what we usually refer to as OLTP online transaction
processing, or sometimes we'll call it operational database activity.
And that's characterized by medical current users, short statements,
and often multiple statements per transaction.
Now most applications will gradually accumulate data through that OLTP activity.
And at some point the business wants to glean insights from,
read, make money out of that data.
And for that you'll need to run analytics queries.
And the analytics query activity can take many different forms and it's got different names.
So you've got OLAP online analytical processing, reporting, decision support,
or business intelligence.
So it's basically any activity that's designed to answer real world business questions.
So it could be something like what was the exact impact of this last marketing campaign that we ran
or what would be the impact on our CO2 emissions if we perform this activity versus this activity.
And OLAP workloads typically will involve complex queries.
They'll work on large data sets.
They'll be joining multiple tables.
And therefore they're probably using high amounts of resource CPU memory and IO.
Now once upon a time you had the OLTP databases,
and then you had the analytics databases.
And the two were very, very much separate.
So data would somehow be thrown over the fence from one or more OLTP databases into an analytics database,
a data warehouse data lake, et cetera.
And that does sometimes still happen,
and there are very good reasons for that still happening in certain circumstances.
But often the business wants to get that data in real time.
So it wants to get the data where it is without waiting for some overnight or weekly batch process.
And that means they want to get that business intelligence directly from the OLTP system.
So where you've got both OLTP and analytics activity going on in the same database,
you'll often hear it described as a hybrid workload.
And it's not a fancy new idea.
Gardener came up with the HTAP hybrid transactional analytical processing back in around 2010.
And it's been described in other places by different names,
transliter call or HAAP hybrid operational and analytic processing.
Okay, so far so good, but what's the problem with this?
So well established in academic circles,
that this type of workload presents performance challenges.
There are papers from back in 2010 to 2011,
where researchers identified that running mixed workloads or hybrid workloads
causes specific performance issues,
and there are ongoing discussions about the type of benchmarking that can be used
to monitor this type of activity.
But the majority of you I'm assuming will be pleased to know
that I'm not planning an academic discussion today.
Today I'm going to focus more on the practicalities of what you can do
if you're in this situation with that mixed workload,
so how you can run performance analytics whilst having minimal impact on your existing
day-to-day database activity.
So to recap, chances are your database is currently optimized for your day-to-day OLTP
operational activity, so many concurrent users running lots of short statements.
Adding analytics activity into the mix,
so you've got complex long-running queries that consume lots of resources,
means that you can end up with the worst of both worlds.
You have analytics queries that perform properly,
and it has a negative impact on your day-to-day activity.
Okay, so now that we've identified the problem is that we're going to talk about
what was that up, an environment to test some things out,
some practical things that we can actually do to fix some of the issues.
Coffee and cake feature in many of my demos, presentations and examples,
because there's some of my favorite things.
So I used the coffee shop sales data set that I found on Kaggle to populate a coffee shop sales table,
and I expanded the data till I had about 26 million rows in my table.
So just in case anybody wants to do this for themselves,
I've included the details in the slides of exactly how I got the data into my database
and then expanded it so that I'd got enough data to run some simple analytics queries
but where we could actually see some kind of impact.
Okay, so I took the Excel file converted it to CSV, removed the header row.
I created a temp file with all text fields mainly because I couldn't be bothered dealing
with any errors at the load stage, loaded the data from the CSV file into my temporary table.
So far so good.
Then I created my real coffee shop sales table with the data types I actually wanted,
and I inserted the rows from my temp table,
making a couple of changes along the way just so that I'd got some nice easy data to work with.
Then I mocked up some data for different time periods,
and I kept adding data and reinserting until I had that 26 million rows that we talked about.
Then I created an analytics query to answer a slightly convoluted question.
I asked which store had the highest total sales for each month of the previous calendar year,
taking into account only transactions with a value greater than 20.
This is the full query, the details in this case really aren't important,
and I'd actually rather any SQL experts in the room didn't look too closely at it,
because I'm sure it is not the most elegant solution,
or the most performance solution that I could have come up with.
The idea is just to have a query that we can use for demo purposes,
so we can see the impact of different actions on that.
Okay, so the important things for us to note are that it can catenate some data,
it filters out some data,
and it aggregates data from that coffee shop sales table that we created.
And this is what the output looks like.
It just gives us a row per month showing month store and the total monthly sales.
Okay, so then we need to look at the execution plan for the query,
so we can see what it's doing behind the scenes.
And to do that, I'm using Explain, which I imagine a lot of you already familiar with.
You simply type in, or in this case I used Explain Analyze before the query.
So the Analyze keyword here is optional,
but I used it because I wanted Postgres to actually run the query
to give me the actual results, the actual timings, numbers of rows, et cetera,
rather than just an estimate of what it was planning to do.
I just want to anticipate the question, why don't you include the buffers keyword,
but somebody is bound to ask that one,
I don't need it for the purposes of this talk,
and I just wanted to keep the amount of data on the slides slightly cleaner
than it otherwise would have been,
but it is a useful parameter to add,
and it's definitely worth looking at the Explain documentation,
so you can see all of the different options that are available to you.
Okay, this is the execution plan that I got back from that,
and you absolutely don't need to read this.
I've just pulled out some of the main details on the next slide,
the things that we're going to be looking at as we go through.
Amongst other things, we see that we've got a full table scan
on our coffee shop tells sales table,
so that's the seek scan on coffee shop sales.
It's doing some sorts on disk.
I can see sort method external merge disk,
and it's taking around three seconds to execute.
Now I haven't included the details of this as I go through the demos in this talk,
but now if you want to, you can simulate some OLTP activity
against your database whilst you run this analytics query
to see what kind of impact it has on that.
And to do that, you could use the PG Bench utility,
so that's available by default with Postgres.
When you initialize it, it will create you four tables,
so you can see the PG Bench branches,
tellers, accounts, and history tables.
The number of rows shown here are if you initialize it with
the default scale factor one.
So for example, if you set scale factor to 100,
during the initialization, you'll get 10 million rows
in your PG Bench accounts table.
The history table, you know, it shows zero.
It starts at zero at the start of every run,
and it will generate data as it goes through.
Once it's initialized, you can run PG Bench telling it
things like how many concurrent sessions you want connected
and how long it should run for.
By default, it repeatedly runs this transaction.
It's loosely based on the TPCB benchmark,
and each transaction, as you can see,
is made up of several SQL statements.
You've got three updates, a select and an insert,
and it takes random values of AID,
TID, BID, and Delta.
And that for the purposes of this talk is all I'm going to cover
on PG Bench, but feel free to go away and have a look at it,
because it is a very useful, very simple little tool.
So now we've got the environment in place.
Let's have a look at some of the different things that you can use
to optimize that analytics query,
hopefully, without slowing down your application.
It's definitely not going to be an exhaustive list.
The idea really is to give you enough ideas to get started
so that you can go out and try other things.
First configuration parameters.
Your configuration parameters,
have probably, hopefully, at least, been set
to optimize your day-to-day database activity
if they haven't, I highly recommend having a look at them,
and that gives me the opportunity for a shameless plug
for another talk that I've done,
which talks about setting those parameters.
Okay, some parameters have to be set across the entire instance.
Some can be set for specific users.
Some can be set for individual statements.
So for those that can be set per user,
that's great.
You could create, for example, one or more analytics users.
Rolls that have the appropriate values for those parameters.
For parameters that have to be set across the whole postgres,
instance, you're going to have to find some kind of compromise
that works well or well enough for both the OLTP activity
and the analytics activity.
And that can be a bit of a challenge,
and it will require repeated testing
and knowing what is the priority in your situation.
Okay, so let's have a look just at some of the most relevant parameters.
Database connections, so the max connections parameter,
tells postgres the maximum number of concurrent client connections
that are allowed for the entire instance.
So it's 100 by default, and on most systems,
you don't want to increase it too much higher than that.
The chances are you won't actually have that many analytics connections,
but as we already discussed, they're likely to be using a lot of resources,
so you want to make sure that you limit the number of them.
To do that, you might want to look for example at connection pooling,
so that you create a separate smaller pool for your analytics connections.
Workmen is the maximum amount of memory that can be used
by a query operation before it will spill to disk and create a temporary file.
By default, that's four megabytes.
Complex queries, performing large sort or hash operations,
might benefit from a larger value.
Now that describes the analytics type queries that we've been talking about.
You can check if workmen needs to be increased by
watching out for the creation of temp files,
so if you use log temp files and set that to zero,
then it will log in your postgres logs every time a temp files created,
and that could imply that you need to increase workmen.
But just be careful, because complex queries could use multiple blocks of workmen.
And you might have lots of that going on at the same time.
So you don't want to set this to a high value across the board.
You likely want this to be one of those settings that is set high
just for the individual roles or statements that need it.
You've got the option if you want to set this parameter for a specific transaction.
So here I set it to 20 meg just for one statement,
and then after that transaction can see it's back to its default.
If you're concerned that certain queries might run for too long
and use up too much resource, you can set statement time out.
So that will abort any statements that run for longer than the specified amount of time.
By default it's set to zero, so that means it's disabled.
You can set statement time out for the whole instance,
or just for specific sessions.
It probably makes or definitely makes sense to set it to different values for your
OLTP and your analytics workload, because analytics queries might be okay
depending for many minutes or even longer, whereas certain of the queries in your OLTP activity
might have to run in milliseconds.
So if you want to make sure that any statement that is timed out in this way
is written into your postgres log so you can see that it's happening,
make sure to set log min error statement to error or lower.
If it's set to error, then any statement is causing errors, logs,
but fatal errors or panics will be logged.
So here you can see from the least problematic to the most problematic
the different values there.
Okay, so that's all of the parameters that we'll look out for now.
Next indexing strategy.
You will probably need to create specific indexes for some or all of your analytics queries.
You just need to be aware that it's not just the space that's taken up by indexes
that see a issue, they need to be maintained.
So if you create too many indexes on your application tables,
then you'll start slowing down your application activities.
So it's a balance that you need to be very aware of.
As well as creating indexes on one or more table columns,
you can also create indexes on functions or scalar expressions.
So expressions that are computed from your table columns.
So here, for example, I created an index on the total sales amount expression
that I used in the query.
And then when I rerun the query, I could see that I now have a bitmap index scan
on the CSS total sale amount.
So that's showing me that the index is being used.
And it actually took the execution time down to just over one second.
So if you remember, it was three seconds to start with.
So that had a reasonable impact.
And obviously that will depend on your use case and on your data.
Most analytics queries aggregate sort and calculate large quantities of data.
You've got totals, averages, comparisons, groupings, all sorts of things like that.
And these are resource intensive operations.
So if you can, anything you can do to pre-calculate, pre-aggregate and pre-thought data
can make the analytics queries much less expensive to run at query time.
So one way you can do that is using stored generated columns.
So you can create a generated column where the values calculated automatically
to match an expression that you use in your queries.
So here, for example, I've used the same thing I've used the total sale amount.
And I've created a column with the default value of that expression.
So I've used the generated always as keywords to do that.
And the stored keyword is the one that says pre-calculate that and actually store it physically in my table.
You can also create indexes on these generated columns to make it even faster.
So this obviously can improve your query time, but you need to be aware that again, this needs to be maintained.
So when you are inserting data or updating data, this needs to be calculated.
So you just need to be aware of the balance needed.
So now I can use that calculated column in my query instead of the expression that I had in there before.
And I don't think I actually made a note of how long it took, but that took the execution time down again.
Please believe me.
Okay, materialized views, another really good way of pre-calculating sorting and aggregating data.
So a materialized view is a physical copy of the results of a query.
But it acts as a normal table that you can query.
So they're very useful where you've got things.
Excuse me, I've just got some notifications I need to get rid of.
So they're really useful tool for pre-aggregating data and particularly useful way you don't need exactly up to date data.
So sometimes for analytics queries, it might be good enough to use data that's an hour old,
or even a day old or a week old, depending on what level you're reporting out.
And again, you can create indexes on materialized views.
So here, for example, we can create a materialized view for the ranked sales part of our original query.
And it's as simple as just using the create materialized view keywords before the query that you want to materialize.
And now wherever that block would have been used in as part of bigger query.
So here, for example, in our original analytics query, you can just select from the materialized view instead.
So the data's already been queried.
It's already been grouped, sorted, aggregated, all of the different things you want to do.
And in this case, the analytics query completed in a couple of milliseconds,
instead of the several seconds when it was selecting from the base table.
Note, of course, that you will have to schedule periodic refreshes of that materialized view,
assuming that it's not going to be just a one-off thing.
You'll want to have maybe that hourly, weekly, daily refresh of that data.
Frequency, obviously, depends on what you need for your queries.
It won't necessarily be the same for all of the materialized views.
Hopefully, you'll be able to schedule that during quieter times when there's not so much application activity happening.
And if you're using PGCron, you can set up a period refresh actually within postgres itself.
So I've just given an example here that schedules an hourly refresh of our materialized view.
Of course, as we all know, the best way to optimize something is by not doing it at all.
So if you can avoid running these analytics queries on your application database, then that's fantastic.
Even if you don't have a completely separate analytics environment, there are probably some things that you can do.
For example, most database environments already have a higher availability architecture in place.
So you've got your primary database that's replicating to one or more replica databases.
Those replica databases are usually available for read-only activity.
So instead of querying your primary database, you could send some of your analytics queries to one of those replicas.
So that will take some of the load of your primary database.
There are some things to do with primary, sorry, physical replication that's being used in this case gives you a complete exact copy of your primary database.
So whatever you're doing on your analytics, sorry, on your replica, you can't create indexes there, you can't create separate users there, you can't create materialized views.
There are many changes that you would want for your application activity.
So the things that we already looked at in the previous bits of the presentation, you would have to do on your primary database and let those be replicated over.
So that might not be an issue, but you'll still have some of those trade-offs.
You still have to find a time when you can refresh your materialized views.
For example, you'll still take the hit on your inserts if you've got pre-calculated columns etc.
So another option that you've got available to you is to use logical replication.
So you can use that to replicate just certain objects from your primary database into, or we'll call it just a replica database again here.
So this, you go from a publisher to a subscriber, so your primary database will be your publisher.
You choose which objects from that database you want to make available to be logically replicated.
Your replica database is the one you're going to use to analytics.
We'll be your subscriber, and you choose which publication, which publisher you want to take that information from.
So this is much more complicated to set up and to maintain than physical replication, but it is a lot more flexible.
So for example, you can replicate just a selection of objects.
You can replicate two and from multiple targets, so if you've got many application databases that you want to consolidate into one analytics database, you can pull data from different sources.
Your subscriber database will be available for read rights, so that means you can make changes on your analytics database.
You can create those indexes, materialised views, etc. You can change the settings just for that database.
Okay, looks like I've spoken much more quickly than I needed to today, so in summary.
So we've looked at kind of what the problem is, what the issues are that we're trying to solve, where we've got this hybrid workload going on in the database.
And then looks at the actual practical things, some of the practical things that you can do to make sure that your database is optimized for that analytics activity.
But with as little impact as possible on your existing day-to-day OLTP activity.
So things like tuning your configuration parameters to be relevant for that hybrid workload, taking into account, of course, where you can do that for specific analytics users, where you have to find a compromise across the whole database.
Consider your indexing strategies, make sure that you create the indexes you need for your analytics queries, but just be careful not to create so many that you will have too much of an impact on your inserts updates, etc. for your day-to-day activity.
Create generated columns, materialised views, etc. so that you can be doing that pre-aggregation and presorting of your data.
And don't forget that you combine all of the different techniques so that you can create a materialised view and add index to it.
You can create a logical replica and then add indexes and change the configuration parameters, etc.
So the important thing is to know what your requirements are and to test so that you know what the different impacts are in your environment so that you can put exactly what you need in place.
Thank you very much, I will very, very happily take questions if you have any.
Thank you very much, Karen. Thank you, thank you.
So it's a very large and full room. We have people also watching for online.
So if you've got a question at the back, please shout it out and I'll ask Karen to summarize and just the question.
So the people online know what we're talking about.
Any hands up for questions? I'm sure there's lots. So at the front here.
Do you talk about the junior age problems and your age values and if you sometimes probably affect one or two different.
Okay, so the question is talking about generated columns and how that impacts the OLTP performance.
So where you've got a generated column in a table, a generated column itself isn't an issue.
It's the stored generated column that's the issue because we're actually creating, we're actually calculating that and storing it in the table.
So the issue is that when you then insert a row into your table or when you update things that impact that generated column, that's got to be calculated and stored as you do your OLTP activity.
So that's where the hit comes.
Thank you, chat with the glasses just in front of me and I'll be fair to that.
So if we have make a bell to see the replicas and sublogical replicas,
yeah, and one of the nautical replicas goes up some heavier than in the analytics query.
The application on the call to the left speaker or stand by.
So the question is if you've got multiple physical replicas and a logical replica.
Yeah, so the physical replication doesn't care about what's going on with the logical replication.
So even if you've got a lag on your logical replica, it doesn't matter.
And we're assuming here that when you're running your analytics query, you don't care about that lag either because it doesn't matter if your data is slightly out of date because you're, you're getting generalized data.
So no, it's fine.
Okay.
Hi there, we have a request.
If you're leaving the room early, please.
Opposite side.
The site with Devran in with the burgundy t-shirt.
So okay, we've got a question over here and past the market.
Hello, that was great talk.
Just one question you talk about the indexes there and where they can start to have a negative effect on the work, the speed on certain use cases.
How did you manage that or do you just just some automated way of kind of monitoring that that your indexes are having negative effect on your performance?
Okay, you can just be really careful with the chairs because they do make a lot of noise.
So if I've understood the question is talking about the fact that indexes can have a negative impact on your OLTP activity and how can you monitor that and.
Okay, I know when it's happened.
So mostly for me, that would be a case of testing before putting the indexes in place to to already know what kind of impacts are going to happen.
I would hope that you would then have kind of general monitoring in place to to see how long things are taking, you know, whether they're taking longer than you expect them to, etc.
But for me, a lot of that work would be testing the running the OLTP activity and the analytics activity alongside each other.
With and without your indexes and with different combinations of indexes to see which gives you the best the best compromise.
Okay, so the question is what about table partitioning, especially for very large tables? I mean, that is a huge topic in itself. So is there a particular aspect of it that you're thinking?
Yeah, so there are particular strategies about table partitioning and moving one of the analytics queries.
Potentially yes, but it's one of those things where it's it's a big kind of design question about how your OLTP activities happening, which parts of the data being query.
So potentially yes, there are things that you might want to think about and do is very hard to give any kind of precise answer because it is so dependent on the data, the use case, the application, the analytics.
But it might be that you can, for example, have partitions where your updating data and others where things are being query, it's possible.
But for me, it wouldn't be the first place that I'd look for this kind of thing. Thank you in the black.
You're so short thing for the dog, my question if you're running microservices, so you have multiple positives instances.
If you have a microservices environment, you've got lots of small postcards.
If you have a microservices environment, you've got lots of small postcards databases that you want to run analytics on.
Would I recommend consolidating those biological replication into one large analytics database and then optimizing that?
Or would I recommend something with Kafka, Spark, etc.
Can I just answer it by it depends?
It really, without knowing what kind of analytics queries you're doing, what rate of data change you've got, there are so many different things.
I genuinely can't know which would or wouldn't be the better approach, but there are lots of situations I can see where consolidating those biological replication into a single analytics database could be the right way to go.
Okay, we have one question here, then I'm going to do some questions on the other side.
Hi, I have a question what materialized views, you'll mention to be pressing them as frequently as needed.
And then you mentioned that when the load is not high, but on the current job we have hardly, and this is a common case.
Is there any recommendation about to be pressing materialized view or is there any performance impact that should be aware of when we schedule it frequently?
Okay, so a question about refreshing materialized views and I think the gist is basically getting that balance between refreshing as often as necessary and trying to mitigate the performance hit on because of the refresh.
So that's it's the same as trying to balance out actually running the analytics queries themselves whilst having your OLTP activity happening.
So it's going to depend on which is the most important to you.
Is it more important to you to have the up-to-date analytics data or is it more important to you to not have the impact on your production environment?
And it's something you can only know by knowing what your particular requirements are and by testing to see exactly what kind of impact it's going to have and then making decisions based on that.
Thank you, there was a question.
Thank you, actually it's very close to to the questions just asked about materialized views.
How can we avoid the full crunch of the data when we update a materialized view?
We want to update to only crunch the deep since the last update.
How can we control or know that? Because it's quite an opaque process.
Okay, so the question there is again about refreshing materialized views and this time about how to avoid having to reprocess all of the data that's used to make up your materialized view.
There are tools around that will help with that external to postgres, so it might be that you would need to look at something like that.
At the moment, it very much is an all or nothing. You refresh the entire materialized view, but I mean if somebody wants to submit a patch that does that, then that would be fantastic.
By the way, it's just as painful in other data platforms with materialized views.
Question on the cloud, are you aware of any of the platforms of service offerings that can't do this kind of mixed load configuration that you've been talking about?
So the question was am I aware of cloud providers that can't do the mixed load?
Do you know I haven't even looked into that and I've deliberately not talked specifically about offerings from my company because I've tried to keep it generic, so there are various cloud providers out there, I encourage you to check them all out.
I genuinely don't know, but I'd be interested to hear what other people have found out.
If it on mind, there is an accession called P. John the score IBM, which is the main incremental view maintenance, which helps to confuse the change of data for refresh material as you think may help.
Thanks Deborah.
No, that's perfect. Thank you.
So in the interest in, Karen is our diverse, the task force leader, so I've tried to cover most of the room. Is there anyone who's got any more questions? I think I've covered both sides.
I think just one in the middle here.
Just one in the middle. Okay, if you can shout out the question out to Karen and back to.
I'm trying to think how that would work. I think the table would have to be the same, so I don't think you could create a generated column just on the logical replica.
But I go on, Deborah, I was just saying, I'm 90% sure to be sure.
Okay, thank you. So good. At least I was right, and I do.
But that's good news that that will be available in 18, because I wanted to say that that would be a good thing in my presentation, but yeah, perfect.
Thank you for the question.
The lady in with the white shirts, I think it is.
You can shout out to Karen.
Yeah. How does that scale for a person?
I don't personally got hands-on experience of using time scale, so I don't know the specifics. Is there anything in particular that you're thinking?
Yeah, and it shouldn't have negative impacts on running the analytical queries, but I just don't note the details of how it might enter out with that.
Sorry, I don't think I repeated the question there. It was about what the impact might be of time scale with analytics queries.
So what was the point?
So the comment there is that time scale, of course, is the external tool that does help to do the incremental materialized view refresh based on certain criteria. Thank you.
Thank you.
Any more questions? We've had a really good round of questions.
I should learn to speak more slowly, so I don't have to answer as many questions.
Sorry, I take just this. Any more questions? If not, we'll wrap up a couple of minutes there.
They can give you a couple of minutes of break. This is my third session in the room, so a really great start to the day.
Thank you very much, Karen, if you can give a warm round of applause.