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.

 

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.

#Verizon’s ‘#LTE Advanced’ #network promises 50 percent higher speed (#mobile #carriers)

Network access companies, including mobile carriers, need to focus on improving network access first. Expand both the footprint and the capacity. That is the biggest issue worldwide and remains a primary issue in America. It is one of the reasons America ranks 18th in the world NOT because the current network is too slow.

In my home town, one of the top 5 fastest growing cities in America for the past 4 years, the Verizon network ranges from horrid to OK. The network often drops connections or significantly reduces access to old-school CDMA (1/10th the speed of LTE) whenever tourists are in town – 8 months of the year. The access, tourists around or not, is non-existent or spotty-at-best all over town; in my neighborhood near the marsh, at the entrance to the public Shem Creek park, driving past the SCEG right-of-way as I drive to the entrance of my neighborhood, and several other locations around town. This is normal in a town that the infamous Verizon-Red-Is-Way-Better-Than-Sprint-Yellow Map shows fully bathed in Verizon red. In fact I am writing this from my home with one bar on 1x; far more common than LTE access around town.

The problem is that for minimal investment in adding “LTE Advanced”, whatever that made-up tech-sounding term is supposed to be, to the existing network gets Verizon a LOT more marketing bang for the buck. Fixing the network is costly. It requires physical hardware upgrades and crews to swap out equipment. It requires buying land or access rights to put up the antennas in the right places. It requires a company with a TRUE focus on serving their customers. Making up a new term that has a good marketing vibe and tweaking your existing network software however is relatively cheap for something that will get a lot of media attention. In return the customers will suffer as fewer channels are available to serve already over-crowded networks.

Marketing BS wins over true progress yet again.


Verizon’s ‘LTE Advanced’ network promises 50 percent higher speed

http://google.com/newsstand/s/CBIwy93F_iw

%d bloggers like this: