mysql
MySql design separates query processing with storage engine.
MySql design has 3 layers. connection handling,query processing,storage engine
Each connection gets its own thread from server and these threads are cached.
MySQL parses queries and creates an internal structure(parse tree) and also caches the results of this query.
locks types.
read locks and write locks.read locks are shared,write locks ar exclusive.
If the system spends too much time managing locks instead of storing and retrieving data,performance can suffer.
write locks have higher priority than read locks.
Lock granularity is an important decision in storage engine design.Ex: table locks ,row locks,row with mvcc
In multi version concurrency control(MVCC) mechanism , a snapshot of the data is created and versioned.
Isolation levels ,read_uncommitted,read_committed,read_repeatable,serializable.
Transaction logging helps to make the requested changes in memory and transmit the changes to the actual storage later.As I/O
takes a lot greater time than an inmemory operation this is effective.
MySQL provides 3 transaction storage engines, InnoDB,NDB Cluster and Falcon.
MySQL autocommits by default.
MyISAM is default storage engine.does not support row level locks.
MyISAM tables are non transactional,InnoDB are.
table is stored in 2 files data and index (.MYD and .MYD)
Table definition is stored in a .frm file in the data directory.
InnoDB uses memory engine for read-only type data.,Archive engine supports only inserts and reads.
csv engine,federated engine,blackhole engine,ndb cluster engine,falcon engine
,solidDB,Primebase XT engine,Maria storage engine
storage engines can be chosen on a table by table basis.
InnoDB is good for transactions.MyISAM for reads and inserts
Benchmarking measures how the application currently performs.
fullstack benchmarking measure the application as a whole,including the web server.
TPS,Response time,Concurrency,Amount of data acted upon and retrieved,
benchmark each step
by logging the sql queries.
This week get all the scripts/parametrisations to be tested ready.
Next week, benchmark. create a benchmark directory on server and place the results of each flow there.
First do the queries, later do the cuds
use Mysql BENCHMARK function to tell how many times and what expression to execute. caching may be possible.
MySql benchmark suite written in Perl /usr/share/mysql/sql-bench
EXPLAIN command to check the identified slow queries.
Put a jboss profiler.
start/stop server.
general log and slow log have to be enabled for profiling.
my.cnf settings
log-slow-queries = /tmp/slow.log
long_query_time = 0
log-queries-not-using-indexes
log-slow-admin-statements
use single user, run unti completion to get a list of all the queries.
mysqladmin -u root -p shutdown
mysqld_safe --log=/tmp/general.log --log-slow-queries=/tmp/slow.log &
SHOW PROCESSLIST;
SHOW STATUS;
SHOW VARIABLES;
SHOW TABLE STATUS
SHOW GLOBAL STATUS
MySql design has 3 layers. connection handling,query processing,storage engine
Each connection gets its own thread from server and these threads are cached.
MySQL parses queries and creates an internal structure(parse tree) and also caches the results of this query.
locks types.
read locks and write locks.read locks are shared,write locks ar exclusive.
If the system spends too much time managing locks instead of storing and retrieving data,performance can suffer.
write locks have higher priority than read locks.
Lock granularity is an important decision in storage engine design.Ex: table locks ,row locks,row with mvcc
In multi version concurrency control(MVCC) mechanism , a snapshot of the data is created and versioned.
Isolation levels ,read_uncommitted,read_committed,read_repeatable,serializable.
Transaction logging helps to make the requested changes in memory and transmit the changes to the actual storage later.As I/O
takes a lot greater time than an inmemory operation this is effective.
MySQL provides 3 transaction storage engines, InnoDB,NDB Cluster and Falcon.
MySQL autocommits by default.
MyISAM is default storage engine.does not support row level locks.
MyISAM tables are non transactional,InnoDB are.
table is stored in 2 files data and index (.MYD and .MYD)
Table definition is stored in a .frm file in the data directory.
InnoDB uses memory engine for read-only type data.,Archive engine supports only inserts and reads.
csv engine,federated engine,blackhole engine,ndb cluster engine,falcon engine
,solidDB,Primebase XT engine,Maria storage engine
storage engines can be chosen on a table by table basis.
InnoDB is good for transactions.MyISAM for reads and inserts
Benchmarking measures how the application currently performs.
fullstack benchmarking measure the application as a whole,including the web server.
TPS,Response time,Concurrency,Amount of data acted upon and retrieved,
benchmark each step
by logging the sql queries.
This week get all the scripts/parametrisations to be tested ready.
Next week, benchmark. create a benchmark directory on server and place the results of each flow there.
First do the queries, later do the cuds
use Mysql BENCHMARK function to tell how many times and what expression to execute. caching may be possible.
MySql benchmark suite written in Perl /usr/share/mysql/sql-bench
EXPLAIN command to check the identified slow queries.
Put a jboss profiler.
start/stop server.
general log and slow log have to be enabled for profiling.
my.cnf settings
log-slow-queries = /tmp/slow.log
long_query_time = 0
log-queries-not-using-indexes
log-slow-admin-statements
use single user, run unti completion to get a list of all the queries.
mysqladmin -u root -p shutdown
mysqld_safe --log=/tmp/general.log --log-slow-queries=/tmp/slow.log &
SHOW PROCESSLIST;
SHOW STATUS;
SHOW VARIABLES;
SHOW TABLE STATUS
SHOW GLOBAL STATUS
Comments