Lance Cleveland

Performance Benefits Of COUNT(*) In SQL

This is something I often come across in code.  Just came across it in our MySLP project today , in fact.

While the performance impact of that statement in itself is negligible, it does add up.  For most database engines that statement incurs additional overhead as it processes logic related to a specific column containing a specific data type.   Some database engines check if the column is null for EVERY RECORD, for example.   Not a big deal if you have an index on the column but even with an index there is overhead.

The better option is to use count(*)

COUNT(*) is a semantic placeholder, NOT an operation directive in-and-of-itself.   Most database engines, MySQL included, recognize this construct and employ “shortcuts”.

Performing this operation on a moderate-sized table, for example will show a slight difference in the speed of getting the count of records in the table with count(*) almost-always pulling the desired count of records in ZERO seconds and other variants taking a second or more.

Not much, granted, but it IS a big deal with you are working in large volume instances where you are processing billions of records not thousands.  Same thing if you have thousands of tables not dozens.   A site with hundreds-of-thousands of visitors not hundreds.

The performance differential is even more pronounced when using joins in your count.

Don’t be fooled into thinking count(id) only reads one column from your database while count(*) reads ALL of them and therefore count(id) MUST be faster.  That is not how it works.  Not for most database engines.

 

Here is an article by Percona on the same subject you may be interested in:

https://www.percona.com/blog/2007/04/10/count-vs-countcol/

OS/X Sierra – External Display Issues

If you’ve not yet updated to OS/X Sierra you may want to wait for the 0.1 release.

Many users experience no issues when upgrading but more than a few are finding they need to do some fairly drastic things to get their system working as normal. There are numerous reports of people doing a simple SMC Reset (hold down Ctrl + Shift + Option and press power) to get things back to normal. Others are having to perform full “clean installs” of Sierra to get things back to normal.

Sierra OS/X Issues

In a brief search for OS/X Sierra problems there seems to be numerous issues related to two critical areas of the operating system: Display Management and Thunderbolt I/O.

Users Report Loss of HiDPI Scaling

Top OS/X Sierra Problems

Troubleshooting OS/X Sierra Problems

Dual Thunderbolt External Monitors

Personally I am having an issue working its way through Apple Support related to the use of dual Thunderbolt-port connected external displays.    Whenever I connect and turn on two monitors, one connected to each TB port, the FAN 0 speed slowly cranks up to 6,000 RPM (full throttle).    Depending on whether or not I’ve recited the proper black-magic incantations I may-or-may-not be able to turn off one of the two displays and make the fan quickly ramp back down to a normal 2500 RPM.    It is interesting to have my external HP monitor button act as though it is wired directly to FAN 0 but that 6,000 RPM fan noise is irritating after about 3.5 seconds.

istats_2016-10-17_11-44-05

Apparently there are times when I’ve used the wrong tone or maybe mispronounced an ancient Egyptian God’s name and the simple monitor on/off trick doesn’t work.   The system will completely hand and deprive me of the use of my keyboard or mouse after I turn off a monitor; Even after disconnecting all peripherals and opening the laptop to use the track pad on on-board keyboard.

I’ve been using the same hardware, same connections, and same applications for 2 years now (other that a myriad of software upgrades).    The only thing that has changed since the “fans working normally” days of last week and the 6,000 RPM mode is having upgraded to OS/X Sierra earlier this week.      My guess is someone at Apple buggered a register in the TB I/O code and has overflowed some bits to piss all over other elements of the OS.   We’ll see if Sierra *.1 fixes the issue.  I can only hope it does.

Looks like my MacBook is at it again – time to sign off… and good luck!

istats_power_tb_still_on_2016-10-18_17-01-16

Update: October 19th 2016

What I’ve Learned About The Display Problem

The issue I am having with fan 0 hitting full throttle seems to be related to running both TB ports.  For the sake of discussion I am calling the port nearest the power input TB0 and the port closest to the USB input TB1.

TB0 = black, TB1 = white

TB0 = black, TB1 = white

The problem appears to triggered by TB0 , but it does require the help of TB1.

Case 1: Reproduction

Plugin HP ZR2440w monitors into TB0 and TB1 using display port.

Make sure TB1 is the primary display.

Start firing up apps.   Firefox,Terminal,iMessage,iTunes,Siri,Snagit,Slack in my case then start Photos and the fan quickly ramps up.

Turn off the monitor connected to TB0.   The ran immediately starts to ramp down.

Case 2: Add HDMI

Running similar scenarios with the HDMI port pushing video to a third HP ZR2440w seems to have no affect.    Whether it is connected or not the results are the same as above when TB0 and TB1 are active.  Turn off TB0 and the fans ramp down.  Turn on TB0 and the fans ramp back up.

Case 3: Drop TB1

An interesting test case, making TB0 the primary monitor and doing the same exact scenarios above and turning TB1 on/off has no impact on the fan RPM.   Once the fan is at 6K RPM it stays that way even though the system clearly shows the TB1 display being dropped from the display stack.

osx_sierra_dual_monitors_2016-10-19_11-22-32

Case 4: TB0 As Primary , Off or Discconnect

This one makes perfect sense when you think about it for a moment; though I do believe OS/X needs to address this as a separate issue.     If you make TB0 the primary monitor it will NEVER drop from the display stack.   If you turn it off OS/X does not make another display the primary display, keeping the TB0 monitor “in memory”.    Same thing if you physically disconnect the TB0 cable.  It will NEVER drop.

Because of this turning off or physically disconnecting TB0 while the fan is pegged at 6KRPM has not affect.

If you make TB1 the primary display at this point, OS/X will immediately drop TB0 from the display stack and the fan RPM starts to drop.

Update: October 19th 2016

It Is Siri

Of course it is.  The biggest change to OS/X Sierra is the addition of Siri.

Through an errant keyboard click I happened to engage Siri and IMMEDIATELY the fan speed dropped from 6K RPM to a normal 2K RPM.     As soon as Siri stopped listening the fan went back to full throttle.

Now that is odd.    Repeated tests, same results.

Siri code is toggling SMC registers.  Sounds like a buffer overrun to me.

Maybe It’s Not Siri

Turns out that Siri turns off the fans in order to better hear the listener.   It seems like Siri is only doing something to force the fan speed to 0 and is not directly related to the issue.   An a related note, some gamers have fried their MacBooks because of this by enabling Siri and then getting it stuck that way while running a game.  Poof… overheated MacBook.  But that’s another issue.  No word if the MacBook was completely fried or not but I’m guessing it shut down before that happened.

It’s Back

After the Sierra 10.12.1 patch turning on Siri doesn’t prevent the problem like it had previously.   Whenever DP0 + DP1 + HDMI are connected the fan is at 6K RPM.   DP0 + DP1 it hovers around 5k RPM.    DP0 + HDMI seems better (not great).    Turn OFF DP0 and DP1 and the fan almost immediately goes 2K or less RPM.

Weeeee!

Maybe Apple support will call me back.   It’s been a month with no updates.

OSX Left Fan (Fan 0) behavior with external monitors in place.

OSX Left Fan (Fan 0) behavior with external monitors in place.

Back To El Capitan

Apparently after the initial interest in the problem, Apple decided they couldn’t be bothered with this.     Sierra constantly runs the left fan at 5K+ RPM any time the HDMI monitor is attached.   I never get a call back from Apple Support when I leave messages these days.

I reverted to El Capitan.    Under peak load the left fan is maxing out at less than 4K RPM with all 3 monitors going.   Not an apps issue, at least not without “assistance” from macOS Sierra and not a hardware issue.

Proper Fan Speed on El Capitan

Proper Fan Speed on El Capitan

Apple, you’ve finally failed me on the same level as Microsoft.     Boo.        I’ll take the 3 hours of downtime in 2 years over countless hours lost to Windows over the same period.  Still sucks having gone from 5 minutes of downtime in 2 years to 3 hours and 5 minutes because I had to revert my entire system to a 45-day old backup running El Capitan.   That HUGELY SUCKS.

Lesson learned:  ignore the future macOS updates from Apple.   Since Steve Jobs has gone on to better things Apple has slowly been working its way to mediocrity.

Lessons Learned : nginx and mixed HTTP/HTTPS Hosting

Stupid computers.    That is exactly how I feel after beating my head against the wall for the past week while migrating several sites to a new nginx server.  These are my crib notes so I can find the answers in one place next time I mess up the configuration.

My sites are running WordPress.  Some are using one base domain with multiple hosts.  Some of the hosts are going through a web proxy.  Others are direct.  Most of the hosts on the primary domain are going through SSL via a wildcard certificate.  It is an interesting mix that pushes the limits of my newb nginx configuration skills.

HTTP2 Is A SSL Feature

This one took a while.  It was NOT my FPM configuration for PHP.  It was not browser cache issues.    What it was, and something I learned tonight, was configuration nginx to process HTTP2 requests on port 80.   Turns out HTTP2 is ONLY available for SSL connections, aka HTTPS on port 443 in web server terms.

My issue was adding http2 to the listen directive for ALL of my sites in the nginx sites-available/* files.

The proper configuration:

If you do try to server http2 data over port 80 all kinds of weird things happen.   In Firefox I was getting downloads of DMS files.    On Safari it just refused to do anything.

The best part is when this incorrect schmutz gets cached.    By the browser, or opcache, or FPM, or even the proxy server.

Clear Your Cache

Once I fixed the HTTP2 protocol mix-up it didn’t appear to fix anything.    Safari was exceptionally good at caching bad connection data.   It has done this several times.   The fix was to find my local browser cache and clear it.   Sadly those people that visited the site while it was broken most likely will not be technical enough to know to even try clearing this so they’ll get a broken site until their cache expires.  Hopefully that’s not set to be sometime in the year 2035.

I found that Sucuri likes to cache things too.   Any sites I had running through that cloud proxy server needed to clear the cache.   It can be very good at remembering malformed data.

Lastly clear the FPM cache on the server.   Restarting FPM and nginx after any configuration changes is always a good idea.

Do Not Set Two Default Servers

Copying configuration settings between my various site configuration files became a common theme.   Make sure only ONE of your sites adds the default_server directive to the listen line.  Otherwise nginx will not start.

All SSL Sites Need A Cert

Make sure you have an SSL certificate specified for all sites that are listening to the SSL port (HTTPS port 443).   You will need a certificate that matches the server_name directive and will need to specify the path to the private key file and certificate file.

Common practice is to store the ssl configuration lines in a snippets subdirectory for nginx.   You include the proper snippet for the server_name you are configuring.   The ssl-params file is typically shared with all your SSL enabled sites.

The snippets/ssl-<domain>.conf file:

 

The CRT Is A COMBINED Certificate File

nginx needs the CA Intermediate certificate concatenated to the end of your domain-specific certificate.  Load them both on your web server then cat cat_intermediate.crt >> domain.com.crt.    If you do not do this nginx will not start after adding the above cert lines.

 

Amazon Music Recapturing Music Fans – Repeat Play

It’s the same old story, same old song and dance, my friend

– Same Old Song And Dance by Aerosmith

 

There has been a lot of media hype about the new Amazon Music service.    I get it.   I’m a HUGE Amazon fan.   I even ditched my premium Slacker account recently for Amazon’s music service included as part of my Prime subscription.    I’m not really missing all that much.    The ONLY thing I did miss, until now, was the ability to listen on demand.  That is what all this big hype is about.  Amazon did one little thing that is completely non-revolutionary.  They added on demand, the ability to find a song and listen to it at any time, to their options.    You no longer have to wait for a “station” to roll that song into your queue.

But that’s just it.    It is a big “so what” moment.  Lots of music services offer that.  In fact it is the staple feature of nearly ALL premium (aka you actually PAY for music, a novel concept) music services.  The BIGGER reason this is HUGE news is the PRICE point.   Prime members get the service for LESS than what Spotify charges.  Prime members can save $3/month, $36/year over the similar Spotify service and have the same “Big Three” catalog you get anywhere else.    Woohoo!  That IS big news.   A race to the bottom and YOU save $3/month for the all-you-can-consume music buffet.   You ALMOST saved enough for a cup of over-priced Starbucks coffee (side note: I also love Starbucks, fully realizing I overpay for my coffee every time I go there).  Even better is non-Prime Members can get the service for… well… $9.99… the same price as EVERY OTHER SERVICE OUT THERE.

The big news is Amazon has done EXACTLY what all the other music services are already doing.

Amazon will do just fine with their new music thing.   They can hide the huge losses in running the service in their ginormous (that’s a technical term) balance sheets.    They have a notable advantage in cost reduction while running the service thanks to hosting one of the largest, if not THE largest, technology presence operations on the planet.   That means that despite their big bill for licensing the actual stuff they are selling they will appear to be doing just fine with their music offering.

This is NOT OK.

The people that actually MAKE the product they are selling, the music, continue to get screwed.   It is not a viable business model to sell music with an “all you can consume” mentality and match that with FIXED FEE pricing.    It is a HUGE disservice to those people that make the music happen; the artists,songwriters, composers, and the teams of people that put this stuff together will earn exactly diddly-squat via this model.

Sure, the streaming companies will complain about their huge licensing bill.   Amazon will claim they are being fair because they paid “nearly A BILLION DOLLARS” in license fees next year.    But you don’t see them selling televisions for $100 no matter how many you buy or what size, paying the manufacturer $99 for it, and they saying “we must have paid them fairly because we paid a BILLION dollars to Sony for those TVs”.  That model wouldn’t last long when those TVs cost Sony $500 to produce.

The mentality of corporations and the general public is that music, like most intellectual and artistic property, is “free” to make.  You just “come up with it”.  There is very little “real cost”.    They do it because “it is fun”.  Or “they just love making music”.    So we feel we shouldn’t have to pay for something people make because they love their job?   The fall-back is that they’ve already “made millions”.  Samsung made a BILLION DOLLARS selling phones, so we should just be able to walk into any store and just take one.  Or Amazon should just pay Samsung what they feel is fair and let Samsung deal with it.    That type of thinking is just an excuse to justify not paying fair value for the music.  Both on a corporate and personal level.

In the music industry the people that are making the product are getting paid whatever the retail outlets and the product warehouse people think is a fair price.    The model DOESN’T WORK.   It never has and never will.

Amazon’s new service isn’t revolutionary or new in any way.  It is nothing more than the next iteration of a failed model.   At a slightly lower price if you buy something else from them.  That’s it.  Bundle-deal lower-cost pricing.

It feels more-than-ever like music has become nothing more than a loss-leader to sell something else.  A Prime Subscription at Amazon.  Some ad space for a product or TV show.   A Charlie-Harper jingle to sell kid’s cereal.

It is a sad time to be in the music business for artists, songwriters, and composers.

 


Amazon Seeks To Recapture Music Fans With New Streaming Service

http://www.npr.org/sections/therecord/2016/10/12/497674943/amazon-seeks-to-recapture-music-fans-with-new-streaming-service?ft=nprml&f=1039

 

CrashPlan Pro Headless Server Configuration on OS/X

The CrashPlan Pro documentation is incomplete and difficult-to-follow at best when it comes to configuring a new headless server using the OS/X CrashPlan Pro client. Here is the info you need to get it done quickly.

For reference, my environment is OS/X Sierra with CrashPlan Pro 4.8 accessing a new CrashPlan Pro installation on a Ubuntu 16.04 server.

Get Your UI Key

Login to your remote server where you want to configure the new CrashPlan install. This is my Ubuntu box in this case.

Go to /var/lib/crashplan.

Copy the line in the hidden .ui_info file that looks similar to this:

Configure Your OS/X Client

Close CrashPlan Pro on your local system.

Go to /Library/Application Support/CrashPlan/ on your OS/X system.

Copy .ui_info to original_ui_info.

Edit the .ui_info file and paste in the info you copied from your remote server.

Change the port (the first 4243 number) to 4200.

Open An SSH Tunnel To The Remote Server

On your OS/X box open a terminal window and connect to your remote server using an SSH session. Add the -L 4200:localhost:4243 option to any keys or other connection data.

Start CrashPlan Pro

Start CrashPlan Pro on your local OS/X system. It should ask you to login.

Make your configuration settings.

Exit CrashPlan Pro.

Go back and restore that original_ui_info file you created on your Local OS/X box.

Close your ssh tunnel.

%d bloggers like this: