Bash / MySQL queries…

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

I “woke up” and realised I often do this wrong …. (too many connections/individual queries on MySQL) when the query is returning simple values (single word like things) for each field.

As a means of example :

So a before query :

MYSQL="mysql -NB my_database"

# Obviously $VAR1/$VAR2 can contain spaces etc in this example
VAR1=$(echo "SELECT field FROM table WHERE some_key = '${IDENTIFIER}'" | $MYSQL)
VAR2=$(echo "SELECT field FROM table WHERE some_key = '${IDENTIFIER}'" | $MYSQL)

And perhaps a nicer way might look more like :

# Chuck output from a query into an array (retrieve many fields at once)
# coalesce( ... ) copes with a field that may be null.

BITS=( $(echo "SELECT coalesce(field1,200) as field1, field2, field3 FROM some_table WHERE some_key = '${IDENTIFIER}'" | $MYSQL ) )

# and then check we had enough values back
if [ ${#BITS[@] -lt 3 ]; then
echo "handle this error..."
# This will not work if field1/field2/field3 contain spaces.

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).
Continue reading “Finding and killing long running MySQL queries”

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 get stuck waiting (and the site to stop working).

Fix / hacky solution after the ‘more’…

Continue reading “Cleaning up a lot of WordPress _transients”

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.
Continue reading “PostgreSQL unbuffered queries and PHP (cursors)”

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 :
Continue reading “MySQL update/write query analysis (query profiling)”

Random MySQL performance tuning stuff

  1. 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….
  2. If you’re using InnoDB stop the O/S from also trying to cache ‘stuff’ in the buffer cache – using¬†innodb_flush_method=O_DIRECT
  3. Download the MySQL Tuner perl script (wget -O > and run (e.g. perl –user root –pass blahblah; it might point out a few variables to change; node the ‘maximum possible memory usage – you don’t want this to exceed 50% for a normal LAMP server).
  4. I use something like the below to optimize all tables, beware this will cause MyISAM tables to lock up … so you really need to run it in a quiet period.
SQL="select concat(TABLE_SCHEMA, '.', TABLE_NAME) from
    information_schema.TABLES where TABLE_SCHEMA IN
    ('database1','database2', 'databaseN') and Data_free > 500
    AND Engine = 'MyISAM' "

for table in $(mysql --skip-column-names  --batch -u root -prahrah -e "$SQL")
    mysql --batch -u root -pxxxxx -e "optimize table $table"
    sleep 10

See also PHP Conference InnoDB talk and MySQL Tuner