Re: info on mysql
Hi, Thanks all for your help. ---Database Table wise Size in MB--- SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2), Mb) AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = dbname; ---Database Table wise Size in GB--- SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024 / 1024),3), Gb) AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = dbname; Result shows the EMPTY SET. On Tue, Aug 10, 2010 at 10:20 AM, Anirudh Sundar sundar.anir...@gmail.comwrote: Hello Pratiksha, To get the uptime of the MYSQL instance :- mysql\s as given above just give the above command \s For Total number of users connected to server :- mysqlshow global status like '%user%'; ---Database Table wise Size in MB--- SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2), Mb) AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = dbname; ---Database Table wise Size in GB--- SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024 / 1024),3), Gb) AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = dbname; Data size, index size no. of tables, engine type* SELECT s.schema_name,t.engine, CONCAT(IFNULL(ROUND(SUM(t.data_length)/1024/1024,2),0.00),Mb) as Data_size, CONCAT(IFNULL(ROUND(SUM(t.index_length)/1024/1024,2),0.00),Mb) as Index_size,COUNT(table_name) total_tables FROM INFORMATION_SCHEMA.SCHEMATA s LEFT JOIN INFORMATION_SCHEMA.TABLES t ON s.schema_name = t.table_schema WHERE s.schema_name not in(mysql,information_schema,test) GROUP BY s.schema_name,t.engine order by Data_size DESC; For top slow queries :- mysqldumpslow -s c -a -t 5 mysqlslow.log top10_slow-count_envr.log For Engine Info of a table and other details :- Show table status like 'tablename'; Cheers, Anirudh Sundar On Mon, Aug 9, 2010 at 9:31 PM, PRATIKSHA JAISWAL pratikshadjayswa...@gmail.com wrote: Hi All, How can i get following information from database or is there any query for the same. (1) mysql server uptime (2) Total number of users connected to server (3) Data file information / where it is located through mysql prompt / size of data file (4) each Database size (5) Database I/O information (6) Invalid object in database (7) Database performance statistics queries (8) Top 5 queries taking more time for executions. (9) Engine information. -- Thanks Pratiksha
Re: info on mysql
check this, http://adminlinux.blogspot.com/2009/12/mysql-tips-calculate-database-and-table.html Thx On Wed, Aug 11, 2010 at 5:23 PM, PRATIKSHA JAISWAL pratikshadjayswa...@gmail.com wrote: Hi, Thanks all for your help. ---Database Table wise Size in MB--- SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2), Mb) AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = dbname; ---Database Table wise Size in GB--- SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024 / 1024),3), Gb) AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = dbname; Result shows the EMPTY SET. On Tue, Aug 10, 2010 at 10:20 AM, Anirudh Sundar sundar.anir...@gmail.comwrote: Hello Pratiksha, To get the uptime of the MYSQL instance :- mysql\s as given above just give the above command \s For Total number of users connected to server :- mysqlshow global status like '%user%'; ---Database Table wise Size in MB--- SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2), Mb) AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = dbname; ---Database Table wise Size in GB--- SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024 / 1024),3), Gb) AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = dbname; Data size, index size no. of tables, engine type* SELECT s.schema_name,t.engine, CONCAT(IFNULL(ROUND(SUM(t.data_length)/1024/1024,2),0.00),Mb) as Data_size, CONCAT(IFNULL(ROUND(SUM(t.index_length)/1024/1024,2),0.00),Mb) as Index_size,COUNT(table_name) total_tables FROM INFORMATION_SCHEMA.SCHEMATA s LEFT JOIN INFORMATION_SCHEMA.TABLES t ON s.schema_name = t.table_schema WHERE s.schema_name not in(mysql,information_schema,test) GROUP BY s.schema_name,t.engine order by Data_size DESC; For top slow queries :- mysqldumpslow -s c -a -t 5 mysqlslow.log top10_slow-count_envr.log For Engine Info of a table and other details :- Show table status like 'tablename'; Cheers, Anirudh Sundar On Mon, Aug 9, 2010 at 9:31 PM, PRATIKSHA JAISWAL pratikshadjayswa...@gmail.com wrote: Hi All, How can i get following information from database or is there any query for the same. (1) mysql server uptime (2) Total number of users connected to server (3) Data file information / where it is located through mysql prompt / size of data file (4) each Database size (5) Database I/O information (6) Invalid object in database (7) Database performance statistics queries (8) Top 5 queries taking more time for executions. (9) Engine information. -- Thanks Pratiksha -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Slow query using string operator
Hi Jerry, all! I second Travis' advice: Travis Ard schrieb: Can you create a second, indexed column in your feed_new temp table that includes the title without the year appended? That might allow you to get by with a single pass through the larger prod table and avoid reading rows from the feed_new table. The original query does a string operation on the values from both sides before checking the result for equality: -Original Message- From: Jerry Schwartz [mailto:je...@gii.co.jp] Sent: Tuesday, August 10, 2010 3:39 PM To: mysql@lists.mysql.com Subject: Slow query using string operator I'm running a set of queries that look like this: [[...]] SELECT feed_new.new_title AS `New Title FROM Feed`, prod.prod_pub_prod_id AS `Lib Code FROM DB`, prod.prod_title AS `Title FROM DB`, prod.prod_num AS `Prod Num`, prod.prod_published AS `Published FROM DB` FROM feed_new JOIN prod ON LEFT(feed_new.new_title, LENGTH(feed_new.new_title) - 5) = LEFT(prod.prod_title, LENGTH(prod.prod_title) - 5) WHERE prod.pub_id = @PUBID AND prod.prod_discont = 0 ORDER BY feed_new.new_title; So neither value is taken directly, which means the values in the indexes (if defined) cannot be used anyway. If you need these calculations, you should compute and maintain these values when inserting/updating data (define triggers doing this, or run periodic maintenance/check jobs), and store them in suitable indexes. AFAIK, this applies to all comparisons which use function results rather than column values directly. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Slow query using string operator
-Original Message-
From: Travis Ard [mailto:travis_...@hotmail.com]
Sent: Tuesday, August 10, 2010 6:53 PM
To: 'Jerry Schwartz'; mysql@lists.mysql.com
Subject: RE: Slow query using string operator
Can you create a second, indexed column in your feed_new temp table that
includes the title without the year appended? That might allow you to get
by with a single pass through the larger prod table and avoid reading rows
from the feed_new table.
[JS] I have thought about that, but haven't tried it yet.
I'll let you know.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com
-Travis
-Original Message-
From: Jerry Schwartz [mailto:je...@gii.co.jp]
Sent: Tuesday, August 10, 2010 3:39 PM
To: mysql@lists.mysql.com
Subject: Slow query using string operator
I'm running a set of queries that look like this:
===
SET @PUBID = (SELECT pub_id FROM pub WHERE pub_code = 'DC');
DROP TEMPORARY TABLE IF EXISTS feed_new;
CREATE TEMPORARY TABLE feed_new (
new_title VARCHAR(255), INDEX (new_title)
);
INSERT INTO feed_new
VALUES
('UK Investment Bonds 2010'),
('UK Protection 2010'),
('UK Personal Insurance Distribution 2010'),
('UK Private Medical Insurance 2010'),
...
('UK Private Motor Insurance 2010'),
('Wealth Management for Non-Resident Indians 2010'),
('Middle Eastern Cards Database 2010')
;
SELECT
feed_new.new_title AS `New Title FROM Feed`,
prod.prod_pub_prod_id AS `Lib Code FROM DB`,
prod.prod_title AS `Title FROM DB`,
prod.prod_num AS `Prod Num`,
prod.prod_published AS `Published FROM DB`
FROM feed_new JOIN prod
ON LEFT(feed_new.new_title, LENGTH(feed_new.new_title) - 5) =
LEFT(prod.prod_title, LENGTH(prod.prod_title) - 5)
WHERE prod.pub_id = @PUBID AND prod.prod_discont = 0
ORDER BY feed_new.new_title;
With a relatively small number of rows in `feed_new`, this can take many
seconds. With 163 rows in `feed_new`, compared against 11234 eligible rows
in
prod, it took about 28 seconds. Here's what an EXPLAIN looks like:
*** 1. row ***
id: 1
select_type: SIMPLE
table: feed_new
type: index
possible_keys: NULL
key: PRIMARY
key_len: 767
ref: NULL
rows: 1
Extra: Using index
*** 2. row ***
id: 1
select_type: SIMPLE
table: prod
type: ref
possible_keys: pub_id,pub_id_2
key: pub_id
key_len: 48
ref: const
rows: 11040
Extra: Using where
=
prod.pub_id is an indexed VARCHAR(15).
If I remove the string functions, I don't get what I want -- but the
remaining
query runs in .05 seconds. Here's an EXPLAIN of that one:
===
us-gii EXPLAIN
- SELECT
- feed_new.new_title AS `New Title FROM Feed`,
- prod.prod_pub_prod_id AS `Lib Code FROM DB`,
- prod.prod_title AS `Title FROM DB`,
- prod.prod_num AS `Prod Num`,
- prod.prod_published AS `Published FROM DB`
- FROM feed_new JOIN prod
- ON feed_new.new_title = prod.prod_title
- WHERE prod.pub_id = @PUBID AND prod.prod_discont = 0
- ORDER BY feed_new.new_title\G
*** 1. row ***
id: 1
select_type: SIMPLE
table: feed_new
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 767
ref: NULL
rows: 163
Extra: Using index
*** 2. row ***
id: 1
select_type: SIMPLE
table: prod
type: ref
possible_keys: pub_id,prod_title,pub_id_2,prod_title_fulltext
key: prod_title
key_len: 768
ref: giiexpr_db.feed_new.new_title
rows: 1
Extra: Using where
Obviously the string manipulation is keeping MySQL from using `prod_title`
as
a key, but I wouldn't have thought that using `pub_id` instead would be that
horrific.
Does anyone have any suggestions as to how to speed this business up? I
can't
get away without some string manipulation, because I'm looking for near
matches by ignoring the year at the end of the title.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=travis_...@hotmail.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Slow query using string operator
Yowsers! I expected that eliminating half of the string manipulation would
help, but I never imagined that the difference would be so great.
The SELECT now runs in well under a second.
=
SELECT pub_id FROM pub WHERE pub_code = 'DC' INTO @PUBID;
DROP TEMPORARY TABLE IF EXISTS feed_new;
CREATE TEMPORARY TABLE feed_new (
new_title VARCHAR(255), PRIMARY KEY (new_title),
new_title_truncated VARCHAR(255), INDEX (new_title_truncated)
);
# The next line loads up dummy data, but it looks like
# what I'm really using.
INSERT IGNORE INTO feed_new
SELECT prod.prod_title, LEFT(prod.prod_title, LENGTH(prod.prod_title) -
5)
FROM prod
WHERE prod.prod_discont = 0
AND prod.pub_id = @PUBID
AND RAND() .01;
SELECT
feed_new.new_title AS `New Title FROM Feed`,
prod.prod_pub_prod_id AS `Lib Code FROM DB`,
prod.prod_title AS `Title FROM DB`,
prod.prod_num AS `Prod Num`,
prod.prod_published AS `Published FROM DB`
FROM feed_new JOIN prod
ON feed_new.new_title_truncated = LEFT(prod.prod_title,
LENGTH(prod.prod_title) - 5)
WHERE prod.pub_id = @PUBID AND prod.prod_discont = 0
ORDER BY feed_new.new_title;
117 rows in set (0.25 sec)
EXPLAIN
SELECT
feed_new.new_title AS `New Title FROM Feed`,
prod.prod_pub_prod_id AS `Lib Code FROM DB`,
prod.prod_title AS `Title FROM DB`,
prod.prod_num AS `Prod Num`,
prod.prod_published AS `Published FROM DB`
FROM feed_new JOIN prod
ON feed_new.new_title_truncated = LEFT(prod.prod_title,
LENGTH(prod.prod_title) - 5)
WHERE prod.pub_id = @PUBID AND prod.prod_discont = 0
ORDER BY feed_new.new_title\G
*** 1. row ***
id: 1
select_type: SIMPLE
table: prod
type: ref
possible_keys: pub_id,pub_id_2
key: pub_id
key_len: 48
ref: const
rows: 11041
Extra: Using where; Using temporary; Using filesort
*** 2. row ***
id: 1
select_type: SIMPLE
table: feed_new
type: ref
possible_keys: new_title_truncated
key: new_title_truncated
key_len: 768
ref: func
rows: 1
Extra: Using where; Using index
==
It look like the optimizer flipped the JOIN around so that it could use the
key in feed_new.
Thanks for your help, all.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com
-Original Message-
From: Travis Ard [mailto:travis_...@hotmail.com]
Sent: Tuesday, August 10, 2010 6:53 PM
To: 'Jerry Schwartz'; mysql@lists.mysql.com
Subject: RE: Slow query using string operator
Can you create a second, indexed column in your feed_new temp table that
includes the title without the year appended? That might allow you to get
by with a single pass through the larger prod table and avoid reading rows
from the feed_new table.
-Travis
-Original Message-
From: Jerry Schwartz [mailto:je...@gii.co.jp]
Sent: Tuesday, August 10, 2010 3:39 PM
To: mysql@lists.mysql.com
Subject: Slow query using string operator
I'm running a set of queries that look like this:
===
SET @PUBID = (SELECT pub_id FROM pub WHERE pub_code = 'DC');
DROP TEMPORARY TABLE IF EXISTS feed_new;
CREATE TEMPORARY TABLE feed_new (
new_title VARCHAR(255), INDEX (new_title)
);
INSERT INTO feed_new
VALUES
('UK Investment Bonds 2010'),
('UK Protection 2010'),
('UK Personal Insurance Distribution 2010'),
('UK Private Medical Insurance 2010'),
...
('UK Private Motor Insurance 2010'),
('Wealth Management for Non-Resident Indians 2010'),
('Middle Eastern Cards Database 2010')
;
SELECT
feed_new.new_title AS `New Title FROM Feed`,
prod.prod_pub_prod_id AS `Lib Code FROM DB`,
prod.prod_title AS `Title FROM DB`,
prod.prod_num AS `Prod Num`,
prod.prod_published AS `Published FROM DB`
FROM feed_new JOIN prod
ON LEFT(feed_new.new_title, LENGTH(feed_new.new_title) - 5) =
LEFT(prod.prod_title, LENGTH(prod.prod_title) - 5)
WHERE prod.pub_id = @PUBID AND prod.prod_discont = 0
ORDER BY feed_new.new_title;
With a relatively small number of rows in `feed_new`, this can take many
seconds. With 163 rows in `feed_new`, compared against 11234 eligible rows
in
prod, it took about 28 seconds. Here's what an EXPLAIN looks like:
*** 1. row ***
id: 1
select_type: SIMPLE
table: feed_new
type: index
possible_keys: NULL
key: PRIMARY
key_len: 767
ref: NULL
rows: 1
Extra: Using index
*** 2. row
Re: Dropping ALL indexes from a database / not just a table?
How does one do that? How do you drop auto-increment attribute then drop the
index then restart the auto increment value where it was before you dropped
it?? I did not know you could do that.
The reason I ask is because the dbf_UID is a unique id tag the coders use to
identify a product by manufacturer kinda like a upc code for their internal db.
Can't have dups and don't want to have non-used id's in the db.
Any help, direction is much appreciated.
TIA...
Nunzio
From: Michael Dykman mdyk...@gmail.com
To: Nunzio Daveri nunziodav...@yahoo.com
Cc: Anirudh Sundar sundar.anir...@gmail.com; mysql@lists.mysql.com
Sent: Tue, August 10, 2010 5:03:44 PM
Subject: Re: Dropping ALL indexes from a database / not just a table?
auto_increment is only allowed on primary-keyed columns. I expect it
is not allowing you to drop the primary key because that column has
the auto_increment attribute. Drop that manually, and the primary key
should be able to let go.
- md
On Tue, Aug 10, 2010 at 5:58 PM, Nunzio Daveri nunziodav...@yahoo.com wrote:
Hi Micheal and all, ok so I did some digging around and I still can't find
why I cant drop the last few indexes.
mysql SELECT COUNT(1) FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema
= 'db_Market' AND table_name = 'dbt_Fruit' and index_name = 'PRIMARY';
+--+
| COUNT(1) |
+--+
|1 |
+--+
1 row in set (0.00 sec)
mysql DESCRIBE dbt_Fruit;
+--+--+--+-+-++
| Field| Type | Null | Key | Default |
Extra |
+--+--+--+-+-++
| dbf_UID | int(10) unsigned | NO | PRI | NULL|
auto_increment |
| dbf_Vendor | varchar(30) | NO | |
||
| dbf_Code | varchar(30) | NO | |
||
| dbf_Notes| text | YES | | NULL
||
+--+--+--+-+-++
mysql ALTER TABLE dbt_Fruit DROP FOREIGN KEY dbf_UID;
Query OK, 2947 rows affected (0.05 sec)
Records: 2947 Duplicates: 0 Warnings: 0
mysql ALTER TABLE dbt_Fruit DROP PRIMARY KEY;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto
column and it must be defined as a key
mysql ALTER TABLE dbt_Fruit DROP PRIMARY;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near '' at line 1
Any ideas??? I am wondering if it has something to do with the fact that
dbf_UID is a primary AND auto_increment?
TIA...
Nunzio
From: Michael Dykman mdyk...@gmail.com
To: Nunzio Daveri nunziodav...@yahoo.com
Cc: Anirudh Sundar sundar.anir...@gmail.com; mysql@lists.mysql.com
Sent: Tue, August 10, 2010 4:10:37 PM
Subject: Re: Dropping ALL indexes from a database / not just a table?
It's not a completely solution and will need some tweaking.. You
might have to run the PRIMARY KEYS distinctly from the rest.
- michael dykman
On Tue, Aug 10, 2010 at 4:43 PM, Nunzio Daveri nunziodav...@yahoo.com
wrote:
Hello Michael, thanks for the one liner. I ran it BUT I started to get
errors after I ran it the first time, this is what I got the 2nd time I
ran
it (first time I ran it I had 63 rows in the query, the 2nd time I have
9).
I ran it twice to make sure it got rid of the indexed. I verified the
index
size dropped from 850 mb to 65 mb.
+-+
| concat('ALTER TABLE ', TABLE_NAME, ' DROP INDEX ', CONSTRAINT_NAME,';')
|
+-+
| ALTER TABLE dbt_Fruits DROP INDEX PRIMARY; |
| ALTER TABLE dbt_Veggies DROP INDEX PRIMARY;
|
.
.
.
| ALTER TABLE dbt_Logs DROP INDEX
PRIMARY; |
+-+
9 rows in set (0.01 sec)
mysql ALTER TABLE dbt_Fruits DROP INDEX PRIMARY;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near 'PRIMARY' at line 1
mysql ALTER TABLE dbt_Logs DROP INDEX PRIMARY;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near 'PRIMARY' at line 1
mysql
Thanks again...
Nunzio
From: Michael Dykman mdyk...@gmail.com
To: Nunzio Daveri nunziodav...@yahoo.com
Cc: Anirudh Sundar sundar.anir...@gmail.com; mysql@lists.mysql.com
Sent: Tue, August 10, 2010 3:17:48 PM
Subject: Re: Dropping ALL indexes from a database / not just a table?
This should give
Re: info on mysql
use Google :) On Wed, Aug 11, 2010 at 10:02 PM, PRATIKSHA JAISWAL pratikshadjayswa...@gmail.com wrote: Hi Prabhat, Thanks a ton for sharing your blogs with us. I am learning mysql database. I am very good at PostgreSQL. Do you have any other material where in i can go for Mysql Certification and mainly mysql replication set up material etc.. -- Thanks Pratiksha On Wed, Aug 11, 2010 at 5:39 PM, Prabhat Kumar aim.prab...@gmail.comwrote: check this, http://adminlinux.blogspot.com/2009/12/mysql-tips-calculate-database-and-table.html Thx On Wed, Aug 11, 2010 at 5:23 PM, PRATIKSHA JAISWAL pratikshadjayswa...@gmail.com wrote: Hi, Thanks all for your help. ---Database Table wise Size in MB--- SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2), Mb) AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = dbname; ---Database Table wise Size in GB--- SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024 / 1024),3), Gb) AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = dbname; Result shows the EMPTY SET. On Tue, Aug 10, 2010 at 10:20 AM, Anirudh Sundar sundar.anir...@gmail.comwrote: Hello Pratiksha, To get the uptime of the MYSQL instance :- mysql\s as given above just give the above command \s For Total number of users connected to server :- mysqlshow global status like '%user%'; ---Database Table wise Size in MB--- SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024),2), Mb) AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = dbname; ---Database Table wise Size in GB--- SELECT TABLE_SCHEMA AS 'Database', TABLE_NAME AS 'Table', CONCAT(ROUND(((DATA_LENGTH + INDEX_LENGTH - DATA_FREE) / 1024 / 1024 / 1024),3), Gb) AS Size FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = dbname; Data size, index size no. of tables, engine type* SELECT s.schema_name,t.engine, CONCAT(IFNULL(ROUND(SUM(t.data_length)/1024/1024,2),0.00),Mb) as Data_size, CONCAT(IFNULL(ROUND(SUM(t.index_length)/1024/1024,2),0.00),Mb) as Index_size,COUNT(table_name) total_tables FROM INFORMATION_SCHEMA.SCHEMATA s LEFT JOIN INFORMATION_SCHEMA.TABLES t ON s.schema_name = t.table_schema WHERE s.schema_name not in(mysql,information_schema,test) GROUP BY s.schema_name,t.engine order by Data_size DESC; For top slow queries :- mysqldumpslow -s c -a -t 5 mysqlslow.log top10_slow-count_envr.log For Engine Info of a table and other details :- Show table status like 'tablename'; Cheers, Anirudh Sundar On Mon, Aug 9, 2010 at 9:31 PM, PRATIKSHA JAISWAL pratikshadjayswa...@gmail.com wrote: Hi All, How can i get following information from database or is there any query for the same. (1) mysql server uptime (2) Total number of users connected to server (3) Data file information / where it is located through mysql prompt / size of data file (4) each Database size (5) Database I/O information (6) Invalid object in database (7) Database performance statistics queries (8) Top 5 queries taking more time for executions. (9) Engine information. -- Thanks Pratiksha -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: idle query
I finally started trying to optimize along the memory-based lines you suggested. I am surprised to find that the query plan is to enumerate the memory-based table and then pick out the hundreds of related rows from the much larger MyISAM table. What's going on here? `show create table` says this about the relevant tables: CREATE TABLE `fldsndm` ( `p` varchar(200) NOT NULL, `cd` datetime NOT NULL, `cms` smallint(6) NOT NULL, `pip` char(15) NOT NULL, `pport` smallint(6) NOT NULL, `pboot` bigint(20) NOT NULL, `msgid` bigint(20) NOT NULL, `startgtime` bigint(20) NOT NULL, `datalen` int(11) NOT NULL, `toself` tinyint(1) DEFAULT NULL, `sepoch` bigint(20) NOT NULL DEFAULT '0', `c` decimal(11,3) NOT NULL DEFAULT '0.000', UNIQUE KEY `pbm` (`p`,`pboot`,`msgid`) ) ENGINE=MEMORY DEFAULT CHARSET=latin1 CREATE TABLE `fldrcv` ( `p` varchar(200) NOT NULL, `cd` datetime NOT NULL, `cms` smallint(6) NOT NULL, `pip` char(15) NOT NULL, `pport` smallint(6) NOT NULL, `pboot` bigint(20) DEFAULT NULL, `qip` char(15) NOT NULL, `qport` smallint(6) NOT NULL, `qboot` bigint(20) DEFAULT NULL, `msgid` bigint(20) NOT NULL, `startgtime` bigint(20) NOT NULL, `datalen` int(11) NOT NULL, `q` varchar(200) DEFAULT NULL, `repoch` bigint(20) NOT NULL DEFAULT '0', `c` decimal(11,3) NOT NULL DEFAULT '0.000', KEY `c` (`c`), KEY `pec` (`p`,`repoch`,`c`), KEY `peqms` (`p`,`repoch`,`q`,`msgid`,`startgtime`), KEY `qbm` (`q`,`qboot`,`msgid`), KEY `pbm` (`p`,`pboot`,`msgid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 And here is the query planning I see: mysql explain select * from fldrcv join fldsndm on fldrcv.q=fldsndm.p AND fldrcv.qboot=fldsndm.pboot and fldrcv.msgid=fldsndm.msgid; ++-+-+--+---+--+-+-+---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+-+--+---+--+-+-+---+-+ | 1 | SIMPLE | fldsndm | ALL | pbm | NULL | NULL| NULL | 29036 | | | 1 | SIMPLE | fldrcv | ref | qbm | qbm | 220 | bigCell2906_flood.fldsndm.p,bigCell2906_flood.fldsndm.pboot,bigCell2906_flood.fldsndm.msgid | 452 | Using where | ++-+-+--+---+--+-+-+---+-+ BTW, here are the table sizes: mysql select count(*) from fldrcv; +--+ | count(*) | +--+ | 13785373 | +--+ 1 row in set (0.00 sec) mysql select count(*) from fldsndm; +--+ | count(*) | +--+ |29036 | +--+ Thanks, Mike Spreitzer
Re: How to use SSL? (SSL is enabled but not used)
On 8/9/2010 5:27 PM, Yves Goergen wrote: Does anybody know how to use SSL-secured connections to a MySQL server? Has anybody done that at all? In the manual I have now found the following statement: http://dev.mysql.com/doc/refman/5.1/en/ssl-options.html Note that use of --ssl does not require an SSL connection. For example, if the server or client is compiled without SSL support, a normal unencrypted connection is used. What's that supposed to mean? If there's no way to force the connection into SSL, it is entirely useless. Anyone on the wire could simply pretend that the server doesn't support SSL and so deny the encryption and the client wouldn't even care... I don't want to use REQUIRE SSL for an account that is regularly used locally and doesn't need SSL. SSL should really be selected by the client per connection when connecting from some other untrusted network. The whole SSL thing looks pretty unfinished like that. If you don't want to require SSL on the local connections then don't set the flag on the @localhost account. If you want the SSL required on the other connections, then set it on the @'...' version of the account that the remote users login through. All MySQL accounts are tripartate. They consist of: 1) a login (user), 2) a host designation (or wildcard pattern), and 3) a password. Use that host portion of the account to make the distinction between local and remote logins. More about how the MySQL authentication works is available in the manual: http://dev.mysql.com/doc/refman/5.1/en/user-account-management.html -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
STRAIGHT JOIN vs. field names
Why is it that a field name that works fine for a JOIN is invalid in a STRAIGHT JOIN? mysql show create table fldsndm; +-++ | Table | Create Table| +-++ | fldsndm | CREATE TABLE `fldsndm` ( `p` varchar(200) NOT NULL, `cd` datetime NOT NULL, `cms` smallint(6) NOT NULL, `pip` char(15) NOT NULL, `pport` smallint(6) NOT NULL, `pboot` bigint(20) NOT NULL, `msgid` bigint(20) NOT NULL, `startgtime` bigint(20) NOT NULL, `datalen` int(11) NOT NULL, `toself` tinyint(1) DEFAULT NULL, `sepoch` bigint(20) NOT NULL DEFAULT '0', `c` decimal(11,3) NOT NULL DEFAULT '0.000', UNIQUE KEY `pbm` (`p`,`pboot`,`msgid`) ) ENGINE=MEMORY DEFAULT CHARSET=latin1 | +-++ 1 row in set (0.00 sec) mysql show create table fldrcv; ++--+ | Table | Create Table | ++--+ | fldrcv | CREATE TABLE `fldrcv` ( `p` varchar(200) NOT NULL, `cd` datetime NOT NULL, `cms` smallint(6) NOT NULL, `pip` char(15) NOT NULL, `pport` smallint(6) NOT NULL, `pboot` bigint(20) DEFAULT NULL, `qip` char(15) NOT NULL, `qport` smallint(6) NOT NULL, `qboot` bigint(20) DEFAULT NULL, `msgid` bigint(20) NOT NULL, `startgtime` bigint(20) NOT NULL, `datalen` int(11) NOT NULL, `q` varchar(200) DEFAULT NULL, `repoch` bigint(20) NOT NULL DEFAULT '0', `c` decimal(11,3) NOT NULL DEFAULT '0.000', KEY `c` (`c`), KEY `pec` (`p`,`repoch`,`c`), KEY `peqms` (`p`,`repoch`,`q`,`msgid`,`startgtime`), KEY `qbm` (`q`,`qboot`,`msgid`), KEY `pbm` (`p`,`pboot`,`msgid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
Re: STRAIGHT JOIN vs. field names
The relationship looks righteous enough but I note that you use 'straight join' in your expression, rather than 'straight_join' as indicated in the manual (http://dev.mysql.com/doc/refman/5.1/en/join.html). Perhaps the message is a red herring and your trouble is elsewhere? - michael dykman On Wed, Aug 11, 2010 at 4:25 PM, Mike Spreitzer mspre...@us.ibm.com wrote: Why is it that a field name that works fine for a JOIN is invalid in a STRAIGHT JOIN? mysql show create table fldsndm; +-++ | Table | Create Table | +-++ | fldsndm | CREATE TABLE `fldsndm` ( `p` varchar(200) NOT NULL, `cd` datetime NOT NULL, `cms` smallint(6) NOT NULL, `pip` char(15) NOT NULL, `pport` smallint(6) NOT NULL, `pboot` bigint(20) NOT NULL, `msgid` bigint(20) NOT NULL, `startgtime` bigint(20) NOT NULL, `datalen` int(11) NOT NULL, `toself` tinyint(1) DEFAULT NULL, `sepoch` bigint(20) NOT NULL DEFAULT '0', `c` decimal(11,3) NOT NULL DEFAULT '0.000', UNIQUE KEY `pbm` (`p`,`pboot`,`msgid`) ) ENGINE=MEMORY DEFAULT CHARSET=latin1 | +-++ 1 row in set (0.00 sec) mysql show create table fldrcv; ++--+ | Table | Create Table | ++--+ | fldrcv | CREATE TABLE `fldrcv` ( `p` varchar(200) NOT NULL, `cd` datetime NOT NULL, `cms` smallint(6) NOT NULL, `pip` char(15) NOT NULL, `pport` smallint(6) NOT NULL, `pboot` bigint(20) DEFAULT NULL, `qip` char(15) NOT NULL, `qport` smallint(6) NOT NULL, `qboot` bigint(20) DEFAULT NULL, `msgid` bigint(20) NOT NULL, `startgtime` bigint(20) NOT NULL, `datalen` int(11) NOT NULL, `q` varchar(200) DEFAULT NULL, `repoch` bigint(20) NOT NULL DEFAULT '0', `c` decimal(11,3) NOT NULL DEFAULT '0.000', KEY `c` (`c`), KEY `pec` (`p`,`repoch`,`c`), KEY `peqms` (`p`,`repoch`,`q`,`msgid`,`startgtime`), KEY `qbm` (`q`,`qboot`,`msgid`), KEY `pbm` (`p`,`pboot`,`msgid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
Re: STRAIGHT JOIN vs. field names
Yes, that's it. I should be typing STRAIGHT_JOIN instead of STRAIGHT JOIN. Thanks! Mike Spreitzer
Searching For Modules In a DB
I have a database called 'gaming' and with in that database there are several tables and data. I was asked to find a module called 'ako ldap' and disable it (setting it from 1 to 0). My question is how in MySQL do I search for a string if I don't even know what table to search in? I know how to search using the 'select' statement as long as I know where the table data is. In this case I only know which database but nothing more except what I am looking for. Can anyone please point me in the right direction? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Searching For Modules In a DB
Recently I went through a link related to this question. check the below link. I haven't checked the script, hope it will be useful. http://tequilaphp.wordpress.com/2010/07/05/searching-strings-in-a-database-and-files/ Regards, Jay MySQL DBA On Thu, Aug 12, 2010 at 2:31 AM, Carlos Mennens carlosw...@gmail.comwrote: I have a database called 'gaming' and with in that database there are several tables and data. I was asked to find a module called 'ako ldap' and disable it (setting it from 1 to 0). My question is how in MySQL do I search for a string if I don't even know what table to search in? I know how to search using the 'select' statement as long as I know where the table data is. In this case I only know which database but nothing more except what I am looking for. Can anyone please point me in the right direction? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jbhara...@gmail.com
