Back to MySQL tuning

Running MySQL Tuner we get:

[!!] Temporary tables created on disk: 65% (640 on disk / 973 total)
Variables to adjust:
tmp_table_size (> 32M)
max_heap_table_size (> 16M)

And
[OK] InnoDB buffer pool / data size: 128.0M/1.8M
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 6.45% (528 used/ 8192 total)

I’ll continue tweaking the tmp-table size and double it, whilst I’m here we’re only using 6% of the InnoDB pool so we are pretty safe to cut it into half. Doubling the tmp-table-size should bring the 640 on disk number down by half.

Bug Fix to Minecraft Warden

After deploying the Minecraft Warden to my friend’s server we found some issues with placing it into production. I think I missed a commit->push because the same use-case was working fine on my system. Having said that, there was an issue with how the software would handle the case where the server wasn’t running and should start it up again.

Basically, the code had determined that the system should be started but wouldn’t flow to the place where the startServer() function was called. That has been fixed and has also been working just fine.

Repo Here: Warden Repo

Tire Weather Website

For some time now, I’ve been thinking about building a quick website to help my fellow enthusiasts in Canada to decide on whether or not they should install their high-specification summer tires. So far, this is what I’ll use to represent the idea/basic logic/basic design spec. I think that I’ll be doing this in C# since I am familiar with .NET but I’m pleasantly surprised at just how similar Ruby on Rails is to .NET MVC. Something tells me that it isn’t an accident ;).

ExtremeTireWeather.ca

– Threshold is 7C
– Outlook is 14 days
– Minimums are used to determine outdoor-overnight parking
– High is used to determine indoor-overnight parking

Location object
– City (String)
– Province (String)
– Country (String)

Parking Object
– Location

Weather Data Object
– City (String)
– Province (String)
– Country (String)

Days Object
– Minimum Temperature (Integer)
– Maximum Temperature (Integer)
– Rain (boolean)

Tire Object
– Class
– Lowest Temperature

UPDATE: Added Tire Object to allow support for different tire classes. Also, cleaned up some of the entities so they are more like a real UML.

Design Patterns In Java

As good review, I have started a repository to track my refresher on design patterns in Java. Keep an eye out and definitely keep an eye out for the same thing but in C. I am a C guy after all ;).

Java Repo

BSDCan coming to town and…more MySQL Tuning

The best conference in Ottawa….in my opinion! Check it out, it is very UNIXy and Linuxy: BSDCan 2016.

I see this: [!!] Temporary tables created on disk: 65% (274 on disk / 416 total)

and this: Variables to adjust:
tmp_table_size (> 16M)

And with some reading at the MySQL handbook, I agree with mysqltuner’s recommendations. Based on the number of tables that were written to disk and the system’s memory usage, we are safe to increase the tmp_table_size to 32MB. Let’s adjust that and check back in a few days! Now my tuning settings become:

# My Tuning
thread_cache_size = 4
query-cache-type = 1
query-cache-size = 16M
tmp-table-size=32M

See you in a few days!

Tuition to Wage Ratio Since 1970.

TuitionToWageRatioInteresting results after trying to actually sift through sources on news articles and reading comments on CBC news sites about inflation, generation this and that. I seriously think that this graph shows it pretty damn clearly. Here, unlike news articles without listed sources, are my sources so you can check my numbers as published by the faculties/universities themselves.

Can we put the rebuttal from a lot of the now older generations claiming that we are “spoiled?” Each generation has its challenges and the populace from each generation is divided differently with regard to income. Not everyone from the boomer generation was rich and not everyone had the same opportunities as everyone else. The same applies to modern times folks.

What do we see?

In 1970, you could work 18.75 hours/week for 4 months (summer vacation) and pay for a year of school. In 2016, you must work 136.85 hours/week for 4 months (summer vacation) to pay for ONE year of school, that’s 19.5 hours/day for those 4 months by the way. That gives you about 4.5 hours of sleep, plenty for a young teen! Spread it out over a year? Sure, that’s 45.62 hours/week for 12 months! See the issue folks? Let’s try to stop pretending that “you had it hard too” because education these days is absolutely and insanely out of reach for middle class children to pay on their own.

The goal here is to show everyone that the numbers are what they are and that it’s an issue for everyone. I think the article written by the CBC was built to hedge Canadian generations against each other and if you read the comments, that is what certainly happened.

Sources:

  1. http://www.uwo.ca/fhs/ot/about/files/history.pdf
  2. http://welcome.uwo.ca/international/finances/tuition_fees_and_living_expenses.html
  3. http://srv116.services.gc.ca/dimt-wid/sm-mw/rpt2.aspx
  4. http://srv116.services.gc.ca/dimt-wid/sm-mw/rpt3.aspx

Time to tune my WordPress database

I’ll start by inspecting the anonymous accounts listed here:

Remove Anonymous User accounts – there are 2 Anonymous accounts.
Set up a Password for user with the following SQL statement ( SET PASSWORD FOR ‘user’@’SpecificDNSorIp’ = PASSWORD(‘secure_password’); )
Set up a Secure Password for user@host ( SET PASSWORD FOR ‘user’@’SpecificDNSorIp’ = PASSWORD(‘secure_password’); )

  1. Log in to your mysql DB as root or an account with sufficient user admin privileges.
  2. Select the MySQL internal DB with: USE mysql;
  3. Display all accounts with: SELECT Host,User FROM user WHERE User=”;

SELECT Host,User FROM user WHERE User=”;
+———–+——+
| Host | User |
+———–+——+
| localhost | |
| wordpress | |
+———–+——+
2 rows in set (0.01 sec)

Ah, I see now. Let’s drop these since requiring “no user name” is a bad exposure for your database using:

mysql> DELETE FROM user WHERE User=”;
Query OK, 2 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

That’ll at least bump up security, but I also noticed that we aren’t using any caching available so let’s enable those and let MySQL “settle” for a few days before revisiting tuning again.
Variables to adjust:
query_cache_size (>= 8M)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
^
|__ Pretty fair to start and enable the caches that MySQL offers.

You can enable the query_cache an the thread_cache by using the following in the configuration file (vi /usr/local/etc/my.cnf):

# My Tuning
thread-cache-size = 4
query-cache-type = 1
query-cache-size = 16M

As per mysqltuner, we’ll start the thread cache at a small 4, the query cache type “1” caches all queries except for queries with “NO_CACHE” prepended and sets the size to 16MB (>= 8MB) as recommended by mysqltuner. I’ve restarted MySQL and so far, the output from mysqltuner is much, much better ;). See you in a few days!

Full MySQL Tuner Stats below:

pkg install mysqltuner
mysqltuner
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: >> MySQLTuner 1.6.0 – Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with ‘–help’ for additional options and output filtering
[–] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.46
[OK] Operating on 64-bit architecture

——– Storage Engine Statistics ——————————————-
[–] Status: +CSV +InnoDB +MRG_MYISAM
[–] Data in InnoDB tables: 1M (Tables: 12)
[!!] Total fragmented tables: 12

——– Security Recommendations ——————————————-
[!!] User ‘@localhost’ is an anonymous account.
[!!] User ‘@wordpress’ is an anonymous account.
[!!] User ‘@localhost’ has no password set.
[!!] User ‘@wordpress’ has no password set.
[!!] User ‘root@127.0.0.1’ has no password set.
[!!] User ‘root@::1’ has no password set.
[!!] User ‘root@wordpress’ has no password set.
[!!] User ‘@localhost’ has user name as password.
[!!] User ‘@wordpress’ has user name as password.
[!!] There is not basic password file list !

——– Performance Metrics ————————————————-
[–] Up for: 33d 15h 47m 10s (1M q [0.585 qps], 112K conn, TX: 7B, RX: 155M)
[–] Reads / Writes: 99% / 1%
[–] Binary logging is disabled
[–] Total buffers: 168.0M global + 2.8M per thread (151 max threads)
[OK] Maximum reached memory usage: 179.0M (2.36% of installed RAM)
[OK] Maximum possible memory usage: 583.2M (7.68% of installed RAM)
[OK] Slow queries: 0% (0/1M)
[OK] Highest usage of available connections: 2% (4/151)
[OK] Aborted connections: 0.00% (1/112210)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 145K sorts)
[!!] Temporary tables created on disk: 95% (74K on disk / 77K total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 87% (49 open / 56 opened)
[OK] Open file limit used: 0% (18/218K)
[OK] Table locks acquired immediately: 100% (1M immediate / 1M locks)

——– MyISAM Metrics —————————————————–
[!!] Key buffer used: 18.2% (1M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/101.0K

——– InnoDB Metrics —————————————————–
[–] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 128.0M/1.7M
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 6.45% (528 used/ 8192 total)
[OK] InnoDB Read buffer efficiency: 100.00% (27787063 hits/ 27787462 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 9647 writes)

——– AriaDB Metrics —————————————————–
[–] AriaDB is disabled.

——– Replication Metrics ————————————————-
[–] No replication slave(s) for this server.
[–] This is a standalone server..

——– Recommendations —————————————————–
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Remove Anonymous User accounts – there are 2 Anonymous accounts.
Set up a Password for user with the following SQL statement ( SET PASSWORD FOR ‘user’@’SpecificDNSorIp’ = PASSWORD(‘secure_password’); )
Set up a Secure Password for user@host ( SET PASSWORD FOR ‘user’@’SpecificDNSorIp’ = PASSWORD(‘secure_password’); )
Enable the slow query log to troubleshoot bad queries
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Set thread_cache_size to 4 as a starting value
Variables to adjust:
query_cache_size (>= 8M)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)

Testing PiggyBack Tunes on a 2015 Fiat 500 Abarth

I have finished editing a quick video that I made when I saw an opportunity to run an HPSI Unichip Stage 2 on my car (once again!). I am currently using a Seletron ChipBox and definitely enjoy it’s additional boost, however, that HPSI tune literally wants to tear your face off any chance it gets.

The thing that I take away from the ChipBox is that it is very smooth power that is smoother than most other piggyback tunes that I’ve used (TMC, RRM). The Unichip, however, is a different breed from other piggybacks and can intercept and manipulate signal voltages more than a simple +/- so you can develop your own boost maps, AFR maps and further refine your tune. Of course, the Unichip is still ultimately limited to what the ECU will allow since the Unichip is not the top authority when it comes to operating the engine, that’s the ECU’s job. The TMC and RRM piggyback boxes connect to the Boost and MAP sensors (and a cam sensor for an undisclosed reason). Though, the ChipBox unit plugs into Boost and MAP sensors it also connects to the boost solenoid and I suspect that it is to assist in reaching its target boost levels. Thus, it removes one other trick that the ECU has up its sleeve when it wants to limit boost pressure.

Here is the video: YouTube Video

Minecraft Warden: Keep your FreeBSD10 Minecraft Server Up and Running

My friend Stephane Potvin had been running a Minecraft server but was stuck with some reliability issues. I did some work for the start-up scripts but that work was lost when the port maintainer (rightfully so) decided to take a step back and fix up the port. He did a great job in my opinion and left the “start-up” process to be figured out by users.

That said, there was still a gap left. The reality is that some of things in the older port used a .lck file and that made things great and portable since you could use whatever the heck you want to wrap the service and start it up, restart it, shut it down, etc.

Having said all of that, I present to you my MinecraftWarden to keep the Minecraft application running like it should! Here is a link to my gitlab repo: MinecraftWarden. The only caveats are that we haven’t tested it on his system since I need to bring him a Gigabit switch to connect the server onto his network.