Categories
bash mysql

Bash / MySQL queries…

Reduce connection counts to MySQL by using an array to get many values at once (assuming they’re single ‘word’ values)

Categories
mysql

Finding and killing long running MySQL queries

Sometimes (Woocommerce?) related queries on a MySQL server can seemingly run forever and eventually lead to the server being clogged up (and few free connections). Something like this can help …. (i.e. kill all MySQL queries which have been running longer than 900 seconds, and aren’t from the ‘root’ user).

Categories
mysql

fixing/restablishing mysql replication with rsync

One approach to fixing a broken MySQL replication setup…. using rsync.

Categories
mysql

Cleaning up a lot of WordPress _transients

WordPress seems to like hiding a load of ‘transient’ (cacheable) stuff in it’s wp_options table. Unfortunately for one site, it seems it didn’t bother to clean up the transient stuff, leaving behind about 750,000 records… which made a WP version upgrade painful, as MySQL locks the wp_option table which causes all other page loads to […]

Categories
debian linux mysql performance

MySQL Max_connections stuck on 214 ?

I found MySQL was being annoying earlier and not ‘accepting’ my max_connections = 450 directive on a Debian Wheezy install, and being seemingly stuck on having 214 connections….

Categories
development mysql php postgresql

PostgreSQL unbuffered queries and PHP (cursors)

From using MySQL, I’ve used the ‘unbuffered queries‘ feature a number of times. It’s where you don’t fetch the entire resultset into memory at once – which is necessary if you’re retrieving more data than you have memory available. If’s often also generally gets results/data back to you sooner.

Categories
mysql performance

MySQL update/write query analysis (query profiling)

Do you have a slow MySQL update/insert/delete query? Obviously, for ‘SELECT’ queries you can prepend the query with “EXPLAIN ” – however that doesn’t work for the other query types (UPDATE/INSERT/DELETE). So, one solution which may explain why the query is slow is to turn on MySQL’s profiling functionality, like in the following example :

Categories
mysql

Random MySQL performance tuning stuff

If you’re using InnoDB, ensure innodb_buffer_pool_size is set to a decent value – I choose about 25% of physical memory… ideally this is larger than your dataset size, but obviously may not be posible, and the server may have to do other stuff…. If you’re using InnoDB stop the O/S from also trying to cache […]