Posts Tagged ‘key’

How to filter dhcp traffic between two networks running separate DHCP servers to prevent IP assignment issues and MAC duplicate addresses

Tuesday, February 8th, 2022

how-to-filter-dhcp-traffic-2-networks-running-2-separate-dhcpd-servers-to-prevent-ip-assignment-conflicts-linux
Tracking the Problem of MAC duplicates on Linux routers
 

If you have two networks that see each other and they're not separated in VLANs but see each other sharing a common netmask lets say 255.255.254.0 or 255.255.252.0, it might happend that there are 2 dhcp servers for example (isc-dhcp-server running on 192.168.1.1 and dhcpd running on 192.168.0.1 can broadcast their services to both LANs 192.168.1.0.1/24 (netmask 255.255.255.0) and Local Net LAN 192.168.1.1/24. The result out of this is that some devices might pick up their IP address via DHCP from the wrong dhcp server.

Normally if you have a fully controlled little or middle class home or office network (10 – 15 electronic devices nodes) connecting to the LAN in a mixed moth some are connected via one of the Networks via connected Wifi to 192.168.1.0/22 others are LANned and using static IP adddresses and traffic is routed among two ISPs and each network can see the other network, there is always a possibility of things to go wrong. This is what happened to me so this is how this post was born.

The best practice from my experience so far is to define each and every computer / phone / laptop host joining the network and hence later easily monitor what is going on the network with something like iptraf-ng / nethogs  / iperf – described in prior  how to check internet spepeed from console and in check server internet connectivity speed with speedtest-cliiftop / nload or for more complex stuff wireshark or even a simple tcpdump. No matter the tools network monitoring is only part on solving network issues. A very must have thing in a controlled network infrastructure is defining every machine part of it to easily monitor later with the monitoring tools. Defining each and every host on the Hybrid computer networks makes administering the network much easier task and  tracking irregularities on time is much more likely. 

Since I have such a hybrid network here hosting a couple of XEN virtual machines with Linux, Windows 7 and Windows 10, together with Mac OS X laptops as well as MacBook Air notebooks, I have followed this route and tried to define each and every host based on its MAC address to pick it up from the correct DHCP1 server  192.168.1.1 (that is distributing IPs for Internet Provider 1 (ISP 1), that is mostly few computers attached UTP LAN cables via LiteWave LS105G Gigabit Switch as well from DHCP2 – used only to assigns IPs to servers and a a single Wi-Fi Access point configured to route incoming clients via 192.168.0.1 Linux NAT gateway server.

To filter out the unwanted IPs from the DHCPD not to propagate I've so far used a little trick to  Deny DHCP MAC Address for unwanted clients and not send IP offer for them.

To give you more understanding,  I have to clear it up I don't want to have automatic IP assignments from DHCP2 / LAN2 to DHCP1 / LAN1 because (i don't want machines on DHCP1 to end up with IP like 192.168.0.50 or DHCP2 (to have 192.168.1.80), as such a wrong IP delegation could potentially lead to MAC duplicates IP conflicts. MAC Duplicate IP wrong assignments for those older or who have been part of administrating large ISP network infrastructures  makes the network communication unstable for no apparent reason and nodes partially unreachable at times or full time …

However it seems in the 21-st century which is the century of strangeness / computer madness in the 2022, technology advanced so much that it has massively started to break up some good old well known sysadmin standards well documented in the RFCs I know of my youth, such as that every electronic equipment manufactured Vendor should have a Vendor Assigned Hardware MAC Address binded to it that will never change (after all that was the idea of MAC addresses wasn't it !). 
Many mobile devices nowadays however, in the developers attempts to make more sophisticated software and Increase Anonimity on the Net and Security, use a technique called  MAC Address randomization (mostly used by hackers / script kiddies of the early days of computers) for their Wi-Fi Net Adapter OS / driver controlled interfaces for the sake of increased security (the so called Private WiFi Addresses). If a sysadmin 10-15 years ago has seen that he might probably resign his profession and turn to farming or agriculture plant growing, but in the age of digitalization and "cloud computing", this break up of common developed network standards starts to become the 'new normal' standard.

I did not suspected there might be a MAC address oddities, since I spare very little time on administering the the network. This was so till recently when I accidently checked the arp table with:

Hypervisor:~# arp -an
192.168.1.99     5c:89:b5:f2:e8:d8      (Unknown)
192.168.1.99    00:15:3e:d3:8f:76       (Unknown)

..


and consequently did a network MAC Address ARP Scan with arp-scan (if you never used this little nifty hacker tool I warmly recommend it !!!)
If you don't have it installed it is available in debian based linuces from default repos to install

Hypervisor:~# apt-get install –yes arp-scan


It is also available on CentOS / Fedora / Redhat and other RPM distros via:

Hypervisor:~# yum install -y arp-scan

 

 

Hypervisor:~# arp-scan –interface=eth1 192.168.1.0/24

192.168.1.19    00:16:3e:0f:48:05       Xensource, Inc.
192.168.1.22    00:16:3e:04:11:1c       Xensource, Inc.
192.168.1.31    00:15:3e:bb:45:45       Xensource, Inc.
192.168.1.38    00:15:3e:59:96:8e       Xensource, Inc.
192.168.1.34    00:15:3e:d3:8f:77       Xensource, Inc.
192.168.1.60    8c:89:b5:f2:e8:d8       Micro-Star INT'L CO., LTD
192.168.1.99     5c:89:b5:f2:e8:d8      (Unknown)
192.168.1.99    00:15:3e:d3:8f:76       (Unknown)

192.168.x.91     02:a0:xx:xx:d6:64        (Unknown)
192.168.x.91     02:a0:xx:xx:d6:64        (Unknown)  (DUP: 2)

N.B. !. I found it helpful to check all available interfaces on my Linux NAT router host.

As you see the scan revealed, a whole bunch of MAC address mess duplicated MAC hanging around, destroying my network topology every now and then 
So far so good, the MAC duplicates and strangely hanging around MAC addresses issue, was solved relatively easily with enabling below set of systctl kernel variables.
 

1. Fixing Linux ARP common well known Problems through disabling arp_announce / arp_ignore / send_redirects kernel variables disablement

 

Linux answers ARP requests on wrong and unassociated interfaces per default. This leads to the following two problems:

ARP requests for the loopback alias address are answered on the HW interfaces (even if NOARP on lo0:1 is set). Since loopback aliases are required for DSR (Direct Server Return) setups this problem is very common (but easy to fix fortunately).

If the machine is connected twice to the same switch (e.g. with eth0 and eth1) eth2 may answer ARP requests for the address on eth1 and vice versa in a race condition manner (confusing almost everything).

This can be prevented by specific arp kernel settings. Take a look here for additional information about the nature of the problem (and other solutions): ARP flux.

To fix that generally (and reboot safe) we  include the following lines into

 

Hypervisor:~# cp -rpf /etc/sysctl.conf /etc/sysctl.conf_bak_07-feb-2022
Hypervisor:~# cat >> /etc/sysctl.conf

# LVS tuning
net.ipv4.conf.lo.arp_ignore=1
net.ipv4.conf.lo.arp_announce=2
net.ipv4.conf.all.arp_ignore=1
net.ipv4.conf.all.arp_announce=2

net.ipv4.conf.all.send_redirects=0
net.ipv4.conf.eth0.send_redirects=0
net.ipv4.conf.eth1.send_redirects=0
net.ipv4.conf.default.send_redirects=0

Press CTRL + D simultaneusly to Write out up-pasted vars.


To read more on Load Balancer using direct routing and on LVS and the arp problem here


2. Digging further the IP conflict / dulicate MAC Problems

Even after this arp tunings (because I do have my Hypervisor 2 LAN interfaces connected to 1 switch) did not resolved the issues and still my Wireless Connected devices via network 192.168.1.1/24 (ISP2) were randomly assigned the wrong range IPs 192.168.0.XXX/24 as well as the wrong gateway 192.168.0.1 (ISP1).
After thinking thoroughfully for hours and checking the network status with various tools and thanks to the fact that my wife has a MacBook Air that was always complaining that the IP it tried to assign from the DHCP was already taken, i"ve realized, something is wrong with DHCP assignment.
Since she owns a IPhone 10 with iOS and this two devices are from the same vendor e.g. Apple Inc. And Apple's products have been having strange DHCP assignment issues from my experience for quite some time, I've thought initially problems are caused by software on Apple's devices.
I turned to be partially right after expecting the logs of DHCP server on the Linux host (ISP1) finding that the phone of my wife takes IP in 192.168.0.XXX, insetad of IP from 192.168.1.1 (which has is a combined Nokia Router with 2.4Ghz and 5Ghz Wi-Fi and LAN router provided by ISP2 in that case Vivacom). That was really puzzling since for me it was completely logical thta the iDevices must check for DHCP address directly on the Network of the router to whom, they're connecting. Guess my suprise when I realized that instead of that the iDevices does listen to the network on a wide network range scan for any DHCPs reachable baesd on the advertised (i assume via broadcast) address traffic and try to connect and take the IP to the IP of the DHCP which responds faster !!!! Of course the Vivacom Chineese produced Nokia router responded DHCP requests and advertised much slower, than my Linux NAT gateway on ISP1 and because of that the Iphone and iOS and even freshest versions of Android devices do take the IP from the DHCP that responds faster, even if that router is not on a C class network (that's invasive isn't it??). What was even more puzzling was the automatic MAC Randomization of Wifi devices trying to connect to my ISP1 configured DHCPD and this of course trespassed any static MAC addresses filtering, I already had established there.

Anyways there was also a good think out of tthat intermixed exercise 🙂 While playing around with the Gigabit network router of vivacom I found a cozy feature SCHEDULEDING TURNING OFF and ON the WIFI ACCESS POINT  – a very useful feature to adopt, to stop wasting extra energy and lower a bit of radiation is to set a swtich off WIFI AP from 12:30 – 06:30 which are the common sleeping hours or something like that.
 

3. What is MAC Randomization and where and how it is configured across different main operating systems as of year 2022?

Depending on the operating system of your device, MAC randomization will be available either by default on most modern mobile OSes or with possibility to have it switched on:

  • Android Q: Enabled by default 
  • Android P: Available as a developer option, disabled by default
  • iOS 14: Available as a user option, disabled by default
  • Windows 10: Available as an option in two ways – random for all networks or random for a specific network

Lately I don't have much time to play around with mobile devices, and I do not my own a luxury mobile phone so, the fact this ne Androids have this MAC randomization was unknown to me just until I ended a small mess, based on my poor configured networks due to my tight time constrains nowadays.

Finding out about the new security feature of MAC Randomization, on all Android based phones (my mother's Nokia smartphone and my dad's phone, disabled the feature ASAP:


4. Disable MAC Wi-Fi Ethernet device Randomization on Android

MAC Randomization creates a random MAC address when joining a Wi-Fi network for the first time or after “forgetting” and rejoining a Wi-Fi network. It Generates a new random MAC address after 24 hours of last connection.

Disabling MAC Randomization on your devices. It is done on a per SSID basis so you can turn off the randomization, but allow it to function for hotspots outside of your home.

  1. Open the Settings app
  2. Select Network and Internet
  3. Select WiFi
  4. Connect to your home wireless network
  5. Tap the gear icon next to the current WiFi connection
  6. Select Advanced
  7. Select Privacy
  8. Select "Use device MAC"
     

5. Disabling MAC Randomization on MAC iOS, iPhone, iPad, iPod

To Disable MAC Randomization on iOS Devices:

Open the Settings on your iPhone, iPad, or iPod, then tap Wi-Fi or WLAN

 

  1. Tap the information button next to your network
  2. Turn off Private Address
  3. Re-join the network


Of course next I've collected their phone Wi-Fi adapters and made sure the included dhcp MAC deny rules in /etc/dhcp/dhcpd.conf are at place.

The effect of the MAC Randomization for my Network was terrible constant and strange issues with my routings and networks, which I always thought are caused by the openxen hypervisor Virtualization VM bugs etc.

That continued for some months now, and the weird thing was the issues always started when I tried to update my Operating system to the latest packetset, do a reboot to load up the new piece of software / libraries etc. and plus it happened very occasionally and their was no obvious reason for it.

 

6. How to completely filter dhcp traffic between two network router hosts
IP 192.168.0.1 / 192.168.1.1 to stop 2 or more configured DHCP servers
on separate networks see each other

To prevent IP mess at DHCP2 server side (which btw is ISC DHCP server, taking care for IP assignment only for the Servers on the network running on Debian 11 Linux), further on I had to filter out any DHCP UDP traffic with iptables completely.
To prevent incorrect route assignments assuming that you have 2 networks and 2 routers that are configurred to do Network Address Translation (NAT)-ing Router 1: 192.168.0.1, Router 2: 192.168.1.1.

You have to filter out UDP Protocol data on Port 67 and 68 from the respective source and destination addresses.

In firewall rules configuration files on your Linux you need to have some rules as:

# filter outgoing dhcp traffic from 192.168.1.1 to 192.168.0.1
-A INPUT -p udp -m udp –dport 67:68 -s 192.168.1.1 -d 192.168.0.1 -j DROP
-A OUTPUT -p udp -m udp –dport 67:68 -s 192.168.1.1 -d 192.168.0.1 -j DROP
-A FORWARD -p udp -m udp –dport 67:68 -s 192.168.1.1 -d 192.168.0.1 -j DROP

-A INPUT -p udp -m udp –dport 67:68 -s 192.168.0.1 -d 192.168.1.1 -j DROP
-A OUTPUT -p udp -m udp –dport 67:68 -s 192.168.0.1 -d 192.168.1.1 -j DROP
-A FORWARD -p udp -m udp –dport 67:68 -s 192.168.0.1 -d 192.168.1.1 -j DROP

-A INPUT -p udp -m udp –sport 67:68 -s 192.168.1.1 -d 192.168.0.1 -j DROP
-A OUTPUT -p udp -m udp –sport 67:68 -s 192.168.1.1 -d 192.168.0.1 -j DROP
-A FORWARD -p udp -m udp –sport 67:68 -s 192.168.1.1 -d 192.168.0.1 -j DROP


You can download also filter_dhcp_traffic.sh with above rules from here


Applying this rules, any traffic of DHCP between 2 routers is prohibited and devices from Net: 192.168.1.1-255 will no longer wrongly get assinged IP addresses from Network range: 192.168.0.1-255 as it happened to me.


7. Filter out DHCP traffic based on MAC completely on Linux with arptables

If even after disabling MAC randomization on all devices on the network, and you know physically all the connecting devices on the Network, if you still see some weird MAC addresses, originating from a wrongly configured ISP traffic router host or whatever, then it is time to just filter them out with arptables.

## drop traffic prevent mac duplicates due to vivacom and bergon placed in same network – 255.255.255.252
dchp1-server:~# arptables -A INPUT –source-mac 70:e2:83:12:44:11 -j DROP


To list arptables configured on Linux host

dchp1-server:~# arptables –list -n


If you want to be paranoid sysadmin you can implement a MAC address protection with arptables by only allowing a single set of MAC Addr / IPs and dropping the rest.

dchp1-server:~# arptables -A INPUT –source-mac 70:e2:84:13:45:11 -j ACCEPT
dchp1-server:~# arptables -A INPUT  –source-mac 70:e2:84:13:45:12 -j ACCEPT


dchp1-server:~# arptables -L –line-numbers
Chain INPUT (policy ACCEPT)
1 -j DROP –src-mac 70:e2:84:13:45:11
2 -j DROP –src-mac 70:e2:84:13:45:12

Once MACs you like are accepted you can set the INPUT chain policy to DROP as so:

dchp1-server:~# arptables -P INPUT DROP


If you later need to temporary, clean up the rules inside arptables on any filtered hosts flush all rules inside INPUT chain, like that
 

dchp1-server:~#  arptables -t INPUT -F

Zabbix rkhunter monitoring check if rootkits trojans and viruses or suspicious OS activities are detected

Wednesday, December 8th, 2021

monitor-rkhunter-with-zabbix-zabbix-rkhunter-check-logo

If you're using rkhunter to monitor for malicious activities, a binary changes, rootkits, viruses, malware, suspicious stuff and other famous security breach possible or actual issues, perhaps you have configured your machines to report to some Email.
But what if you want to have a scheduled rkhunter running on the machine and you don't want to count too much on email alerting (especially because email alerting) makes possible for emails to be tracked by sysadmin pretty late?

We have been in those situation and in this case me and my dear colleague Georgi Stoyanov developed a small rkhunter Zabbix userparameter check to track and Alert if any traces of "Warning"''s are mateched in the traditional rkhunter log file /var/log/rkhunter/rkhunter.log

To set it up and use it is pretty use you will need to have a recent version of zabbix-agent installed on the machine and connected to a Zabbix server, in my case this is:

[root@centos ~]# rpm -qa |grep -i zabbix-agent
zabbix-agent-4.0.7-1.el7.x86_64

 placed inside /etc/zabbix/zabbix_agentd.d/userparameter_rkhunter_warning_check.conf

[root@centos /etc/zabbix/zabbix_agentd.d ]# cat userparameter_rkhunter_warning_check.conf
# userparameter script to check if any Warning is inside /var/log/rkhunter/rkhunter.log and if found to trigger Zabbix alert
UserParameter=rkhunter.warning, (TODAY=$(date |awk '{ print $1" "$2" "$3 }'); if [ $(cat /var/log/rkhunter/rkhunter.log | awk “/$TODAY/,EOF” | /bin/grep -i ‘\[ Warning \]’ | /usr/bin/wc -l) != ‘0’ ]; then echo 1; else echo 0; fi)
UserParameter=rkhunter.suspected,(/bin/grep -i 'Suspect files: ' /var/log/rkhunter/rkhunter.log|tail -n 1| awk '{ print $4 }')
UserParameter=rkhunter.rootkits,(/bin/grep -i 'Possible rootkits: ' /var/log/rkhunter/rkhunter.log|tail -n 1| awk '{ print $4 }')


2. Prepare Rkhunter Template, Triggers and Items


In Zabbix Server that you access from web control interface, you will have to prepare a new template called lets say Rkhunter with the necessery Triggers and Items


2.1 Create Rkhunter Items
 

On Zabbix Server side, uou will have to configure 3 Items for the 3 configured userparameter above script keys, like so:

rkhunter-items-zabbix-screenshot.png

  • rkhunter.suspected Item configuration


rkhunter-suspected-files

  • rkhunter.warning Zabbix Item config

rkhunter-warning-found-check-zabbix

  • rkhunter.rootkits Zabbix Item config

     

     

    rkhunter-zabbix-possible-rootkits-item1

    2.2 Create Triggers
     

You need to have an overall of 3 triggers like in below shot:

zabbix-rkhunter-all-triggers-screenshot
 

  • rkhunter.rootkits Trigger config

rkhunter-rootkits-trigger-zabbix1

  • rkhunter.suspected Trigger cfg

rkhunter-suspected-trigger-zabbix1

  • rkhunter warning Trigger cfg

rkhunter-warning-trigger1

3. Reload zabbix-agent and test the keys


It is necessery to reload zabbix agent for the new userparameter to start to be sent to remote zabbix server (through a proxy if you have one configured).

[root@centos ~]# systemctl restart zabbix-agent


To make the zabbix-agent send the keys to the server you can use zabbix_sender to have the test tool you will have to have installed (zabbix-sender) on the server.

To trigger a manualTest if you happen to have some problems with the key which shouldn''t be the case you can sent a value to the respectve key with below command:

[root@centos ~ ]# zabbix_sender -vv -c "/etc/zabbix/zabbix_agentd.conf" -k "khunter.warning" -o "1"


Check on Zabbix Server the sent value is received, for any oddities as usual check what is inside  /var/log/zabbix/zabbix_agentd.log for any errors or warnings.

Short SSL generate new and self-signed certificates PEM, view and convert to and from PKCS12 to java key store cookbook commands cheat sheet

Tuesday, January 12th, 2021

OpenSSL-logo

Below is a short compilation of common used openssl commands (a kind of cookbook) helpful for sysadmins who has to commonly deal with OpenSSL certificates.

Lets say you have to generate new certificate / key and a PEM files, prepare self-signed certificates, show CSR / PEM or KEY ssl file contents, get information about certificate such as expiry date a type of encryption algorithm or sign certificate with self-signed authority convert PEM to PKCS12, convert from PKCS12 file format to .PEM, convert java X509 to java key store SSL encryptionor convert java key store format certificate to PKCS12, then below will be of use to you.

1. Generate Private RSA Key with 2048 bits

# openssl genrsa -out $ (hostname -f) .key 2048

2. Create CSR file

# openssl req -new -key $ (hostname -f) .key -out $ (hostname -f) .csr

3. Create a Self Certified Certificate:

# openssl x509 -req -days 30 -in $ (hostname -f) .csr -signkey $ (hostname -f) .key -out $ (hostname -f) .crt
Enter password:

# openssl rsa -in key.pem -out newkey.pem


4. Show CSR file content

# openssl req -in newcsr.csr -noout -text


5. Get Certificate version / serial number / signature algorithm / RSA key lenght / modulus / exponent etc.

# openssl x509 -in newcert.pem -noout -text


6. Server certificate as CA self signeded

# openssl ca -in newcert.csr -notext -out newcert.pem


7. Generate a certificate signing request based on an existing certificate

# openssl x509 -x509toreq -in certificate.crt -out CSR.csr -signkey privateKey.key


8. Convert .pem / .key / .crt file format to pkcs12 format
 

# openssl pkcs12 -export -in newcert.pem -inkey newkey.key -certfile ca.crt -out newcert.p12


9. Convert pkcs12 pfx to common .pem

# openssl pkcs12 -in mycert.pfx -out mycert.pem


10. The Formats available

# openssl x509 -inform the -in certificate.cer -out certificate.crt


11. Convert a pkcs # 7 certificate into PEM format

# openssl pkcs7 -in cert.p7c -inform DER -outform PEM -out certificate.p7b
# openssl pkcs7 -print_certs -in certificate.p7b -out certificate.pem


12. Convert X509 to java keystore file

# java -cp not-yet-commons-ssl-0.3.11.jar org.apache.commons.ssl.KeyStoreBuilder pass_for_new_keystore key.key certificate.crt

13. Convert java keystore file to pkcs12

# keytool -importkeystore -srckeystore keystore.jks -destkeystore intermediate.p12 -deststoretype PKCS12

12 Lessons Steve Jobs Taught Guy Kawasaki – SEO Summit Guy Kawasaki speech

Monday, July 30th, 2012

I'm not a big fan of Steve Jobs, neither I like the cult that is nowdays to his personality. After his recent death the cult to Jobs and his works has bloomed once again. From philosophical point of view I don't like Jobs ideas that there is no good and bad but only success matters. However I should admit as a SEO and as a business enterpreneur his achievements are significant. Hence I decided to share with you a video of SEO Summit ex-employee Guy Kawasaki, who "worked for jobs twice and survived". Jobs is famous for not being loved too much by his employees. Also it is not a secret historically he screw up, Steve Wozniak and a number of other people who were either employeed or worked in any way with him.

The points his ex-employee KAWASAKI shares on this SEO Summit presentation are quite interesting and are things, beginning business enterpreneurs like me could learn tremendously from. One key point that is hardly underlined in the presentation is the importance of simplicity.

* Simplicity in everything is essential for the success. It was quite curious to me Steve Jobs presentations were consisting often with a slides of just one word. Obviously this means Jobs was a simplicity freak.

Some of other concepts of Jobs was:
 

Either it works or it doesn't work.

You see again his trend to be simplifying things. In business we all know the SMART (Keep it Simple And Stupid). It seems Jobs SMART was only SAS (Simple and Stupid) 🙂 ….

Some other things Kawasaki learned from Jobs was:
 

Never believe Experts

– If someone tells you he is an expert in something he is definitely not ….

Another belief (understanding) of Jobs and probably many other succesful enterpreneurs is that DESIGN COUNTS. Design is one of the most crucial points in any product, so one has to be extra-careful here. A fail in design is a fail in product line ….

There is plenty of other interesting stuff in the video but the key point is SIMPLICITY. Enjoy Kawasaki Speech …

12 Lessons Steve Jobs Taught Guy Kawasaki

 

Don’t revoke GoDaddy SSL certificate. (Expired) Revoked SSL is impossible to revert

Thursday, April 12th, 2012

godaddy_logo
One of our company SSL (https) Certificates recently expired so I needed to renew the SSL certificate.

I was in a hurry doing plenty of other stuffs so it seemed logical for me to Revoke the Certificate. I thought revoking the certificate will simply cancel it and afterwards, in Godaddy's SSL (Manager Certificates) interface the Revoked – Cancelled certificate will re-appear in the menu, ready to be generated in the same way as earlier I initially generated the Godaddy's bought SSL certificate

Hence I proceeded and used Revoke button:

Godaddy SSL certificate manager browser certificate Screenshot

Well guess what my calculations, were wrong.
 Revoking, just cancel it. The  revoked domain SSL certificate did not show up again in Godaddy's Cert Manager and I have no way from their interface to revert the changes.

To deal with the situation, I contacted Godaddy Support immediately with the following inquiry:
 

Other : Revoked SSL Certificate
Issue :
Hello we have revoked the SSL certificate for our domain our.domain-name.com.
 Can we revert back the certificate as it was.
If not how to generate a new key for our domain https://our.domain-name.com
Thanks in Advance.
Kindest Regards

"My-Company-name" Tech Support

In 5 hours time I received the following tech support answer:
 

Dear Tech Support,

Thank you for contacting Online Support. It is not possible to reinstate a canceled certificate. You will need to purchase a new certificate. I have requested that a refund be applied to your account. Once the credit appears in your account, please allow 5-7 business days to see the funds applied to the associated payment method. Thank you for your patience and understanding in this matter.

Please let us know if we can help you in any other way.

Sincerely,

Christian P.
Online Support Team
Customer Inquiry
Name : Cadia Tech Support
Domain Name : our.domain-name.com
ShopperID : xxxxxxxxx
Phone : xxxxxxxx
Shopper Validated : Yes
Browser : Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.3) Gecko/20100401 Firefox/3.6.3

Apparently Godaddy, can work out a bit on their tech support answering time 5 hours for a simple reply is quite long.

Now taking in consideration, above reply from Godady, my only options are to either wait for 5 to 7 (business days) or buy a new credit for SSL certificate.

Buying a new credit will probably not happen as our company is experiencing some financial troubles because of the crisis. So I guess we will have to wait for this 7 days at worst. So again if you wonder to REVOKE or not an SSL certificate. Think again …

Just a small note to make here, that Godaddy has a very straight forward way to just renew an expered certificate, which I succesfully later have done for 4 domains. Well, if only I knew earlier what REVOKE SSL cert really does I wouldn't have ended in this mess …

How to renew self signed QMAIL toaster and QMAIL rocks expired SSL pem certificate

Friday, September 2nd, 2011

qmail_toaster_logo-fix-qmail-rocks-expired-ssl-pem-certificate

One of the QMAIL server installs, I have installed very long time ago. I've been notified by clients, that the certificate of the mail server has expired and therefore I had to quickly renew the certificate.

This qmail installation, SSL certificates were located in /var/qmail/control under the names servercert.key and cervercert.pem

Renewing the certificates with a new self signed ones is pretty straight forward, to renew them I had to issue the following commands:

1. Generate servercert encoded key with 1024 bit encoding

debian:~# cd /var/qmail/control
debian:/var/qmail/control# openssl genrsa -des3 -out servercert.key.enc 1024
Generating RSA private key, 1024 bit long modulus
...........++++++
.........++++++
e is 65537 (0x10001)
Enter pass phrase for servercert.key.enc:
Verifying - Enter pass phrase for servercert.key.enc:

In the Enter pass phrase for servercert.key.enc I typed twice my encoded key password, any password is good, here though using a stronger one is better.

2. Generate the servercert.key file

debian:/var/qmail/control# openssl rsa -in servercert.key.enc -out servercert.key
Enter pass phrase for servercert.key.enc:
writing RSA key

3. Generate the certificate request

debian:/var/qmail/control# openssl req -new -key servercert.key -out servercert.csr
debian:/var/qmail/control# openssl rsa -in servercert.key.enc -out servercert.key
Enter pass phrase for servercert.key.enc:writing RSA key
root@soccerfame:/var/qmail/control# openssl req -new -key servercert.key -out servercert.csr
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:UK
State or Province Name (full name) [Some-State]:London
Locality Name (eg, city) []:London
Organization Name (eg, company) [Internet Widgits Pty Ltd]:My Company
Organizational Unit Name (eg, section) []:My Org
Common Name (eg, YOUR name) []:
Email Address []:admin@adminmail.com

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

In the above prompts its necessery to fill in the company name and location, as each of the prompts clearly states.

4. Sign the just generated certificate request

debian:/var/qmail/control# openssl x509 -req -days 9999 -in servercert.csr -signkey servercert.key -out servercert.crt

Notice the option -days 9999 this option instructs the newly generated self signed certificate to be valid for 9999 days which is quite a long time, the reason why the previous generated self signed certificate expired was that it was built for only 365 days

5. Fix the newly generated servercert.pem permissions debian:~# cd /var/qmail/control
debian:/var/qmail/control# chmod 640 servercert.pem
debian:/var/qmail/control# chown vpopmail:vchkpw servercert.pem
debian:/var/qmail/control# cp -f servercert.pem clientcert.pem
debian:/var/qmail/control# chown root:qmail clientcert.pem
debian:/var/qmail/control# chmod 640 clientcert.pem

Finally to load the new certificate, restart of qmail is required:

6. Restart qmail server

debian:/var/qmail/control# qmailctl restart
Restarting qmail:
* Stopping qmail-smtpd.
* Sending qmail-send SIGTERM and restarting.
* Restarting qmail-smtpd.

Test the newly installed certificate

To test the newly installed SSL certificate use the following commands:

debian:~# openssl s_client -crlf -connect localhost:465 -quiet
depth=0 /C=UK/ST=London/L=London/O=My Org/OU=My Company/emailAddress=admin@adminmail.com
verify error:num=18:self signed certificate
verify return:1
...
debian:~# openssl s_client -starttls smtp -crlf -connect localhost:25 -quiet
depth=0 /C=UK/ST=London/L=London/O=My Org/OU=My Company/emailAddress=admin@adminmail.com
verify error:num=18:self signed certificate
verify return:1
250 AUTH LOGIN PLAIN CRAM-MD5
...

If an error is returned like 32943:error:140770FC:SSL routines:SSL23_GET_SERVER_HELLO:unknown protocol:s23_clnt.c:607: this means that SSL variable in the qmail-smtpdssl/run script is set to 0.

To solve this error, change SSL=0 to SSL=1 in /var/qmail/supervise/qmail-smtpdssl/run and do qmailctl restart

The error verify return:1 displayed is perfectly fine and it's more of a warning than an error as it just reports the certificate is self signed.

Howto install GeoTrust RapidSSL certificate on Debian Lenny / Squeeze / Wheezy Linux

Thursday, March 25th, 2010

I faced the task of generating official Validated SSL Certificates by in mydaily duties as a System Administrator at cadiaholding.com . Though generating self-signedSSL certificate is comparatively easy task. It was a pain in the ass setting Apache version 2.2.9-10+lenny6to correctly serve pages through https:// protocol over openssl version 0.9.8g-15+lenny6.I’ll try to go through the whole process of Generating the certificate in order to help some other Debianusers out there to face less setbacks in such a simple task as installing a Trusted SSL Certificate issued(bought) by RapidSSL. Even though this article will mostly deal with SSL certificate issued by RapidSSL,it should be not a problem to apply this methodogy with Verisign or some of the other Geotrust issuedSecure Socket Layer certificates.

In generating the Validated certficate I used enom which is a domain name,ssl certificates, email and hosting company whole-saler.
Fron emon’s website after logging in and using the web interface, there are two major things required to fill inin order to issue your Trusted SSL certificate.

1. Fill in in a form a CSR file, this is usually generated on the Linux server using the openssl.
To issue the CSR file required by Enom use the following commands:

a. First we generate an DES3 RSA encrypted key which we will use next to generate the opeensl CSR file required by ENOM.
debian:~# /usr/bin/openssl genrsa -des3 -out www.domain.com.key 2048
Enter pass phrase for www.domain.com.key:

You’ll be required to fill in a pass-phrase that will be later be required to fill in before Apache servers starts or restarts,so make sure you fill something you either remember or you keep the password stored in a file.
You have to change also the www.domain.com.key in accordance with your domain name.
Now as we already have a proper generated DES3 RSA key afterwards it’s necessery to generate the CSR file with the openssl command line frontend.
So here is how:

debian:~# /usr/bin/openssl req -new -key /home/hipo/www.domain.com.key -out /home/hipo/www.domain.com.csr

Again in the above example change all the paths and file names as you wish.
It’s necessery that the end user fill in a number of questions related to the Certificate Signing Request.
Herein I’ll list what kind of prompts will emerge after executing the above command:

Enter pass phrase for /home/hipo/www.domain.com.key:
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, YOUR name) []:
Email Address []:
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

Note that you’ll hav eto fill in the pass phrase previously entered during the generation of the www.domain.com.key file.
In case if you’d like to read more thoroughly on the subject of howto create a Certificate Signing Request or (CSR) as we called it on multiple times, you can read About Certificate Signing Request (CSR) Generation Instructions – Apache SSL more in depth here

2. Hopefully following the above instructions you’ll now have a file named www.domain.csrJust open the www.domain.scr and copy paste it’s content to the ENOM website CSR * webform.

3. Further on select your Webserver type on Enom’s website:In our case we have to select Apache + ApacheSSL

4. What follows next is filling in your company contact information This is also required for proper certificate generation, you have to think twice before you fill in this data, take a note this can’t be changed later on without issuing a brand SSL new certificate.

Apart from the 3 major above requirements to fill in Enom there are some few more radio buttons to use to make some selections according to your personal preferences, however I won’t take time to dig in that and I’ll leave this to you.
After all the above is fulfilled you’ll have to submit your certificate details and choose an email address to which you will receive in a minute a RapidSSL Certificate Request Confirmation

Following a link from the email, will show you some basic information about the certificate about to be generated. That’s your final chance to cancel the issued Trusted Certificated.
If you’re absolutely sure the information about to enter the certificate is correct then you’ll have to follow a link and approve the certificate.

You’ll be informed that you’ll receive your certificate either through Certifier website (e.g. Enom’s website) or via another email.
I thought it’s more probable I receive it via email but anyways I was wrong. More thank 4 hours has passed since the certificate was issued and is available via Enom’s interface but I haven’t received nothing on my mail.
Therefore my friendly advice is to check about your brand new shiny Trusted Certificate on Emom’s website. I had mine ready in about 10 minutes after the CSR was issued.

Assuming that you’ve succesfully obtained the SSL Trusted certificate from RapidSSL what follows is setting up the certificate.
Initially I tried using documentation from RapidSSL website called Installing your SSL Certificate / Web Server Certificate / Secure Server Certificate from RapidSSL.com
I tried to configure one of my Virtualhost as shown in their example inserting in my /etc/apache/sites-available/www.domain.com file, few directives within the VirtualHost something like the shown below

SSLEngine on
# domain.com.crt cointains the Trusted SSL certificate generated and obtained by you from RapidSSL
SSLCertificateFile /etc/apache2/ssl/www.domain.com.crt
# www.domain.com.key contains the file used to generate the CSR file as described earlier in this post
SSLCertificateKeyFile /etc/apache2/ssl/www.domain.com.key

It is also possible insetad of using the SSLCertificateFile and SSLCertificateKeyFile directvies directives in Apache config to use:

 

Another alternative is to use

SSLCertificateFile /etc/ssl/certs/your-domain-name.crt
SSLCertificateKeyFile /etc/ssl/certs/your-domain-name.key
SSLCACertificateFile /etc/ssl/certs/gd_bundle.crt

The key file is the one generated earlier on the server and handed to the SSL regisrar, the files:

your-domain-name.crt and gd_bundle.crt files are provided by RapidSSL or from whatever SSL registrater the SSL was purchased.

After trying the above configuration and restarting apache with:

/etc/init.d/apache2 restart

Apache failed to start, it might be helpful to somebody out there the error I had in my apache error.log:
The error.log red the following:

[warn] RSA server certificate is a CA certificate (BasicConstraints: CA == TRUE !?)

After some 30 minutes or an hour of Googling on the error I came to the conclusion that the error is caused, becauseApache is supposed to work with .PEM files instead of the classical .CRT and .KEY files asnormally approached in most of the other Unix operating systems.

It took me a bit more of reading on the internet to find out that actually the .pem files so widely adopted in Debian simply contain both the www.domain.com.key file and the www.domain.com.crt key simply pasted one after another, this I also observed from the default Apache self-signed certificate that I believe comes with debian /etc/apache2/ssl/apache.pem .
So I copied both the content of my www.domain.com.key and www.domain.com.crt and store it in one file:
/etc/apache2/ssl/www.domain.com.pem

Also the following configuration:
SSLEngine on
SSLCertificateFile /etc/apache2/ssl/www.domain.com.pem

had to go in your
/etc/apache2/sites-enabled/www.domain.com

Last thing that’s left is to restart your Apache;

/etc/init.d/apache2 restart

Apache will prompt you for your certificate password entered by you during the www.domain.com.key generation. Type your password and with a bit of luck and hopefully with God’s help you’ll be having a Trusted Certificate on your webserver.

Last step is to check if the certificate is okay accessing your domain https://www.domain.com.

Well this is the end of the article, hope you enjoy.If you do please leave your comments, any corrections are also welcomed 🙂

WordPress Plugins to monitor and debug WP enabled plugins – Find Errors / Warnings and Remove WP problematic plugins slowing down your Website (blog) database

Thursday, February 19th, 2015

plugins-to-monitor-debug-wordpress-enabled-plugins-how-to-track-find-errors-and-warnings-and-remove-problematic-wp-extensions-that-slow-down-your-website

Recent days, I'm spending a lot of time again trying to optimize my wordpress blog. Optimizing WP for better efficiency is becoming harder and harder task day by day as the website file content data is growing along with SQL databases. Moreover situation gets even worse because the number of plugins enabled on my blog is incrementally growing with time because, there is more and more goodies I'd like to add.
Optimizing WordPress to run for Speed on a server is a whole a lot of art and its a small universe in itself, because as of time of writting this post the count (number) of WordPress available PLUGINS is 36,197 ! 

1. Manually Tracking WordPress  Plugins causing Slow SQL Queries (MySQL bottleneck) issues directly using console / SSH

Because of its open source development and its nice modular design wordpress has turned into a standard for building small, middle sized and large websites (some WordPress based blogs and sites have from 50 000 to 100 000 unique pages!). My blog is still a small WordPress site with only 1676 posts, so I still haven't reached the high volume traffic optimization requirements but still even though I have a relatively good server hardware  8GB RAM / (2×2.70 Ghz Intel CPU) / 500 GB (7400 RPM HDD) at times I see Apache Webservers is unable to properly serve coming requests because of MySQL database (LEFT JOIN) requests being slow to serve (taking up to few seconds to complete) and creating a MySQL table lock, putting all the rest SQL queries to stay in a long unserved queues line, I've realized about this performance issue by using a a mysql cli (command) client and few commands and console command (tool) called mytop (also known as mtop). MyTop refreshes every 3 seconds, so the slow query will immediately stay on screen to view moer info about it press "f" and type the  in query ID.

mysql-top-running-on-gnu-linux-server-tracking-sql-queries-in-console-screenshot.png

mysql-top-running-on-gnu-linux-server-tracking-sql-queries-in-console-screenshot2

Finally it is very useful to run  for a while MySQL server logging to /var/log/mysql/slow-query.log:
Slow query is enabled (on my Debian 7 Wheezy host) by adding to /etc/mysql/my.cnf
after conf section

 

vim /etc/mysql/my.cnf
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# Here you can see queries with especially long duration

 

Paste:

 

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2
log-queries-not-using-indexes

 

And then to make new mysql configuration load restarted mysql server:

 

debian-server:~# /etc/init.d/mysql restart
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld ..
Checking for tables which need an upgrade, are corrupt or were
not closed cleanly..

 

Leaving mysql-slow.log to be enabled for 30 minutes to an 1 hrs is a good time to track most problematic slow queries and based on this queries, I took parts of  SQL UPDATE / SELECT / INSERT etc. Db queries which was problematic and grepped throughout /var/www/blog/wp-content/plugin files in order to determine which WordPress Plugin is triggering the slow query, causing blog to hang when too many clients try to see it in browser.

My main problematic SQL query having long execution time  (about 2 to 3 seconds!!!) most commonly occuring in slow-query.log was:

 

SELECT DISTINCT post_title, ID, post_type, post_name FROM wp_posts wposts LEFT JOIN wp_postmeta wpostmeta ON wposts.ID = wpostmeta.post_id LEFT JOIN wp_term_relationships ON (wposts.ID = wp_term_relationships.object_id) LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) WHERE (post_type='page' OR (wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN(11))) AND post_status = 'publish' AND LENGTH(post_title)>=5 ORDER BY LENGTH(post_title) ASC LIMIT 500

Because above query uses SQL Column names and Tables which are not hard coded in PHP code, to find out which plugins is most probably to launch this complex LEFT JOIN query, I used a quick bash one-liner:

 

# cd /var/www/blog/wp-content/plugins

 

# for i in $(grep -rli 'SELECT DISTINCT' *); do grep -rli 'LEFT JOIN' $i; done 
./seo-automatic-links/seo-links.php
./wp-postviews/wp-postviews.php
./yet-another-related-posts-plugin/classes/YARPP_Cache_Tables.php

 

I wanted to put less load on CPU during grep so looked for string only in .PHP extensioned files with:

 

 # for i in $(find . -iname '*.php' -exec grep -rli 'SELECT DISTINCT' '{}' \;); do grep -rli 'LEFT JOIN' $i; done
./seo-automatic-links/seo-links.php
./wp-postviews/wp-postviews.php
./yet-another-related-posts-plugin/classes/YARPP_Cache_Tables.php


As you can see the complex query is being called from PHP file belonging to one of 3 plugins

  • SEO Automatic Links – this is SEO Smart Links WP plugin (Does internal bliog interlinking in order to boast SEA)
  • WP PostViews – WordPress Post Views plugin (Which allows me to show how many times an article was read in WP Widget menu)
  • Yet Another Related Posts – Which is WP plugin I installed / enabled to show Related posts down on each blog post


2. Basic way to optimize MySQL slow queries (EXPLAIN / SHOW CREATE TABLE)

Now as I have a basic clue on plugins locking my Database, I disabled them one by one while keeping enabled mysql slow query log and viewing queries in mytop and I figure out that actually all of the plugins were causing a short time overheat (lock) on server Database because of LEFT JOINs. Though I really like what this plugins are doing, as they boast SEO and attract prefer to disable them for now and have my blog all the time responsible light fast instead of having a little bit better Search Engine Optimization (Ranking) and loosing many of my visitors because they're annoyed to wait until my articles open

Before disabling I tried to optimize the queries using MySQL EXPLAIN command + SHOW CREATE TABLE (2 commands often used to debug slow SQL queries and find out whether a Column needs to have added INDEX-ing to boast MySQL query).

Just in case if you decide to give them a try here is example on how they're used to debug problematic SQL query:
 

  1. mysql> explain SELECT DISTINCT post_title, ID, post_type, post_name
  2.     -> FROM wp_posts wposts LEFT JOIN wp_postmeta wpostmeta
  3.     -> ON wposts.ID = wpostmeta.post_id LEFT JOIN wp_term_relationships
  4.     -> ON (wposts.ID = wp_term_relationships.object_id) LEFT JOIN wp_term_taxonomy
  5.     -> ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
  6.     -> WHERE (post_type='page'
  7.     -> OR (wp_term_taxonomy.taxonomy = 'category'
  8.     -> AND wp_term_taxonomy.term_id IN(11,15,17)))
  9.     -> AND post_status = 'publish'
  10.     -> AND LENGTH(post_title)>=5
  11.     -> ORDER BY LENGTH(post_title) ASC
  12.     -> LIMIT 500;
  13. +—-+————-+———————–+——–+——————+———+———+———————————————+——+———————————————-+
  14. | id | select_type | table                 | type   | possible_keys    | key     | key_len | ref                                         | rows | Extra                                        |
  15. +—-+————-+———————–+——–+——————+———+———+———————————————+——+———————————————-+
  16. |  1 | SIMPLE      | wposts                | ALL    | type_status_date | NULL    | NULL    | NULL                                        | 1715 | Using where; Using temporary; Using filesort |
  17. |  1 | SIMPLE      | wpostmeta             | ref    | post_id          | post_id | 8       | blog.wposts.ID                              |   11 | Using index; Distinct                        |
  18. |  1 | SIMPLE      | wp_term_relationships | ref    | PRIMARY          | PRIMARY | 8       | blog.wposts.ID                              |   19 | Using index; Distinct                        |
  19. |  1 | SIMPLE      | wp_term_taxonomy      | eq_ref | PRIMARY          | PRIMARY | 8       | blog.wp_term_relationships.term_taxonomy_id |    1 | Using where; Distinct                        |
  20. +—-+————-+———————–+——–+——————+———+———+———————————————+——+———————————————-+
  21. 4 rows in set (0.02 sec)
  22.  
  23. mysql>
  24.  

     

     

  1. mysql> show create table wp_posts;
  2. +———-+————————–+
  3. | Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                 |
  4. +———-+————————–+
  5. | wp_posts | CREATE TABLE `wp_posts` (
  6.   `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  7.   `post_author` bigint(20) unsigned NOT NULL DEFAULT '0',
  8.   `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  9.   `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  10.   `post_content` longtext NOT NULL,
  11.   `post_title` text NOT NULL,
  12.   `post_excerpt` text NOT NULL,
  13.   `post_status` varchar(20) NOT NULL DEFAULT 'publish',
  14.   `comment_status` varchar(20) NOT NULL DEFAULT 'open',
  15.   `ping_status` varchar(20) NOT NULL DEFAULT 'open',
  16.   `post_password` varchar(20) NOT NULL DEFAULT '',
  17.   `post_name` varchar(200) NOT NULL DEFAULT '',
  18.   `to_ping` text NOT NULL,
  19.   `pinged` text NOT NULL,
  20.   `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  21.   `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  22.   `post_content_filtered` longtext NOT NULL,
  23.   `post_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
  24.   `guid` varchar(255) NOT NULL DEFAULT '',
  25.   `menu_order` int(11) NOT NULL DEFAULT '0',
  26.   `post_type` varchar(20) NOT NULL DEFAULT 'post',
  27.   `post_mime_type` varchar(100) NOT NULL DEFAULT '',
  28.   `comment_count` bigint(20) NOT NULL DEFAULT '0',
  29.   PRIMARY KEY (`ID`),
  30.   KEY `post_name` (`post_name`),
  31.   KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
  32.   KEY `post_parent` (`post_parent`),
  33.   KEY `post_author` (`post_author`),
  34.   FULLTEXT KEY `post_related` (`post_title`,`post_content`)
  35. ) ENGINE=MyISAM AUTO_INCREMENT=12033 DEFAULT CHARSET=utf8 |
  36. +———-+———————-+
  37. 1 row in set (0.00 sec)
  38.  
  39. mysql>
  40.  


By the way above output is a paste from the the new PasteBin Open Source (Stikked powered) service I started on www.pc-freak.net – paste.www.pc-freak.net (p.www.pc-freak.net) 🙂

Before I took final decision to disable slow WP plugins, I've experimented a bit trying to add INDEX to Table Column (wposts) in hope that this would speed up SQL queries with:

 

mysql> ALTER TABLE TABLE_NAME ADD INDEX (wposts);

 

But this didn't improve query speed even on the contrary it make execution time worse.

3. Tracking WordPress Plugin PHP Code Execution time and Plugins causing Slow SQL Queries (MySQL bottleneck) issues through WP itself

Well fine, I'm running my own hosted Blog and WordPress sites, but for people who have wordpress sites on shared hosting, there is usually no SSH (Terminal) Access to server, those people will be happy to hear there are 2 Free easy installable WordPress plugins which can be used to Debug Slow WordPress Plugins SQL Queries as well as plugin to Track which plugin takes most time to execute, this are:
 

 

a) P3 Plugin Performance Profiler  

runs a scan over your site to determine what resources your plugins are using, and when, during a standard page request. P3 PPP Can even create reports in a beatiful Excel like Pie chart sheet.

p3-plugin-performance-profiler-godaddy-screenshot-debian-gnu-linux-wordpress-website

Another useful thing to see with P3 PPP is Detailed Timeline it shows when the plugins are being loaded during new page request so you can see if there is a certain sequence in time when a plugin slows down the website.

detailed_timeline-wordpress-p3-performance-plugin-on-website-screenshot

The pictures says it all as P3 PPP is Godaddy's work, congrats to GoDaddy, they've done great job.

 

b) WordPress memory Viewer WP plugins

Is useful to check how much memory each of WordPress plugin is taking on user (visitor) request.
Memory Viewer is allows you to view WordPress’ memory utilization at several hooks during WordPress’ execution. It also shows a summary of MySQL Queries that have ran as well as CPU time.
To use it download it to plugins/ folder as usual enable it from:

Installed Plugins -> (Inactive) -> Memory Viewer (Enable)

To see statistics from Memory Viewer open any post from your blog website and scroll down to the bottom you will notice the statistics, showing up there, like on below screenshot.

wordpress-memory-viewer-plugin-debian-gnu-linux-hosted-website-show-which-plugin-component-eats-most-memory-in-wordprses-blog
 

Though WP Memory Viewer is said to work only up to WP version 3.2.1, I've tested it and it works fine on my latest stable WordPress 4.1 based blog.

c) WordPress Query Monitor

wordpress-query-monitor-plugin-to-monitor-track-and-optimize-problems-with-sql-caused-by-wp-plugins.png
 

Query Monitor is a debugging plugin for anyone developing with WordPress but also very helpful for anyone who want to track issues with plugins who use the database unefficient.
It has some advanced features not available in other debugging plugins, including automatic AJAX debugging and the ability to narrow down things by plugin or theme.
You can view plenty of precious statistics on how enabled plugins query the database server, here is a short overview on its Database Queries capabilities:

  • Shows all database queries performed on the current page
  • Shows affected rows and time for all queries
  • Show notifications for slow queries and queries with errors
  • Filter queries by query type (SELECT, UPDATE, DELETE, etc)
  • Filter queries by component (WordPress core, Plugin X, Plugin Y, theme)
  • Filter queries by calling function
  • View aggregate query information grouped by component, calling function, and type
  • Super advanced: Supports multiple instances of wpdb on one page
  • Once enabled from Plugins you will see it appear as a new menu on bottom Admin raw.

An important note to make here is latest Query Monitor extension fails when loaded on current latest Wordpress 4.1, to use it you will have to download and useolder Query Monitor plugin version 2.6.8 you can download it from here

d) Debug Bar

If you want you want a Memory Viewer like plugin for more complex used components memory debugging, reporting if (WP_DEBUG is set in wp-config.php) also check out Debug Bar .
For me Debug Bar was very useful because it show me depreciated functions some plugins used, so I substituted the obsoleted function with new one.

 

debug-bar-debug-wordpress-plugins-memory-use-screenshot-website


4. Server Hardware hungry (slow) WordPress plugins that you better not use

While spending time to Google for some fixes to WP slow query plugins – I've stumbled upon this post giving a good list with WordPress Plugins better off not to use because they will slow down your site
This is a publicly well known list of WP plugins every WordPress based site adminstrator should avoid, but until today I didn't know so my assumption is you don't know either ..

Below plugins are extremely database intensive mentioned in article that we should better (in all cases!) avoid:

  • Dynamic Related Posts
  • SEO Auto Links & Related Posts
  • Yet Another Related Posts Plugin
  • Similar Posts
  • Contextual Related Posts
  • Broken Link Checker — Overwhelms even our robust caching layer with an inordinate amount of HTTP requests.
  • MyReviewPlugin — Slams the database with a fairly significant amount of writes.
  • LinkMan — Much like the MyReviewPlugin above, LinkMan utilizes an unscalable amount of database writes.
  • Fuzzy SEO Booster — Causes MySQL issues as a site becomes more popular.
  • WP PostViews — Inefficiently writes to the database on every page load. To track traffic in a more scalable manner, both the stats module in Automattic’s Jetpack plugin and Google Analytics work wonderfully.
  • Tweet Blender — Does not play nicely with our caching layer and can cause increased server load.


A good Complete list of known WordPress slow plugins that will hammer down your wordpress performance is here

There are few alternatives to this plugins and when I have some free time I will download and test their alternatives but for now I plan the plugins to stay disabled.
 

For the absolute WP Performance Optimization Freaks, its good to check out the native way to Debug a wordpress installation through using few embedded
variables

 

define('WP_DEBUG', true);
define('WP_DEBUG', false);
define('WP_DEBUG_LOG', true);
define('WP_DEBUG_DISPLAY', false);
define('SAVEQUERIES', true);

 

An article describing how you can use native WP debug variables is here


Happy Optimizing  ! 🙂

mod_rewrite redirect rule 80 to 443 on Apache webserver

Wednesday, April 2nd, 2014

A classic sysadmin scenario is to configure new Apache webserver with requirement to have an SSL ceriticate installed and working on port 443 and all requests coming on port 80 to be redirected to https://.
On Apache this is done with simple mod_rewrite rule:

RewriteEngine On
RewriteCond %{HTTPS} off
RewriteRule (.*) https://%{HTTP_HOST}%{REQUEST_URI}

Before applying the rule don't forget to have Apache mod_rewrite enabled usually it is not enabled on default most Linux distributions by default.
On shared hostings if you don't have access to directly modify Apache configuration but have .htaccess enabled you can add above rules also to .htaccess

Add this to respective VirtualHost configuration and restart Apache and that's it. If after configuring it for some reason it is not working debug mod_rewrite issues by enabling mod_rewrite's rewrite.log

Other useful Apache mod_rewrite redirect rule is redirect a single landing page from HTTP to HTTP

RewriteEngine On
RewriteRule ^apache-redirect-http-to-https.html$ https://www.site-url.com/apache-redirect-http-to-https.html [R=301,L]

!Note! that in case where performance is a key requirement for a website it might be better to use the standard way to redirect HTTP to HTTPS protocol in Apache through:

ServerName www.site-url.com Redirect / https://www.site-url.com/

To learn more on mod_rewrite redirecting  check out this official documentation on Apache's official site.

xkill for Windows – Kill hanged programs with one click like in UNIX

Thursday, September 18th, 2014

Windows Xkill by Solo Dev logo kill easily hanged, crashed windows programs - Linux xkill for Windows alternative

I’ve used Linux as I’ve used it over the last 10 years and thus I’m so used to xkill that I consider it as a normal tool every modern operating system should have.

Since I’m forced to work on a Windows platform over the last 1 year, every now and then I have crashing / hanged apps Window which sometimes is hard to kill using Task Manager or command line tools like tasklist, taskkill or pstools – Windows Sysinternal tools cause you don’t know the exact process name of the Windows crashed application.

Thanksfully a good hearted guy SuprVillain made Windows Xkill program. Windows Xkill is portable app, so you don’t need to install it but simply download and run it.

windows-7-system-tray-xkill-screenshot-xkill-for-windows

Win-Xkill runs in system tray and has “kill mode” of operation, when Kill mode it is running Windowns Xkill operates exactly like UNIX’s xkill. The mouse pointer turns into Skull and Cross Bones, you point at a Window app you wanted to kill and it gets terminated.
Windows Xkill kill mode enabled killing a windows notepad app
Win Xkill kill mode, can be invoked using also a key press Control+Alt+Backspace as well as there is an option to leave xkill running in the background background, but disabling the tiny skull icon in systray from its interface.
Enjoy 🙂