James Crisp – Software dev, tech, mind hacks and the occasional personal bit

A few months ago, when I was doing some detailed database backup and restore testing, I discovered there were, out of millions of records which had user-entered dates, a handful that had null dates in a database column. I scratched my head for a while and couldn’t work out how this had happened, as the field is validated in Rails for empty/null.

Just today, I got an exception report from a different part of the system which does a query based on the user entered date, and it revealed the source of this extremely rare problem! So.. drum roll..

Accidentally, a user had entered a date with the year 20223.

This is valid in Ruby/Rails but too big to be stored in the mysql Date column, so had ended up (silently) being stored as null!

Easily fixed by limiting the date range a bit!

Testing performance before upgrading from Mysql 5.7 to Mysql 8

By James

On July 31, 2023

In Technical

Mysql 5.7 is reaching end of life in October, so it is becoming important to upgrade. I am using Percona Mysql and the upgrade process with Apt and the Percona repositories is simple. Mysql automatically upgrades files and tables to suit the new version too. There are config file changes required, and quite a lot of defaults changed in Mysql 8, but that is not the focus of this post.

Reading up on the performance differences from Mysql 5.7 to Mysql 8, the story is mixed. In my case, Mysql 8 is definitely slower out of the box for queries that are not well indexed. We are talking often about 50% slower on larger selects. With well indexed queries, the difference is negligible. I kept looking for some config setting that would bring back old Mysql 5.7 performance, but I found no way to make Mysql 8 perform as well out of the box. The solution I found was to add more indexes. These indexes had not been required in 5.7 for good performance. In Mysql 8, they proved vital.

Considering the difference in performance between versions, and additional indexes I had put in place, I wanted to test the performance before upgrading my production setup to Mysql 8. A good way to test performance would have been to mirror incoming requests between real production, and a cloned production server, and watch the comparative performance. This is arguable the best option, but requires quite a bit of infrastructure work and HTTPS offloaded from the production server to something that is mirroring the requests and ignoring responses from the clone. AWS supports traffic mirroring, but I decided it wasn’t the best option in my situation as it would have required significant infrastructure and production changes for my setup.

The alternative that worked in my case, was to record all database queries for a period of medium-high site load, and then replay these on clone servers, to test the relative performance between database server versions and index additions. Tools exist to do this, but they are a bit dated.

If you’re interested in using approach, first record all queries to file at a time of significant load, using the Slow Log on your production server. Simultaneously, take a snapshot of the server that you can use for creating clone servers at this point in time.

To test relative performance on clones, I used Percona Playback. This is a very handy tool but old and unmaintained – still, it was the best option I found. To make Playback work, you need to run it under CentOS 7, which is easily achieved using Docker. I tried updating the code to run on modern Ubuntu but it was too big a job, libraries it depended on had changed too much.

To install, set up a data directory and grab and build my Dockerfile (I updated it a little from @lichnost’s version):

mkdir -p playback-docker/data
cd playback-docker
curl https://github.com/jcrisp/percona-query-playback/blob/master/Dockerfile > Dockerfile
docker build -t percona-playback .

Transfer your query log (eg, MyServerName-slow.log) to the clone. I’d also recommend taking a snapshot at this point, and creating new clones for performance testing from this new snapshot, since it now has Percona Playback installed and the query log file available.

To replay the queries in full on a cloned database server:

docker run --mount type=bind,source=$PWD/data,target=/app/data  --mount type=bind,source=/var/run/mysqld/mysqld.sock,target=/var/lib/mysql/mysql.sock percona-playback --mysql-max-retries 1 --mysql-host localhost --mysql-port 3306 --mysql-username <USERNAME> --mysql-password <PASSWORD> --mysql-schema <DATABASE_NAME> --query-log-file /app/data/MyServerName-slow.log

Note that this is “destructive”. All updates/inserts/deletes will be applied, so the run is only fully valid once. Re-running it will lead to errors like duplicate ID inserts or deletes of records that don’t exist any more.

For a non-destructive performance test, you can filter out just the selects using another tool, pt-query-digest, which is happily still maintained and can be installed as part of the percona-toolkit package. To make a select only log file:

cat MyServerName-slow.log |  pt-query-digest --filter '$event->{arg} =~ m/^select/i' --output slowlog > selects.log

For performance comparisons, I ran the non-destructive select-only version first to warm up the database before running the full destructive version as the real test.

This approach gave me confidence that, with additional indexes, Mysql 8 would be faster than Mysql 5.7 on a real production load for my app.

Percona MySQL: Collecting All Queries with the Slow Query Log

By James

On July 31, 2023

In Technical

If you want to collect all queries running on your database for a period of time, for analysis or replay, the simplest way to do this is using the Slow Query Log.

To start collecting:

SET GLOBAL slow_query_log=1;
SET GLOBAL long_query_time=0;
SET GLOBAL log_slow_admin_statements=1;
SET GLOBAL log_queries_not_using_indexes=1;
SET GLOBAL slow_query_log_always_write_time=0;
set global slow_query_log_use_global_control="all";

To stop collecting:

SET GLOBAL slow_query_log=0;
SET GLOBAL long_query_time=10;
SET GLOBAL log_slow_admin_statements=0;
SET GLOBAL log_queries_not_using_indexes=0;
SET GLOBAL slow_query_log_always_write_time=10;

The statements will be logged to your mysql data directory, into a file named after the hostname with -slow.log on the end. For example:

/var/lib/mysql/MyServerName-slow.log

The performance degradation from logging all queries was not significant in my case, with my site under medium-high load.

ChatGPT Programming Test

By James

On March 5, 2023

In Ruby / Rails, Technical

I was writing a little function in Ruby and thought I’d ask ChatGPT to have a go at it.

It is easy to read, explicit, and fairly idiomatic Ruby (though not concise). Not the most performant implementation but nothing terrible. It also mainly works but does have a bug in some cases.

Here is the code:

def working_days_between(start_date, end_date)
  # Make sure the start date is before the end date
  start_date, end_date = end_date, start_date if start_date > end_date
  
  # Calculate the number of days between the two dates
  days = (end_date - start_date).to_i
  
  # Calculate the number of weekends between the two dates
  weekends = ((start_date..end_date).count { |date| date.saturday? || date.sunday? })
  
  # Subtract the weekends from the total number of days to get the number of working days
  working_days = days - weekends
  
  return working_days
end

If you have the start/end date on a weekend, then you get a negative answer. Eg,

working_days_between(Date.parse("Sat, 04 Mar 2023"), Date.parse("Sun, 05 March 2023"))
 => -1

It is because the weekend number of days calculation is including both the start date and the end date. Ie, working_days = 1 – 2 = -1

A human could easily have made the same mistake, mind you.

A better / simpler implementation is:

(from_date...to_date).count { |date| date.on_weekday? }

Note the 3 dots (…) for the date range, which does not include the end date.

Later, I tried asking ChatGPT to regenerate the answer multiple times. It gave me quite a different version every time – some versions with bugs, some with no functions, some with support for public holidays, etc.

OrthoK vs. Contacts vs. Glasses

By James

On August 1, 2022

In Personal

A review of Ortho-K after 5 years, and a comparison with other options.

I spent the majority of my life with glasses, and generally they worked well except for water sports and fogging up (or getting knocked against my face when doing martial arts). For the last 5 years I’ve used OrthoK (Orthokeratology nighttime contact lenses) to correct my vision. Recently I’ve decided to give them a break and I’ve been using daily soft contact lenses.

OrthoK

+ Feel like naturally good vision when you are awake, no lenses to get dry, or get lost. No risk of you losing a lense and not being able to drive home.

+ Great for in water sports like surfing, boogie boarding and swimming without googles (pretty much the only option besides surgery or just not seeing well). Soft contacts have the risk of washing out in waves and also are not meant to be worn in these conditions as can get unclean water stuck behind the contact, leading to more chance of infection.

+ Good for pool swimming with googles, good for on water sports like sailing and kayaking (though need sun glasses).

+ Good for computer use (corrects astigmatism and don’t get dry eyes looking at screen like you can with soft contacts)

+ Cost effective compared to soft lenses. About $900 for a pair for 3-4 years, and say $120 of solutions every 3 months. That comes to about $740/year.

– + Change is long lasting. Takes about 1 month for effect to wear of completely (ie, can’t wear your old glasses for a long time). During this month, you need progressively changing glasses/contacts to correct your changing prescription and have to make do with the closest prescription you have on hand (can lead to headaches and poor vision). Not wearing for a night still leaves you with “good enough” vision for most things (except driving) but generally you have to wear every night (can’t just wear sometimes like soft contacts).

– Night-time vision is usually less good than daytime, so I tended to need to wear weak glasses for driving in the dark, and this tended to give me the edge of a headache sometimes. Also walking around in the dark in a place you didn’t know was harder though I usually didn’t bother with glasses for this.

– Variable vision ranging from perfect to OK and sometimes a bit poor, depending on how you slept / random variations in your eye condition.

– Slow to put in and out and clean. Budget about 5-10 minutes morning and evening (say average 15 min/day). Also monthly deep clean required, and you need to be very careful to wash hands well and use fresh towels to dry hands to avoid getting dirt in the lenses at all times.

– Occasionally get some dirt in the lense overnight and need to get up and take out and clean and re-insert and have poorer vision the next day.

– When travelling, always need to pack various solutions and backup glasses in case of problems. Hard to put in on an airplane so usually skip on overnight flights. Bit of a hassle on camping or multi-day sailing trips as need good lighting and water/soap in morning and evening, and not good for doing night watches on a boat where you need to get up in the night for your turn.

– Once you put the lenses in, you need to go to bed as not comfortable for extended use while awake. And when you get up, need access to bathroom right away to take them out. You can function in the middle of the night with lenses in if needed, but it is not that comfortable.

– Recently stopped OrthoK due to getting dry eye developing in one eye. This led to a sore eye and poor vision. With lots of expensive eye drops, my eye was not sore, but I was still finding it very hard to get good vision and putting in so many drops was a hassle. This problem only just started to happen this year, perhaps due to very dry winter air, and may not happen again if I resumed OrthoK later.

Glasses

+ Accurate eye correction for good vision every day.

+ Cheap, say $100-200 year assuming you keep your glasses for a few years.

+ Transitions allow automatic tinting like sunglasses in strong light.

+ Good for on water sports (with prescription sunglasses or transitions), but need band to stop them getting lost in water (eg, dinghy boat capsize).

+ Very fast to put on and take off (negligible!) and no hand washing required. Cleaning required daily or when get dirty but doesn’t take long.

+ Provides eye protection from sea spray, cooking with oil, etc

+- Highly visible on your face

– Peripheral vision is around the edge of the glasses and uncorrected on all sides.

– Easily gets dirty from rain/sea spray/accidental touches etc.

– No use for in water sports (though can get prescription goggles for swimming).

– Fogs up during exercise and when wearing a mask.

– Not good for martial arts as they tend to get knocked off or bent.

Soft Daily Contact Lenses

+ Fairly quick to put in and out (under 5 min a day) and need less hygiene than OrthoK. Easy to take with you when travelling.

+ Reliable vision correction that is still good at night and includes peripheral vision.

+ Can wear intermittently (eg, only some days based on activities planned).

+ Fine for on water sports, but still need sun glasses and a risk of losing a contact lense in dinghy boat capsize or similar.

+ Cost wise, not a big deal if you lose one.

– Not recommended for in water sports like surfing, boogie boarding and swimming without googles. Soft contacts have the risk of washing out in waves and also are not meant to be worn in these conditions as can get unclean water stuck behind the contact, leading to more chance of infection.

– Can get dry eyes in A/C (especially when driving) or using a computer (hasn’t been a big issue for me).

– Relatively expensive for daily use, about $1150 / year if used every day and bought 4 x 90 packs. Could be cheaper with weekly/fortnightly soft lenses.

– Makes quite a bit of rubbish since disposable.

At this point, I am still waiting for my OrthoK correction to fade and being able to try wearing my old glasses again. At the moment, I am using soft contact lenses which I am finding quite convenient, though it would be handy to have glasses of the right prescription to use as well (eg, when you get up). I will update this post when my OrthoK correction has worn off. I may resume my OrthoK in future (still have the lenses ready) but thought it was time for a review to see if they are worth the hassle and variable vision.

“4,000 Weeks” by Oliver Burkeman

By James

On July 22, 2022

In Soft Skills and Mind Hacks

When I heard about this book, I was intrigued by the idea of a time management book more about your lifetime rather than getting things done. I was not disappointed. Here are the take-aways I found the most interesting.

Often, you’re looking at your phone to escape an uncomfortable/boring situation.
If you focus totally on the current uncomfortable situation (accept you are there, and life is full of uncomfortable situations, rather than trying to escape) it will become bearable. As finite humans, we don’t get to dictate the course of events.
Having tricky problems means you’re alive, it’s the normal state. Life “is a process of engaging with problem after problem, giving each one the time it requires … the presence of problems in your life isn’t an impediment to a meaningful existence but the substance of one.”
Don’t wait for some future perfection when you get X, Y or Z or on top of things. There is always more to do, and things are always broken, and there will always be far more to do than you have time for. It is fine to neglect many things. Resign yourself to this finite reality. Serially choose one or a few things to focus on.
“Pay yourself first” – do what you really want to do first with your time each day, the rest will work out. Otherwise there won’t be time for what you want to do after doing the rest.
You don’t have time, your life is made up of time, “you are time”.
Making a decision/choice is liberating as then there is only one path forward. It is an affirmation – ie, you’ve chosen how to spend your time (no matter if it is earning money to support your family, playing with the kids, buying a house, or going hiking).
Don’t go with most comfortable option, think which grow or diminish you as a person.
If you want to feel like life is not going so fast as you get older, do more new things rather than follow routine – explore somewhere new, take a different route to work, take up a new hobby, etc.
“Attention is the beginning of devotion… you can’t truly love a partner or a child, dedicate yourself to a career or to a cause – or just savour the pleasure of a stroll in the park – except to the extent that you can hold your attention on the object of your devotion to being with.” — Mary Oliver
Notice you are already living in the moment anyway, like it or not. Trying self-consciously to “live in the moment” will fail.
Moments of bliss: enjoy a hobby where time passes without you noticing and you have no hope of achieving acclaim or profit. Freedom to pursue something you enjoy for no other reason.
Herzen: “Because children grow up, we think a child’s purpose is to grow up… but a child’s purpose is to be a child. Nature doesn’t disdain what only lives for a day… Life’s bounty is in its flow. Later is too late”.
Refuse to hold yourself to “an abstract and over-demanding standard of remarkableness.. drop back from godlike fantasies of cosmic significance into the experience of life as it concretely and finitely – and often enough, marvellously – really is.”
“No matter how much you plan or fret.. you can’t know that things will turn out all right” – there’s no real certainty so stop trying to manufature it.
A plan is “an expression of your current thoughts about how you’d ideally like to deploy your modest influence over the future. The future, of course, is under no obligation to comply”. Wonder what will happen next, rather than demand it to be as you want it to be.
You coming into existence at all, and most of your life is a highly unlikely series of events of which you had no control. So relax 🙂
You can put things in place to increase the chances of a happy outcome.. but there is no certainty in anything, or that things will turn out as you predicted.
When the uncontrollable future arrives we’ll have what it takes – we’ve got this far!
So “I don’t mind what happens.”

“How to Stop Worrying and Start Living” by Dale Carnegie

By James

On February 28, 2022

In Book Reviews, Soft Skills and Mind Hacks

As a long-time fan of How to Win Friends and Influence People, I was excited read this book. Especially the early sections I found very interesting and useful. Some of the later sections feel dated and less relevant. The ideas I found most useful were:

If you are worried about something, write out:
1. What am I worrying about?
2. What is the worst that can possibly happen?
3. [Accept it and imagine it happening.]
4. What can I do about it?
5. [Then do something about it right away.]
“When I am up against a tough situation, if I can do anything about it, I do it. If I can’t, I just forget it. I never worry about the future, because I know no man living can possibly figure out what is going to happen in the future.”
Check the facts, and come to a decision, then stick to it unless new facts come to light.
“God grant me the serenity to accept the things I cannot change; the courage to change the things I can; and the wisdom to know the difference.”
“Instead of worrying about ingratitude, let’s expect it.. the only way to find happiness is not to expect gratitude, but to give for the joy of giving.”
“Count your blessings, not your troubles!” — find out what is good in your present situation.
“Happiness is not mostly pleasure; it is mostly victory.”
“The really important thing is to profit from your losses. That requires intelligence.”
Do a good deed every day – ie, one that brings a smile of joy on the face of another.
Unjust criticism? Just laugh.
Ask for advice: “Won’t you please tell me what I did that was wrong when I tried to do XYZ? You are far more experienced and successful than I am. Please give me your criticism. Be frank. Don’t pull your punches.”
According to Henry Ford, how to increase energy and endurance: “I never stand up when I can sit down; and I never sit down when I can lie down”.
Relax muscles of face and eyes, relax the tension in your body (do it often) to have more energy.
Laugh at your sillier worries, you can laugh them out of existence.

Talk: Credit cards / Gateways

By James

On November 28, 2021

In ALT.NET, Talks, Technical

Tune in to the next Sydney ALT.NET meetup on Tuesday (30 Dec)! I’ll be giving a talk from around 6pm.

Accept credit cards: Gateways, architectures, code, and… money!

Have you been thinking to accept credit card payments for your new clever MVP, startup, or maybe even in your day job? Well, you’re in luck! James will give you a primer on how to do it simply and securely, based on his recent journey to the Gateway jungle.

Please RSVP on meetup and join the Twitch stream for some fun!

“Authentic Gravitas” by Rebecca Newton

By James

On November 28, 2021

In Soft Skills and Mind Hacks

I read this book a while back and have been meaning to write about it. I found it contained some interesting points, especially on being intentional (and comparing your actual impact with what you were aiming for) and working appropriately with different communication styles/personalities.

Here’s my favourite parts:

“The reality is, we all have a gap between our intention, our action, and our actual impact. To have gravitas, be clear about your convictions (what really matters in a given situation), be clear about your intention (how you want to show up and impact the situation and the people in it), be disciplined in checking that your impact is lining up with your intention (through your own observations and seeking feedback), and be committed to continually adapting your style.”
“On being intentional, ask yourself, What footprint do I want to leave very day? How would I want other people to describe me?“
“People who are regarded as having high levels of authentic gravitas may look confident, but actually they are choose to be courageous.” “They feel fear and do it anyway. They choose courage.”
Ask more open questions in meetings to find out what is actually driving the other person’s thinking and decision-making. Eg, “What are you excited about right now”, “What’s your biggest concern?”, “What matters most to you at the moment?”, “How do you feel about what’s happening?”, “What is the main thing stopping you from making progress?”, “What are the forces at play in this issue?”.
If people ask for help, give them your full attention or ask them to come back later. Eg, “Yes, I’ve got 10 minutes – will that work?” “Or we can have a longer chat later today at 2pm?”
Script the opening of a talk carefully to engage and hold the room – verbally and nonverbally – from the outset before rushing into the details. Eg, open with “Thank you Angela. [pause] Good morning, all. It’s great to have you with us today. It’s a critical time in our industry. We’re currently facing uncertain market conditions, unexpected legislative changes and rapid technological development. Navigating this environment seems more challenging than ever. I’d like to share with you…”. Script the finish also. The middle can be looser. Telling personal stories helps connect with the audience.
IMPACT preparation for a meeting: What do you believe about this situation/possibility? What do you want them (the people you engage with) to think, feel and potentially act differently as a result of this encounter with you? What is motivating them (acquire, bond, comprehend, or defend)? What is their perception of this situation and of you right now (and what do you want it to be)? If nothing else, what would you want them to remember and pass on (max 3)? How should you open and close? What stories would be relevant and support your message? And finally, think about your technique.
Avoid the “face freeze”: Put your hands into fists and roll your knuckles in circles around and around in your cheeks. It loosens your face and you become more conscious of your facial muscles, enabling you to feel the tension in them and make the choice to relax them.
Even if people don’t give you positive non-verbal/verbal affirmation, “Choose to give everyone equal energy and attention.. [remember they have a gap between intention and impact too]”. With big audience, look at a back corner of the room and slowly use your eyes to follow an “S” pattern down through the group to the front row. After a minute, go back up to the opposite corner and find down again following an inverted “S”. In a small meeting, be mindful from the outset of your decision to give everyone eye contact.
Opportunity mind-set rather than a threat mind-set. You’re most likely nervous because this situation matters. And it matters because it’s an opportunity that could lead to positive outcomes. Don’t get anxious, get excited!
“When working with people who tend to be optimistic and big-picture oriented, it’s important to respond with energy if they are sharing an idea. Explain why you like the ideas (be authentic and only communicate what you genuinely feel positively about), or comment on how much work they’ve clearly put into it, or the interesting nature of the project or topic. And share that with energy.. not over the top.. but at least some energy.” Immediately critiquing a new idea will deflate them and close their ears. Instead, after initial positively, ask something like “Would it help if I went through the technical details and came up with a list of things for us to look at? I want to make sure it’s all smooth when you take it to market.”
Prepare a short causal response to the “How are you?” type question in case of casual coffee machine conversations at work. Eg, “Oh, hi Chip. I’m well thanks. We’re busy with a new project around sustainability. We think this can really drive some positive change. We’re seeing a lot of interest from clients. How are things with you?”. Make brief encounters matter.
Remember the wide variety of influencing techniques available: rational persuasion, legitimating (because X says so), favours and exchange (Can I ask you a favour?), inspirational appeals (emotions and values), ingratiation (I don’t how to do it!! reply: I know you can. You you’ll be good at this because [reasons why]) and consulting (ask their help/feedback to increase buy-in).

Lockdown with Kids – Take 2

By James

On July 24, 2021

In Personal, Soft Skills and Mind Hacks

It’s coming up to the second month of lockdown in Sydney with my energetic primary school age sons and lovely (and patient) wife. Since this is the second time around, I thought I’d share some ideas which we have been using which have worked for us. Your mileage my vary!

First of all, let us reframe the lockdown as an epic cruise through ~~space-~~time, towards normal life.

With this framing in mind, think about the entertainments and events and plan for a long voyage on a ship, to stave off boredom and keep the crew happy and productive.

Decide and family routine together and publish it on the fridge. Stick to it to give structure to the day. Make sure it includes recess and lunch similar to school – these are also when the family gets together from their separate activities during the work/school day.
Have a guessing competition on the number of covid cases for the day (new cases & number in community) and a prize draw at recess (eg, a chocolate for the winner). Everyone records their guess at breakfast.
Make use of every room in the house, balconies, porches, gardens etc to give more opportunity for privacy and space.
Celebrate the end of the work/school week by ordering delivery food for dinner on Friday. This delineates the week from the weekend, and gives parents a break from cooking and everyone something to look forward to.
Have international food nights once a week: choose a few countries for the first few, then when you run out, play pin the tail on the country with a map! Research the food, order appropriate ingredients and cook. Dress up if you feel like it. Make it a highlight of the week.
Family kareoke nights if that’s your thing. I think dancing could work too though we haven’t tried it yet.
Get kids involved in cooking whenever you can, they love it and learn too.
Get lots of outdoor activity gear such as basket ball hoops, soccer balls, pull up bars, bikes, kites, etc to provide variety.
Exercise every day to get out the the house (and usual exercise benefits). Choose a mix of quiet activity by yourself to get some space (eg, running) and with kids (eg, bicycling) and go with what you feel like on the day. Calisthenics is good in a park where kids can play while you work out.
Make computer game playing time for kids a currency that they earn. For example, our kids earn:
- 50% of the time they spend on extra study outside of school for game playing.
- I review their school work at the end of the day and award game time based on amount and quality of work they have done during the day (in range 15 min – 1 hour).
- Chores like taking out the garbage/compost earn 5 minutes.
- Repeatedly doing something we’ve asked them not to do (or not doing something they are meant to do) leads to a fine of 1 or 5 minutes of game time.
- Ask kids to do a written accounting of their time before they use their game time, to check the amount of time and to give them practice presenting tabular data and doing calculations.
Remember that work done at home by the kids for home schooling does not need to be perfect and fixed by parents before submission. The teacher can correct kids’ work during the day. Sure, there are times when the boys need help, and that is OK too, but you don’t need to sit with them every minute. Home schooling does give you the possibility of getting more involved yourself, but I like to have the boys work as independently as possible during the day and go over things with them after school “finishes”. This also allows me to get more of my work done during the day.
For school work, if possible, ensure each child has their own device with a camera for submitting hand-written work, to avoid having lots of logging in and out to switch accounts, and fights over the device.
Make an effort to stay in touch with friends via video calls (at least one call a week).
Get good noise cancellation headphones for when you are working. For me this is vital.

I hope you find some of these ideas useful as we cruise on towards freedom.