Blog Archives

Synchronous Replication using Percona

So, I’m really happy to have discovered Percona’s XtraDB Cluster replication http://www.percona.com/doc/percona-xtradb-cluster/3nodesec2.html I’ve used MySQL Replication for a while, but there were always sync problems, and it was difficult to bring a server back in the mix.  Mutex contention was a

Posted in MySQL

ssh mount drive

INSTALL LIBARY: yum install fuse-utils sshfs   load the FUSE kernel module: sudo modprobe fuse   Now mount: sshfs user@host: mountpoint   to unmount fusermount -u mountpoint

Posted in MySQL

Postgres ‘Show’ database, table, …

mysql: SHOW TABLES postgresql: \d postgresql: SELECT table_name FROM information_schema.tables WHERE table_schema = ‘public’; mysql: SHOW DATABASES postgresql: \l postgresql: SELECT datname FROM pg_database; mysql: SHOW COLUMNS postgresql: \d table postgresql: SELECT column_name FROM information_schema.columns WHERE table_name =’table’; mysql: DESCRIBE TABLE postgresql: \d+ table postgresql: SELECT column_name FROM information_schema.columns WHERE table_name =’table’;

Posted in MySQL, PostgreSQL

open-files-limit / innodb_open_files What is the right setting

This is what I do to get an idea: sudo yum install lsof for innodb you should count the *.ibd files sudo lsof | grep -c “\.ibd$” for myisam you should count the *.MYD    

Posted in MySQL

Recover from MySQL Replication failure

If your slave stops working first do this: show slave status; This will give you the last error when it failed. If you can move past the error, and want to restart replication do this:   STOP SLAVE;  SET GLOBAL

Posted in MySQL

High created_tmp_disk_tables

You should monitor this, along with several other status vars regularly. I have an automated report create bar charts to rank all the servers I watch. Why is this important? Most MySQL DBs lately are configured using the InnoDB engine. Don’t

Posted in MySQL

locate and updatedb

If you have a linux box and locate doesn’t work. Try this: sudo yum install mlocate sudo /etc/cron.daily/mlocate.cron

Posted in MySQL

MySQL – Some stats to watch

If you’re staring at the screen, wondering what to look at to indicate something needs tuning in MySQL, run this:   show global status where Variable_name like ‘created_tmp_disk_tables’ OR Variable_name like ‘handler_read_first’ OR Variable_name like ‘innodb_buffer_pool_wait_free’ OR Variable_name like ‘key_reads’

Posted in MySQL

Unable to connect to PostgreSQL server: FATAL: no pg_hba.conf entry for host

Here is the fix:   There is a file somewhere called pg_hba.conf so I do this: updatedb locate pg_hba.conf vi /path/pg_hba.conf add line at bottom host    all         all         10.20.30.40    

Posted in MySQL

Monitory MySQL Error Log

A good dba keeps an eye on the Error Logs, and slow query logs often. This should be a proactive activity to prevent the reactive cleanup. If you’re like me you’d often forget to look, or the log fills at

Posted in MySQL