MediaTemple, MySQL GridContainer and MySQL Database queries

I have a question for you all who have experience/knowledge about MySQL and databases... etc...

I've recently been flagged by MediaTemple because supposedly my website is generating a lot of MySQL queries... I am suspicious that StockAPI is wholly responsible for this. I have performance settings on "Normal" and "None" for lifetime.

I have no idea how to read this... please find the MySQL report below...
- line 51 and line 10 are highlighted...

My question is... is this level of mySQL activity that large and significant?

Thing is.. if this level of activity keeps going I'll get charged $20 a month:
http://www.mediatemple.net/webhosting/gs/mysql-containers.htm

Any help or insight will be appreciated. Thanks!

I also clicked their "slow queries report and it said that in this database:

### 1 Query
### Total time: 2, Average time: 2
### Taking 2 seconds to complete
### Rows analyzed 0
SELECT u.*, s.* FROM users u INNER JOIN sessions s ON u.uid = s.uid WHERE s.sid = 'XXX';

### 1 Query
### Total time: 2, Average time: 2
### Taking 2 seconds to complete
### Rows analyzed 0
SELECT data, created, headers, expire FROM cache_filter WHERE cid = 'XXX';

Here is the MySQL report:

How to read the report:

Line 1 - Report Header

The first line of a report, the report header, has three pieces of information: the MySQL server version, the MySQL server uptime, and the server's current date and time.

The MySQL server version indicates what features the MySQL server does or does not have. The MySQL server uptime indicates how representative the report values are. The MySQL server uptime is important for assessing the report because the report values will be skewed and misleading if the MySQL server has not been running for at least a few hours. However, even a few hours may not be enough if, for example, the MySQL server has only been running for six hours in the middle of the night with almost no usage. Ideally, the MySQL server should be up for at least one day before using the mysql report. The report values will better represent the MySQL server the longer that the MySQL server is up.
Line 3-7 - Key Report

The first major report section of the mysql report is the Key report because keys (indexes) are the most important part of a MySQL server. Although the report cannot indicate if the MySQL server is well indexed or not, it can generally indicate how well the shared key buffer is being utilized. This report section only applies to the default shared key buffer for MyISAM tables; it currently does not look at other key buffers (created by the administrator, like hot and cold key buffers).
Line 4 - Buffer Used

The first question to ask of a MySQL server is: how much of the key buffer is being used? If it is not being used a lot, that is okay because MySQL only allocates system RAM for the key buffer when needed. That mean that if the key buffer size is set to 512M, MySQL does not automatically allocate 512M of system RAM when it starts. MySQL allocates up to 512M of system RAM when it needs to.

The fourth line, Buffer used, is supposed to indicate the maximum amount of key buffer MySQL has ever used at once. In actuality, MySQL may currently being using less or, strangely, more. MySQL calls this a "high water mark." This line is usually indicative of whether or not the key_buffer_size system variable is sufficiently large. If this line indicates that MySQL has used upwards of 80% to 90% of the key buffer, then key_buffer_size should be increased. Note, however, that this line will probably never indicate above 95% used because, as the MySQL documentation states, some of the shared key buffer is used for internal data structures which the mysql report cannot account for. Therefore, 95% used is practically 100% used.
Line 5 - Current

This line indicates how much key buffer MySQL is actually using right now. If the previous line is truly a high water mark, then this line should always be less than or equal to it, but this is not always the case. Whether this is a bug in MySQL or not is unknown. Regardless, this line in combination with the preceding line gives a good indication of whether or not key_buffer_size is set sufficiently large.
Line 6 - Write ratio

Indexes (keys) are inherently RAM-based. Their usefulness is due in part to the fact that they exist in RAM which is very quick to access instead of existing only on a hard disk which is very slow to access. However, MySQL inevitably must write and read indexes to and from a hard disk at times.

This line, Write hit, indicates the effectiveness of key writes. (Technically, it is the ratio of key writes to hard disk to key writes to RAM expressed as a percentage.) There is no standard value for key write hit. Key write hit will depend on what kind queries the MySQL server primarily executes. If MySQL primarily executes updates, inserts, etc., then the key write hit may be near 0% and this is acceptable. If MySQL primarily executes selects, then the key write hit may be 90% or more and this is acceptable too. However, a negative key write hit (i.e., less than zero percent) indicates that MySQL is more often writing keys to hard disk than RAM which is usually slow, undesirable, and unacceptable.

To best interpret the key write hit effectiveness, it is necessary to know how the MySQL server is primarily used. The DMS sub-report can help determine this.
Line 7 - Read ratio

More important than the key write hit is the key read hit. This line indicates the effectiveness of key reads. (Technically, it is the ratio of key reads from hard disk to key reads from RAM.) Key read hit should be no less than 99%. A lower percentage may indicate a problem. A low key hit percentage is usually caused by the key buffer being too small (indicated in the Key Report section above) which prevents MySQL from loading more indexes into RAM. When this happens, MySQL must revert to reading indexes from the hard disk which is terribly slow and completely negates the point of indexes.

It is common, however, for this value to be less than 99% within the first hour or two of starting (or restarting) MySQL. After an hour or two it should definitely be at least 99%.
Lines 9 - 26 - Questions Report

The second major report section, Questions, is the second most important because it shows a lot about what MySQL is busy doing and how busy it is doing all that it is doing. Questions includes SQL queries and MySQL protocol communications. A common concern is how many queries per second the MySQL server is executing, but this metric is actually very arbitrary when considered in the larger context. The larger context is all the other questions that MySQL is handling. This report provides the larger context.
Line 10 - Total

The first line is simply the total number of all questions that MySQL has answered� (first column) and the rate of those answers over the MySQL server uptime (second column). The rate is what is commonly referred to when people make statements such as, "My MySQL server averages one hundred queries a second." However, the real question is: of those one hundred, how many are really accomplishing something? The mysql report answers this question in the following lines.

(� A clarification on terms: questions are answered and queries are executed. The mysql report makes the distinction between questions and queries, especially in the Questions Report. Questions are every and any kind of request made to the MySQL server. This includes SQL queries but also MySQL-specific commands and protocol communications. Queries are only SQL queries: SELECT, UPDATE, etc.)
Lines 11 - 15 - Distribution of Total Queries (DTQ):

All questions can largely be divided into five categories: Data Manipulation Statements (DMS), query cache hits (QC Hits), COM_QUIT, all other Com_ commands, and Unknown. These five categories are indicated in the five lines 11 through 15 but their order is dynamic: The mysql report sorts them in descending order based on total number (first column).

This sub-report quickly indicates what MySQL is most busy doing. Ideally, MySQL should be most busy with DMS or QC Hits because these are the categories of questions that are really accomplishing something. COM_QUIT, Com_, and Unknown are necessary but should play only a minor role.

Before explaining each category further, it will be helpful to mention that the third column for this and other sub-reports in the Questions report shows the percentage of that line's total value to all questions (Total, line 10).

Data manipulation statements include: SELECT, INSERT, REPLACE, UPDATE, and DELETE. (Technically, there are others but the mysql report uses only these.) Basically, DMS is what one thinks of when thinking of MySQL doing something useful. Hence, DMS should be what MySQL is most busy doing. This category is expanded in more detail in the DMS sub-report later, lines 17 through 22.

QC Hits is the number of queries that MySQL has executed by retrieving the result set from the query cache instead of actually executing the query. Having a high percentage of QC Hits is coveted because returning result sets from the QC is very fast. However, it can be difficult to achieve a very effective QC cache for reasons explained in the Insrt:Prune and Hit:Insert Ratios section of the Query Cache Report.

COM_QUIT is a category which is written about in the article COM_QUIT and Questions. It is an unimportant category which can be ignored. It is included in the mysql report for completeness.

Com_ represents all the various commands that MySQL handles, usually protocol related. Ideally, this category should be low because when it is high it is like MySQL is spinning its wheels really fast but going nowhere. A high value for this category can indicate some weird problems which are discussed later in the Com_ sub-report (lines 23 through 26 usually).

Unknown is an inferred category. Ideally, the sum total of the preceding four categories should equal total questions, but they usually do not. This is because there are a few questions that MySQL handles and increments the total questions counter for but does not otherwise maintain a separate status value for.

This line is dynamic in that it can read "+Unknown" or "-Unknown." +Unknown means there are more total questions than the mysql report can account for. -Unknown means the mysql report counted less questions than total questions.

This category can vary greatly. On some servers it is near the top, but on most it is at the very bottom. It is better for it to be at the very bottom. Eventually, the nature of these unknowns will be discovered and the mysql report will account for them correctly.
Line 16 - Slow

Line 16 is very important: it indicates the number of slow queries that MySQL has executed. What constitutes "slow" is set by the system variable long_query_time which is 10 (seconds) by default. (Many people consider 10 seconds an eternity in database time. long_query_time is best set to 1.) Ideally, there should be zero slow queries, but usually there are a few. Generally, Slow as a percentage of total questions (third column) should be 0.05 or less. There can be a lot of slow queries (first column), but it is the percentage of all them compared to total that indicates a problem. This line also adds a fourth column: percentage of DMS questions. For Slow, zero is best, but this column is more useful in the DMS sub-report.
Lines 17 - 22 - DMS

The DMS sub-report, like the DTQ sub-report, is sorted in descending order of value (first column). This sub-report's 6 lines, 17 through 22, represent the data manipulation statements mentioned earlier (SELECT, INSERT, etc.). The first line (17) is the total of all these again (identical to line 11 in the DTQ sub-report).

This sub-report indicates what "kind" of MySQL server this is: is it SELECT heavy, or INSERT heavy, etc. MySQL servers tend to be SELECT heavy. Knowing what kind of MySQL server a server is helps orient one's thoughts and understanding about other the other values. For example, an INSERT heavy server should have a write ratio near 1.0. It will probably have high values regarding table locks. It would also be a candidate for InnoDB tables. A SELECT heavy server had better have a read ratio of zero and a very low table lock values. It maybe be using query caching. It will probably use MyISAM.
Lines 23 - 26 Com_

The Com_ sub-report. The contents of this sub-report vary from server to server because each line (default 3; more can be specified like --com 10) represents some Com_ status value which, in turn, usually represent some COM_ command in the MySQL protocol. Most of the names are intuitive, like Com_change_db.

This sub-report matters when Com_ in the DTQ sub-report is near the top because it indicates MySQL is busy doing "program things" instead of answering SQL queries.

For most servers, the Com_ sub-report indicates nothing weird, but it is good to check it from time to time.
Lines 28 - 36 - SELECT and Sort Report

The SELECT and Sort Report. It details the various Select_ status value which are described in the article MySQL Select and Sort Status Variables.

The most important lines are 29 and 31: Scan and Full join. Scan indicates how many SELECT statements resulted in a full table scan which is a slow process. Full join is like Scan except that it applies to tables being joined in a multi-table query. Such tables are joined by process of a full table scan, but in the context of a join, a table scan is even slower. Therefore, these two values should be as low as possible, but there is no real standard for "low" here. Some servers which are running really well have a relatively high percentage of Scan to all SELECT statements (third column).
Lines 38 - 45 - Query Cache Report

The Query Cache report is related to MySQL's query caching feature.
Line 39 - Memory usage

This first line of this report indicates how much of the query cache memory is being used. If it is at max capacity, this will probably also be reflected in the Prunes value below since queries in the QC are pruned when memory is low.
Line 40 - Block Fragmnt

Line 40, Block Fragmnt (Fragmentation), indicates a condition particular to the way the query cache functions. Quoting from the MySQL manual section 5.14.3.

Query Cache Configuration: The default value of query_cache_min_res_unit is 4KB. This should be adequate for most cases. ... If you have a lot of queries with small results, the default block size may lead to memory fragmentation, as indicated by a large number of free blocks. Fragmentation can force the query cache to prune (delete) queries from the cache due to lack of memory. In this case, you should decrease the value of query_cache_min_res_unit. The number of free blocks and queries removed due to pruning are given by the values of the Qcache_free_blocks and Qcache_lowmem_prunes status variables.

This value is a percentage of free QC blocks to total blocks. The higher the percentage, the more the QC memory is fragmented. 10% to 20% is about average.
Lines 41 - 42 Hits, and Inserts

Query cache Hits and Inserts are indicated on lines 41 and 42. Hits is the most important because it indicates how many SELECT statements were served from the cache, so the more the better. Inserts are better understood in terms of the ratio on line 44. Although, as mentioned earlier, a high rate of Prunes can be indicative of the QC size being too small, but not always.
Lines 43 - 44 - Insrt:Prune and Hit:Insert Ratios

The QC Insert:Prune ratio on line 44 is an indicator of QC volatility. In a highly stable QC, more queries will be inserted than are pruned. In a volatile QC, this ratio will be one-to-one or heavy on the prune side, indicating a kind of evacuation of queries from the QC. A stable QC is desirable because a stable QC implies that the cached queries are being used often. A volatile QC can indicate two things: one, the QC size is too small so MySQL has to keep pruning and inserting queries, or two, MySQL is trying to cache everything to a self-defeating end.

In the first case, simply increasing the QC size may help. This type of volatility may be further indicated by high block fragmentation and QC memory usage.

The second type of volatility is more common because MySQL does try to cache nearly everything it can when the QC is enabled with the default type 1. Type 1 means (quoting the MySQL manual): "Cache all query results except for those that begin with SELECT SQL_NO_CACHE." It seems, however, that SQL_NO_CACHE is rarely used. A better way to enable the QC is with type 2 "DEMAND": "Cache results only for queries that begin with SELECT SQL_CACHE." Demand caching requires more work for developers because they have to explicitly add SQL_CACHE to the queries that they want MySQL to cache, but the advantage is that they probably know what queries are good, stable cache candidates.

The other ratio is Hit:Insert. This ratio indicates QC effectiveness. Ideally, the MySQL server should insert a bunch of stable queries into the QC, then get a lot more hits on them. Therefore, this ratio should be heavy on the hit side if the QC is effective. If it is heavy on the insert side, then the QC is not really helping much and it is probably too volatile. Consider a Hit:Insert ratio of 1:1. This practically means that a cached result is only used once before it is replaced. This completely defeats the idea of a query cache. A worse ratio, like 0.34:1, indicates that some results are not even hit before they are pruned or replaced.
Lines 46 - 48 - Table Locks Report

The Table Locks report consists of two lines: the first, Waited, shows the number of table locks that MySQL had to wait to obtain, and the second, Immediate, shows the number of table locks MySQL obtained immediately. Waiting is almost always a bad thing in database terms, therefore, table locks waited should be as low as possible. What is most indicative of table locking problems is the third column of table locks waited: %Total of all table locks. The percentage of table locks that had to wait should be 10% or less. Higher percentage can indicate poor table/query indexing or slow queries.
Lines 50 - 52 - Tables Report

The Tables report is also two lines: the first, Open, shows how many tables are open right now (first column), of how many total possible (table cache; second column), and the percentage of table cache usage (third column). The second line, Opened, shows the total number of tables MySQL has ever opened and this value over the MySQL server uptime (second column).

Two things are important here: first is the table cache usage. It is not bad to have 100% table cache usage, but if it is close to 100%, then it may be beneficial to increase the table_cache system variable. Second, the rate of opening tables can also help determine if table_cache is too low. Generally, it is nice to have this value less than 1/s. However, a busy and well running MySQL server can, for example, be opening 7 tables/s and running at 100% table cache.
Lines 54 - 56 - Connections Report

Another two line report, the Connection report is practically identical to the Tables report and so it will not be explained again. If the max number of connections used is approaching 100% (first line, third column), the max_connections system variable might need to be increased. However, this is often misleading. One often sees MySQL servers with very high max_connection for no good reason. The default value is 100 and this works for even extremely busy, well-optimized servers. A connection to MySQL should last a fraction of a second, so even 100 connections goes a long way. If max connections is very high or slowly rising over time, the problem might be elsewhere, like slow queries, poor indexing, or even slow DNS resolution. Before setting max_connections above 100, discover the fundamental reason why 100 connections at once is not enough and verify that it is a legitimate need and not actually another problem that manifests itself as too few connections.

Regarding the number of connections per second, this value can be rather high. In fact, if it is high and everything else is working well, it is usually a good indication. Upwards of 10 connections/s is possible, but most server's connections/s are well under 5/s.
Lines 58 - 61 - Created Temp Report

MySQL can create temporary tables on hard disk, in RAM, and temporary files. Each of these three corresponds to the three lines of the Created Temp report. These value are relative; there is no standard for them. Since temporary tables on hard disk are the slowest (indicated by the first line, Disk table), it is best if this value is the lowest of the three. A temporary table is created on hard disk only if it cannot fit into a temporary table in RAM which is limited by the system variable tmp_table_size. Temporary tables in RAM (indicated by the second line, Table) and temporary files are so common that these value are completely relative to one's MySQL server.
Lines 63 - 75 - Threads, Aborted, Bytes Reports

The three reports, Threads, Aborted, and Bytes, are the least important. Therefore, they are not discussed in detail and they are mostly self-evident.

There is one line of particular interest: line 66 of the Threads report, Cache and specifically %Hit. Every connection to MySQL is handled by a separate thread. At startup, MySQL creates a few threads and keeps a few in a thread cache so that it does not have to constantly keep killing and creating threads. Although threads are not expensive to make, it is not good to "thread thrash." When the number of connections/s to MySQL exceeds the thread cache (set by the system variable thread_cache_size) MySQL starts to thread thrash: it goes crazy creating threads to keep up with the demand for new connections. When this happens, the thread cache hit rate drops.