MySQL Performance

I'm trying to tune a mysql database, recently installed on a new server.
Performance was just fine when this same database on the old server, and the new hardware is nearly identical to the old, but the performance on the new server sucks.

I've set query_cache_size = 0, and I'm running a sample query that does a select count(*) on a table with 4M records, with a where clause that includes indexed fields. The result is <8k.

Here's my problem. When I run the mysql command-line tool, on the server machine, and enter the query directly, it runs in 0.11 seconds. When I pipe the query through the mysql command-line tool, it runs in 7.338 seconds. (I had originally planned on scripting a number of such queries using the Unix time utility to track performance, which is why I was working on piping code through mysql, instead of working in it. Had I not done this, I'd not have noticed the problem.)

So, the very same tool, reading the same sql from two different sources, gives a 7100% difference in performance. More than a little odd.

That got me wondering whether the delay was in connecting to the database. But that's not it. Mysql started without a piped command starts instantaneously. And if I pipe a simple 'SELECT COUNT(*) FROM ...' into mysql, without a where clause, it runs in 0.01 seconds. So its not startup.

Now the really odd bit. When I run 'EXPLAIN SELECT ...' with my original query, it displays the key I'd expected it to use as a possible key, and as the key, and shows rows=15k.

BUT!

If I pipe the string 'EXPLAIN SELECT ...' with same query through mysql, it shows the key I had expected to use as a possible key, but it shows NULL for the key it's actually using, and it shows rows=4M.

Why the difference?

How could I have managed to configure the query optimizer so that it uses indexes sometimes, but not others?

Where should I be looking to figure out how to fix this?

What is the most efficient way to create a temporary table from single rows retrieved from a large number of tables?

My DB has:
1) one master_table containing 10,000+ rows with the unique id and other metadata about child tables
2) 10,000+ child tables named after this id (e.g. "table_5")
3) each child table stores a series of data retrieved on a daily basis (i.e. 1 row per day, timestamped)

Now, I need to extract temporal snapshots from the whole database (or a portion of it), describing the state of the system at a given date.

More precisely, I need to:
a) SELECT a subset of table ids from master_table matching some criteria
b) look up a single row in all of these tables matching a given date (e.g. WHERE `ts` LIKE '2008-10-10%')
c) aggregate all these rows in a temporary table (e.g. master_table_20081010)

Can any of you suggest the best way to do this in MySQL?

I've been fighting with queries on a relatively large table for the past few days and have come across behavior that I'm hoping somebody can explain to me.

The table in question is:
CREATE TABLE `t` (
`d` date NOT NULL default '0000-00-00',
`src` char(3) NOT NULL default '',
`dst` char(3) NOT NULL default '',
`tot` decimal(10,1) default NULL,
PRIMARY KEY (`d`,`src`,`dst`,`tot`),
) ENGINE=MyISAM DEFAULT CHARSET=latin1

The table has approx. 21 million rows. Dates are always stored in YYYY-MM-01 format (dates are only applicable to months).

Here is the query:
SELECT * FROM t
WHERE
d BETWEEN "2006-01-01" AND "2006-12-01"
# d IN (
# "2006-01-01","2006-02-01","2006-03-01","2006-04-01","2006-05-01","2006-06-01",
# "2006-07-01","2006-08-01","2006-09-01","2006-10-01","2006-11-01","2006-12-01" )
AND src IN ( "aaa","bbb", ... ,"zzz" )
AND dst IN ( "aaa","bbb", ... ,"zzz" )
ORDER BY tot
LIMIT 10

A pretty simple query all in all - just returning the top 10 'tot' rows for a given date range, 'src' array and 'dst' array.

Now, for the confusion. There are large differences whether you are using BETWEEN or IN() for the date range (see the two methods in the query above - one is commented out).

When using BETWEEN the profiler says the majority of time is spent in "Sorting Result"

When using WHERE IN() the profiler says the majority of time is spent in "statistics" and "preparing"

So, if you have small arrays for 'src' and 'dst' the WHERE IN() method is much faster then using BETWEEN(). In fact BETWEEN() seems to have a constant time associated with it about about 14 seconds on my system all spent in "Sorting Data" no matter the size of the arrays.

However, once your arrays start getting large WHERE IN() becomes extremely slow and BETWEEN() becomes the much faster option.

My question is why? How is the optimizer treating these two queries so differently when the actually equate to the exact same thing?

Hello,

I would like to perform Update Statistics on my MySQL InnoDB as we can do on Oracle with DBMS_STATS.GATHER_SCHEMA_STATS or MS SQL with j'utilise sp_Updatestats.

Does exist an equivalent for this method or approach

Thank you for your help.

Hi, I need to do a lookup of a lot of values in a large table. I first tried using the IN operator, but due to some bug in MySql (http://bugs.mysql.com/bug.php?id=20932) that is WAY too slow. So now I put the values I need to look up into a temp table and join that with the data table:

create temporary table tmp_lsh03 (value INT NOT NULL) ENGINE=MEMORY;

INSERT into tmp_lsh03 (value) VALUES (374129778),(374128754),(2521613426),(2521678946),(2521678962),(2521810034),(2521744498),(2521613426),(2521678962),(2521677938),(2521677938),(2488123504),(340377712),(340377696),(877248608),(3024732256),(3024732274),(3058286706),(2521415794),(2521416818),(2521547890),(2521547874),(2521482338),(2521482354),(2521482354),(2521351282),(2529739890),(382256242),(382255218),(382255474),(382255458),(382255458),(382257504),(382257440),(113821984),(113297696),(109103392),(109103392),(109103392),(109103392),(75548960),(75548928),(75548928),(209766656),(1283508480),(1283508480),(1283508480),(1283508480),(1317062912),(1853933824),(1853933824),(1853934848),(1853934848),(1854459136),(1854467328),(1854467328),(1854467328),(1856564480),(1856564496),(1856564496),(1856564496),(1856564496),(1856564480),(1856564480),(1854467328),(1854459136),(1317588224),(1317587200),(1317587200),(1317570816),(1317570816),(243828992),(243845408),(512280864),(516475232),(516476256),(516476256),(516475232),(1053346144),(1053346144),(1053346144),(1053346160),(1019791664),(1019791664),(751356192),(751355936),(751421472),(2898905120),(2898905120),(3167340640),(3167602784),(3159214176),(3159212128),(3159212144),(1011728496),(1011728496),(1011728480),(1011728480),(1011730528),(1011738720),(3159222368),(3159222336),(3155028032),(4228769856),(4232964096),(4232955904),(3159197696),(3024979970),(3024980066),(3024980066),(3024980066),(3058550882),(3058550882),(3058548834),(2521677938),(2521809010);

SELECT d.chunk_id, d.location FROM tmp_lsh03 t JOIN descriptors d ON t.value=d.lsh03;
+---------+----------+
| chunk_id | location |
+---------+----------+
| 1080 | 1151 |
| 1080 | 9119 |
.
.
.

| 1098 | 45 |
| 1238 | 6910 |
| 1238 | 6916 |
| 599 | 14484 |
| 779 | 3232 |
| 779 | 19067 |
| 779 | 19071 |
| 859 | 13802 |
+---------+----------+
7190 rows in set (3.72 sec)

A time profile says that all the time is spent "sending data".

Doing an EXPLAIN shows the following:

EXPLAIN SELECT d.chunk_id, d.location FROM tmp_lsh03 t JOIN descriptors d ON t.value=d.lsh03;
+----+-------------+-------+------+---------------+-----------+---------+---------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-----------+---------+---------------------------+------+--------------------------+
| 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 116 | |
| 1 | SIMPLE | d | ref | lsh03ndex | lsh03ndex | 4 | db3.t.value | 2 | Using where; Using index |
+----+-------------+-------+------+---------------+-----------+---------+---------------------------+------+--------------------------+
2 rows in set (0.06 sec)

This shows that the BTREE index on the big descriptors table is actually being used.

The descriptors table is MyIsam and has the lsh03ndex key used defined as:
KEY `lsh03ndex` (`lsh03`,`chunk_id`,`location`)

In other words all fields used by the query is contained in the index, so minimal I/O should be needed.

Any ideas on how to improve this performance? The query is very critical to my application, so any suggestions may be useful.

This is a re-post of my original here.

I am very curious to know how to write the following join query for MySQL so it will perform as well as PostgreSQL. I never used PostgreSQL before now. I like MySQL and I always use it. However I was disappointed by its performance on this query and I installed PostgreSQL to compare. The purpose of this query is to map ip addresses to countries. On my CentOS 5 machine, MySQL 5.0.45 takes 10 mins 45 seconds, PostgreSQL 8.1.11 takes 0 mins 1.3 seconds. Yes MySQL is 496 times slower. I'm sure there must be a way to hint MySQL to run faster. Here is the query:

mysql> select range.id_country from address join range on address.address between range.begin_num and range.end_num; mysql> describe address; +---------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+------------------+------+-----+---------+-------+ | address | int(10) unsigned | YES | | NULL | | +---------+------------------+------+-----+---------+-------+ mysql> describe range; +-------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------------+------+-----+---------+-------+ | begin_num | int(10) unsigned | NO | PRI | | | | end_num | int(10) unsigned | YES | UNI | NULL | | | id_country | tinyint(3) unsigned | YES | MUL | NULL | | +-------------+---------------------+------+-----+---------+-------+ Both tables are MyISAM type. Table `address` is 2124 rows (all distinct). Table `range` is 105920 rows (all distinct).

The best answer I found so far is here. Steinbrink gives a way to write it as a join on subquery that my MySQL finished in 6 min 42 sec (63% the time of the simple join version). That is still 310 times slower than PostgreSQL. Too slow!

Actually there is a small error in the SQL at that url:
ORDER BY ip_address DESCshould be:
ORDER BY start DESC Here is MySQL's explanation of the original query:
mysql> explain select range.id_country from address join range on address.address between range.begin_num and range.end_num; +----+-------------+---------+------+-----------------+------+---------+------+--------+------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+-----------------+------+---------+------+--------+------------------------------------------------+ | 1 | SIMPLE | address | ALL | NULL | NULL | NULL | NULL | 2124 | | | 1 | SIMPLE | range | ALL | PRIMARY,end_num | NULL | NULL | NULL | 105920 | Range checked for each record (index map: 0x7) | +----+-------------+---------+------+-----------------+------+---------+------+--------+------------------------------------------------+ Here is PostgreSQL's explanation of the original query:
postgresql# explain select range.id_country from address join range on address.address between range.begin_num and range.end_num; QUERY PLAN ----------------------------------------------------------------------------------------------------------------- Nested Loop (cost=5.72..7061709.90 rows=83990942 width=2) -> Seq Scan on range (cost=0.00..3316.47 rows=185547 width=18) -> Bitmap Heap Scan on address (cost=5.72..31.25 rows=453 width=8) Recheck Cond: ((address.address >= "outer".begin_num) AND (address.address <= "outer".end_num)) -> Bitmap Index Scan on addresses_pkey (cost=0.00..5.72 rows=453 width=0) Index Cond: ((address.address >= "outer".begin_num) AND (address.address <= "outer".end_num)) Here is MySQL's explanation showing that a simple query can use the index on begin_num in a "range" type query plan:
mysql> explain select id_country from range where 123123123 between begin_num and end_num; +----+-------------+---------+-------+-----------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+-------+-----------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | range | range | PRIMARY,end_num | PRIMARY | 4 | NULL | 35 | Using where | +----+-------------+---------+-------+-----------------+---------+---------+------+------+-------------+ Others encountered this problem before.
References:

[*] MySQL Forums :: Optimizer :: How to optimize a range (best answer I found but not good enough)
[*] MySQL Performance Blog: Optimize IP Range Join / Daniweb: Optimize IP Range Join - MySQL
[*] MySQL Performance Blog: Range Optimization
[*] MySQL :: inner join on range criteria: unable to use index?
[*] Sergey Petrunia's blog Use of join buffer is now visible in EXPLAIN
[*] MySQL :: MySQL 5.0 Reference Manual :: 7.2.5 Range Optimization
[*] MySQL Bugs: #26963: Incorrect query results with CONST join compared to RANGE or ALL
[*] MySQL Bugs: #9693: bug in trying to join a value on a range (using indexes)

I'm reading through another developer's code today and found this query that runs on our AJAX suggest box (whenever anyone types in our search box). This runs on every key press to generate a list of terms that the user might be searching for.

As far as I know it grew from something simple into this monster because this is more accurate, but takes 0.2s to run.

Each part of the query is given different weighting to allow the most relevant to be displayed first.

Is there an easier/cleaner way to do this? At the moment being that there's so many UNIONS MySQL cannot sort effectively over the whole result set returned (derived tables etc).

TIA

SELECT suggestion, type, url, popularity FROM ( SELECT searchterm AS suggestion, popularity, 3 AS pop2, type, url FROM searchterms WHERE searchterm = 'am' AND type = 0 UNION SELECT searchterm AS suggestion, popularity, 1 AS pop2, type, url FROM searchterms WHERE searchterm LIKE 'am%' AND type = 0 UNION SELECT searchterm AS suggestion, popularity, 0 AS pop2, type, url FROM searchterms WHERE searchterm LIKE '%am%' AND type = 0 ORDER BY pop2 DESC, popularity DESC, suggestion ASC LIMIT 0,10 ) r1 UNION SELECT suggestion, type, url, popularity FROM ( SELECT searchterm AS suggestion, popularity, 3 AS pop2, type, url FROM searchterms WHERE searchterm = 'am' AND type = 1 UNION SELECT searchterm AS suggestion, popularity, 1 AS pop2, type, url FROM searchterms WHERE searchterm LIKE 'am%' AND type = 1 UNION SELECT searchterm AS suggestion, popularity, 0 AS pop2, type, url FROM searchterms WHERE searchterm REGEXP '[[:<:]]am' AND type = 1 ORDER BY pop2 DESC, popularity DESC, suggestion ASC LIMIT 0,4 ) r1 UNION SELECT suggestion, type, url, popularity FROM ( SELECT searchterm AS suggestion, popularity, 3 AS pop2, type, url FROM searchterms WHERE searchterm = 'am' AND type = 2 UNION SELECT searchterm AS suggestion, popularity, 1 AS pop2, type, url FROM searchterms WHERE searchterm LIKE 'am%' AND type = 2 UNION SELECT searchterm AS suggestion, popularity, 0 AS pop2, type, url FROM searchterms WHERE searchterm REGEXP '[[:<:]]am' AND type = 2 ORDER BY pop2 DESC, popularity DESC, suggestion ASC LIMIT 0,4 ) r1 UNION SELECT suggestion, type, url, popularity FROM ( SELECT searchterm AS suggestion, popularity, 0 AS pop2, type, url FROM searchterms WHERE searchterm REGEXP '[[:<:]]am[[:>:]]' AND type = 3 ORDER BY popularity DESC, suggestion ASC LIMIT 0,1 ) r1

Hi:

We had a dynamic HTML social networking website developed for us. With only a couple of objects in the database, the website is extremely slow.

Taking a closer look, we saw complex views like the one below (which provides a view of the most commonly accessed attributes of a user in the website) being used as the basis for obtaining basic user data e.g. age, photograph, marital status, etc. This view was queried over 5 times to produce the content of some HTML pages.

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `socialprofileView` AS select distinct sql_no_cache `tbl_users`.`usr_userid` AS `usr_userid`,`tbl_users`.`usr_type` AS `usr_type`,`tbl_users`.`usr_joindate` AS `usr_joindate`,`tbl_users`.`usr_status` AS `usr_status`,`tbl_users`.`usr_emailid` AS `usr_emailid`,`soc_profilebasic`.`pb_userid` AS `pb_userid`,`soc_profilename`.`pn_userid` AS `pn_userid`,`soc_profileinterest`.`pi_userid` AS `pi_userid`,`soc_profilebasic`.`pb_gender` AS `pb_gender`,((year(curdate()) - year(`soc_profilebasic`.`pb_birthdate`)) - (right(curdate(),5) < right(`soc_profilebasic`.`pb_birthdate`,5))) AS `age`,`tbl_users`.`usr_lastlogin` AS `usr_lastlogin`,`tbl_users`.`usr_basicprofileviews` AS `usr_basicprofileviews`,`soc_profilebasic`.`pb_occupation` AS `pb_occupation`,`soc_profilebasic`.`pb_lookingfor` AS `pb_lookingfor`,`soc_profilebasic`.`pb_telephone` AS `pb_telephone`,`soc_profilebasic`.`pb_companyname` AS `pb_companyname`,`soc_profilebasic`.`pb_address` AS `pb_address`,`soc_profilebasic`.`pb_industry` AS `pb_industry`,`soc_profilename`.`pn_firstname` AS `pn_firstname`,`soc_profilename`.`pn_lastname` AS `pn_lastname`,`soc_profilebasic`.`pb_city` AS `pb_city`,`soc_profilename`.`pn_naunaname` AS `pn_naunaname`,`soc_profilename`.`pn_naunaurl` AS `pn_naunaurl`,`soc_profileinterest`.`pi_caption` AS `pi_caption`,(select sql_no_cache `soc_userimages`.`img_imgid` AS `img_imgid` from `soc_userimages` where ((`soc_userimages`.`img_userid` = `tbl_users`.`usr_userid`) and (`soc_userimages`.`img_status` = 1))) AS `userimage`,(select sql_no_cache `tbl_state`.`sta_name` AS `sta_name` from `tbl_state` where (`tbl_state`.`sta_abbrv` = `soc_profilebasic`.`pb_state`) group by `tbl_state`.`sta_abbrv`) AS `state`,(select sql_no_cache `tbl_country`.`cou_name` AS `cou_name` from `tbl_country` where (`tbl_country`.`cou_code` = `soc_profilebasic`.`pb_country`)) AS `country`,`soc_profilebackground`.`pb_maritualstate` AS `pb_maritualstate` from ((((`tbl_users` left join `soc_profilebasic` on((`soc_profilebasic`.`pb_userid` = `tbl_users`.`usr_userid`))) left join `soc_profilename` on((`soc_profilename`.`pn_userid` = `soc_profilebasic`.`pb_userid`))) left join `soc_profileinterest` on((`soc_profileinterest`.`pi_userid` = `soc_profilename`.`pn_userid`))) left join `soc_profilebackground` on((`soc_profilebackground`.`pb_userid` = `tbl_users`.`usr_userid`)));

Altogether, around 360 different queries access this view alone. These are examples of such queries:

$rs_postedbyname=$dbconn->Execute("SELECT pn_naunaname FROM socialprofileView WHERE usr_userid='".$puserid."' ");

$rs_userinfo=$dbconn->Execute("SELECT usr_joindate,pn_naunaname,userimage,state,country,pb_city FROM socialprofileView WHERE usr_userid='".$rslist->fields['fo_userid']."' ");

$rs_location=$dbconn->Execute('SELECT usr_joindate,pb_city,state,country FROM socialprofileView WHERE usr_userid='.$USERID.'' );

There are around 30 of such views and we are at our wits end on how to improve the query performance of these views. Changes to each query will be a time consuming effort.

Some type of caching on each view would be an ideal solution if it does not require caching the result of each query - they were not created with caching in mind.

Your help is appreciated.

Thank you

My application becomes very slow (10 times slower) when the database is in a different machine. For instance a program in Java that takes about 4 minutes connecting to a localhost MySQL database, takes 45 minutes if the MySQL database is in the machine "next to it".

The only change is the connection url, from localhost to the IP of the other machine.
The machines have the same hardware and are in the same LAN.

Which parameters can I fine tune in MySQL to improve performance?

Thanks in advance
------------------------
OS: Linux Suse 10.3 (32 bits)
MySQL version: Server version: 5.0.51a SUSE MySQL RPM
System Memory: 3 GB

On Sun Solaris, can we specify o_direct option to log file, just for log file not for data files?

If I have multiple databases, and all tables created with innodb storage engine, is all transactions share the same innodb log files? Does each db use its own log files?

Hello everyone, I really hope someone can help me out with this.

I have a large multi-table mysql database (each table has between 10 million and 200 million rows). All the tables have a personID column which they can be joined on. There may or may not be an entry in each table for each personID. Some tables have one entry per personID. In these the personID is primary key. Other tables can have multiple entries for one personID in which case personID has a non-unique index. Most tables are innodb but a few (where writes are infrequent) are MyISAM.

The user fills in a few boxes of their choice in a search form and the program builds a query at runtime based on what fields the user filled in, this can be a join on up to eight tables.

Generally this works well in most cases, where at least one of the tables being queried returns only a small number of matches so the number of rows to look at in the remaining tables is small.

My problem is queries which end up looking like this:

SELECT DISTINCT personID FROM name,country WHERE name.first='Dave' AND country.id='US' AND country.personID=name.personID LIMIT 1000;

If either 'Dave' was an unusual name, OR he lived in a small country, this query would work well, but because there are millions of Daves and millions of people in the US, mysql then takes several minutes using a huge temporary table to join these results together.

The two tables cannot be merged because each person can be associated with multiple countries (or none) but only has one name. The DISTINCT is needed because otherwise I get duplicate entries where a person has several associations with the same country.

Any solution needs to be very generic rather than specific to this query, because the query has to be machine generated and the combination of tables in the query can vary dramatically. It also needs not to slow down all the other queries which run plenty fast enough.

Anyone got any ideas on this? I'm stumped on this one!

Many thanks for your help,

Chris

Hey folks,

New user here, so I'm not sure if I have the proper forum to ask this question, so my apologies in advance if this belongs elsewhere.

Essentially, here's my scenario. We have a web app that functions as a warehouse management system for locations of products, quantities, etc. A mini ERP basically, but far more simplistic.

I have 3 tables with the following structure: (there are more fields, only the relevant ones are shown)

product:
PKProductID, FKWarehouseLocationID

productgroup:
SKU, FKProductID

Warehouse:
PKWarehouseLocationID, WareHouseLocationName

The foreign key FKProductID from productgroup references the primary key PKProductID in the product table, and the Foreign key FKWarehouseLocationID in the product table references the primary key PKWarehouseLocationID in the warehouse table.

Now, I'm using this query to update individual warehouse locations for a given sku:

UPDATE warehouse, product, productgroup PG
JOIN product p ON (PG.`FKProductID` = p.`PKProductID`)
JOIN warehouse w ON (p.`FKWareHouseLocationID` = w.`PKWareHouseLocationID`)
set p.`FKWareHouseLocationID` = w.`PKWareHouseLocationID`
WHERE PG.`SKU` = 'enter_sku_here'
AND w.`WareHouseLocationName` = 'enter_location_of_product_here'

That query works, but it's ugly; it takes a good 12-13 seconds per record to update. On a per record basis, that's not a *huge* issue, but we've gotten to the point where there's too many changes to efficiently do singly.

So I created a temp table I use for importing new sheets of warehouse locations with the structure of:

temptable:
SKU, WareHouseLocation

I adjusted the above query to basically update locations based on the values entered into the temptable. the new query looks like this:

UPDATE warehouse, product, temptable tt, productgroup PG
JOIN product p ON (PG.`FKProductID` = p.`PKProductID`)
JOIN warehouse w ON (p.`FKWareHouseLocationID` = w.`PKWareHouseLocationID`)
set p.`FKWareHouseLocationID` = w.`PKWareHouseLocationID`
WHERE PG.`SKU` = 'tt.sku'
AND w.`WareHouseLocationName` = 'tt.WareHouseLocation'

Only problem is, as you can see from my performance numbers, 12-13 seconds per record when you have a sheet of 1,000 rows just isn't viable.

I'm not an SQL wizard by any stretch, so I'm sure there's got to be a better way to accomplish this. Can anyone suggest a better way to write queries that involve multiple, large tables like these? Even a point in the right direction would be appreciated.

Hi,

Running MySQL 4, experiencing high loads on a webserver operating a few blog based sites. MySQL is regularly using 100-200% CPU usage, the majority of the queries are "Copying to tmp table...". I see quite a few of these when running show processlist;

I have changed/increased several settings to try improve performance, but not seeing any change in the loads. My settings:

set-variable = key_buffer_size=64M
set-variable = max_connections=150
set-variable = key_buffer=256M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer=2M
set-variable = table_cache=64M
set-variable = thread_cache_size=256
set-variable = wait_timeout=100
set-variable = connect_timeout=600
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=100
set-variable = thread_concurrency=8
set-variable = tmp_table_size=1024M
set-variable = max_heap_table_size=1024M
set-variable = query_cache_size=64M
set-variable = read_buffer_size=8M
set-variable = long_query_time=5

thanks

I am getting some strange differences between query times in my development and production environment. I am using the exact same data in both environments and no one is yet using the production environment. Most queries in the development environment take 0-1 milliseconds, while in the production they take 1-50 milliseconds. The strange thing is the queries that take the longest are simple ones often returning just one row, and they are consistently slow. Moreover, there are queries that are more complex and use multiple joins that are much faster then these simple ones. These simple queries are accessing the the row based on the primary key, for instance:

select * from table where id = 20; #Where 'id' is the primary key

I have made sure as many settings as possible are matched, including the query cache. All the tables are InnoDB in both environments. Has anyone here experienced anything similar before? Do you have any ideas?

Hi,
I have a session tracking table that uses varchar(40) as the session_id , it being a session tracking, it will get called on every page load. I'm trying to optimize it by having another column which is the crc32() of the session_id, this of course introduces alot of collisions given enough rows.

1st query is to SELECT * WHERE hashed_session_id=hashed_session_id AND session_id=session_id
I query both , the first part to get it fast, second part is to ensure uniqueness.
this gives me



id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE ci_sessions const PRIMARY PRIMARY 46 const,const 1


2nd query
I query just by the hashed_session_id and to ensure uniqueness I check on the application on the session_id, which gives me, voila!

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE ci_sessions ref PRIMARY PRIMARY 4 const 1 Using where

Now the big question is, is the 2nd query better? Is my approach flawed?
comments?

Hey people,

I was wondering, i need to store a string between 0 and 500 characters, what do you recomment, 2 varchar field (where i split the string in 2) or 1 text field. i know text fields are a lot slower, but because i need it to do searchs on those fields with %LIKE%, i was thinking this might be faster.

also, i know the contents of the string, so i know where i can decently split it in half without compromizing words.

side note: the final table is going to have 25k+ rows...

Ty for all good thoughts

Cheers Daanoz

Hi all,

I have the following query...

SELECT * FROM order_lines WHERE transaction_id IN (SELECT txn_id FROM orders WHERE DATE(due_by) = '2008-09-25')

The subquery returns 36 rows. The order_lines table is about 10,000 rows in size.

Everytime I run this query it crashes my MySQL server! How can I fix it?

Thanks,

David

We have the follow queries that are running slowly

mysql> explain select USERS.ID as USER_ID, USERS.MSISDN, USERS.JOINED,
BILLS.ACCOUNT, BILLS.ID as BILL_ID, BILLS.PRECALC_TYPE, `BILLS`.`SERVER`,
BILLS.PRECALC_CURRENCY, BILLS.PRECALC_AMOUNT, BILLS.MODE, BILLS.CALC_TABLE,
BILLS.SUCCESS, BILLS.REQCURRENCY, BILLS.REQAMOUNT, BILLS.CHARGED, BILLS.CURRENCY,
BILLS.BILLED, BILLS.PAYOUT-(BILLS.PAYOUT*BILLS.FEE_RATE) as PAYOUT, BILLS.SENT,
BILLS.RECUR, BILLS.TIMESTAMP, BILLS.CLIENTREF, BILLS.MSGLIST, 1 as LINK
from `smsbilling`.BILLS left join `smsbilling`.USERS on USERS.ACCOUNT = BILLS.ACCOUNT and
USERS.ID = BILLS.USER where MSISDN = '+447973296088' order by `SENT` desc;
+----+-------------+-------+------+---------------+--------+---------+---------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+---------------------+------+----------------------------------------------+
| 1 | SIMPLE | USERS | ref | User,MSISDN | MSISDN | 18 | const | 28 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | BILLS | ref | Bill,USER | USER | 4 | smsbilling.USERS.ID | 7 | Using where |
+----+-------------+-------+------+---------------+--------+---------+---------------------+------+----------------------------------------------+
2 rows in set (0.01 sec)

mysql> explain select `CODE`, sum(`AMOUNT`) `AMOUNT`, `CURRENCY`
-> from `gatewaytest`.FINANCE where `ACCOUNT` = 'friendfinder' group by `CURRENCY`, `CODE` order by `CODE`;
+----+-------------+---------+------+---------------+---------+---------+-------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+---------+---------+-------+--------+----------------------------------------------+
| 1 | SIMPLE | FINANCE | ref | ACCOUNT | ACCOUNT | 34 | const | 753460 | Using where; Using temporary; Using filesort |
+----+-------------+---------+------+---------------+---------+---------+-------+--------+----------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select sum(`TARIFF` != 0) as `s`, sum(`TARIFF` = 0) as `zs`, `STATUS`, `ACCOUNT`, min(`TIMESENT`) as `FIRSTSENT`,
-> max(`TIMESENT`) as `LASTSENT` from `gatewaytest`.OUTARCHIVE where `TIMESENT` between '19700101000000'
AND '20080925075519' AND `ACCOUNT` = 'friendfinder' group by `ACCOUNT`, `STATUS`;
+----+-------------+------------+------+------------------+---------+---------+-------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+------------------+---------+---------+-------+--------+----------------------------------------------+
| 1 | SIMPLE | OUTARCHIVE | ref | TIMESENT,ACCOUNT | ACCOUNT | 34 | const | 364965 | Using where; Using temporary; Using filesort |
+----+-------------+------------+------+------------------+---------+---------+-------+--------+----------------------------------------------+
1 row in set (0.00 sec)


Just wondering how we could look at tuning mysql to increase performance the machine that current runs the database has 12 gig of ram and a quaid core xeon processors.

We have tried altering
key_buffer = 3000M #384M #1024M
tmp_table_size = 1024M
thread_cache_size=960
key_buffer=1800M
sort_buffer_size=12M
read_buffer_size=12M
read_rnd_buffer_size=8M
thread_concurrency=4
table_cache = 512
thread_concurrency = 4

just wondering if you are looking the the right area's

I have a rather complex query that runs just fine (about 1 second) when the server is freshly started. But after half a day or so, the query slows to a crawl (about 25 seconds). Other queries are not affected and run as fast as always. I have to restart the server to get the original performance back. In the course of a day, the query is performed less than 20 times. It doesn't appear to degrade slowly, it's abrupt. I'll be testing it for hours and it's fine, then I come back after lunch and it's slowed down to 25 seconds.

The database has 4 tables, and I'm only querying 2 of them. One table has 25 records, and the other has several hundred thousand.

I'm not sure where to even begin with this. Shouldn't the same query take the same (roughly) amount of time each time it's performed, assuming the server is not maxing out on some other process (it's not - it's sitting quietly idle)?

Here's the query. I'm happy to provide more info, server config, stats, etc. Thanks.

-Bill

select
rn.*,
count(*) as ct,
sum(rn.status) as sm,
max(rn.min_nights) as max_mnights,
r.name,
r.building,
r.desc_long,
ra1.status as status_1,
ra1.night_date as date1,
ra2.status as status_2,
ra2.night_date as date2
from room_nights rn, rooms r, room_nights ra1, room_nights ra2
where rn.night_date between '2008-09-24' and '2008-09-25' - interval 1 day
and rn.room_code = r.code
and r.capacity >= 1
and (r.code = ra1.room_code and '2008-09-23' = ra1.night_date)
and (r.code = ra2.room_code and '2008-09-25' = ra2.night_date)
group by rn.room_code
having ct=sm and
(
max_mnights <= 1 or
(
max_mnights = 3 and
(
(dayofweek('2008-09-25') in (7,1) and status_2 > 1) or
(dayofweek('2008-09-24') in (1,2) and status_1 > 1)
) or
max_mnights = 2 and
(
(dayofweek('2008-09-25') in (7) and status_2 > 1) or
(dayofweek('2008-09-24') in (1) and status_1 > 1)
)
)
)
order by rn.rate desc

The below is the SHOW VARIABLES, SHOW STATUS from my db, what's the suggestion for the tuning? Appreciated for your help.

mysql> SHOW GLOBAL VARIABLES;
+---------------------------------+------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------------------------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /usr/local/mysql-enterprise-gpl-5.0.36-osx10.4-powerpc/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql-enterprise-gpl-5.0.36-osx10.4-powerpc/share/mysql/charsets/ |
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
| completion_type | 0 |
| concurrent_insert | 1 |
| connect_timeout | 5 |
| datadir | /usr/local/mysqldata/ |
| date_format | %Y-%m-%d |
| datetime_format | %Y-%m-%d %H:%i:%s |
| default_week_format | 0 |
| delay_key_write | ON |
| delayed_insert_limit | 100 |
| delayed_insert_timeout | 300 |
| delayed_queue_size | 1000 |
| div_precision_increment | 4 |
| engine_condition_pushdown | OFF |
| expire_logs_days | 7 |
| flush | OFF |
| flush_time | 0 |
| ft_boolean_syntax | + -><()~*:""&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 4 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
| group_concat_max_len | 1024 |
| have_archive | YES |
| have_bdb | NO |
| have_blackhole_engine | YES |
| have_compress | YES |
| have_crypt | YES |
| have_csv | NO |
| have_dynamic_loading | YES |
| have_example_engine | YES |
| have_federated_engine | YES |
| have_geometry | YES |
| have_innodb | YES |
| have_isam | NO |
| have_merge_engine | YES |
| have_ndbcluster | NO |
| have_openssl | DISABLED |
| have_query_cache | YES |
| have_raid | NO |
| have_rtree_keys | YES |
| have_symlink | YES |
| init_connect | |
| init_file | |
| init_slave | |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 2147483648 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_rollback_on_timeout | OFF |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| innodb_thread_sleep_delay | 10000 |
| interactive_timeout | 28800 |
| join_buffer_size | 4194304 |
| key_buffer_size | 8388600 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| language | /usr/local/mysql-enterprise-gpl-5.0.36-osx10.4-powerpc/share/mysql/english/ |
| large_files_support | ON |
| large_page_size | 0 |
| large_pages | OFF |
| lc_time_names | en_US |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | ON |
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| log_error | |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | ON |
| log_warnings | 1 |
| long_query_time | 5 |
| low_priority_updates | OFF |
| lower_case_file_system | ON |
| lower_case_table_names | 2 |
| max_allowed_packet | 1048576 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10 |
| max_connections | 100 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 18446744073709551615 |
| max_length_for_sort_data | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_sp_recursion_depth | 0 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 8388608 |
| myisam_stats_method | nulls_unequal |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | OFF |
| open_files_limit | 1024 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | /usr/local/mysqldata/heineken-2.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 104857600 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 268435456 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| server_id | 1 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_compressed_protocol | OFF |
| slave_load_tmpdir | /var/tmp/ |
| slave_net_timeout | 3600 |
| slave_skip_errors | OFF |
| slave_transaction_retries | 10 |
| slow_launch_time | 2 |
| socket | /tmp/mysql.sock |
| sort_buffer_size | 65536 |
| sql_big_selects | ON |
| sql_mode | |
| sql_notes | ON |
| sql_warnings | OFF |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_key | |
| storage_engine | MyISAM |
| sync_binlog | 0 |
| sync_frm | ON |
| system_time_zone | EDT |
| table_cache | 64 |
| table_lock_wait_timeout | 50 |
| table_type | MyISAM |
| thread_cache_size | 8 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmp_table_size | 134217728 |
| tmpdir | /var/tmp/ |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| updatable_views_with_limit | YES |
| version | 5.0.40-enterprise-gpl-log |
| version_comment | MySQL Enterprise Server (GPL) |
| version_compile_machine | powerpc |
| version_compile_os | apple-darwin8.6.0 |
| wait_timeout | 28800 |
+---------------------------------+------------------------------------------------------------------------------+
220 rows in set (0.06 sec)

mysql> SHOW GLOBAL STATUS;
+-----------------------------------+----------+
| Variable_name | Value |
+-----------------------------------+----------+
| Aborted_clients | 1231278 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 150 |
| Binlog_cache_use | 6754154 |
| Bytes_received | 2234312 |
| Bytes_sent | 67468922 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 3238232 |
| Com_change_db | 0 |
| Com_change_master | 0 |
| Com_check | 321 |
| Com_checksum | 0 |
| Com_commit | 3138121 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 142 |
| Com_create_user | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 378232 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 334 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 78293 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 10 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 2783289 |
| Com_set_option | 12783732 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 0 |
| Com_show_databases | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 0 |
| Com_show_triggers | 0 |
| Com_show_variables | 1 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 34892 |
| Com_stmt_execute | 359329 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 348921 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 23923 |
| Com_update_multi | 0 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
| Connections | 6734354 |
| Created_tmp_disk_tables | 123349 |
| Created_tmp_files | 52372 |
| Created_tmp_tables | 256798 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 3794 |
| Handler_read_key | 78665646 |
| Handler_read_next | 15645655 |
| Handler_read_prev | 333435 |
| Handler_read_rnd | 2345332 |
| Handler_read_rnd_next | 23675456 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 2206753 |
| Innodb_buffer_pool_pages_data | 512 |
| Innodb_buffer_pool_pages_dirty | 456 |
| Innodb_buffer_pool_pages_flushed | 0 |
| Innodb_buffer_pool_pages_free | 0 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 512 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead_seq | 132 |
| Innodb_buffer_pool_read_requests | 1155785 |
| Innodb_buffer_pool_reads | 630232 |
| Innodb_buffer_pool_wait_free | 2342 |
| Innodb_buffer_pool_write_requests | 12478 |
| Innodb_data_fsyncs | 3 |
| Innodb_data_pending_fsyncs | 4 |
| Innodb_data_pending_reads | 1 |
| Innodb_data_pending_writes | 3 |
| Innodb_data_read | 394100736|
| Innodb_data_reads | 24054 |
| Innodb_data_writes | 12450 |
| Innodb_data_written | 203980800|
| Innodb_dblwr_pages_written | 12450 |
| Innodb_dblwr_writes | 11983 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 0 |
| Innodb_log_writes | 176233 |
| Innodb_os_log_fsyncs | 17612 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 348923 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 3235 |
| Innodb_pages_read | 538203 |
| Innodb_pages_written | 12203 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 26924306 |
| Innodb_row_lock_time_avg | 478 |
| Innodb_row_lock_time_max | 123498 |
| Innodb_row_lock_waits | 56327 |
| Innodb_rows_deleted | 897 |
| Innodb_rows_inserted | 12764 |
| Innodb_rows_read | 3782354 |
| Innodb_rows_updated | 23353 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 0 |
| Key_blocks_used | 7248 |
| Key_read_requests | 6786353 |
| Key_reads | 1327838 |
| Key_write_requests | 112893 |
| Key_writes | 112053 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 100 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 80 |
| Open_streams | 0 |
| Open_tables | 64 |
| Opened_tables | 12749 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 104847096|
| Qcache_hits | 12 |
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2783289 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
| Questions | 23167761 |
| Rpl_status | NULL |
| Select_full_join | 1867 |
| Select_full_range_join | 129 |
| Select_range | 989635 |
| Select_range_check | 0 |
| Select_scan | 25643 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 12761 |
| Sort_merge_passes | 38291 |
| Sort_range | 0 |
| Sort_rows | 87923733 |
| Sort_scan | 9837 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 1272139 |
| Table_locks_waited | 542987 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 8 |
| Threads_connected | 92 |
| Threads_created | 809 |
| Threads_running | 1 |
| Uptime | 405423 |
+-----------------------------------+----------+
247 rows in set (0.00 sec)