Pacific-Design.com

    
Home Index

1. MariaDB

2. Sharding

MariaDB / Sharding /

Hash URLs based on striped http:// and trailing slash


#!/bin/bash
#--------------------------------------------------------------------------------------#
# Multi process Database partitioning that separates very large databases 
# into smaller, faster, more easily managed parts called data shards, based 
# on the last 3 characters SHA256 algorithm result of normalized url.
#
# python = int("b5f", 15)
# gosleep=$((gosleep+2)) 
#--------------------------------------------------------------------------------------#
USERNAME='root'
PASSWORD='password'
#--------------------------------------------------------------------------------------#
alter_tables()
{
  DATABASE=$1
  TABLE_LIST=`mysql -u $USERNAME -p$PASSWORD -NB -e "SHOW TABLES FROM $DATABASE"`
  counter=0; gosleep=0; last=0;

  for E in $TABLE_LIST
  do
    proc=`mysqladmin -u $USERNAME -p$PASSWORD processlist | wc | awk '{ print $1 }'`

    #----------------------------------#
    if [ "$proc" -lt "16" ]; then
    	gosleep=0
    else
    	if [ "$proc" -lt "$last" ]; then
    		((gosleep--))
    	else
    		((gosleep++))
    	fi
    fi
    #----------------------------------#

    #----------------------------------------------------------------------------------#
    SQL="
         UPDATE $DATABASE.$E
         SET sha256url =  
		SHA2(
		  REPLACE(
		    REPLACE(
		
		      CASE ( RIGHT(url,1)) 
		      WHEN '/' 
		        THEN LEFT(url, (SELECT (LENGTH(url) - 1))) 
		        ELSE url 
		      END 
		
		    , 'http://', '' ) 
		   , 'www.', '' )
		  , 256)
        "

    echo "$SQL"; sleep 0.1
    nohup mysql -u $USERNAME -p$PASSWORD -NB -e  "$SQL" &
        
    #----------------------------------------------------------------------------------#
    last=$proc
    sleep $gosleep      
    ((counter++))

  done
}
#--------------------------------------------------------------------------------------#
#         Repair and optimize all tables in the following databases
#--------------------------------------------------------------------------------------#
mydb="engine22"
for db in $mydb
do  
    echo "Database: is [$db]"
    alter_tables $db
done
#--------------------------------------------------------------------------------------#