Currently Browsing: LAMP/Node

Swap column values in MySql (via temp variables)

If you want to swap values in one query in MySql without a temporary table, you can use variables to hold values that otherwise would be changed before you can use them.

E.g., swap start_date and end_date via:

UPDATE mydates SET start_date=@tmp:=start_date, start_date=end_date, end_date=@tmp WHERE start_date > end_date

Install amqp-ext PHP extension to Apache in Docker

I wanted to add RabbitMQ support to my PHP app and prefer to use the PECL amqp package:

This extension can communicate with any AMQP spec 0-9-1 compatible server, such as RabbitMQ, OpenAMQP and Qpid, giving you the ability to create and delete exchanges and queues, as well as publish to any exchange and consume from any queue.

Using pecl install won’t install all the dependencies. There are various options to how to solve this including fetching from Git repos, however this is the one that worked for me and seemed the most straight-forward (ymmv). See below for code used in a Dockerfile with base image FROM php:7.1.14-apache which uses debian:jessie, so the native commands should work in similar Debian/Ubuntu linux distros.

RUN curl -o librabbitmq4_0.8.0-1_amd64.deb
RUN curl -o librabbitmq-dev_0.8.0-1_amd64.deb
RUN dpkg -i librabbitmq4_0.8.0-1_amd64.deb
RUN dpkg -i librabbitmq-dev_0.8.0-1_amd64.deb
RUN pecl install amqp-1.9.3 && docker-php-ext-enable amqp

Note if you do use this in a Dockerfile, you can combine some of the statements to optimize layers. You also may need to update the lib version if it’s updated or if you change the PECL version to install.

Redis database size and key info

You can use INFO to get total memory size and a lot of other useful bits but if you just want to know the size of a single database or other info about database-specific keys, it can be a bit tricky.


tldr; in many cases, all you might need is:

redis-cli --bigkeys

To see what’s going on real-time:

redis-cli MONITOR

Yii2 fix “Malformed UTF-8 characters” error with master/slave db

You need to specify charset in slave config, e.g.,

'slaveConfig' => [
  'charset' => 'utf8',

Sysctl optimization updates for Redis, MongoDB, etc.

Sysctl Performance tips for Redis, MongoDB, etc.

# backup:
$ sysctl -a > /home/sysctl_$(date +%Y%m%d).bak
$ sysctl -a
# make changes to sysctl.conf:
$ vi/nano /etc/sysctl.conf
# web - nginx, redis, mongo, etc:
vm.swappiness = 10
vm.dirty_ratio = 40
vm.dirty_background_ratio = 10
# for larger servers:
net.ipv4.tcp_rmem = 4096 12582912 33554432
net.ipv4.tcp_wmem = 4096 12582912 33554432
net.core.rmem_max = 33554432
net.core.wmem_max = 33554432
net.ipv4.tcp_window_scaling = 1
net.ipv4.tcp_timestamps = 1
net.ipv4.tcp_sack = 1
net.ipv4.tcp_max_syn_backlog = 65536
kernel.keys.root_maxkeys = 1000000
#Raise somaxconn (above 511)
net.core.somaxconn = 4096
# vm.overcommit_memory (optional):
# vm.overcommit_memory = 1
# set in /etc/rc.local (test reboot stickiness)
# ensure changes to sysctl configuration persist across reboots:
$ sysctl -p
# disable transparent hugepages (manually):
echo 'never' > /sys/kernel/mm/transparent_hugepage/enabled
echo 'never' > /sys/kernel/mm/transparent_hugepage/defrag
# to persist restart (on systemd), create service:
nano /etc/systemd/system/disable-thp.service
    Description=Disable Transparent Huge Pages (THP)
    ExecStart=/bin/sh -c "echo 'never' > /sys/kernel/mm/transparent_hugepage/enabled && echo 'never' > /sys/kernel/mm/transparent_hugepage/defrag"
# save the file and reload SystemD daemon:
systemctl daemon-reload
# start the script and enable it on boot level:
sudo systemctl start disable-thp
sudo systemctl enable disable-thp

more info:

  • « Previous Entries