Artem Russakovskii's programming and technology blog

Updated: April 18th, 2008

• Tom Hanlon of MySQL presents
• monitoring tool basics
  • SHOW FULL PROCESSLIST
  • SHOW GLOBAL STATUS
  • SHOW GLOBAL VARIABLES
• basic tools
  • mysqladmin is provided with the server
    • mysqladmin -i 10 extended status: will repeat the same command every 10 seconds. Pipe through grep "and smoke it" (bad pun, hah hah)
    • -r: show only changed values
  • MySQL Administrator
• cacti
  • rrdtool based network graphing tool
  • uses snmp
  • PHP apache and MySQL based solution
  • MySQL plugins, download and install
  • "poller" gathers data and populates the graphs
  • someone offers munin as an alternative
    • not snmp based, its own agent is used
  • pros
    • cacti is fairly easy to configure
  • cons
    • could be CPU intensive with lots of machines (Perl polling seems to be the

…

Read the rest of this article »

• Tom Hanlon of MySQL presents
• Benchmarking tools
  • mysqlslap (with MySQL 5.1)
  • sql-bench
  • supersmack – Jeremy Zawodny's tool
  • Apache Bench (combined with some sample PHP scripts)
  • MySQL's benchmark() function
  • mybench
  • WAST
  • JMeter
• sql-bench
  • pros
    • ubiquitous
    • long history of use
  • cons
    • single thread
    • Perl
    • not always real-life test cases (create 10k tables?)
  • list of tests follows
• supersmack
  • configurable, flexible
  • 1000 queries, 50 users
    • super-smack -d mysql select-key-smack 50 1000
  • can modify queries to be closer to what your own application uses
  • pros
    • benches concurrent connections
    • well documented
  • cons
    • test language sucks
• Apache Bench
  • webserver benchmarking tool
  • point to a webserver, utilizes concurrent users
  • siege, httperf, httpload are similar
  • 404 errors deliver really quickly, so make sure to check for those
• benchmark()
  • tests

…

Read the rest of this article »

Updated: April 24th, 2008

Unfortunately I didn't find any available seats to take notes for this but this morning a very interesting keynote took place. Representatives from 7 large companies mentioned in the title gathered on stage and answered various questions by MySQL's Kaj Arno.

These questions included things like "how many MySQL servers do you have", "how many DBAs", etc. It was a lot of fun, hopefully someone (Sheeri) will edit and post the video soon.

Keith has a nice summary of everything that went on together with the numbers here.

Update: Venu has even better notes here….

• Paul McCullagh presents
• BLOB

• invented by Jim Starkey
• Basic Large OBject
• Binary Large OBject
• photos, films, mp4 files, pdfs, etc

• mysql client send buffer -> receive buffer on the server (max_allowed_packet)
• streaming a BLOB

• continuous data stream
• stream BLOB data directly in and out of the database
• store BLOBs of any size (>4GB) in the database
• create a scalable back-end that can handle any throughput and storage requirements. Wouldn't need to know in advance how big the database will get
• provide an open system that can be used by all engines
• provide extensions for BLOB streaming to existing MySQL clients

• referential integrity (no invalid references), can take a lot of

…

Read the rest of this article »

• Jay Pipes, Tobias Asplund
• Finding out the number of rows that would have been returned (MyISAM and InnoDB)

• SQL_CALC_FOUND_ROWS and FOUND_ROWS()
• COUNT(*)
• MEMORY table
• if query cache is on, then it makes no difference
• if it's off

• Memory MyISAM is fastest
• FOUND_ROWS() is slightly slower than count(*)

• SELECT … WHERE a UNION SELECT … WHERE b
  vs
  SELECT … WHERE a AND b
• index_merge wins

• composite index is faster
• of course, multiple indexes are more flexible than composite index

• …

• query cache

…

Read the rest of this article »

• Phil Hilderbrand of thePlatform for Media, Inc presents
• classic partitioning

• old school – union in the archive tables
• auto partitioning and partition pruning
• great for data warehousing
• query performance improved
• maintenance is clearly improved

• often id driven access vs date driven access
• 1 big clients could be 80% of the whole database, so there's a difficulty selecting partitioning schemes

• reducing seek and scan set sizes
• improving inserts/updates durations
• making maintenance easier

• Robert Hodges from Continuent presents
• About Continuent

• leading provider of open source database availability and scaling solutions

• uni/cluster – multi-master database clustering that replicates data across multiple databases and load balances reads
• uses "database virtualization"

• protection from db and site failures
• continuous operation during upgrades

• Brewer's conjecture

• DDL support
• inconsistent reads between replicas
• deadlocks
• sequences
• non-deterministic SQL

• data replication

• where are updates processed? master/master vs master/slave
• when are updates replicated? sync vs async

• views – who is active, who is crashed, do

…

Read the rest of this article »