Ubuntu 16.04 LTS – Here I Come!

After catching myself up with my MCO sysadmin duties and discovering some custom code on their wordpress page, I started thinking about the systems that I’m running at home. For one, the GitLab server that I’ll be storing said custom code needs a safe place to sit.

After seeing what was what on the system, I had found that the server wasn’t even running (note to self, set up monitoring ASAP), the server could use another CPU and finally, I was running 14.04 LTS. Whew, work has been piling up for sure.

Naturally, as I make this post, there are wordpress updates too :). It’ll be a busy weekend for me!

RAM Upgrade!!!!

Did some hardware work this morning on the FreeNAS box. I’ve added another 8GB DDR3 so I’m now running with headroom (yay!) and a total of 16GB of RAM. Additionally, I swapped the SATA cable connected to my 4TB Toshiba drive (the one giving me CDMA errors) and hopefully that eliminates the SATA communication errors that have been cropping up. FYI, the reallocated sector counts were not that. They were indeed SATA read errors but the way the smartctl display output to my tiny terminal screen made me read it incorrectly.

I’ll look at fixing the automatic update feature on WP since I need to do some updating on the MCO side of things; some good security improvements are being released so I want to run them. First, my own server as a test!

FYI, check out the lack of swap usage in my screenshot (The lack of a red spot on the swap graph!):

No More Swap!
No More Swap!

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.

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!

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)

Troubleshooting WordPress Attachments

Went to make a post this morning and found that I was getting a nice little error from WordPress, posted below:WPError

Sounds like a permissions issue I think. /me makes some coffee and proceeds to troubleshoot the root cause. Let us see what is in the wordpress directory and take a look at wp-content’s permission settings.

drwxr-xr-x   4 wordpress  wordpress      5 Feb  2 12:11 wp-content/

Okay so wordpress is the owner and group, the owner has full permissions and the group has read and execute permissions.

Let’s try this, root@Wordpress:/usr/local/www/apache24/data/wordpress # chmod 775 wp-content/, same error.

Let’s try root@Wordpress:/usr/local/www/apache24/data/wordpress # chmod 777 wp-content/, voila. Hrmm, I’d be shot if I left 777 and called it a day. Let me check to see what permissions were set when the file was successfully uploaded with 777 in-place.

-rw-rw-rw-  1 www  wheel  16896 Apr  8 08:45 AbarthSuspensionForm.xls, ah ha!

Reset the permissions to 755 and change ownership via: chown www: wp-content

Check our work:

drwxr-xr-x   4 www        wordpress      5 Apr  8 08:47 wp-content/

Success!

WPSuccess

Details on the WordPress server ;)

Some details on my quaint little FreeNAS box. It has had a relatively simple life providing files over CIFS and NFS, but now it’s time to put her to work and see how far I can stretch it out! This wordpress site is running off of a jail if you haven’t noticed ;). My ZFS kit is a stripe (gasp!) and has no redundancy (yet) but for my purposes before hosting any public facing stuff, that stripe was all that I could need.

  • FreeNAS 9.3
  • AMD Sempron(tm) 3850 APU with Radeon(tm) R3
  • 8GB RAM

Hosting: 3 jails/4 Virtual Systems Total

  • transmission (jail)
  • virtualbox (jail) hosting 1 VM for GitLab (Ubuntu)
  • WordPress (jail)

More Backup Scripts (This time for wordpress)

I was able to complete my backup scripts for the WordPress site lickity split. I also found that grive in ports is broken and should be deprecated since the Google API it uses has been defunct since 2012. I had to download and compile grive2 from the interwebs (a quick search will yield success).

Here is a link to the wordpress backup script: WordPress Backup

And a link to the BSD version of the Google Drive backup script: GoogleDrive Backup Script (BSD). The BSD version points to /usr/local/bin/bash instead of /bin/bash, but that could likely be soft linked.

FYI, I pass the TARGETDIR in through the script’s args but here is an example of how my crontab looks like on the WordPress server:

0 0 * * * /home/brodey/src/Scripts/backupWordPressh.sh
30 0 * * * /home/brodey/src/Scripts/backupBSDFilesToGoogleDocs.sh /FreeNAS/WordPress

Yes, I’ll eventually have a “common place” for all of my scripts to run and better maintainability  but this is a “get something in place now” type of deal.

First things that I’m working on

Looks like I may be looking at a possible drive failure or, “normal” wear-in on one of my brand new hard drives (A 4TB Hitachi). I received a lovely error message from FreeNAS indicating that my drive error count increased from 0 to 1, so I shall be monitoring that count to see if it’s rate of increase changes.

In the mean time, and after taking manual backups of data. I decided to get some backup scripts in place so that I don’t have to continuously monitor the situation. I developed a simple script with a touch of ‘hard coding’ to get the job done for now, using a couple posts about grive and BASH.  Grive is awesome, grive -f is also equally awesome when forcing a “download” or “pull” from your Google Drive.

Here is a link to my backup script: Backup Script