Focus for the last month has been on tuning the new PostgreSQL 9.0 features. The latest set of changes have been around Hot Standby.
Erik Rijkers published some good performance test results, very helpful, thanks. Mark Kirkwood also published some insightful results, also thanks.
After the latest set of patches the results show that we get equivalent performance between master and standby under heavy load of very fast indexes SELECTs. The peak reported transaction rate was 28,000 tps (transactions per second), showing that the internal mechanics of queries during Hot Standby are very efficient. This is great news and has taken much work from myself and the usual suspects.
What we have now is a very tight set of code that should provide good performance and scalability from all angles. Changes from the master are applied using minimal locking, so that a high rate of user queries should be possible even under a mixed workload of changes from master and many standby queries. What we don't yet have good independent performance tests to confirm that, or do further tuning.
Of course, there's no magic there. If we give the standby the same workload of changes that the master had and then we add a whole new workload we can probably bring a server to its knees. That's just CPU physics, so nothing surprising there. What we've done is relax the internal locking to allow multiple concurrent workloads to hit the server from both directions, and also allow PostgreSQL to scale well to high numbers of CPUs.
Anyway, bold words. I'm happy to hear from people that have got a good test environment, publish controlled tests and can answer detailed questions to help us isolate any strangeness in the tests. Particularly keen to have Dtrace installed, so either Solaris or SystemTap. pgbench is a good benchmarking tool for what we need, though other controlled testing is also welcome.
We need independent measurements of CPU overheads and processing rates of
* standby when no queries running, just replaying transactions
* standby when we have a mix of changes and queries
especially on multi-socket servers. It would also be useful to have comparisons of how moving from a single master to master plus slave can improve total cluster throughput.
Summary is that things are looking good so far. Is that the end? Doubt it. What I'm saying is that these new features can now be taken seriously enough to spend time on in-depth benchmarking of your workloads to prove them for yourselves and also to see if you can find some performance issues in the current code. Plan it now, but wait for beta, which is soon...
If you want to hear more about these types of issues, come to CHAR(10) conference:
http://www.char10.org/
Tuesday, 27 April 2010
Thursday, 1 April 2010
The Empty Set?
April 1st, so time to goof around a little.
I discovered recently that you can have a table called " ". That's one space character or ascii #32. Just make sure you enclose it in quotes. You can also have a table with no columns. So try
CREATE TABLE " " ();
which works fine. What does that look like?
postgres=# select * from " ";
--
(0 rows)
Can you have rows in this almost non-existent table?
INSERT INTO " " VALUES ();
fails, but let's try
INSERT INTO " " DEFAULT VALUES;
Works! And in fact you can insert multiple rows this way.
How about an index? Surely not?? Yep, we can build an index on it, even if it has no columns, as long as it references an immutable function with constant input
CREATE INDEX ON " " (exp(1));
The name of the index is " _exp_idx"! And for my coup de grace, yes, it can be a unique index
CREATE UNIQUE INDEX ON " " (exp(1));
which then enforces that there can only be a single row in our table (called " "). I've tried and failed to create an index that would enforce that our empty table called " " with no columns should have zero rows. Any takers?
So how about a Schema? Surely not? Yep.
CREATE SCHEMA " ";
and a table called " " in the " " schema? Oh yes.
CREATE TABLE " "." " ();
I also tried VACUUM VERBOSE " "." ";
INFO: vacuuming " . "
INFO: " ": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
Nothing practical comes out of that at all, just some fun. Needless to say, we only cover practical and useful things on 2ndQuadrant's courses...serious stuff.
I discovered recently that you can have a table called " ". That's one space character or ascii #32. Just make sure you enclose it in quotes. You can also have a table with no columns. So try
CREATE TABLE " " ();
which works fine. What does that look like?
postgres=# select * from " ";
--
(0 rows)
Can you have rows in this almost non-existent table?
INSERT INTO " " VALUES ();
fails, but let's try
INSERT INTO " " DEFAULT VALUES;
Works! And in fact you can insert multiple rows this way.
How about an index? Surely not?? Yep, we can build an index on it, even if it has no columns, as long as it references an immutable function with constant input
CREATE INDEX ON " " (exp(1));
The name of the index is " _exp_idx"! And for my coup de grace, yes, it can be a unique index
CREATE UNIQUE INDEX ON " " (exp(1));
which then enforces that there can only be a single row in our table (called " "). I've tried and failed to create an index that would enforce that our empty table called " " with no columns should have zero rows. Any takers?
So how about a Schema? Surely not? Yep.
CREATE SCHEMA " ";
and a table called " " in the " " schema? Oh yes.
CREATE TABLE " "." " ();
I also tried VACUUM VERBOSE " "." ";
INFO: vacuuming " . "
INFO: " ": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
Nothing practical comes out of that at all, just some fun. Needless to say, we only cover practical and useful things on 2ndQuadrant's courses...serious stuff.
Friday, 26 March 2010
Tuning Replication
Performance of streaming replication had been bothering me for some time. I'd noticed that the CPU utilisation was extremely high, so that the WALSender process was guzzling CPU even when nothing was happening.
When we think we have a clue about something and we were right we call that "intuition" and it sounds all mystical and clever. When we are wrong about something we call those "preconceptions". Strange difference of wording. Anyway, I had an idea that turned out to be a preconception. I started by looking for a smoking gun: one part of the system that would turn out to be using more CPU than another. There was no smoking gun, so that trail was a deadend. So next idea was just to look at the bgwriter code to see if there were any clear differences.
Anyway, the real culprit was something trivial. WALSender was supposed to sleep every 200ms by default. What it was actually doing was sleeping for 200 microseconds and then seeing if there was anything to send. So all it took to improve performance was to multiply by 1000 for the call to pg_usleep(). If only tuning databases was ever that simple!
That leaves me to say that the streaming replication now has a clean bill of health from an overall performance perspective. As far as I can see, YMMV, caveat emptor etc..
When we think we have a clue about something and we were right we call that "intuition" and it sounds all mystical and clever. When we are wrong about something we call those "preconceptions". Strange difference of wording. Anyway, I had an idea that turned out to be a preconception. I started by looking for a smoking gun: one part of the system that would turn out to be using more CPU than another. There was no smoking gun, so that trail was a deadend. So next idea was just to look at the bgwriter code to see if there were any clear differences.
Anyway, the real culprit was something trivial. WALSender was supposed to sleep every 200ms by default. What it was actually doing was sleeping for 200 microseconds and then seeing if there was anything to send. So all it took to improve performance was to multiply by 1000 for the call to pg_usleep(). If only tuning databases was ever that simple!
That leaves me to say that the streaming replication now has a clean bill of health from an overall performance perspective. As far as I can see, YMMV, caveat emptor etc..
Sunday, 14 March 2010
3.14159265358979
I'm told today is Pi day, in celebration of our friendly transcendental constant and because today is the 14th March, which is written as 3.14 in some locales....
So I thought I'd celebrate with a look at Postgres' mathematical musculature.
postgres=# select pi();
pi
------------------
3.14159265358979
Cool! That matches on all the digits, so we're rocking. So what datatype is this?
postgres=# select pg_typeof(pi());
pg_typeof
------------------
double precision
and as the manual says, we support at least 15 digits for this datatype. Even better. So let's flex those long dormant trigonometry muscles:
postgres=# select sin(90);
sin
-------------------
0.893996663600558
Oh no! Surely sin() of 90 degrees is 1.0? Perhaps the default is radians and I just forgot. Of course, doh! The manual doesn't actually say, which seems like a flaw. Here comes a doc patch. So lets supply radians instead.
postgres=# select sin(radians(90));
sin
-----
1
Phew! That works. Now lets try some advanced stuff. There are 2*pi() radians in a circle, so pi() radians is half way round. So the sin() of that should be 0, and the cos() should be 1.
postgres=# select sin(pi()), cos(pi());
sin | cos
----------------------+-----
1.22464679914735e-16 | -1
Hmmm. That is somewhat strange, but I guess that pi is transcendental so any representation with a fixed number of digits will always be slightly wrong by exactly that number of digits. That makes sense, but I guess I was expecting sin(pi()) to return 0.
Just to put this in perspective, in comparison to the diameter of the earth that is a precision of about one millionth of a millimetre. So that is very accurate for most applications.
Let's quickly check "e", another well known transcendental. We don't provide a function for e as we do for pi, but its easy to calculate.
postgres=# select exp(1);
exp
------------------
2.71828182845905
That looks good. So lets play some games with that also.
postgres=# select ln(exp(1));
ln
----
1
Thank goodness for that. All good. I notice in the docs that exp() takes either a double precision or a numeric input, so which one did we just try there? Let's see
postgres=# select ln(exp(1::float));
ln
----
1
and lets also try
postgres=# select ln(exp(1)::numeric);
ln
--------------------
1.0000000000000018
Hmmm. Looks like Postgres is being irrational. Or should that be insufficiently irrational? It's been a long day. I can't wait for "sqrt(2) day".
So I thought I'd celebrate with a look at Postgres' mathematical musculature.
postgres=# select pi();
pi
------------------
3.14159265358979
Cool! That matches on all the digits, so we're rocking. So what datatype is this?
postgres=# select pg_typeof(pi());
pg_typeof
------------------
double precision
and as the manual says, we support at least 15 digits for this datatype. Even better. So let's flex those long dormant trigonometry muscles:
postgres=# select sin(90);
sin
-------------------
0.893996663600558
Oh no! Surely sin() of 90 degrees is 1.0? Perhaps the default is radians and I just forgot. Of course, doh! The manual doesn't actually say, which seems like a flaw. Here comes a doc patch. So lets supply radians instead.
postgres=# select sin(radians(90));
sin
-----
1
Phew! That works. Now lets try some advanced stuff. There are 2*pi() radians in a circle, so pi() radians is half way round. So the sin() of that should be 0, and the cos() should be 1.
postgres=# select sin(pi()), cos(pi());
sin | cos
----------------------+-----
1.22464679914735e-16 | -1
Hmmm. That is somewhat strange, but I guess that pi is transcendental so any representation with a fixed number of digits will always be slightly wrong by exactly that number of digits. That makes sense, but I guess I was expecting sin(pi()) to return 0.
Just to put this in perspective, in comparison to the diameter of the earth that is a precision of about one millionth of a millimetre. So that is very accurate for most applications.
Let's quickly check "e", another well known transcendental. We don't provide a function for e as we do for pi, but its easy to calculate.
postgres=# select exp(1);
exp
------------------
2.71828182845905
That looks good. So lets play some games with that also.
postgres=# select ln(exp(1));
ln
----
1
Thank goodness for that. All good. I notice in the docs that exp() takes either a double precision or a numeric input, so which one did we just try there? Let's see
postgres=# select ln(exp(1::float));
ln
----
1
and lets also try
postgres=# select ln(exp(1)::numeric);
ln
--------------------
1.0000000000000018
Hmmm. Looks like Postgres is being irrational. Or should that be insufficiently irrational? It's been a long day. I can't wait for "sqrt(2) day".
Subscribe to:
Posts (Atom)
Posts
