Transcript paris-sakai-mysql

Sakai and mysql
Stephen Marquard, David Horwitz
[email protected]
University of Cape Town
July 2008
mysql in production
• UNISA experience
– mysql for >100K users
– recently migrated to Oracle (for corporate DBA support)
• UCT experience
– approx 25K users, average 10K users over 24 hours,
max 1800 distinct users
– mysql 4.1, 5.0.51a
– 2 db crashes (auto restarts) in 30 months of production
• Other mysql sites
– Foothill / Etudes
– Rutgers
– Look in JIRA PROD category for more details:
http://jira.sakaiproject.org/jira/browse/PROD
Provisioning server(s)
• Use a dedicated server! (or dedicated virtualized
CPUs) – don’t let db compete for other resources
• UCT: 16G RAM, 2 x dual core CPUs, SAN storage
• Benchmark
– Other institutions with similar usage profiles (concurrent
sessions, toolset, course size)
– Projected growth
– From db system stats
• Provision db for “burst” capacity of 5x to 10x typical
load
• Load-test to tune load balancer, app server and db
limits so that your system degrades gracefully
Configuration
• Server (/etc/my.cnf)
– Use InnoDB
(MyISAM is not ACID: some services/tools will break)
– Use as much memory as you have without pushing the app
server into swap
– Additional connections use additional memory: calculate
memory usage for max connections (see mysql docs)
– Enable the query cache!
– Use an in-memory table for SAKAI_PRESENCE
• Client (sakai.properties)
– mysql connector parameters
– db pool parameters
UCT
my.cnf
[mysqld]
# Need these for Sakai
default-table-type=innodb
character-set-server = utf8
# Logging
log-slow-queries = /var/log/mysql/mysql-slow.log
long_query_time = 1
# At least App servers * max per app server
max_connections = 805
# Enable the query cache
query_cache_size = 160M
# Some buffer and cache sizes
key_buffer = 64M
max_allowed_packet = 64M
table_cache = 512
# c/f http://johnjacobm.wordpress.com/2007/06/29/mysql-server-tuning/
table_cache = 750
tmp_table_size = 64M
# InnoDB settings
innodb_buffer_pool_size = 7168M
innodb_additional_mem_pool_size = 30M
# Set .._log_file_size to 25 % of buffer pool size
# (can't change this after db has been created)
innodb_log_file_size = 500M
innodb_log_buffer_size = 8M
# Avoid some deadlocks from index gap locking (not safe for replication)
innodb_locks_unsafe_for_binlog=1
UCT sakai.properties
# Connector properties
[email protected]=jdbc:mysql://srvslscle006.uct.ac.za:3306/vula?
useUnicode=true&characterEncoding=UTF-8&
useServerPrepStmts=false&cachePrepStmts=true&
prepStmtCacheSize=4096&prepStmtCacheSqlLimit=4096&
slowQueryThresholdMillis=750&logSlowQueries=true&maxQuerySizeToLog=128000
# increase number of connections in the connection pool
[email protected]=200
•
Also read the release notes and installation documents for your Sakai release
•
Known issue with DBCP deadlocks (SAK-11852, SAK-11877, SAK-11005,
https://issues.apache.org/jira/browse/DBCP-44) pending switch to c3po. Possible
workaround by setting maxThreads (server/conf.xml) to < maxActive, though check your
loadbalancer behaviour first.
Backups
• What we do (every day @ 4am, takes 6-8min, 920M,
does not lock db or tables):
#! /bin/sh
## Daily backup of mysql database to a snapshot.
BFOLDER=/data/mysql/backup
DBNAME=vula
## Rename old dump file
rm -f $BFOLDER/$DBNAME.sql.gz.OLD
mv $BFOLDER/$DBNAME.sql.gz $BFOLDER/$DBNAME.sql.gz.OLD
## Backup mysql Vula database
/bin/logger Starting mysql backup of database $DBNAME
/usr/bin/mysqldump -u root --password=goaway --verbose --max-allowed-packet=64M --debug-info --quick --single-transaction $DBNAME 2>
$BFOLDER/mysqldump.log | gzip > $BFOLDER/$DBNAME.sql.gz
/bin/logger Finished mysql backup of database $DBNAME
• Or, replication to slave server, backup from slave
• Or third-party backup utilities
• Set things up so that you can restore a snapshot
of your production db to a test server
Archiving
• Archive SAKAI_SESSION and
SAKAI_EVENT data
• Inserts into SAKAI_SESSION become
expensive when the table is very large
• Data mining queries on historical
SAKAI_SESSION and SAKAI_EVENT data
can be slow. Run them on a different db
server if possible.
• Example script:
http://source.cet.uct.ac.za/svn/sakai/scripts/trunk/db/archive.pl
Monitoring and analysis
• Server
–
–
–
–
–
–
Stats (graph metrics and throughput, e.g. in mrtg)
show table status (volumes of data, index sizes)
explain select … (use of indexes, query complexity)
profile build (step-by-step query execution reporting)
slow query logging (minimum precision 1s, not that helpful)
query logging
• Client
– profiling through connection parameters:
http://dev.mysql.com/doc/refman/5.0/en/connector-jreference-configuration-properties.html
UCT mysql
server metrics
graphed with
mrtg
See them live at
https://vula.uct.ac.za/web/dev/dashboard/
Diagnosing db-related
performance issues
• How do you know it’s a database issue?
–
–
–
–
Query time
Volume of queries
High connection count
High load average / CPU use / other system
metrics
– Lock wait timeouts, deadlock rollbacks
• Check for missing indexes (manually or tools)
• Switch on query logging (server or client),
look at queries for a single request (volume
and/or expensive queries)
Examples of performance issues
• These affect both Oracle and mysql (often worse on
Oracle)
• Look for application performance that degrades
O(n^2) or O(n) with site membership or volume of
data
• Section Info – in a large course site with 100
sections, to render default student view
– Sakai 2.5.0: 3000 queries O(n) complexity
– Sakai 2.5.1: 3 queries O(1) complexity
– Usage patterns turn bad problems into acute problems:
e.g. competitive tutorial group signup
(1500 students, 400+ seater-lab, signup opens at 4pm).
• Assignments: one operation O(n^2) in early post-2.4
• Content: O(n) to O(1) for quota checks
Factors contributing to poor
performance
• ORM/Hibernate (too many queries for what you
actually need)
• XML blobs in db (hard to filter, sort, search, migrate)
• Inadequate schema design
• Bad query design (for mysql), e.g. subselects
• Missing indexes
• Naïve use of service APIs
(e.g. iterating through sets of items)
• Service APIs not meeting tool requirements
• API boundaries (avoiding cross-service table joins)
Let the database do as much work as possible!
High Availability
• Talk to Ian Boston from CARET