Brian Mark - Guitaraholic

I have two tables, one with bases and one with transmitters. Bases work without transmitters, but transmitters don't work without bases. This means a HAS_MANY / BELONGS_TO relationship works out well in most cases. I recently ran into a two-fold problem. 1) I wasn't getting the null values from the related table when filtering on !=7. 2) If I added an OR statement, it ran really... really... really slow. The second was easily fixed by adding an index containing the joining field and the filtering field, but that still left the OR, which wasn't real efficient. The solution was coalesce in my search() function. $criteria->addCondition('coalesce(transmitters.field,0) != 7'); transmitters was the related table I was filtering on, and field was the field. Basically, anything that's null becomes a 0, so it's not a 7 (and not a null), so it shows up. Nothing too difficult this time, but hopefully it can save someone (probably me in a few weeks) s...

I got a survey from Sun about our not renewing MySQL support. That's fine, I can answer those types of questions, but please - don't make me think about it. That just takes too much time to figure out what number I want to choose. Hopefully the database on the other end understands the confused responses.

We recently got fed up with UPS's server outages (of the 2 IP's we typically use for shipping quotes, 1 was down pretty regularly for a while). So, I decided to populate our shippingrates table with all of the data we could ever need. It all went along fine, until it got to about 10M records. At that point, our site started slowing and slowing, and what I found was the keys weren't able to be used for our standard query. It wasn't that big of a deal with 100k or even 200k records, but when I got up to 10M (it's over 30M now), that caused problems. I did an alter table and made our index include one more field, and it now sorts everything the way I wanted by default. It wasn't actually the select statement causing the problem, it was the ORDER BY portion. It was having to use a filesort, which for a huge table doesn't work real quickly. What other symptoms was this causing? For one, I was seeing the time spent waiting for IO growing. When looking at the outpu...

After what seems like forever (even though it was only 5pm - 11:30 am), we're back up and running. Time for me to get some sleep and warm up my feet. It's too cold in the basement without shoes on.

We've got a pair of replicated MySQL database servers. That's great - until the replication process corrupts both. That's the situation we're currently in. Much to my (dis)pleasure, I get to use the platinum support that we paid a bunch for. I called them and had an engineer on the phone within 15 - 20 minutes (they answered right away and had him call me back, which was awesome.) The little bit of hair I have left is getting pulled out by the minute, though. It's really not simple troubleshooting a crashed pair of servers, as we've already been working on this for 4-1/2 hours. I'm waiting for the engineer to contact me once again, so I thought I'd post something quick. This was, however, a great opportunity to upgrade to the latest version of MySQL enterprise. Oh, the joys of doing business online.

We ordered our database servers from IBM. These have less RAM than the Dell machines we ordered as web servers, but they have much more processor. I didn't even think about the Itaniums in the Dell's being 64 bit when I placed the order, so they were more CPU than I thought, but that's nothing compared to what I've got going on here. Base Model The base model is an Opteron powered 1U server with 1GB of RAM, 1 Gb card, and a SCSI adapter on-board. Memory Upgrade We upgraded the RAM on each of these to be a mere 4GB each. Processor Upgrade We went from the base model Opteron to dual Opteron 275's (dual-core) in each machine. Hard Drive Upgrade Databases are only as reliable as their drives, so we went with mirrored storage. Another NIC I also added in a second NIC for total redundancy (one for each switch). Warranty Upgrade IBM seems to have more confidence in their servers than Dell, because for a little less than what Dell's 3 year warranty was we're gettin...