MySQL
MySQL is a relational database management system. The program runs as a server providing multi-user access to a number of databases. MySQL is popular for web applications and acts as the database component. Its popularity for use with web applications is closely tied to the popularity of PHP and Ruby on Rails, which are often combined with MySQL. MySQL is written in C and C++ and it works on many different system platforms.
MySQL Quiz
In the following linkד you will find community generated MySQL questions and MySQL quizzes:
MySQL Performance
MySQL Performance Optimization becomes more important when the system is very heavily loaded. It is very common for a problem to occur only when the database is heavily used (The database experiencing load of inserts, deletes and retrievals. In most cases, MySQL Performance Optimization problems turn out to be due to issues of basic database design or problems with the operating system or libraries. Most of the time, these problems would be much easier to fix if the systems were not already in production. MySQL Performance Optimization is a complex task because it requires understanding of the entire system to be optimized. Although it may be possible to perform some local optimizations with little knowledge of your system or application, the more optimal you want your system to become, the more you must know about it.Benchmarking MySQL performance
As a start it is a good practice to benchmark your application and database, to find out where the bottlenecks are. After fixing some bottlenecks, don't rest and proceed to identify the next bottleneck. Even if the overall performance for your application currently is acceptable, you should at least make a plan for each bottleneck and decide how to solve it as in the growth of your system the soft bottlenecks will be hard. Sometimes, you will find out that optimizing your application code will save you a lot of load on your MySQL database ( such as replacing 10000 little queries done in a loop by one lightly performing query that takes less than 1 percentage time of the over all 10000 little queries). To avoid MySQL Performance problems, you should put some effort into benchmarking your whole application under the worst possible scenarios and load. to achieve that you can use:- The MySQL Benchmark Suite for MySQL database best configuration
- Build your own Benchmark for MySQL database best configuration and for your slow queries
MySQL performance optimization sections
MySQL performance optimization can be divided in two major sub-categories: Optimizing MySQL queries and Optimizing the MySQL Server.- Optimizing MySQL queries: Optimize the MySQL queries by changing them, changing the database structure (like adding new indexes) etc.
- Optimizing MySQL Server: changing the setting of the OS and the MySQL configuration file (my.cnf).
Optimizing MySQL Queries and Indexes Quiz
The importance of query and index tuning
If you are serious about MySQL performance, and you should, then you must be serious about query optimization. Query and index optimization are the first and most important task of MySQL performance. Optimizing the queries and indexes can make a crawling site (loses visitors) to a rapid respond site. Remember, however, that there are always additional ways to make the system even faster, although they may require increasing effort to achieve.Indexes
An index is usually a B-tree which speeds up selective comparison considerably. You should probably create indexes for any field on which you are selecting, grouping, ordering, or joining. Obviously each index requires space proportional to the number of rows in your table, so too many indexes winds up taking more memory. You also incur a performance hit on write operations, since every write now requires that the corresponding index be updated. Badly defined or non-existent indexes are one of the primary reasons for poor performance, and fixing these can often lead to phenomenal improvementsDatabase normalization
Database normalization is a technique for designing relational database tables to minimize duplication of information. This prevent certain types of logical or structural problems, namely data anomalies. proper normalization results in a minimization of redundant data. Fundamentally that means a smaller memory space at the cost of performance (the usual performance/memory tradeoff found everywhere in computer science). The best approach is to normalize first and denormalize where performance demands it. Your schema will be more logical and you won't be optimizing prematurely.Optimizing queries
When writing or examine a query you should not think in terms of data and operations on data but you must think of sets of data and relationships among those sets. Most of the time, this change in perception will lead to well performance optimized queries. The very first thing you should do in order to catch problematic queries is turn on the MySQL slow query log. The slow query log can be used to find queries that take a long time to execute and are therefore candidates for optimization. The most important tool for optimizing a query is the EXPLAIN statement. The EXPLAIN statement gives you a way to obtain information about how MySQL executes a SELECT statement. When you precede a SELECT statement with the keyword EXPLAIN, MySQL displays information from the optimizer about the query execution plan. That is, MySQL explains how it would process the SELECT, including information about how tables are joined and in which order. Most of the time the MySQL optimizer pick the right way and the right indexes in order to execute a query. But, sometimes it wrongs. By using the EXPLAIN statement you can discover these cases and give MySQL index hints about which indexes to use. please note that MySQL gets it right most of the time and when it doesn't it is usually because of a bad scheme or poorly written query. You should also learn about others MySQL internal tools like show status and show processlist. These will tell you what resources are being spent where, and what side effects your queries are having, e.g., whether your heinous triple-join subselect query is sorting in memory or on disk.Optimizing MySQL Server Quiz
Building an Optimized Platform Make your MySQL Database Faster
An Important element in making MySQL faster is checking the machine design, hardware and the relevance of other CPU's consumers. You must deploy MySQL on the right hardware. In most cases, system bottlenecks arise from these sources:- Memory bandwidth. MySQL is a big memory consumer, so install as much RAM as you can afford.
- Disk operations. First, disk seeks are relatively slow. Second, it takes time to read and write (disk reading and writing). The way to optimize seek time and delivery time is to distribute the data onto multiple disks. Of course, a good application design will use less disk seeks.
- CPU cycles. When we have the data in main memory, we need to process it to get our result. Your system must have sufficient CPU.
MySQL binaries
The official MySQL binaries are configured and built with varius compilers and options and they come extremely well tuned (they put a great deal of effort into ensuring that the binaries are built with the best possible options). If, for some reason, the right MySQL binaries are not available for your platform or if it wont works for you, you can download MySQL source and compile it yourself.MySQL Server tuning
MySQL provides a configuration file located in /etc/my.cnf. By default, MySQL is configured to be extremely resource-friendly, but that can be a performance killer. By my.cnf you can set all of the memory, table, and connection limits as well as a host of other options. We suggest you get acquainted with the my.cnf file as well as the tuning parameters within it. If you configure MySQL Server so it uses too small amount of memory it will likey perform suboptimally. If you however configure it so it consumes too much memory it may be crashing , failing to execute queries or make operation to swap seriously slowing down. What settings to change and the values of those parameters are specific to your hardware and workload, so you’ll need to do some research to find your own optimal settings.More Tips for Optimizing and Tuning Your MySQL Database Server
- Enable Slow Query Log: The very first thing you should do is turn on the MySQL slow query log. The slow query log is used to find queries that take a long time to execute and are therefore candidates for optimization. There are varius applications that summarize the queries that appear in the log and show more statistics.
- Enable Query Cache: Be sure to enable the query cache. Make sure that you’re code takes advantage of that cache. The cache for a table is invalidated every time the table is written to
- Mix MySQL Storage Engines: Mix MySQL storage engines on a per-table basis. You can use MyISAM tables for heavily read tables or for tables that require FULLTEXT search. Use InnoDB tables if you need row level locking and transactions.
- Replicate your Databases to Multiple Machines: Send all writes to the single master and distribute the reads to as many slaves as you need to keep up with demand.
- Shard your MySQL Database: If your server is limping because of your tremendous growing data and you expect your data is in growth, you should consider Sharding. Sharding is horizontally partitioning (by row). You should split (or partition) the data on the original single server into equal portions distributed amongst new set of servers (shards). This way you can parallelize query load across them.
