So, I’ve been working on a management AMI for Redshift. It consisted of a number of fixed sets of information, such as pages for tables, for query routing, user privileges and so on. A few days ago I realised there was likely a much better way - let the users pick the columns they want, and aggregations for those columns if they wish, and then in the back-end figure out the minimum set of joins needed to produce the SQL necessary to provide those columns.
So that’s what I have here now - first light half an hour ago. First image is the index page, just a long list of the available columns and agg functions, second image is scroll down on the index page, three selected columns (user name, queue name, average and sum of compiled segments), third page is output!
So now you can mix’n’match to your heart’s content - obvious stuff to begin with, like just a listing of all tables and the number of sorted, unsorted and total blocks, then simple and useful things, like avg query time per queue (ah, I need to add standard deviation!) or avg query time per user (per queue if you like), or then crazy things, like number of blocks per encoding type, or number of users issuing queries per table, or total number of bytes read per slice…
The URL will contain everything except the Redshift username and password (they’re in a cookie), so when you produce a page you like, bookmark it. All output is available via cURL (there’s a choice in the URL between generating a full HTML page, or CSV).
https://www.amazonredshift-observatory.ch/cross_region_benchmarks/index.html
me-central-1, is still not being benchmarked, as it
remains impossible to connect to clusters started in that
region.
Four regions, ap-south-1, eu-central-1,
us-east-2 and us-west-1 have now, after a
month long pause in version changes, been upgraded from 1.0.41881 to
1.0.43331. Of these, all but the first for the ra3.xlplus
node type (the only ra3 type I benchmark in all regions) is
now displaying significantly degraded performance for the
disk-read-write benchmark.
region : mean/standard_deviation
eu-central-1 : was 2.87/0.11, now 3.98/2.51
us-east-2 : was 2.92/0.00, now 3.92//2.54
us-west-1 : was 2.99/0.07, now 3.92/2.52
Remember, there are five benchmark iterations, with the slowest and fastest being discarded - so at least two iterations ran remarkably slowly.
ap-east-1, ap-northeast-1 and
ap-northeast-2, on dc2.large has now
previously unseen large standard deviations in the processor benchmark.
For example, ap-east-1 two weeks ago was 3.86/0.02
(mean/stddev), the benchmark today was 4.77/1.07. The standard deviation
for the processor test is usually 0.00 to 0.05 - very small.
us-west-1 node type dc2.large read
benchmark has returned to normal; in the previous benchmarks, it was 10x
slower than normal.
It’s the most powerful work I’ve seen in my life. My heart ached. The innocence of children, contrasting the violence, horror and death of this war.
It turned up in the last day or two. I went down to Independence Square today to see - and lo and behold, yup, there it is. When I arrived there was a Norwegian film crew taking footage, and while I was taking photos, a sequence of single people coming to it, on foot or on bike, taking their photos.
After, I walked down to the City Hall and let them know what they now have. Hopefully they took it on board and have now sent someone to guard it. I’ll check tomorrow.
The photos are about 12mb each, so give them a minute to load.
And finally, this also, taken at the same time, across the road - a little while ago, I was walking through the square and passed the woman who was painting this, while she was at work. She has a trolley of paints and brushes, and IIRC was then, first, painting the hedgehog white. I always carry some decent chocolate with me, so when I need to say thankyou properly I can, and I gave it to her.
So, it’s been a little bit since I blogged.
The main development of course has been the Russian programme of infrastructure attacks. It seemed to be settling into a pattern of a major effort on Monday - I mean, none of us like Monday, but if you’re the dictator-in-chief of a major country you can express your feelings more strongly than ordinary people - but today was quiet.
The power cuts so far, for me, have been a non-event. I had not turned the heating on anyway, because of the gas shortage, and now I keep the lights off. I’d like to turn the freezer off, it’s empty, but I don’t think I can, which is a shame. I work at the laptop, sitting in a quilt, and light a couple of tea-lights as the evening sets in. It’s absolutely fine.
Something I’ve come to realise is that - unless you’ve been killed - life just keeps on keeping on.
If there’s no power, then - you know - you’re still alive, still breathing, still doing stuff. In my case, I have plenty of stuff which needs to be done which does not need net access (which for me is the main impact of a power outage). But if, say, the water cuts out for a day or two, then - hey - I’m still here. Still doing stuff. I would need to walk down to the supermarket to buy water. That’s all that changes; nothing else.
Changing subject; the barbarism of the Russian armed forces. It looks like whenever they’re pushed back, they loot absolutely everything they can get their hands on. I’m in mind of a plague of locusts; an army of thugs and looters, equipped by the State with weapons, set upon their neighbours.
Along those lines it seems the retreating Russian forces in Kherson have also disabled or destroyed the local power and water stations, and all the mobile network masts. Last I heard there are still about 100k people in Kherson. A friend I met at my local swimming pool, his father is still in Kherson.
I may be wrong, but I have come to the view that all frozen Russian State assets should be given to Ukraine, in part to repair the damage and theft, and in part also to prosecute the war now. It’s about 300 billion USD; about half so far of the estimates I’ve seen for rebuilding the damage done to date.
The basic rule in all things is that everything must be voluntary and well-informed, except in self-defence. You cannot force others, or deceive or trick them, unless it’s self-defence, and then all bets are off. This is the definition of freedom, and I think it also the definition of justice.
So, been a little while; been busy working on the Redshift product.
There have been I think two more major waves of Russian missile and drone strikes, since I last wrote, but I saw and heard none of it; I think the targets were not near enough to me for that, and also I think the air defence here is getting better.
As it is, I suspect I am in some way insulated from the power cuts. I’ve not had any for two weeks, not in my apartment; but I go swimming Mon/Wed/Fri, and the pool often enough has a power cut. I think they must be at about 50% power. I had a chat with the chap who runs this AirBnB, and he implied he’s seeing a lot, because he’s thinking of moving to somewhere with more stable power supplies; and when I go out and walk around the city, which is on the days I do not swim, I always see the power is out in various ways or places - traffic lights powered down, the street lighting is off, etc.
There’s a heating or power plant of some kind near this apartment, and I wonder if that needs electricity in some way to operate, and so this little area I’m in is being kept supplied.
I think I’ve written already that I keep the heating, lights and hot water off now; I want to turn off the freezer and microwave, but I can’t see how to. Candles provide light, and I sit in a quilt.
The season is now changing to winter. The very first snow came today. It used to be about 10C during the day, but now it’s 0C, and it will get colder - I have the impression it’s not that cold here, -5C or so, but I could be wrong, and there is some meteorological expectation for a cold winter this year, where the cold air vortex around the north pole is this year weak and disrupted, which allows cold air to escape south, which normally be kept bottled up over the pole.
I’m fine with weather down to say -25C, I lived in Sweden. I have the clothing for it, and the experience of it, but it will be interesting to see how cold it becomes inside the apartment. I suspect I will need to be fully dressed, and sit in a quilt, and that should be fine.
The chap who runs this AirBnB is making his way through basic training. He’s changing shape : his chest is broadening, his waist shrinking. He’s learned to be a sniper and a machine gunner, and is in charge of a unit of ten men. Last time I saw him, he was telling me about the training on that day, where they dug a decent trench, got in, and had a tank drive over it.
This brings to me of the war in general, and its progression.
Kherson was recently retaken, which is a profound relief, for everyone - for us, and much more so, for the people in Kherson; an acquaintance of mine from swimming, his father has been in Kherson the whole time.
Life for them has been hell. It is I think hard for us to imagine the barbarity of Russian occupation; it makes no sense to us, because it is so destructive and seems to be so flatly against what we would imagine the Russian self-interest. We - you and I - are from a different culture, a different background, and just do not see the world in this way.
To my eye, the way it works, is that everyone who disagrees or resists Russian occupation is either beaten up, threatened - and in any way possible, including “we will take your children away”, or “we will beat up your grandparents” - tortured, or deported, or killed, and so in the end, after a while, the region loses all those with the will to resist, and becomes quiet. All those who dissent have been silenced; and part of being in Russian control is that the schools then are used to control and indoctrinate the children, who must learn Russian, write Cyrillic, are fed Russian propaganda, and after some decades of that, you are Russian.
Russian has always been territoriality expansionist, and by this means.
It’s a throwback, to a more violent and bloody time. Lord knows how Russia will move on from this.
In any event, Russia invaded, they were stopped, and having been stopped found themselves in awkward positions, such as Kherson, which was indefensible as it could not be reliably supplied.
To my eye, the war is going to be a long hard slog. The Ukrainians, with Western backing, will need to push the Russians out, meter by meter. Putin is not going to give up and go home; the lives of those killed and maimed I am quite sure mean absolutely nothing to him. For him, he’s already lost a lot, is committed, and is trying to get something from all of this.
I see no chance of any kind of a change in leadership in Russia. Just as with Hitler, and Stalin, I think Putin’s grip on power is so absolute that nothing can unseat him.
My one hope, and hope is the right word because it is hard to quantify, lies in the fighting qualities of the Russian military.
My sense of the Russian military right now is that they have absolutely no wish to be in this war; they have no motive or drive to risk their lives and actually fight; and this is a billion times more true for the new and barely trained slave soldiers, conscripts, being pushed into the front line.
I also suspect a significant proportion of the Russian military is ill-disciplined, thugs, looters and murderers; these are not units which can stand hard fighting.
But set against this is that in war, the power of defense is enormous; you do not need much will to fight when you are being attacked and all you have to do is sit there and fire your weapon - you are not actually yourself attacking and need to deliberately move forward into danger.
In the north, the Ukrainians advanced spectacularly, from what I’ve read the real army units pulled out, and the ancillary units left behind, expendable units just to slow the Ukrainians down, fell apart.
There are real, professional Russian army units out there, I’m sure, but I suspect they also have taken heavy losses, and now are receiving conscript replacements.
So… what the outcome of all these questions upon the Russian military will be, I cannot say, as I lack information. But I have hope.
ap-south-1, us-east-2 and
us-west-1 processor times have improved by about a third
for both dc2.large and ra3.xlplus.
Historically, these two node types have been at about 4 and 3 seconds
respectively; they are now at about 3 and 2 seconds, respectively.
Normally the processor benchmark is very stable and I feel this an
intentional change.
ap-southeast-2 node type dc2.large
having a bad day; the disk-read and disk-read-write benchmarks are both
running unusually slowly.
No other news. A quiet fortnight.
https://www.amazonredshift-observatory.ch/cross_region_benchmarks/index.html
I’ve just for the first time been using recursive CTEs in Redshift.
There’s a derogation from the Postgres implementation; in Postgres, in the CTE, the seed and recursive parts can be connected by UNION or UNION ALL.
Using UNION results in de-duplication.
In Redshift, it’s only UNION ALL - no de-duplication.
Furthermore, and obviously relating to this, you also cannot use distinct in either the seed or the recursive parts (which is supported in Postgres).
There is then on the face of it missing functionality. The Redshift docs make no mention of this, which is improper. Users should not discover absent functionality by stumbling across it, especially as they would expect to be informed about this in the docs and when this is not so, users are forced into the situation of having try everything out before thinking to use it in a design.
I’m rather of the view there is a culture of secrecy in AWS, at least as far as Redshift is concerned; anything which is a weakness is obfuscated, or simply not mentioned, and this is an example of that.
So I just threw this together. I have SQL code from ages ago for complete dependency generation, but it was in a proc, had to be, you must have recursion, but about a year ago recursion was added to CFEs so now I can produce this output from a single select, which I’ve now done.
I’ve thrown in for this graph a quick (and imperfect - schemas for example not showing they depend on their owner) hack to strip out the system stuff (pg_catalog, information_schema), because it’s overwhelming, and now you can see the ditzy objects made by my random data generator (so no views depends on views, for example, which you would otherwise see).
What’s interesting though is that I can emit this in SVG, so you can SEARCH THE GRAPH. Hit ctrl-f and look for the name of the object you care about.
One of the ways this will be deployed in the AMI is the ability to view the dependencies, in a graph, for a single object - so in the list of views, for example, you can click on a link to show this graph for that view.
Home 3D Друк Blog Bring-Up Times Consultancy Cross-Region Benchmarks Email Forums IRC Mailing Lists Reddit Redshift Price Tracker Redshift Version Tracker Redshift Workbench System Table Tracker The Known Universe Twitter White Papers