Ingres event deadlock hunting

The Ingres database utilizes a log file to store uncommitted transactions.
Upon commit, the portion of interest in the log file is transferred to the journal.

On Monday, the database died again.

The logfile showed
“UPDATE abc FROM xyz SET a=x, c=z WHERE y = b”

The database was back up in no time.

I looked at the queries.
“EXECUTE IMMEDIATE SELECT … FROM …;”
“COMMIT;”
“UPDATE abc FROM def SET c=d WHERE b=e”
“UPDATE abc FROM ghi SET c=i WHERE a=g”
“UPDATE abc FROM xyz SET a=x WHERE b=y”
“COMMIT;”

I repeated the last query by hand as the user. It was in the logfile after all.
‘Your transaction has been aborted due to the transaction log file having reached
one of the limits set by the system administrator. These limits are log_full, force_abort,
and 90 percent of force_abort when using the fast_commit option to start DBMS Servers.’

I’ve seen this message thousands of times.
Multimillion row queries.
Half a million rows with 40+ columns.
Double aggregates on a quarter million rows (each aggregate [SUM/MIN/MAX] creates a temporary table to reorg data).

I’ve never had it crash the database.

Maybe we ran out of file descriptors.
This seemed logical. Commits are always opening file handles to the journal/log and swapping data.
Under a large load, the number of file descriptors reaches ridiculous numbers.
Sadly, lsof | wc -l proved that we were nowhere near the database user’s limit or system limit.

I went back to the logfile.
E_DM011F_TRAN_ABORTED    The transaction has been aborted by the force abort event. This error is caused by killing a session from the external events.
E_DM010C_TRAN_ABORTED    The transaction has been aborted by the logging system in order to reclaim log space.
E_QE0024_TRANSACTION_ABORTED    The transaction log file is full.  The transaction will be aborted.
E_DM011F_TRAN_ABORTED    The transaction has been aborted by the force abort event. This error is caused by killing a session from the external events.
E_QE0022_QUERY_ABORTED    The query has been aborted.
E_DM011F_TRAN_ABORTED    The transaction has been aborted by the force abort event. This error is caused by killing a session from the external events.
E_DM9715_PSORT_PFAIL    Error in parent thread of parallel sort – child terminating.
E_DM9715_PSORT_PFAIL    Error in parent thread of parallel sort – child terminating.
E_QE0022_QUERY_ABORTED    The query has been aborted.
E_DM011F_TRAN_ABORTED    The transaction has been aborted by the force abort event. This error is caused by killing a session from the external events.
E_DM9715_PSORT_PFAIL    Error in parent thread of parallel sort – child terminating.
E_DM9715_PSORT_PFAIL    Error in parent thread of parallel sort – child terminating.
E_CL1035_LK_EVENT_BAD_PARAM    LKevent() failed due to a bad parameter. The lock identified was already waiting for an event; status of the lock is 328, and flag is 18.
E_CL1003_LK_BADPARAM    Bad parameter(s) passed to routine
E_DM904B_BAD_LOCK_EVENT    Error requesting lock event with flag 2 and value 196608 for lock list 000000C5.
E_DM942B_CRASH_LOG    Server has written dmd_check() information to crash log file: iicrash.log.
E_DMF015_EVENT_SET    Unexpected error set event wait reason.

I determined that each E_DM011F_TRAN_ABORTED corresponded to one of the uncommitted UPDATE statements.

This led me to believe that the ABORT sequence did not cancel the remainder of the UPDATE statements.

Yet, there was an extra E_DM011F_TRAN_ABORTED!

I took the code and repeated the process in a development environment.

E_QE0024_TRANSACTION_ABORTED    The transaction log file is full.  The transaction will be aborted.
E_DM011F_TRAN_ABORTED    The transaction has been aborted by the force abort event. This error is caused by killing a session from the external events.
E_DM9715_PSORT_PFAIL    Error in parent thread of parallel sort – child terminating.
E_DM9715_PSORT_PFAIL    Error in parent thread of parallel sort – child terminating.

My idea lined up better. The three UPDATEs all caused the transaction to be aborted by FORCE ABORT events.

Something I had not considered, the first TRAN_ABORTED was the result of an external program filling up the log at the same time.

Then, it was all clear. It was our Ingres configuration file.

We setup Ingres with a soft limit called LOG FORCE ABORT LIMIT at 72% of the log file being filled up.

We also setup Ingres with a hard limit called LOG FULL LIMIT at 90% of the log file being filled up.

18%+ of the logfile was utilized. A query then used 72%+ of the logfile.

I opened two isql windows. I performed a query to ‘CREATE TABLE asdf AS SELECT * FROM WHERE non-indexed key”. That would take some time. I performed the UPDATE from the ‘problem script’. Database instantly crashed. I had reproduced the bug.

The events triggered at the same time, and attempted to reclaim the log space and kill the query.

Without freeing the lock from the first UPDATE (from trying to reclaim log space), it allowed the other two UPDATE statements to overflow the logs past 100%.

Once this happened, the system panicked and brought down the whole DBMS.

The solution is simple: increase the log file size, lower the soft limit and increase the hard limit.

If our log file is 2GB:
(hard) 90% is 1.8GB
(soft) 72% is 1.44GB
(active) 18% is 368MB

Most queries in our systems can easily utilize 400MB of temporary space. Recall that using GROUP BY or any aggregate (COUNT/SUM/etc) creates a temporary table.

If we increase our log file to 8GB:
(hard) 90% is 7.2GB
(soft) 72% is 5.76GB
(active) 18% is 1.44GB (Our old 72% limit!)

I also propose restructuring the limits to create a larger spread, because as the amount of data increases this gap will become closer over time:
(hard) 96% is 7.68GB
(soft) 47% is 3.76GB
(active) 48% is 3.84GB

Now, your active limit is higher than your soft limit. Your active limit + soft limit will not cause the logging system event to reclaim space the same time as the soft limit being hit.

So, if 48% of the log file is used, and a query comes along and hits 47% of the soft limit, it will only trigger the FORCE ABORT.

Only if 49% of the log file is used, and a 47% query occurs, the dead lock will happen again.

Or we could just patch our database to prevent the deadlock between the soft/hard limits causing events at the same time.

Sony a200 Infrared Conversion

I could not find a guide for this on the internet. So here is my quick 2 minute intro:

Attack from the bottom and back where towards the LCD screen. As I learned in my 200 bolt adventure, the front only results in pain.

The front will only let you view what you’re after:

Sony A200 Front Removal

 

Once you remove the screws from the bottom and side, the back should fall off. If it does not fall off, start looking under the USB covers and the various covers for hidden screws. There are actually two under the eye piece’s rubber.

You should end up with a fully detached LCD screen. The orange ribbons can be removed by lifting up the plastic clips holding the end of the ribbons.

Sony a200 LCD screen

 

You should end up with a mess of ribbons and notice the huge PCB in the center of the camera.

Sony A200 Infrared Conversion

 

I ended up taking off the ribbon of this to avoid breaking it. Once I got the ribbon off, I used a flat head screw driver to pop off the pink infrared. Afterwards, the camera only worked in Manual mode.

Sony A200 Full Infrared Conversion

 

The infrared glass is surrounded by a black piece of plastic. This piece of plastic is glued down and is easily pried off. The end result was not too great. It definitely took enhanced infrared pictures with a filter to block out natural light. Yet, without a viewfinder, this camera made for difficult shots.

 

Replacing brake pads rotors 2005 Saab 93

My wife and I both knew the signs. That little spring was digging into the rotor creating a wonderful metal to metal sound. The front was taking more control than the back in hard braking scenarios. Our brake pads were at the end of their travels.

It is Memorial Day weekend. The office let out at 1pm on Friday and Monday was a holiday. I needed a project. For months we have been wanting to get our brakes, shocks, and struts checked out by the labor-free people at Sears. Little did we know, we were not going to like what we heard from the mechanic. The estimate was around $1000 just for the brakes. Potentially closing on a house on May 30th or June 7th, we knew we were in over our heads when we heard “I wouldn’t let my wife or daughter drive it until the brakes were completely replaced.”

Old versus new brake pads

Original compared to new brake pads.

Damn. So, can we break down that estimate just for the brake repairs? $275 for the front per tire. $225 for the rear per tire. A smooth $1000 before tax. Sadly, the labor was nearly $700. They planned on replacing all of the rotors and brake pads with a brake fluid flush.

A little internet research led to a video of a normal guy performing this repair on a 2006 Saab 93. As he says, he’s not a mechanic and if he can do it, I can too. Rotors should be replaced in pairs.

I found a 40% off coupon for Advance Auto Parts. Let the shopping commence:

  • Wearever Brake Rotor x2
  • (Part: YH145601) [Discount Price: $57.40]
  • Wearever Gold Ceramic Rear Brake Pads (x4 pack)
  • (Part: GNAD 1095) [Discount Price: $25.89]
  • Wearever Silver Semi-Metallic Front Brake Pads (x4 pack)
  • (Part: MKD 972) [Discount Price: $17.49]
  • Permatex Ultra Disc Brake Caliper Lube
  • (Part: 85188) [Discount Price: $2.75]
  • Total: After Discount: $103.64

Just to complete the shopping list:

  • T45(Caliper)/T30(Rotor) Torx Bit laying around from a 100pc obscure bit set.
  • HarborFreight (Part: 68457) [$9.99]
  • Ratchet, flat head screwdriver, and pliers.
  • Lisle Torx E20 Socket for Rear Rotor (E18 for Front)
  • Oreilly (Part: 26870) [$8.49]
  • Brake Cleaner (For wax on new rotors + buildup on tires/caliper)
  • $3.99 most stores
  • Jack/Jack stand/Tire tool.
  • Check your trunk.
  • Latex Gloves
  • NEVER EVER PUT ROTORS ON WITH GREASE/DIRT. USE GLOVES WHEN HANDLING.

Then, I loaned/rented:

  • Disc Brake Caliper Tool
  • Autozone [$60.00]
  • 1/2″ 24″ Breaker Bar
  • Autozone [$25.00]

Note on loaning: If you do not know about loaning tools, you’re missing out! Automotive store will lend you used tools for the price of the tool. They charge it to your debit/credit card and will refund it all once you return the items. From vacuum systems, drivers, to extreme hardware.

That brings my overall total to $127 for parts/tools. $85 for loaned tools which will be reimbursed to my account once I return the tools to the store. $127 for a $1000 job, what am I missing?

Once again, not a thing. The mechanics were going to replace my front rotors. After a thorough inspection, there were no signs of extreme wear. To check yourself (I’d advise you have someone else do it for free), look for odd colors (blue/orange/red) which are signs of heat damage, look for cracks, and finally look for and spots which are not smooth. Rotors should be smooth.

If you’re changing the pads and not cleaning your brake system/tires, you’re looking at a 45 minute job for all four tires. Sigh, what a ripoff. Rotors add a little more time due to those E20/E18 sockets that require the strength of an ox, unless you’re lucky.

The prep work consists of opening up your rotor packages. Rotors come in cardboard boxes. The cardboard box reveals a shiny rotor wrapped in plastic, or so I thought. I realized I was betrayed by the plastic because there was a layer of yellow wax coating nearly 1/4th of my rotor. A few paper towels and some brake cleaner will get this off. Just clean one side of the rotor in the plastic with some brake cleaner, then lay some paper towels down and do the other side. Shake out the cardboard box, freeing dust, and lay the rotor in it. Repeat for any remaining rotors.

Prior to jacking up the car, you will need to loosen the lug nuts on the wheels you are going to work on. I ended up having to stand on my bar and put weight into it in an up and down motion (130lbs of fury…). After the lug nuts loosened, I hand tightened them back up.

I jacked the car up. The Saab has handy arrows which point to square slots for the jack to fit in and not slip in any direction. Look for these triangles on the side of your vehicle. Most people will place a jack stand underneath the car and lower the jack to meet the jack stand. I trusted the Saab’s handy square of stability to keep the jack in place. I also knew that the brake work would not have my body under the car. Remove the lug nuts that you hand tightened once the wheel is in the air.

There’s one last thing before you near the brakes: pop the hood and remove the cap off of the brake fluid. Once you start using the brake disc tool and adjusting the caliper’s slide pins, brake fluid is going to pressurize in the system. We don’t want it to pop off the cap and explode everywhere.

Alright, pull out a ratchet and a T45 torx bit. There are two plastic caps on the two bolts of interest. Ensure the T45 fits in snug. There is a bit of pink lock tite on these bolts.

2005 Saab 9-3 Brake Caliper Sliding Pins

Once the two bolts are removed, use a flat head screw driver to jimmy out the spring. Take note (or a picture) of the direction it is pointing. You’re going to need to put that back in later on. It is used to  give an audio queue by scraping against the rotor once your pads are too low.

2005 9-3 Saab Brake Warning Pin

Now that the caliper’s bolts and springs are off, remove it and place it on the axle. Be sure not to stretch out those cords. We have an emergency brake line and brake fuel line. Too delicate/expensive to not be gentle.

The brake pads should fall off. Take a mental note that the spring is on the inside, the pads face the brake, and the other side of the pad will need grease on it to prevent squeaking brakes.

Once that’s off, we’re looking at the rotor, and man is it ruff. It’s seen years, cities, states, weather, and had an intimate relationship with what used to be a brake pad. In the middle of the rotor, there is a T30 torx bolt. Place a bit in it and undo it while holding down the rotor. DO NOT STRIP. If it is giving you too much trouble, there are plenty of chemicals and heat sources (but not a combination) that will loosen up this bolt. These are very delicate because they actually face the weather. They are usually surrounded in rust.

2005 Saab 9-3 Rotor Removal

Now, you’re either lucky or you’re not. Depending on the make, model, and year, you either need to take off the disc holders or you can turn the rotor at an angle and pull it off.

If you’re the unlucky type, this is where your breaker bar and 1/2 inch E20 socket come into play. You’ll find the E20 bolts pretty easily. They have lock tite and nearly 200lbs of torque on them. Good luck.

Either my mathematical inclination come through or I was really lucky, the right angle had my rotor off in less than 3 seconds of removing that T30 bolt. I placed on my clean rotor using latex gloves and aligned it with the holes. Screwed back in the T30 bolt.

I greased up the backs of the brake plates and placed them back on just hanging there.

2005 Saab 9-3 Brake Pad Grease

The brake disc tool is used to recompress the caliper. If you do not have the funds to rent one of these, a vice grip can do the same job. Loop the long piece of metal through the vice starting at the turning handle. Bring it down to the circular part. Align the circular disc to the holes in the caliper with the long piece inside of the caliper. Hand tighten the bolt on the tool to ensure a tight fit. Give it a few turns. You should see the circular piece being compressed to allow room for the new brake pads. Untighten the bolt once it stops screwing in. (As a note, I HAD to use a vice grip on the front calipers).

2005 9-3 Saab Brake Caliper Piston

Place the caliper over the brake pads, put a little grease on the caliper sliding bolts (the T45), then tighten them back on. Ensure the rotor spins. Open the car and give the emergency brake a pull. Ensure the rotor does not spin. Put back on the tire, hand tighten the lug nuts.

Place the cap back on your brake fluid reservoir under the hood and close the hood. Lower the car. Release the emergency brake. Pump the brakes a few times with the car off to get the fluid running. Turn the car on and engage the brakes. Go easy and let them wear in before going crazy.

Bam, I’m eating steak tonight because I saved $500+ on my brake repairs. The majority of my time was spent being OCD and cleaning up the old caked on brake material off the rims with the brake cleaner and a rag.

Requirements change

In software engineering, I was thoroughly warned about the maintenance stage being the most vital stage of development. The ability to reuse and extend existing code, address defects without creating functionality issues, and evaluating the costs and risks of maintenance in production environments. I also heard that ‘requirements change.’ Much more at my internship than at school.

It was echoed throughout the halls and meetings. Requirements change. Underlying in that phrase, business evolves. People change their minds, information gets funneled over time, and a solid requirement can turn into a nightmare after a few users have emails back and forth while in development.

The issue comes along when requirements fundamentally change code in the maintenance stage. The production code reaches ‘legacy’ status and no longer can evolve without defects. Technical values and ideals evolve with the business. The ideas of five years ago were standard, but the extended version needs to be brought up to standards. On top of being extended, standardized, and sometimes mangled, the code keeps evolving over time. Sometimes these modifications are on a monthly or yearly basis, but there are legacy pieces that are modified each decade or so.

The design of years ago begins to be lost over time. The extended code fails to reuse or adapt the existing system to meet the new requirements. Instead, add-ons and modules are created and built on top, around, or surround the program to keep it in line. Yet, the cost to redesign always seems to be too high.

I find myself questioning when to throw away code (ie: move it to a safe place as a contingency plan), leave in unused functionality (in tables and code), or just comment out the code.

Being burned quite a few times, I know that throwing away existing data is not an option. From code, to tables, to columns. The minute you get it to production, the business evolves and needs portions of the logic back or needs the whole thing back and extended.

Yet, how did it come to this? I could easily code up the requested functionality back into the program if it was removed from the system. The answer lies in our development process.

The waterfall process is a ‘requirements upfront’ procedure. We have meetings, gather requirements, design, code, peer review, test, test, test, and move to production. This is where my habit of commenting out code has become habit. If its just commented out code coming back into existence, it only goes through one stage of testing. If it is new code, it must reenter the whole waterfall process from start to finish. Endless meetings, debates, departmental delays, etc.

Following the same reasoning, we extend the code beyond the code’s extendability. We complicate designs by wrapping existing code. Legacy code becomes this mangled black box. New code sanitizes the legacy code from the beginning to the end and manipulates behavior to the point of losing functionality.

I suppose, sometimes, it is beneficial just to redesign. I don’t know exactly how to determine when it is time to flip the switch, but when you have 6 tables that have the same exact column names and partially differentiated data communicating with each other in 36 different ways… it becomes a nightmare. What should be a one to one relationship turns into a one to many to one to many to one to one to many for a simple sanitation on an input.

An initial post

Scratch ideas to cover:

Modifying DSLR to infrared, SOA and REST and deterministic finite automata, Django tutorials, explaining monitoring/distribution setup for site, FHA, craigslist, adwords, analytics, Windows 8, Android apps,