- JET PROFILER FOR MYSQL ARCHIVE
- JET PROFILER FOR MYSQL PROFESSIONAL
defragment tables, rebuild indexes, do table maintenance.with Linux, “swappiness” (bypass OS filecache for innodb data files, innodb_flush_method=O_DIRECT if possible (this is also OS specific)) check SHOW STATUS and SHOW VARIABLES (GLOBAL|SESSION in 5.0 and up).key_buffer, unix cache (leave some RAM free), per-connection variables, innodb memory variables.
JET PROFILER FOR MYSQL PROFESSIONAL
Know that there are many consulting companies out there that can help, as well as MySQL’s Professional Services. Do you really need a windowing system on that server? Keep the database host as clean as possible. Use a clever key and ORDER BY instead of MAX. don’t be afraid of table joins, often they are faster than denormalization BLACKHOLE engine and replication is much faster than FEDERATED tables for things like logs. Utilize different storage engines on master/slave ie, if you need fulltext indexing on a table. InnoDB ALWAYS keeps the primary key as part of each index, so do not make the primary key very large, be careful of redundant columns in an index, and this can make the query faster. ALTER TABLE…ORDER BY can take chronological data and re-order it by a different field. enable and increase the query and buffer caches if appropriate. (self-join can speed up a query if 1st part finds the IDs and use it to fetch the rest) derived tables (subqueries in the FROM clause) can be useful for retrieving BLOBs w/out sorting them. separate text/blobs from metadata, don’t put text/blobs in results if you don’t need them. Use PROCEDURE ANALYSE() to help determine if you need less Optimize for data types, use consistent data types. JET PROFILER FOR MYSQL ARCHIVE
use MERGE tables ARCHIVE tables for logs.Know your storage engines and what performs best for your needs, know that different ones exist.use row-level instead of table-level locking for OLTP workloads.try to use shared lock, not exclusive lock. use optimistic locking, not pessimistic locking.isolate workloads don’t let administrative work interfere with customer performance.
ORDER BY and LIMIT work best with equalities and covered indexes. innodb_flush_commit=0 can help slave lag. no calculated comparisons - isolate indexed columns. avoid correlated subqueries and in select and where clause (try to avoid in). avoid wildcards at the start of LIKE queries. Use SQL_NO_CACHE when you are SELECTing frequently updated data or large sets of data. Don’t use ORDER BY RAND() if you have > ~2K records. LIMIT m,n may not be as fast as it sounds. Start thinking about Cluster *before* you need them Don’t use DISTINCT when you have or could use GROUP BY. Use Slow Query Log (always have it on!). Use EXPLAIN to profile the query execution plan. Be wary of lots of small quick queries if a longer query can be more efficient. Minimize traffic by fetching only what you need. Kaj (Most Excellent Obvious Facilitator) Index stuff. 
You can check the same tips from here.Here is very useful tips for all mysql DBA’s,Developers these tips are noted from MySQL Camp 2006 suggested by mysql community experts. It is a client/server implementation that consists of a server daemon (mysqld) and many different client programs/libraries. MySQL is a widely used and fast SQL database server.
0 kommentar(er)
