2 Linux MySQL Tuning Scripts you must have
Are you running a large MySQL database or website that uses MySQL databases for content storage? These 2 scripts when run, review historical as well as current connections and related resource utilization to provide you with recommendations to tune your my.conf file.
As long as you know where and how to edit you my.conf file these 2 scripts can help you set your values to maximize the efficiency of your MySQL engine.
- MySQL Tuner Primer - https://launchpadlibrarian.net/78745738/tuning-primer.sh
######################################################################### # # # Usage: ./tuning-primer.sh [ mode ] # # # # Available Modes: # # all : perform all checks (default) # # prompt : prompt for login credintials and socket # # and execution mode # # mem, memory : run checks for tunable options which # # effect memory usage # # disk, file : run checks for options which effect # # i/o performance or file handle limits # # innodb : run InnoDB checks /* to be improved */ # # misc : run checks for that don't categorise # # well Slow Queries, Binary logs, # # Used Connections and Worker Threads # #########################################################################
- MySQL Tuner - https://raw.github.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief format along with some basic performance suggestions.
To run one of these scripts you may need an additional program to execute the script be sure to download “bc” from your repository.
# CentOS – RedHat
yum install bc -y# Debian / Ubunty
apt-get install bc -y
Now to download the scripts to your server run the following 2 commands to download the 2 files into the current directory you are in:
wget https://launchpadlibrarian.net/78745738/tuning-primer.sh
wget https://raw.github.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl
Now run tuner-primer.sh
sh tuning-primer.sh
tuner-primer.sh output:
– MYSQL PERFORMANCE TUNING PRIMER –
- By: Matthew Montgomery -
MySQL Version 5.0.95 i686
Uptime = 0 days 2 hrs 59 min 18 sec
Avg. qps = 350
Total Questions = 3771760
Threads Connected = 42
Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations
To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL’s Enterprise Monitoring and Advisory Service
SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10 sec.
You have 5210 out of 3772481 that take longer than 10 sec. to complete
Your long_query_time seems to be fine
BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html
WORKER THREADS
Current thread_cache_size = 4
Current threads_cached = 0
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine
MAX CONNECTIONS
Current max_connections = 100
Current threads_connected = 42
Historic max_used_connections = 42
The number of used connections is 42% of the configured maximum.
Your max_connections variable seems to be fine.
INNODB STATUS
Current InnoDB index space = 185 M
Current InnoDB data space = 257 M
Current InnoDB buffer pool free = 0 %
Current innodb_buffer_pool_size = 256 M
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory
MEMORY USAGE
Max Memory Ever Allocated : 397 M
Configured Max Per-thread Buffers : 293 M
Configured Max Global Buffers : 273 M
Configured Max Memory Limit : 567 M
Physical Memory : 1.00 G
Max memory limit seem to be within acceptable norms
KEY BUFFER
No key reads?!
Seriously look into using some indexes
Current MyISAM index space = 70 K
Current key_buffer_size = 7 M
Key cache miss rate is 1 : 0
Key buffer free ratio = 88 %
Your key_buffer_size seems to be fine
QUERY CACHE
Query cache is enabled
Current query_cache_size = 8 M
Current query_cache_used = 90 K
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 1.10 %
Current query_cache_min_res_unit = 4 K
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
MySQL won’t cache query results that are larger than query_cache_limit in size
SORT OPERATIONS
Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 512 K
On average 2 sort merge passes are made per sort operation
You should raise your sort_buffer_size
You should also raise your read_rnd_buffer_size
JOINS
Current join_buffer_size = 132.00 K
You have had 0 queries where a join could not use an index properly
Your joins seem to be using indexes properly
OPEN FILES LIMIT
Current open_files_limit = 1024 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine
TABLE CACHE
Current table_cache value = 64 tables
You have a total of 21 tables
You have 63 open tables.
Current table_cache hit rate is 56%
, while 98% of your table cache is in use
You should probably increase your table_cache
TEMP TABLES
Current max_heap_table_size = 32 M
Current tmp_table_size = 32 M
Of 1212 temp tables, 21% were created on disk
Created disk tmp tables ratio seems fine
TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 797330 : 1
You have a high ratio of sequential access requests to SELECTs
You may benefit from raising read_buffer_size and/or improving your use of indexes.
TABLE LOCKING
Current Lock Wait ratio = 0 : 3774988
Your table locking seems to be fine
Now run mysqltuner.pl
perl mysqltuner.ph
mysqltuner.ph output:
>> MySQLTuner 1.2.0 – Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with ‘–help’ for additional options and output filtering
[!!] Successfully authenticated with no password – SECURITY RISK!
——– General Statistics ————————————————–
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.95
[OK] Operating on 32-bit architecture with less than 2GB RAM
——– Storage Engine Statistics ——————————————-
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in InnoDB tables: 257M (Tables: 4)
[OK] Total fragmented tables: 0
——– Security Recommendations ——————————————-
[!!] User ‘@localhost’ has no password set.
[!!] User ‘root@127.0.0.1′ has no password set.
[!!] User ‘root@localhost’ has no password set.
——– Performance Metrics ————————————————-
[--] Up for: 3h 2m 37s (3M q [352.019 qps], 326 conn, TX: 197M, RX: 219M)
[--] Reads / Writes: 0% / 100%
[--] Total buffers: 306.0M global + 2.9M per thread (100 max threads)
[OK] Maximum possible memory usage: 599.7M (58% of installed RAM)
[OK] Slow queries: 0% (5K/3M)
[OK] Highest usage of available connections: 42% (42/100)
[OK] Key buffer size / total MyISAM indexes: 8.0M/66.0K
[!!] Query cache efficiency: 0.5% (5 cached / 932 selects)
[OK] Query cache prunes per day: 0
[!!] Sorts requiring temporary tables: 203% (1K temp sorts / 601 sorts)
[OK] Temporary tables created on disk: 21% (347 on disk / 1K total)
[OK] Thread cache hit rate: 87% (42 created / 326 connections)
[OK] Table cache hit rate: 56% (63 open / 112 opened)
[OK] Open file limit used: 3% (34/1K)
[OK] Table locks acquired immediately: 100% (1M immediate / 1M locks)
[!!] InnoDB data size / buffer pool: 258.0M/256.0M
——– Recommendations —————————————————–
General recommendations:
MySQL started within last 24 hours – recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Variables to adjust:
query_cache_limit (> 1M, or use smaller result sets)
sort_buffer_size (> 2M)
read_rnd_buffer_size (> 512K)
innodb_buffer_pool_size (>= 257M)
Get Operating System & Version Info on Linux
Not sure what operating system or version of an operating system you are running on your linux box?
Run one the the following 2 commands:
# Debian / Ubuntu variants
cat /etc/issue# RedHat, CentOS variants
tail /etc/redhat-release
Update Proxmox Software & Proxmox Templates
When your done doing a clean install from a Proxmox ISO be sure to update your Proxmox and Templates to the latest version to ensure maximum support for latest features.
Things to consider before you update Proxmox:
- Have you read up on all the changes the latest update provides ( new functionality, changes in specific working features, things that need to be done after the update is completed )
- Are you running a testing or production environment?
- If an update goes wrong what plan do you have setup to undo changes?
Commands to run when updating Proxmox:
# Update Proxmox software
apt-get update && apt-get dist-upgrade -y# Update Proxmox Downloadable Templates
pveam update
#####
Since Proxmox 2.2 kernel version .16 OpenVZ handles VNC differently providing more security so you may need to download the latest Debian templates. (Proxmox Website says “Debian 6 and 7 templates are up2date”)
If you are using any of the other operating systems you will need to update the OS configuration manually to get VNC working. I already wrote a script to programmatically get this done for you ( http://crivera.com/developer/proxmox-2-2-openvz-vnc-update )
Proxmox 2.2 OpenVZ :: VNC update
Our Problem:
I am in charge of running a large proxmox cluster with over 600 running vms. Since we recently updated our cloud to the latest .16 kernel with OpenVZ updates we found out shortly that Proxmox and OpenVZ has updated VNC and will not work by default.
VNC Update Instructions ( http://pve.proxmox.com/wiki/OpenVZ_Console )
Each operating system has its own configuration to update to get VNC working again.
My Solution:
Create 2 scripts to update all OpenVZ containers so VNC works with Proxmox 2.2 new vnc update for pve kernel .16.
Scripts Created:
- Loader script – script created to get active running containers and load action script on to the vm
- Action script – script created to make adjustments on the client vm, then delete the file after running
#####
Loader Script:
#!/bin/bash
function getCTIDs {
CTIDS=`vzlist -o ctid`
for id in $CTIDS;
do
if [ "$id" != "CTID" ]; thenecho “Connecting To: $id”
cat updateVNC | vzctl exec2 “$id” “cat – > /home/updateVNC”
vzctl exec “$id” bash /home/updateVNC
vzctl exec “$id” rm /home/updateVNC
vzctl exec “$id” rebootfi
done}
getCTIDs
#####
Action Script:
#!/bin/bash
OSINFO=`tail /etc/redhat-release`if [ "$OSINFO" == "" ]
then
OSINFO1=`cat /etc/issue`if [[ "$OSINFO1" == *Debian* ]]
then
echo “Debian”
echo “1:2345:respawn:/sbin/getty 38400 tty1″ >> /etc/inittabelif [[ "$OSINFO1" == *Ubuntu* ]]
then
echo “Ubuntu”
echo “# tty1 – getty## This service maintains a getty on tty1 from the point the system is# started until it is shut down again.
start on stopped rc RUNLEVEL=[2345]
stop on runlevel [!2345]
respawnexec /sbin/getty -8 38400 tty1″ > /etc/init/tty1.conffi
echo $OSINFO1
elif [[ "${OSINFO}" == *5.* ]]
then
echo “CentOS 5″;
echo “1:2345:respawn:/sbin/agetty tty1 38400 linux” >> /etc/inittabelif [[ "${OSINFO}" == *6.* ]]
then
echo “CentOS 6″;
echo “# This service maintains a getty on tty1 from the point the system is# started until it is shut down again.
start on stopped rc RUNLEVEL=[2345]
stop on runlevel [!2345]
respawnexec /sbin/agetty -8 tty1 38400″ >> /etc/init/tty.conf
fi
#####
Notes:
The loader script can be called anything, but the action script must remain “updateVNC” since its hardcoded in the loader script to find that file and copy it over to the vm before running it.
Downloads:
iCare | User Interface Development
I was hired by iCare to provide them with a HTML5 compliant cross browser & platform user interface. Designed by their inhouse staff I received a PSD and was in charge of completing the conversion (HTML, Javascript, jQuery)
This is for an Electronic Medical Records (EMR) / Electronic Health Records (EHR) for hospitals to use so the interface needs to work on all computers as well as all android and apple mobile devices.
Generating Unique ID string using PHP
Best way to create a unique id strings in PHP would be to use the PHP built in function uniqid().
// generate unique string echo uniqid(); /* prints 4bd66c543221a */ // generate another unique string echo uniqid(); /* prints 7cd66c9472340 */// with prefix echo uniqid('php_'); /* prints php_4gc54d6cd8b8f */ // with more entropy echo uniqid('',true); /* prints 5hc65d6cd8b096.23135875 */ // both echo uniqid('php_',true); /* prints php_4gc65da217b650.43764655 */
The Juice Report
This was a project to create a full featured blog website from scratch. TheJuiceReport has a video section with custom branded video player to push the client brand and a music section with streamable media.
Services Offered:
- Web Development
- Blog Development
Skill Set: HTML, CSS, PHP, MySQL, Javascript, Photoshop, Dreamweaver
