Last USA 747 Passenger Airplane Retired

The last 747 has been retired from passenger service in the USA.

I’ll miss the 747 because it was the only widely-used airliner built for trans-oceanic flights:

  1. For safety reasons, I’d rather have 4 engines than 2 for trans-oceanic flights. The slow acceptance of twin-engined airliners under ETOPS over the past few decades means greater fuel efficiency for airlines, but in a pinch you’re left with one engine, which is not a good situation for passengers.
  2. The 777’s that I’ve flown on to Asia don’t have eyeball air vents, so I end up roasting on long flights.

Delta Retires Last USA 747 to Boneyard

Malaysia hunts owners of Three Boeing 747s abandoned at airport

Posted in Tech | Leave a comment

Part 2: Migration Notes from MySQL to Postgresql Using pgloader

This is a multi-part series starting with Part 1.

After perfecting the schema and data migration with pgloader rules and getting the application sessions and login code working, it was time to convert the non-working SQL statements to Postgres syntax.

Here’s the SQL syntax I had to change (note: I use placeholders (?) whenever possible):

MySQL Postgresql 9.1 Notes/PG 9.5
UNIX_TIMESTAMP(?) FLOOR(EXTRACT(EPOCH FROM ?))
FROM_UNIXTIME(?) TO_TIMESTAMP(?)
DATE_ADD(?, INTERVAL ? unit) ? + INTERVAL ‘$x timeunit’ Perl DBI can do placeholder with ?::interval and “$x timeunit”
DATE_SUB(?, INTERVAL ? unit) ? – INTERVAL ‘$x timeunit’ see above for interval placeholder info
? = 0 or ‘0000-00-00 00:00:00’ ? IS NULL
RAND() RANDOM()
INSERT IGNORE INSERT in the case when the action is not important. PG 9.5: INSERT … ON DUPLICATE KEY IGNORE
REPLACE INTO custom code in the case when rows are immutable (never updated). PG 9.5: INSERT … ON DUPLICATE KEY UPDATE
IF() CASE WHEN … THEN … ELSE … END
LIMIT ?, ? LIMIT ? OFFSET ? can use the pg syntax for both databases :)
GROUP BY GROUP BY Postgresql is stricter about the columns list here
ORDER BY NULL MySQL optimization only
SQL_CALC_ … FOUND_ROWS SELECT COUNT(*) MySQL optimization only
DATE(?) TO_CHAR(?, ‘YYYY-MM-DD’)
HOUR(?) EXTRACT(HOUR FROM ?)
table `user` table users Postgresql has public.user, so treat the user table name as a reserved symbol
UPDATE table1, table2 … UPDATE table1 … WHERE id IN (SELECT id FROM table2 WHERE …)
last insert id (various) RETURNING

Don’t forget to:

  • ensure string lengths don’t exceed your schema column widths, and that Postgresql char() space-padding isn’t an issue for your application code
  • verify how Postgresql dates use timezones
  • run EXPLAIN on each of your statements. :)

Perl CGI::Session Notes

pgloader migrates the MySQL CGI::Session table using a Postgresql text column. This works better:

alter table sessions alter a_session type bytea using a_session::bytea;
Posted in Open Source, Postgresql, Tech | Leave a comment

PSA: Intel and AMD Security Bugs and the DBA


CNN.com homepage featuring Meltdown and Spectre
Being famous is not always a good thing …

There’s at least 5 problems related to the on-going Meltdown and Spectre serious CPU security bugs (AWS announcement) that impact the Database Administrator (DBA):

  1. in shared environments, like AWS or VMs, neighbour VMs can read/write your data on unpatched systems. A privacy solution is to provision the entire server to yourself.
  2. forthcoming patches might work, or not. Complex security patches often don’t address the issue initially, so there will be a sequence of related patches (whack-a-mole, like Shellshock) that will affect database uptime and cache performance. Say good-bye to your 400-day uptimes!
  3. the patches are reported to consume more memory and reduce system performance. If your database server is configured, like with MySQL’s innodb_buffer_pool_size, to use 90% of RAM you should consider 80% or 75% to avoid OOMs.
  4. in AWS, significant clock skew has been reported, so add that to your monitoring.
  5. there are Javascript exploits to read your notebook. That means if you connect to a remote database server with a database client or monitoring program from your notebook, your credentials can be read/changed. So keep your notebook OS and browser(s) up-to-date.

Note: innodb_buffer_pool_size can be set dynamically in MySQL 5.7 with some caveats:

SET GLOBAL innodb_buffer_pool_size=4G;

The above applies doubly to server consolidation and microservices in VMs.

Of course, if you’re an experienced production DBA, then you never trusted VMs anyway. :)

Some numbers from Redhat (paywalled):

> Measureable: 8-12% – Highly cached random memory, with buffered I/O, OLTP database workloads, and benchmarks with high kernel-to-user space transitions are impacted between 8-12%. Examples include Oracle OLTP (tpm), MariaBD (sysbench), Postgres(pgbench), netperf (< 256 byte), fio (random IO to NvME).

>Modest: 3-7% – Database analytics, Decision Support System (DSS), and Java VMs are impacted less than the “Measureable” category. These applications may have significant sequential disk or network traffic, but kernel/device drivers are able to aggregate requests to moderate level of kernel-to-user transitions. Examples include SPECjbb2005 w/ucode and SQLserver, and MongoDB.

Redis: Meltdown fix impact on Redis performances in virtualized environments
Cassandra: Meltdown/Spectre Linux patch – Performance impact on Cassandra?

I’ll leave it to others to pontificate on what it means when you can’t trust any desktop, server or mobile computer in an Internet-connected world. Or what HIPAA compliance means in the cloud where your server is a party-line telephone.

forums.aws.amazon.com: Degraded performance after forced reboot due to AWS instance maintenance , HN
ARM: Vulnerability of Speculative Processors to Cache Timing Side-Channel Mechanism
Escaping Docker container using waitid() – CVE-2017-5123
theregister.co.uk: Azure VMs borked following Meltdown patch, er, meltdown
CPU hardware vulnerable to side-channel attacks (Replace CPU hardware), HN (I called this in advance, but there needs to be two steps: re-design CPUs in 2018 if there’s no possible microcode update, then replace them in 2019)
blog.appoptics.com: Visualizing Meltdown on AWS
Intel alerted computer makers to chip flaws on Nov 29 – new claim – Total coincidence: That’s the same day Chipzilla’s CEO sold off his shares

Posted in Microservices, MySQL, MySQL Cluster, Tech | Leave a comment

Part 1: Migration Notes from MySQL to Postgresql Using pgloader

Recently I migrated a small but non-trivial (25 tables, about 500 columns, no triggers or SPs) MySQL schema to Postgresql using the Open Source pgloader utility.

pgloader supports migration from several databases/formats (MySQL, Sqlite, MS SQL, dBase, CSV) to Postgresql.

pgloader is fast: it took about 20 seconds to migrate/load both the schema and data into Postgresql 9.2 on my Mac notebook running in Virtualbox. The total number of rows was about 3 million narrow rows.

The impressive speed of pgloader lets you iterate quickly when writing pgloader rules, and lets you embed the loading process into automated scripts without any concern about performance.

Executive Summary

pgloader is a free scriptable ETL tool that lets you quickly migrate database schemas and data to Postgresql. It is helpful for setting up a PoC to estimate the work required for a full migration analysis. (It does not do SQL syntax or application code migration.) Although pgloader is excellent at what it does, any database migration is a major project.

Installation on CentOS 7

yum install freetds postgresql
createdb mydb
# unpack and run the build script from github or use the Docker image
make pgloader
pgloader mysql://root@localhost/mydb postgresql:///mydb

Installation on Debian or Ubuntu

apt-get install pgloader postgresql
createdb mydb
pgloader mysql://root@localhost/mydb postgresql:///mydb

pgloader pluses:

– free
– fast
– 100% scriptable and customizable with pgloader rules (called a “load file”)
– reasonable result for a first pass even without custom pgloader rules
– “pro DBA” feeling – accepts configuration file, and emits log files, similar to the behavior of Oracle’s BCP utility.

pgloader minuses:

– not included with Postgres
– unusual in that it is written in Lisp, but that is not user-visible
– doesn’t create Postgresql users/roles (to be expected as the SQL standard doesn’t specify these, thus they vary greatly across databases)
– doesn’t convert MySQL partitions to Postgresql inheritance-syntax partitions in 8.x or 9.x, but should work with declarative-syntax partitions in 10.x+
– detects and double-quotes reserved object names, but doesn’t notify of schema name conflicts with the Postgresql public namespace ie. table name ‘user’
– will require time-consuming schema cleanup for most use cases. See below.

My pgloader project files are available for download from my github.

Schema/Data Cleanup Notes

  • MySQL timestamps do not display the tz, but by default pg shows … “+00” unless you specify “timestamp without time zone”, or you use EXTRACT() or TO_CHAR(). Read about related pgloader rules here.
  • MySQL text and varchar columns can be migrated with varying results to pg text and bytea types
  • treat the table name ‘user’ as reserved since there is a public.user symbol that overrides the search path
  • don’t underestimate how long schema cleanup will take. Although pgloader runs quickly, Postgresql does not do casting automatically, so is extremely sensitive to application SQL statements
  • MySQL and Postgresql have different models for representing users/roles and timezones that need to be dealt with sooner than later. Here is some advice on timezone setting: Adding timezone to naive datetime fields from MySQL #331
  • migrating applications from MySQL to Postgresql is easier with Postgresql 9.5 since it has INSERT … ON CONFLICT DO UPDATE (UPSERT.)
  • index names are table-specific in MySQL, but schema-wide in Postgresql, so by default pgloader names them idx_NNNNN_name to uniqify them. If you need to use named indexes, like with ON CONFLICT ON CONSTRAINT in 9.5, then you either need to uniqify the index names in MySQL and do pgloader –with “preserve index names”, or add a pgloader rule to do CREATE INDEX your_index_name.

My Migration Results

After renaming the table ‘user’ to ‘users’ and altering the sessions table (see above), around half of the SELECT queries worked as-is and I could login to the application and click around. :)

However, virtually all of the INSERT and UPDATE statements had to be rewritten, taking 2 man days to get to an alpha version and 2 weeks for something worth doing formal QA.

Database Migration Alternatives

Amazon AWS provides 3 powerful data migration tools under the AWS Database Migration Service banner that are either free to use, or have a 6 month trial.

Click here to read Part 2.

pgloader: Homepage, github, Manual, Licence
postgresql.org: pg_dump
http://rhaas.blogspot.com: The State of VACUUM

Posted in MySQL, Open Source, Postgresql, Tech | Leave a comment

PSA: Upgrade Early Macbook Pro Notebooks Now

Macbook Pro 2009/2010 notebooks came pre-installed with Snow Leopard (10.6) Mac OS X.

As of Dec. 2017, very little popular software will work or update on anything older than Mavericks (10.9):

  • Several popular chat programs no longer work – Skype app, GotoMeeting, Google Hangouts, Highfive
  • No major browsers are supported
  • Even Apple updates require 10.8 or higher – for now.

Before you update Mac OS X, note that many users have complained of post-update issues including:

  • failure to boot and very slow operation on hard disks. It is possible to downgrade later, but there’s no guarantee that your data will be ok. So backup your files first!
  • new, empty keychain folder. Delete it and reboot. (You probably will have to re-enter all of your wifi, browser and application passwords.)
  • Xcode will have to be upgraded. Virtualbox will have to be upgraded to open previous VMs, and if you use Dia, read this to fix it
  • if your Mac was not purchased in your name, apps like iMovie will not be updatable from the App Store. (iPhoto 2011 will not work at all on High Sierra since it has been deprecated. Your photos will be migrated into the iCloud and accessed with the new Apple Photos app.)
  • Restart and Shutdown takes a long time or hangs. If I need to restart with High Sierra, I close the apps manually and hold the power button for 6 seconds to force a power off.
  • Resume (open lid to resume) is flaky. Always slow to resume, sometimes restarts.

The best way to update Mac OSX is to use the Apple softwareupdate command line interface (CLI) tool:

  1. Confirm you have 2 GB RAM and at least 8 GB free disk space
  2. plug your charger in and connect to a reliable WiFi hotspot, or find a tutorial on creating a USB update media
  3. Close all open applications, including Textedit.app files
  4. Backup your files and all passwords! If the in-place update fails, you may have to wipe OS X and do a fresh install, and the install will likely bork your keychain file.
  5. open Terminal.app and run these commands to find out what updates are available, and then apply the updates:
    1. sudo softwareupdate -l (copy and paste the updates list to a file and save it for later reference)
    2. sudo softwareupdate -ia
$ sudo softwareupdate -l
Password:
Software Update Tool

Finding available software
Software Update found the following new or updated software:
   * macOS High Sierra 10.13.2 Supplemental Update- 
	macOS High Sierra 10.13.2 Supplemental Update ( ), 138293K [recommended] [restart]

Otherwise you need to register with the App Store using a credit card to use Software Update. Expect 10 or more reboots/logins and about 4 hours total time if everything goes smoothly. The final reboot will actually install the new software – it will take about 41 minutes on a hard drive, or 20 minutes on an SSD.

Tips

  • if you want to keep all your windows open as long as possible, use “Apple … Restart” instead of the installer restart button until the installer says “Ready to Install”
  • Command+L on the installer dialog window will show the installer log.

Troubleshooting

  • If the install window closes and you want to retry running the installer, open the Applications folder in Finder and click on “Install macOS High Sierra”.
  • After the installer is downloaded you will not be able to see the update label name in sudo softwareupdate -l
  • An error dialog saying “The recovery server cannot be contacted.” means a network connection error or clock setting error that causes SSL certificate problems
  • if the machine becomes not bootable, power cycle while holding Command+R to restore the old OS X and try again while following a recovery tutorial on another machine.

apple.com: High Sierra macOS freezing and stops, HN
W: Macintosh operating systems

Posted in Tech | Leave a comment