MySQL Performance

Members

Help

Home

Today's Messages (OFF) | Unanswered Messages (ON)

Forum: MySQL

Topic: Raid disk configuration for InnoDB

Raid disk configuration for InnoDB [message #3246]

Wed, 02 July 2008 11:15

DoktorPZ
Messages: 3
Registered: February 2007

Junior Member

I try to find out best raid configuration for InnoDB MySQL engine. I have 1 server for test with Adaptec 2130SLP raid controller + 6 SCSI 15k HDD. If somebody have benchmark data - please share it for comparison.

[Updated on: Wed, 02 July 2008 11:15]

Report message to a moderator

Topic: Transaction not committed properly

Transaction not committed properly [message #3245]

Wed, 02 July 2008 08:33

newtomysql
Messages: 24
Registered: February 2007

Junior Member

Dear All,
I have this application which is using Innodb engine and .netconnector 5.0.8.1.So I am using C# for my coding. So I am not sure either is the engine or the connector problem? So I want to run transaction base appplication where either all my sql statement go through or all fail. So the funny part I notice it works well for most of my cases but for certain cases the second sql statement go through. I really cant figure out why is this happening. I have attached the code below. So the problem there is that my myInsertQuery1 goes through but not my myUpdateQuery1 ? So whyis this happening because both are under the same transactions right. I have separately attached below my function for thetransactionConnectionLocal1 where I keep the connection details.

int rollbackBoolean = 0;
MySqlTransaction transactionLocal1 = null;
MySqlConnection connectionLocal1 = null;
transactionConnectionLocal1 callTransactionConnectionLocal1= null;
try
{
callTransactionConnectionLocal1 = newtransactionConnectionLocal();
connectionLocal1=callTransactionConnectionLocal1.localConnec tion1;
connectionLocal1.Open();
transactionLocal1 =connectionLocal1.BeginTransaction();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error From Database Connection " +ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error Sockets From Database Connection" + ex.Message);
}

String myUpdateQuery1 = "Update tblStock " +"Set tblStock.stockStatus = 'b'" +"Where tblStock.stockSerial ='" +serial + "'" ;
MySqlCommand myCommand1 = newMySqlCommand(myUpdateQuery1);
try
{
myCommand1.Connection = connectionLocal1;
myCommand1.Transaction = transactionLocal1;myCommand1.ExecuteNonQuery();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error From myUpdateQuery1 " +ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error Sockets From myUpdateQuery1 " +ex.Message);
}
finally
{
myCommand1.Dispose();
}

String myInsertQuery1 = "Insert into tblTemp " +"Set stockSerial ='" + serial+ "'," +"tempTimeStamp='" +DateTime.Now.ToString(("yyyy:MM:dd HH:mm:ss")) + "';";MySqlCommand myCommand2 = newMySqlCommand(myInsertQuery1);

try
{
myCommand2.Connection = connectionLocal1;
myCommand2.Transaction = transactionLocal1;
myCommand2.ExecuteNonQuery();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
rollbackBoolean = 1;MessageBox.Show("Error From myInsertQuery1 " +ex.Message);
}
catch (System.Net.Sockets.SocketException ex)
{
rollbackBoolean = 1;
MessageBox.Show("Error Sockets From myInsertQuery1 " +ex.Message);
}
finally{myCommand2.Dispose();
}

if (rollbackBoolean == 1)
{
transactionLocal1.Rollback();
}
else
{
try
{
transactionLocal1.Commit();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
try
{
transactionLocal1.Rollback();
}
catch (MySqlException ex1){MessageBox.Show("An exception of type " +ex.GetType() +" was encountered while insertingthe data.");
if (transactionLocal1.Connection != null)
{
MessageBox.Show("An exception of type " +ex1.GetType() +" was encountered whileattempting to rollback the transaction.");
}
}
}
catch (System.Net.Sockets.SocketException ex){rollbackBoolean = 1;MessageBox.Show("Error Sockets From Commit Process" + ex.Message);
}
finally{connectionLocal1.Close();
}
}

// connection details function
using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
namespace mSytemNonFranchise
{
public class transactionConnectionLocal1
{
public MySqlConnection localConnection1;
public MySqlCommand command;
public transactionConnectionLocal1()
{
this.localConnection1 = newMySqlConnection("Address='localhost';Database='localDB';UserName='root';Pas sword='local12';Pooling='false'");
// this.command = this.localConnection1.CreateCommand();
// this.localConnection1.Open();}
// destructor - explicitly
~transactionConnectionLocal1()
{//this.command.Dispose();this.localConnection1.Close();this .localConnection1.Dis pose();
}
}
}

Report message to a moderator

Topic: Dynamic Create table using cursor ??

Dynamic Create table using cursor ?? [message #3234]

Tue, 01 July 2008 05:01

focusora
Messages: 5
Registered: June 2008

Junior Member

Hi all,
Is it possible to create Dynamic temporary table in pl/sql .
using cursor to stores values.
my code works for static table ., very well
but my need is in Dynamic table to store the values and fetch
that values in cursor and insert into another tables.
is it possible ??

=========
DELIMITER $$

CREATE PROCEDURE "testproc"()
BEGIN

declare abc varchar(50);

declare SSS cursor for
select id from
[ DYNAMIC_TEMP_TABLE ] where number = 11;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_row_fetched=1;
SET l_last_row_fetched=0;
open SSS;
SSS_loop: LOOP

fetch SSS into abc;

IF l_last_row_fetched=1 THEN
LEAVE f_loop;
END IF;

insert into newtable (id) values ([values stored in abc]);
===============
any ideas or assist to create dynamic table in pl/sql
OS : windows 2000
DB : MySQL 5.0

Report message to a moderator

Topic: Problem with slow mysql select count(id)

Problem with slow mysql select count(id) [message #3227]

Sun, 29 June 2008 02:00

student
Messages: 1
Registered: June 2008

Junior Member

hello,
I have a mysql database run web site.
Table has 7000 rows.
I have fulltext search on three columns.

when I look into mysql_slow_queries log, there are great many slow queries like this:

# Fri Jun 27 18:18:47 2008
# Query_time: 4 Lock_time: 0 Rows_sent: 1 Rows_examined: 43
SELECT COUNT(id) FROM table WHERE MATCH (title, keywords, description) AGAINST ('information' IN BOOLEAN MODE )

In most of these logs the query_time is 2.

I have almost no problem with other queries in my scripts.

id is my primary key.

EXPLAIN for this query is :

id : 1
select_type : SIMPLE
table : table
type : fulltext
possible_keys :search
key :search
key_len :0
ref :
rows :1
Extra: Using where

when I run SQL for this query in phpmyadmin, i always get the result in less than 1 second.

I have enabled profiling in phpmyadmin and the result shows like this:

Status Time

(initialization) 0.000008
Opening tables 0.000016
System lock 0.000003
Table lock 0.000003
init 0.000004
optimizing 0.000002
statistics 0.000004
preparing 0.000004
executing 0.000019
Sending data 0.000014
end 0.000002
query end 0.000003
freeing items 0.000004
closing tables 0.000002
removing tmp table 0.000011
closing tables 0.000003
logging slow query 0.000002

There is no overload for the table.
I optimize the table every hour because i update the data very often.

I have MYISAM tables.
I do not have any CPU exceeded logs.

I have shared hosting. I doubt if shared hosting is responsible for slow queries.

I need your expert advice to solve these slow queries.

Let me know if you wish to know more information.

Please help me to solve these slow queries.

Thank you.

Report message to a moderator

Topic: Master-Master or dedicated app/db servers?

Master-Master or dedicated app/db servers? [message #3226]

Sat, 28 June 2008 10:30

edsuit
Messages: 14
Registered: April 2007
Location: The Netherlands

Junior Member

We're running several websites on Windows 2003 servers in an ASP VBScript / MySQL environment. Right now they are divided over two servers on two different locations.
I don't really know which variables give a good impression of server load, but I'm going to put the pageviews per day for every website.

Server A (Intel Xeon 2.8 Ghz / 2 GB RAM)
Website 1 - 23,000 pageviews per day

Server B (Intel Pentium 4 2.8 Ghz / 1 GB RAM)
Website 1 - 23,000 pageviews per day
Website 2 - 4700 pageviews per day
Website 3 - 7100 pageviews per day

The websites all have mostly the same structure, and they're dynamic for the most part, so pretty much every pageview involves database interaction.
Each server also hosts several smaller websites, but these generate hardly any traffic compared to the 'big' ones only a few use MySQL.

On server B, the websites have seen rapidly growing amounts of visitors lately, and we've been having quite a bit of performance problems. I've almost run out of optimization options, so we're thinking about buying a new server and putting it on the same location as server A. We will probably then use server B to host all the smaller websites.
So then we have two servers on the same location. My question is, what would be the best configuration?

- Two dedicated servers; one application server and one database server
- Divide the websites over the two servers and set up a Master-Master or Master-Slave relationship for MySQL

Or maybe another option?

Report message to a moderator

Topic: Blobs for property bags

Blobs for property bags [message #3224]

Fri, 27 June 2008 14:29

Jjhuff
Messages: 5
Registered: October 2007

Junior Member

In several places we want the ability to store a 'property bag', i.e. a set of key:value pairs for some object (like a file or user). We don't query or sort on their values.

One option is to do something like:
CREATE TABLE file_properties (
nFileId BIGINT NOT NULL,
strKey VARCHAR(64) ASCII NOT NULL,
strValue VARCHAR(512),
PRIMARY KEY (nFileId, strKey)
) ENGINE=InnoDB CHARACTER SET utf8;

The other:
CREATE TABLE file_properties (
nFileId BIGINT NOT NULL,
blobProps BLOB,
PRIMARY KEY (nFileId)
) ENGINE=InnoDB;

Files typically have 6 or 7 props and are typically write once, ready many. We also have a cleanup process that deletes these in batch jobs. In the BLOB approach, we'd probably store things as a pickled (and probably compressed) python dictionary.

What do people think? Is there an obvious winner here?

thanks!!

Report message to a moderator

Topic: slow perfomance with multiple LEFT JOINs

slow perfomance with multiple LEFT JOINs [message #3223]

Fri, 27 June 2008 06:34

phlype
Messages: 4
Registered: October 2006

Junior Member

I have a query that is very slow and I am wondering how to improve the speed; here are the details:
The slow query (table description can be found at the end of the post):
SELECT * FROM users u LEFT JOIN (user_bookmark ub LEFT JOIN review r ON r.site=ub.bookmark) ON ub.userid=u.userid
The EXPLAIN of this query tells me
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE u ALL NULL NULL NULL NULL 3000
1 SIMPLE ub ALL NULL NULL NULL NULL 31220
1 SIMPLE r ref site site 128 db1.ub.bookmark 2
and what I find strange is that for ub no index is used (although as you can see from the definition, all fields are indexed).

The goal of the query is simply to create an overview table of users with all their bookmarks (if they have marked any) and the accompanying site reviews (if any are available).

If I tryout a first step of the enrichment ie create a table of users extended with their bookmarks I get a very fast query:
SELECT * FROM users u LEFT JOIN user_bookmark ub ON ub.userid=u.userid

Does this mean that the "serial" LEFT JOINs kill the efficiency? Is there a way to solve this problem?

Table definitions

CREATE TABLE `users` (
`userid` int(11) NOT NULL auto_increment,
`name` varchar(100) NOT NULL default '',
PRIMARY KEY (`userid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3001 ;

CREATE TABLE `user_bookmark` (
`bookmark` varchar(128) NOT NULL default '',
`userid` int(11) NOT NULL default '0',
KEY `bookmark` (`bookmark`),
KEY `userid` (`userid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `review` (
`site` varchar(128) NOT NULL default '',
`review` text NOT NULL,
KEY `site` (`site`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Report message to a moderator

Topic: InnoDB clusterd Index databasedesign

InnoDB clusterd Index databasedesign [message #3221]

Wed, 25 June 2008 14:33

erkules
Messages: 51
Registered: December 2007

Member

Playing around with InnoDB, i wonder for tips how to design tables for InnoDB.
Are there some special things you can recommend?

Report message to a moderator

Topic: join_buffer_size

join_buffer_size [message #3220]

Wed, 25 June 2008 12:08

howachen
Messages: 7
Registered: February 2007

Junior Member

Hello,

1. Is it true that if my table join is using index, then this value is meaningless?

2. On the other hand, what value should I set? Assuming I have 4GB of main memory and do a lot of large table join.

3. The mysql tunning script (www.day32.com/MySQL) said more than 4MB is not advised, is it too conservative?

Many Thanks.

Report message to a moderator

Topic: More than 400 concurrent small queries

More than 400 concurrent small queries [message #3218]

Wed, 25 June 2008 05:40

gacilu
Messages: 1
Registered: June 2008

Junior Member

Hello guys,

I am new to this forum and also kind of new to mysql too.

I have a multi-thread application written in Ruby. The application is reading one table that has two columns (Father, Children). As you might see, this is a tree. The fields are foreign keys to a second table, but the second table is not involved in the problem. The table has around 100.000 rows.

What the code does is simple. It starts in the root and it goes to the leafs. The first thread takes the root and runs a select to get all the children. Then it triggers new threads per children and it ends, leaving the other threads alive. Every thread does exactly the same until they reach the leafs.

When the threads reach the leafs, they read the description from the other table, write the value in a global array and leave.

With a few rows, the algorithm is very fast. The problem starts when each node has many children. To give you an idea, in one point in time there are more than 600 threads running, but for some reason I always see no more than two queries running in parallel from the MySQL Administrator.

Each thread open a new connection, runs the select and it close the connection. I have the default maximum connections, 100. So I should see more queries in parallel than only two or three. All the connections are constantly used while the algorithm runs.

The other symptom I can see is that when I start the script, there are up to 30 or 40 queries in parallel, but then the number goes down quickly until it reaches only 2 or 3 until the end.

I've started playing around with the caches and memory values for MySQL server, but to be honest, I am just guessing and the performance does not change.

I am with Mac OS X Leopard in a very fast machine and MySQL 5.1.

Any ideas why is this happening?

Thanks,

Report message to a moderator

Topic: Optimize IP Range Join

Optimize IP Range Join [message #3211]

Sun, 22 June 2008 14:04

tmarket
Messages: 1
Registered: June 2008

Junior Member

I have 2 tables: an IP Address table, and an IP Ranges table. I want to retrieve a list of ranges in the ranges table for which one or more of the IP addresses in the IP address table fall within that range. The IP addresses are represented as integers.

What I'm trying to use is this:

SELECT title FROM ranges JOIN ips ON ip BETWEEN start AND stop

but it takes too long. Is there a better way?

Here is the structure:

mysql> describe ranges;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| range_id   | int(20) unsigned | NO   | PRI | NULL    | auto_increment |
| start      | int(10) unsigned | NO   | UNI | NULL    |                |
| stop       | int(10) unsigned | NO   | UNI | NULL    |                |
| title      | varchar(200)     | NO   |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

mysql> describe ips;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| ip_id      | int(20) unsigned | NO   | PRI | NULL    | auto_increment |
| ip         | int(10) unsigned | NO   | MUL | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

This is similar but different from this: http://forums.mysql.com/read.php?115,106747,106747#msg-10674 7

I've tried several different combinations of indexes, but no success.

Report message to a moderator

Topic: Help Me optimize my query

Help Me optimize my query [message #3208]

Fri, 20 June 2008 05:58

delta2cain1
Messages: 7
Registered: May 2008

Junior Member

Hi I have a table with 2350k records I have the following query

SELECT Name ,
count(CASE WHEN P_A = 'Y' and CC = 'Y' THEN Name END),
count(CASE WHEN P_A = 'Y' and CC = 'Y' and flag = '1' THEN Name END),
count(CASE WHEN P_A = 'Y' and CC = 'Y' and flag = '0' and Score >= 76 and Score < 300 THEN Name END),
count(CASE WHEN P_A = 'Y' and CC = 'Y' and flag = '0' and Score >= 300 and Score <= 330 THEN Name END)
FROM Table1
WHERE IF($P{StartDate} = $P{EndDate} ,Date_from_Table like CONCAT(DATE_FORMAT(IF(length($P{SelectDate}) > 4,$P{SelectDate},curdate()), '%m%d%Y'),"%"), date_format(str_to_date(CONCAT(SUBSTRING(Date_from_Table,1,2 ), "-",LOWER(SUBSTRING(Date_from_Table,4,3)),"-",'20',SUBSTRING(Date_from_Table,8,2)), "%d-%b-%Y"), "%Y-%m-%d") BETWEEN IF(length($P{StartDate}) > 4,$P{StartDate},curdate()) AND IF(length($P{EndDate}) > 4,$P{EndDate},curdate()))
GROUP BY ClientName;

Now The first part of the IF condition works quit fast on its own like .15 sec but when I extend it to consider a range of Date It takes about a minute or so.

Date_from_Table unfortunately is in string format and I cannot at the moment alter the Table.

IF(length($P{SelectDate}) > 4,$P{SelectDate},curdate()) part is for if no date is specified

Also I would like to get the total of the columns . Can I do that??

Report message to a moderator

Topic: UNION question

UNION question [message #3207]

Thu, 19 June 2008 19:02

rmarshsj
Messages: 1
Registered: June 2008

Junior Member

Hi All,

I am creating a temporary table which is the union of three selects each producing an ID and a score. If I use UNION on its own I lose any duplicated rows. If I use UNION ALL I get some rows with the same ID but different scores. What I want instead is to end up with unique IDs with the score column being the sum of the scores for any duplicate rows. Is this possible? Does anyone have any suggestions?

Thank you,

Rob

Report message to a moderator

Topic: Date field index not working when using > operator

Date field index not working when using > operator [message #3186]

Tue, 17 June 2008 04:35

leonch
Messages: 1
Registered: June 2008

Junior Member

Hi we have a database table with about 1 million rows. Quite often we do selects using the greater than operator on a date field so:

SELECT id,user FROM data WHERE pub_date > "2008-06-09";

We have an index on pub_date , this weekend the query stopped using the index and does a full table scan so if I do an explain it shows almost 1 million rows have been scanned which is very slow and it shows it is not using the key.

Even weirder if I change the date to 2008-06-10 and run explain it uses the index and scans far less rows.

This is very bizare behaviour. I am thinking of doing:

SELECT min(id) FROM data WHERE pub_date = "2008-06-09";

to get the id to start searching on and then using this in the second query

SELECT id,user FROM data WHERE pub_date > "2008-06-09" and id> idfromfirstquery;

This will obviously force a lot less rows to be scanned, however this isn't ideal. Does anyone have any idea what might be going on with my date field index?

Thankyou

Report message to a moderator

Topic: Table/Column Layout for multiple pricing

Table/Column Layout for multiple pricing [message #3184]

Tue, 17 June 2008 00:29

Jeremyreger
Messages: 1
Registered: June 2008

Junior Member

Hello all,
I am wanting to structure a database that will allow for multiple prices per products. I also want to be able to upload an image and enter details about the product.

My initial thought would be to have a product_db and have a price table, a product table, and a description table all linked together with product id's

I am lost...

I would really appreciate your help on the table and column layout for achieving the mulitple pricing...

Report message to a moderator

Topic: Joining rows based on their timestamp

Joining rows based on their timestamp [message #3173]

Wed, 11 June 2008 21:02

pytrin
Messages: 1
Registered: June 2008

Junior Member

I'm building a simple message-board like app. For every topic there could be several posts, and I would like to present the topic in a list with some content from the latest post in that topic.
Simplifying it, say I have two tables:

Topics:
- id (INT / AI )
- subject (VARCHAR(150))
- created (TIMESTAMP)

Posts:
- id (INT / AI)
- topic_id (INT / FK)
- content (TEXT)
- created (TIMESTAMP)

I want to retrieve rows from the Topics table, along with the latest row for each topic from the Posts table.
Can anyone suggest the most efficient way of accomplishing this (hopefully without a GROUP BY clause)?

Report message to a moderator

Topic: STR_TO_DATE does not function when using only year and month?

STR_TO_DATE does not function when using only year and month? [message #3170]

Tue, 10 June 2008 10:50

festerwim
Messages: 3
Registered: May 2008
Location: Belgium

Junior Member

Either STR_TO_DATE does not work properly or I'm doing something wrong. Consider this statement:

SELECT DATE_FORMAT( NOW(), '%Y%m');

This will output '200806' since we are currently June in the year 2008.
Then do this:

SELECT STR_TO_DATE(DATE_FORMAT( NOW(), '%Y%m'), '%Y%m');

This outputs:
31-mei-2008 0:00:00

I would expect it to have printed out:
1-june-2008 0:00:00

Anybody has an idea why this might happen?

I need this because I want to group by month and want to have a column 'CalculatedDate' which contains a real date with the first of each month in it.

regards,

Wim

Report message to a moderator

Topic: Memory tables max size

Memory tables max size [message #3169]

Tue, 10 June 2008 08:06

adrien
Messages: 1
Registered: June 2008

Junior Member

Is there a way to bypass size limitation in memory tables?

Thank you

Adrien

[Updated on: Wed, 11 June 2008 17:05]

Report message to a moderator

Topic: Can someone please check to see if we're overloading MySql?

Can someone please check to see if we're overloading MySql? [message #3164]

Mon, 09 June 2008 02:32

spib
Messages: 1
Registered: June 2008

Junior Member

Report message to a moderator

Topic: Can MySQL use multiple threads for single query?

Can MySQL use multiple threads for single query? [message #3162]

Sun, 08 June 2008 08:38

janvantonder
Messages: 1
Registered: June 2008

Junior Member

Hi everybody!

I'm using MySQL for storing fairly large amounts of data and would like to know if MySQL is capable of using multiple threads for a single complex query and thereby spread the load across several CPU's?

For example: A single query on a large table takes let's say 5 minutes to complete. Does MySQL take advantage of several CPU's or would this query only use a single CPU?

Thanks and regards!
Jan

Report message to a moderator

Topic: HELP ME PLEASE

HELP ME PLEASE [message #3161]

Sat, 07 June 2008 04:25

diaabednary
Messages: 1
Registered: June 2008
Location: egypt

Junior Member

Hi ,
What is the opposite of this function , as i have same filed as after GROUP_CONCAT query . and i want to extract the data from it . also the separator "," . this field contain numbers and i want to make search in the field .
what i mean Example I've 1,13,25,71 in this field when i search by like%1% it gaves me results contain the 1&13&71 .

Report message to a moderator

Topic: string query help

string query help [message #3159]

Fri, 06 June 2008 01:55

mysqllearner
Messages: 1
Registered: June 2008

Junior Member

In my database table there is a text field named "summary" which stores the values as
Typical APR: 14.9% - Intro APR: 0.0% / 3 months Intro Balance Transfer: 0.0% / 14 months

Typical APR: 4.9% - Intro APR: 10.0% / 3 months Intro Balance Transfer: 0.0% / 14 months

I want to get the result as
Typical APR: 14.9%
Intro APR: 0.0% / 3 months Intro
Balance Transfer: 0.0% / 14 months

means i want there seperate strings.Can anyone give me a query for this.Also there will be some rows where some other information is written and for those fields my query should return no rates.Please help me out.

Report message to a moderator

Topic: RAMDISK for filesorts?

RAMDISK for filesorts? [message #3157]

Thu, 05 June 2008 16:44

boxmonkey
Messages: 1
Registered: June 2008

Junior Member

Hello,

I work for a small but rapidly growing web company, I'm a PHP programmer mostly but end up doing a lot of stuff with MySQL as well. Our data set has been growing exponentially, and of course many of our queries are not scaling. The biggest slow down by far seems to be filesorts.

Some of these we have been able to eliminate through tweaking, but some just cannot be eliminated. For those, I thought it would be a cool hack to get MySQL to use ram disk. Our server has 32GB of RAM so we can certainly afford to dedicate a couple of GB to a ram disk.

Has anyone else done this? Suggestions, pointers, caveats? Is this a horrible idea?

For anyone who is wondering, we have increased our tmp_table_size to 128MB to help alleviate some of the filesorts, but we can't really afford to go higher than that (in fact I'm not sure we should even be that high); our max allowed connections is 500, which means that theoretically more than all of our memory could be used for temp tables...

Report message to a moderator

Topic: Strange Sorting/Updating Problem

Strange Sorting/Updating Problem [message #3156]

Wed, 04 June 2008 14:43

Carsten_H.
Messages: 2
Registered: August 2006

Junior Member

Hello!

I'm using a mix of UPDATE and SELECT to pre-sort rows for a faster SELECT later on. This is my query:

SET @num = 0;

UPDATE rel_artist_album 
LEFT JOIN (
 SELECT raa.album_id, 
  CASE 
   WHEN (a.is_sampler = 0 AND a.is_bootleg = 0 AND a.album_fullength = 1 AND a.is_movie = 0 AND a.is_live = 0 AND a.is_compilation = 0 AND a.is_misc = 0) THEN 1 
   WHEN (a.is_sampler = 0 AND a.is_bootleg = 0 AND a.album_fullength = 0 AND a.is_movie = 0 AND a.is_live = 0 AND a.is_compilation = 0 AND a.is_misc = 0) THEN 2 
   WHEN (a.is_sampler = 0 AND a.is_bootleg = 0 AND a.is_movie = 1) THEN 3 
   WHEN (a.is_sampler = 0 AND a.is_bootleg = 0 AND a.is_live = 1) THEN 4 
   WHEN (a.is_sampler = 0 AND a.is_bootleg = 0 AND a.is_compilation = 1) THEN 5 
   WHEN (a.is_sampler = 0 AND a.is_bootleg = 0 AND a.is_misc = 1) THEN 6 
   WHEN (a.is_bootleg = 1) THEN 7 
   WHEN (a.album_fullength = 1 AND a.is_sampler = 1 AND a.is_bootleg = 0) THEN 8 
  END AS sort_col 
 FROM rel_artist_album AS raa
 LEFT JOIN albums AS a
 ON raa.album_id = a.album_id 
 WHERE raa.artist_id = 541 
 ORDER BY sort_col ASC, IF (a.is_sampler = 1, a.album_name, a.album_year) ASC, a.album_name ASC) AS oai 
ON oai.album_id = rel_artist_album.album_id 
SET rel_artist_album.album_order = @num := @num +1 
WHERE rel_artist_album.album_id = oai.album_id 
AND rel_artist_album.artist_id = 541;

In most cases everything will be sorted right. But sometimes, if there are only very few rows to sort, the sorting goes wrong. If I take only the inner SELECT the value generated by the CASE is okay, but the UPDATE sorts the rows the other way.

Lets say, I have three rows and the inner SELECT sorts them like this (ID - sort_col value):

1. 7825 - 1
2. 7826 - 1
3. 7824 - 2

But after the UPDATE the rows are ordered like this:

1. 7824
2. 7825
3. 7826

Is there anyone who faced a similar problem?

Report message to a moderator

Topic: Does MYSQL do "OPTIMAZE TABLE" online ???

Does MYSQL do "OPTIMAZE TABLE" online ??? [message #3154]

Tue, 03 June 2008 10:11

vpestovnikov
Messages: 8
Registered: December 2007

Junior Member

Hi all,
Probably it is dump question, but I couldn't find it in the documentation and this forum.

Q1: When I do "OPTIMIZE TABLE" (table has MyISAM type), does MySQL lock this table?

Q2: Is this operation online?

Q3: Is that right that, in during the "OPTIMIZE TABLE" process, MySQL is copying everything into a temp table and then copies it back when this operation will be finished. Am I right?

What I need to do is to create a trigger to purge a data older then 6 month from an original table to archive table and then optimize them both.

Q4: Are there any better methods to purge the old data to the different tables?

Thanks in advance. Any answers are appreciated.

Report message to a moderator

Topic: Does MYSQL do "OPTIMAZE TABLE" online ???

Does MYSQL do "OPTIMAZE TABLE" online ??? [message #3153]

Tue, 03 June 2008 10:10

vpestovnikov
Messages: 8
Registered: December 2007

Junior Member

Report message to a moderator

Topic: INSERT INTO ... SELECT ... ON DUPLICATE KEY UPDATE

INSERT INTO ... SELECT ... ON DUPLICATE KEY UPDATE [message #3151]

Fri, 30 May 2008 08:08

Emul
Messages: 1
Registered: May 2008

Junior Member

Hi.

I want to archive a table contains search strings and results.
I just want to sum the data like this:

INSERT INTO
  video_search_query_log_archive
  (query_string, query_string_count, result_count_sum)
(
SELECT
  query_string, COUNT(query_string) AS qsc, SUM(result_count) AS rc
FROM
  video_search_query_log
GROUP BY
  query_string
)
ON DUPLICATE KEY UPDATE
  query_string_count = qsc,
  result_count_sum = rc

Bu its not working, anyone can tell me how to make it work?!

Report message to a moderator

Topic: Question about MySQL with huge amount of INSERTS

Question about MySQL with huge amount of INSERTS [message #3150]

Fri, 30 May 2008 04:39

dauz
Messages: 1
Registered: May 2008

Junior Member

I need help for solution for big MySQL DB.

The database must take around 200000 inserts per second, the selects are not a problem.

All the inserts are in ~10 tables and are ~250 bytes with the index.

Total disk space will be around 10TB.

If someone has expirience with such kind of databases can you help me with examples of cluster configuration and hardware needed for it.

Thanks in advance.

Report message to a moderator

Topic: Throttle Slave IO BW

Throttle Slave IO BW [message #3148]

Thu, 29 May 2008 12:16

Jjhuff
Messages: 5
Registered: October 2007

Junior Member

Is it possible to throttle how fast the slave IO thread reads data from the master? When I bring a slave back from 8hrs of running an ALTER, my master pegs it's disk transferring binlogs to the slave.
thanks!
--Justin

Report message to a moderator

Topic: Questions about mysqlreport output

Questions about mysqlreport output [message #3146]

Wed, 28 May 2008 22:21

dcampano
Messages: 2
Registered: May 2008

Junior Member

I had a couple questions about the output of mysqlreport. I have some very active web applications and the database server is doing 2k questions/second. 2 questions I have are the number of connections/second is pretty high, and also subsequently the number of change_db _COM statements are high. I'm wondering if I should be looking into using mysql_pconnect?

MySQL 5.0.22-log uptime 4 2:51:24 Wed May 28 19:48:20 2008

__ Key ____________________________________________________________ _____
Buffer used 2.74M of 8.00M %Used: 34.19
Current 1.56M %Usage: 19.52
Write hit 99.02%
Read hit 99.67%

__ Questions ___________________________________________________________
Total 701.51M 2.0k/s
QC Hits 330.94M 929.9/s %Total: 47.18
DMS 149.25M 419.4/s 21.28
Com_ 114.55M 321.9/s 16.33
COM_QUIT 107.29M 301.5/s 15.29
-Unknown 528.42k 1.5/s 0.08
Slow 1 s 76.52k 0.2/s 0.01 %DMS: 0.05 Log: ON
DMS 149.25M 419.4/s 21.28
SELECT 106.10M 298.1/s 15.12 71.09
INSERT 26.93M 75.7/s 3.84 18.04
UPDATE 16.17M 45.4/s 2.30 10.83
DELETE 56.73k 0.2/s 0.01 0.04
REPLACE 0 0/s 0.00 0.00
Com_ 114.55M 321.9/s 16.33
change_db 107.28M 301.5/s 15.29
set_option 6.09M 17.1/s 0.87
admin_comma 528.64k 1.5/s 0.08

__ SELECT and Sort _____________________________________________________
Scan 2.17M 6.1/s %SELECT: 2.05
Range 9.99M 28.1/s 9.41
Full join 697 0.0/s 0.00
Range check 0 0/s 0.00
Full rng join 80 0.0/s 0.00
Sort scan 2.10M 5.9/s
Sort range 12.14M 34.1/s
Sort mrg pass 5.94k 0.0/s

__ Query Cache _________________________________________________________
Memory usage 2.29M of 7.63M %Used: 30.03
Block Fragmnt 26.10%
Hits 330.94M 929.9/s
Inserts 101.44M 285.0/s
Insrt:Prune 205.92:1 283.7/s
Hit:Insert 3.26:1

__ Table Locks _________________________________________________________
Waited 0 0/s %Total: 0.00
Immediate 171.19M 481.0/s

__ Tables ____________________________________________________________ __
Open 1024 of 1024 %Cache: 100.00
Opened 42.41k 0.1/s

__ Connections _________________________________________________________
Max used 501 of 500 %Max: 100.20
Total 107.29M 301.5/s

__ Created Temp ________________________________________________________
Disk table 3.08k 0.0/s
Table 10.90M 30.6/s Size: 32.0M
File 11.89k 0.0/s

__ Threads ____________________________________________________________ _
Running 3 of 21
Cached 113 of 128 %Hit: 99.85
Created 165.30k 0.5/s
Slow 0 0/s

__ Aborted ____________________________________________________________ _
Clients 64 0.0/s
Connects 8.36k 0.0/s

__ Bytes ____________________________________________________________ ___
Sent 104.97G 295.0k/s
Received 66.63G 187.2k/s

__ InnoDB Buffer Pool __________________________________________________
Usage 4.00G of 4.00G %Used: 100.00
Read hit 99.88%
Pages
Free 0 %Total: 0.00
Data 225.48k 86.01 %Drty: 7.03
Misc 36666 13.99
Latched 0 0.00
Reads 21.48G 60.3k/s
From file 26.38M 74.1/s 0.12
Ahead Rnd 999896 2.8/s
Ahead Sql 642942 1.8/s
Writes 262.64M 738.0/s
Flushes 5.65M 15.9/s
Wait Free 0 0/s

__ InnoDB Lock _________________________________________________________
Waits 7427 0.0/s
Current 0
Time acquiring
Total 194305 ms
Average 26 ms
Max 1840 ms

__ InnoDB Data, Pages, Rows ____________________________________________
Data
Reads 34.82M 97.8/s
Writes 42.10M 118.3/s
fsync 591.00k 1.7/s
Pending
Reads 0
Writes 0
fsync 0

Pages
Created 198.44k 0.6/s
Read 55.34M 155.5/s
Written 5.65M 15.9/s

Rows
Deleted 55.28k 0.2/s
Inserted 24.95M 70.1/s
Read 31.55G 88.7k/s
Updated 13.94M 39.2/s

Report message to a moderator

Topic: no concurrent insert

no concurrent insert [message #3129]

Tue, 27 May 2008 07:02

erkules
Messages: 51
Registered: December 2007

Member

Im playing with large inserts.
There are 7 Prozesses inserting 100000 Rows.
Ive got concurrent_insert=2 but looking at the processlist, the processes are locked most of the time?

How can I get rid of that locking?

Report message to a moderator

Topic: MySQL 4.1.13: replication and myisampack

MySQL 4.1.13: replication and myisampack [message #3121]

Sun, 25 May 2008 13:01

nicku
Messages: 1
Registered: May 2008

Junior Member

Dear Folks,

We have a master with slaves. The disks are all getting full, and I'd like to compress some historical data with myisampack. All are running MySQL 4.1.13. Turning off the master for more than a very short time is not an option.

Can I use myisampack on the master safely?
Will this compression be replicated in any way to the slaves?
Would it be better to run myisampack on one of the slaves and then netcat the result to the master and other slaves?
Can you suggest any safety precautions?

Report message to a moderator

Topic: Howto solve this huge query

Howto solve this huge query [message #3114]

Fri, 23 May 2008 05:09

justin
Messages: 1
Registered: May 2008

Junior Member

Hi,

I'm in a bit of a mess here.
I run an web application thats hosts campaigns.
Each campaign has it's own table with it's activity (impression, click, order). The reason for this is that a single campaign can have more than 1 000 000 impressions, 100 000 clicks, 10 000 orders for example.
At the moment I have about 100 campaigns and therefore about 100 campaign tables.

I have partners that use my campaigns at their sites and I want to show partners how many impressions, clicks and orders they generated so they can see how much money they made.

The problem is when i want to show, let's say the number of imps, clicks and orders for this week, ordered by date, and then ordered by campaign name.

At this moment i join the campaign table (1 row per campaign with campaign info) with the corresponding activitytable and check if theres been som activity within the given period, and then a UNION ALL with the next campaign etc.

This query will just grow and grow the more campaings I host.

How would you guys solve this problem?
Would you change the database design?

Even today I have problems with mysql crashing from time to time.

I would really be grateful if you try to help me, thanks!

[Updated on: Fri, 23 May 2008 05:27]

Report message to a moderator

Topic: Please help to optimize mysql

Please help to optimize mysql [message #3113]

Fri, 23 May 2008 04:16

anebi
Messages: 1
Registered: May 2008

Junior Member

Hi, i need help to optimize better mysql. We have run on CentOs 5 2.6.18-53.1.19.el5. Mysql 5 and apache 2.2.8. But we are getting high load when server is accessing harder from visitors. I think that we have some memory bottleneck and this is the reason to want to optimize better mysql, because i suppose that it might be one of the reasons for this. Also i see that mysql sometimes is using more cpu, i would like to reduce it. We use Innodb and we have more that 1 million records in our database.

The system is we 4GB RAM, Intel Core Duo

This is mysql configuration right now.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=0
bind-address=127.0.0.1
max_connections=300
# Set MyISAM parameters
key_buffer_size=3M
tmp_table_size=16M
# Set InnoDB parameters
innodb_file_per_table
# Set buffer pool size to 50 - 80% of availiable memory.
innodb_buffer_pool_size=500M
innodb_additional_mem_pool_size=30M
#innodb_flush_method=O_DIRECT
# Set the log file size to about 25% of the buffer pool size.
#innodb_log_file_size=5M
#innodb_log_buffer_size=1M
table_cache=1024
#sort_buffer_size=3M
query_cache_type=1
query_cache_limit=1M
query_cache_size=70M
net_read_timeout=120
#long_query_time=10
#log_warnings=2
#log_slow_queries=/var/log/mysql/slow-queries.log
collation_server=utf8_unicode_ci
character_set_server=utf8

[mysql]
default-character-set=utf8

[mysqldump]
default-character-set=utf8

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

This is output from show status:

mysql> show status;
+-----------------------------------+------------+
| Variable_name                     | Value      |
+-----------------------------------+------------+
| Aborted_clients                   | 148        | 
| Aborted_connects                  | 4          | 
| Binlog_cache_disk_use             | 0          | 
| Binlog_cache_use                  | 0          | 
| Bytes_received                    | 79         | 
| Bytes_sent                        | 67         | 
| Com_admin_commands                | 0          | 
| Com_alter_db                      | 0          | 
| Com_alter_table                   | 0          | 
| Com_analyze                       | 0          | 
| Com_backup_table                  | 0          | 
| Com_begin                         | 0          | 
| Com_change_db                     | 0          | 
| Com_change_master                 | 0          | 
| Com_check                         | 0          | 
| Com_checksum                      | 0          | 
| Com_commit                        | 0          | 
| Com_create_db                     | 0          | 
| Com_create_function               | 0          | 
| Com_create_index                  | 0          | 
| Com_create_table                  | 0          | 
| Com_dealloc_sql                   | 0          | 
| Com_delete                        | 0          | 
| 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                         | 0          | 
| Com_grant                         | 0          | 
| Com_ha_close                      | 0          | 
| Com_ha_open                       | 0          | 
| Com_ha_read                       | 0          | 
| Com_help                          | 0          | 
| Com_insert                        | 0          | 
| 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                         | 0          | 
| 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                        | 0          | 
| Com_set_option                    | 0          | 
| 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                | 0          | 
| Com_show_warnings                 | 0          | 
| Com_slave_start                   | 0          | 
| Com_slave_stop                    | 0          | 
| Com_stmt_close                    | 0          | 
| Com_stmt_execute                  | 0          | 
| Com_stmt_fetch                    | 0          | 
| Com_stmt_prepare                  | 0          | 
| Com_stmt_reset                    | 0          | 
| Com_stmt_send_long_data           | 0          | 
| Com_truncate                      | 0          | 
| Com_unlock_tables                 | 0          | 
| Com_update                        | 0          | 
| 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                       | 139        | 
| Created_tmp_disk_tables           | 0          | 
| Created_tmp_files                 | 5          | 
| Created_tmp_tables                | 1          | 
| 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                | 0          | 
| Handler_read_key                  | 0          | 
| Handler_read_next                 | 0          | 
| Handler_read_prev                 | 0          | 
| Handler_read_rnd                  | 0          | 
| Handler_read_rnd_next             | 0          | 
| Handler_rollback                  | 0          | 
| Handler_savepoint                 | 0          | 
| Handler_savepoint_rollback        | 0          | 
| Handler_update                    | 0          | 
| Handler_write                     | 130        | 
| Innodb_buffer_pool_pages_data     | 30962      | 
| Innodb_buffer_pool_pages_dirty    | 4          | 
| Innodb_buffer_pool_pages_flushed  | 9439       | 
| Innodb_buffer_pool_pages_free     | 0          | 
| Innodb_buffer_pool_pages_latched  | 0          | 
| Innodb_buffer_pool_pages_misc     | 1038       | 
| Innodb_buffer_pool_pages_total    | 32000      | 
| Innodb_buffer_pool_read_ahead_rnd | 704        | 
| Innodb_buffer_pool_read_ahead_seq | 223        | 
| Innodb_buffer_pool_read_requests  | 59240240   | 
| Innodb_buffer_pool_reads          | 35301      | 
| Innodb_buffer_pool_wait_free      | 0          | 
| Innodb_buffer_pool_write_requests | 37073      | 
| Innodb_data_fsyncs                | 4789       | 
| Innodb_data_pending_fsyncs        | 0          | 
| Innodb_data_pending_reads         | 0          | 
| Innodb_data_pending_writes        | 0          | 
| Innodb_data_read                  | 1017548800 | 
| Innodb_data_reads                 | 44144      | 
| Innodb_data_writes                | 11748      | 
| Innodb_data_written               | 315497984  | 
| Innodb_dblwr_pages_written        | 9439       | 
| Innodb_dblwr_writes               | 578        | 
| Innodb_log_waits                  | 0          | 
| Innodb_log_write_requests         | 13597      | 
| Innodb_log_writes                 | 2658       | 
| Innodb_os_log_fsyncs              | 3081       | 
| Innodb_os_log_pending_fsyncs      | 0          | 
| Innodb_os_log_pending_writes      | 0          | 
| Innodb_os_log_written             | 5985280    | 
| Innodb_page_size                  | 16384      | 
| Innodb_pages_created              | 118        | 
| Innodb_pages_read                 | 61973      | 
| Innodb_pages_written              | 9439       | 
| Innodb_row_lock_current_waits     | 0          | 
| Innodb_row_lock_time              | 0          | 
| Innodb_row_lock_time_avg          | 0          | 
| Innodb_row_lock_time_max          | 0          | 
| Innodb_row_lock_waits             | 0          | 
| Innodb_rows_deleted               | 1590       | 
| Innodb_rows_inserted              | 1720       | 
| Innodb_rows_read                  | 17506842   | 
| Innodb_rows_updated               | 398        | 
| Key_blocks_not_flushed            | 0          | 
| Key_blocks_unused                 | 2508       | 
| Key_blocks_used                   | 96         | 
| Key_read_requests                 | 503599     | 
| Key_reads                         | 5339       | 
| Key_write_requests                | 151136     | 
| Key_writes                        | 0          | 
| Last_query_cost                   | 0.000000   | 
| Max_used_connections              | 32         | 
| Not_flushed_delayed_rows          | 0          | 
| Open_files                        | 78         | 
| Open_streams                      | 0          | 
| Open_tables                       | 195        | 
| Opened_tables                     | 0          | 
| Qcache_free_blocks                | 460        | 
| Qcache_free_memory                | 27848048   | 
| Qcache_hits                       | 795817     | 
| Qcache_inserts                    | 34280      | 
| Qcache_lowmem_prunes              | 11739      | 
| Qcache_not_cached                 | 1697       | 
| Qcache_queries_in_cache           | 1124       | 
| Qcache_total_blocks               | 2756       | 
| Questions                         | 839206     | 
| Rpl_status                        | NULL       | 
| Select_full_join                  | 0          | 
| Select_full_range_join            | 0          | 
| Select_range                      | 0          | 
| Select_range_check                | 0          | 
| Select_scan                       | 1          | 
| Slave_open_temp_tables            | 0          | 
| Slave_retried_transactions        | 0          | 
| Slave_running                     | OFF        | 
| Slow_launch_threads               | 0          | 
| Slow_queries                      | 0          | 
| Sort_merge_passes                 | 0          | 
| Sort_range                        | 0          | 
| Sort_rows                         | 0          | 
| Sort_scan                         | 0          | 
| 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             | 112376     | 
| Table_locks_waited                | 0          | 
| Tc_log_max_pages_used             | 0          | 
| Tc_log_page_size                  | 0          | 
| Tc_log_page_waits                 | 0          | 
| Threads_cached                    | 0          | 
| Threads_connected                 | 26         | 
| Threads_created                   | 138        | 
| Threads_running                   | 1          | 
| Uptime                            | 4344       | 
+-----------------------------------+------------+
245 rows in set (0.00 sec)

this is the output from show variables;

mysql> show variables;
+---------------------------------+--------------------------------------------------------+
| Variable_name                   | Value                                                  |
+---------------------------------+--------------------------------------------------------+
| auto_increment_increment        | 1                                                      | 
| auto_increment_offset           | 1                                                      | 
| automatic_sp_privileges         | ON                                                     | 
| back_log                        | 50                                                     | 
| basedir                         | /usr/                                                  | 
| bdb_cache_size                  | 8388600                                                | 
| bdb_home                        | /var/lib/mysql/                                        | 
| bdb_log_buffer_size             | 524288                                                 | 
| bdb_logdir                      |