improve performance of this sql
The below query performance in 10 sec when there are no other activity on db
, but when any insert or LOAD DATA Index creation happens it takes close to
80 sec. Any ways to improve the performance of this sql.
innodb_buffer=11GB , key_buffer=3 GB, we have totally 16GB
EXPLAIN select * from (select A.LEAF_CATEG_ID, A.CLUSTER_ID, A.SIGNATURE,
A.IS_NULL, A.HEIGHT, A.NO_LISTINGS,
A.NO_SUCC_LISTINGS,
A.TOTAL_QTY,A.SOLD_QTY, A.ASP, A.NO_BIDS, A.MIN_PRICE, A.MAX_PRICE,
A.MIN_ITEM_ID, A.MAX_ITEM_ID from C_DATA A,
R_DATA B, ER_MAP C where B.KEYWORDS IN ('CAMERA') and B.KR_ID = C.KR_ID and
C.CLUSTER_ID = A.CLUSTER_ID ) A limit
40;
++-+++-+-+-+-
--+--+--+
| id | select_type | table | type | possible_keys |
key | key_len | ref
| rows | Extra|
++-+++-+-+-+-
--+--+--+
| 1 | PRIMARY | | ALL| NULL|
NULL| NULL| NULL
| 3278 | |
| 2 | DERIVED | B | ref| PRIMARY,KD_KW_KI_IDX_0805230323 |
KD_KW_KI_IDX_0805230323 | 767 |
| 1524 | Using where; Using index |
| 2 | DERIVED | C | ref| PRIMARY |
PRIMARY | 10 |
reh.B.kr_id |1 | Using index |
| 2 | DERIVED | A | eq_ref | PRIMARY |
PRIMARY | 10 |
reh.C.cluster_id |1 | |
++-+++-+-+-+-
--+--+--+
Re: Error with max and group by
On Fri, Jun 20, 2008 at 10:50 PM, Joe Pearl <[EMAIL PROTECTED]> wrote: > I want to get back only the most recent entry for each person and I don't > care about the order. I want the result to show Jim with the acq_date of > "2008-01-03", Mary and Sally with the location and date for all of them. http://dev.mysql.com/doc/refman/5.0/en/example-maximum-row.html - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error with max and group by
Hi, My sql is rusty but I'm trying to solve a problem and I'm getting a result that does not make sense. The table is mysql> select * from cust_full; +---+-+--++ | name | item_id | location | acq_date | +---+-+--++ | Jim | 1 | OH | 2007-03-15 | | Mary | 2 | PA | 2007-01-15 | | Sally | 1 | OH | 2007-03-15 | | John | 0 | | -00-00 | | Jim | 3 | PA | 2008-01-03 | +---+-+--++ I want to get back only the most recent entry for each person and I don't care about the order. I want the result to show Jim with the acq_date of "2008-01-03", Mary and Sally with the location and date for all of them. However, when I run what I think should be the sql, I get: mysql> select name, item_id, location, max(acq_date) from cust_full group by name; +---+-+--+---+ | name | item_id | location | max(acq_date) | +---+-+--+---+ | Jim | 1 | OH | 2008-01-03| | John | 0 | | -00-00| | Mary | 2 | PA | 2007-01-15| | Sally | 1 | OH | 2007-03-15| +---+-+--+---+ Why am I getting the wrong location for Jim? It should be "PA". This is on a Mac. joe. 813.528.3859 My LinkedIn profile: http://www.linkedin.com/in/joepearl " We could learn a lot from crayons... Some are sharp, some are pretty and some are dull. Some have weird names, and all are different colors, but they all have to live in the same box. " - unknown
InnoDB File Fragmentation
I have a question about how InnoDB deals with fragmentation within it's data files. Let me describe my usage scenario to you: 1.) Records are inserted into a InnoDB table. We'll call this table "A". It contains several different kinds of columns including VARCHARs. 2.) Records are then processed by a process running on another server. The processed information is then stored in table "B" (this table also has VARCHARs), and the row that was processed it DELETEed from table A. This happens tens of times per second.Over time, additional InnoDB data files have been added because of data growth. My questions are these: * How does InnoDB store VARCHAR information? Is it based on the column max length? * How does InnoDB decide to re-use free blocks within the data files? Are rows prone to fragment? Thanks, -Aaron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR_FOR_DIVISION_BY_ZERO question
Hello all, Currently, if I use sql_mode ERROR_FOR_DIVISION_BY_ZERO and have a select statement that has division by zero, I get back a warning: mysql> set sql_mode=ERROR_FOR_DIVISION_BY_ZERO; Query OK, 0 rows affected (0.00 sec) mysql> select 1/0; +--+ | 1/0 | +--+ | NULL | +--+ 1 row in set, 1 warning (0.00 sec) mysql> show warnings; +---+--+---+ | Level | Code | Message | +---+--+---+ | Error | 1365 | Division by 0 | +---+--+---+ 1 row in set (0.00 sec) Is there a way to force an error instead? Thanks, Paul
MySQL using only 1 CPU
Hi, I've recently installed a MySQL 5.1.23-rc-log: | version | 5.1.23-rc-log | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | redhat-linux-gnu | I noticed that I always have 2 processes runing on Linux doesn't matter the amount of concurrent connections that is running on MySQL. Due to that, MySQL is using only 1 CPU instead of using 8 CPUs that exist on this machine: linux$ ps aux | grep mysql root 20327 0.0 0.0 64048 1272 ? S 10:05 0:00 /bin/sh ./bin/mysqld_safe --defaults-extra-file=/mysql/my.cnf --datadir=/mysql/data --pid-file=/mysql/data/mysql.pid mysql 20690 0.8 5.0 1571908 411772 ? Sl 10:05 3:09 /usr/local/mysql/bin/mysqld --defaults-extra-file=/mysql/my.cnf --basedir=/usr/local/mysql --datadir=/mysql/data --user=mysql --log-error=/mysql/log/orion-err.log --pid-file=/mysql/data/mysql.pid --port=3306 linux$ uname -a Linux orion.servers 2.6.18-92.1.1.el5 #1 SMP Thu May 22 09:01:47 EDT 2008 x86_64 x86_64 x86_64 GNU/Linux linux$ top top - 15:58:49 up 12:48, 6 users, load average: 0.24, 0.23, 0.13 Tasks: 183 total, 1 running, 182 sleeping, 0 stopped, 0 zombie Cpu0 : 4.7%us, 0.3%sy, 0.0%ni, 94.6%id, 0.0%wa, 0.0%hi, 0.3%si, 0.0%st Cpu1 : 0.0%us, 0.3%sy, 0.0%ni, 99.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu2 : 0.0%us, 0.3%sy, 0.0%ni, 99.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu3 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu4 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu5 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu6 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu7 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 8178664k total, 3641540k used, 4537124k free, 217664k buffers Swap: 8191992k total, 0k used, 8191992k free, 2443964k cached I've checked the new option thread_handling, but it indicates that MySQL has 1 thread per connection: mysql> show variables like 'thread_h%'; +-+---+ | Variable_name | Value | +-+---+ | thread_handling | one-thread-per-connection | +-+---+ 1 row in set (0.00 sec) Does anybody has any hints about it? Thanks in advance. Best wishes, Eber. Abra sua conta no Yahoo! Mail, o único sem limite de espaço para armazenamento! http://br.mail.yahoo.com/
Re: Migration of mysql 3.23.32 from Tru64 to RHEL 5.1
Hi Tom, all, Swigg, Tom C wrote: I have been given the task of migrating some 200+ web sites fom Tru64 UNIX to Red Hat Enterprise Linux 64 bit V5.1 Many of these sites use PHP3 (3.0.18) and mysql 3.23.32 and the developers have long gone... The objective is to provide a 3.23.32 environment on RHEL 5.1 which will run in parallel with the native mysql 5.0.22. IMO, the main problem is that these sources are very old and were not written (or checked) to work with current compilers. I have absolutely no experience with PHP, but I still think it might be easier to use current software versions and get rid of those old ones. [[...]] I have to do something similar for legacy php 4.2.3 again linked with mysql 3.23.32. We do not have the resources to migrate the code to mysql 5 and php 5 so building a mysql3/4 environment on RHEL 5.1 is essential. Any help gratefully received. I know that migrating to MySQL 5 and PHP 5 would need quite some resources, but I also think that adapting those old sources for current compilers might need a similar amount of resources. I do not think your current approach will take less effort than the migration to current versions. Consider that the information about your old versions is phasing out too, so staying with these versions would lead into a dead row. Regards, Jörg -- Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED] (+49 30) 417 01 487 Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Very large temporary file(s)
Double nuts! I corrected the wrong number. This time, I am reading my message more carefully. The temporary space used is indeed 800 MEGABYTES! I still need help, though. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Very large temporary file(s)
Nuts: not only did I write a huge message, but I made a booboo up at the top! I won't repost the whole thing. >I'm running MySQL 4.1.22-standard Community on CentOS. My problem is a >query >that is using about 800mb for what I assume is a temporary sort file, [JS] That should be 800Gb. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Very large temporary file(s)
I'm running MySQL 4.1.22-standard Community on CentOS. My problem is a query
that is using about 800mb for what I assume is a temporary sort file, and
I'm hoping that I can do something about it.
The purpose of this query is to populate an unnormalized table with data
from several other tables. I do this to make it easier for MS Access users
to use simple filters to see subsets of the data without having to construct
queries that have to be done as pass-through SQL.
This post is going to be long, so that you can see exactly what is going on.
I don't want to leave anything out that might be critical. Sorry...
Here are the tables involved:
Table: customers
Create Table: CREATE TABLE `customers` (
`customer_id` int(11) NOT NULL auto_increment,
`priority` int(3) NOT NULL default '0',
`account_id` int(2) NOT NULL default '0',
`sal` varchar(50) NOT NULL default '',
`first_name` varchar(125) NOT NULL default '',
`last_name` varchar(125) NOT NULL default '',
`company` varchar(255) NOT NULL default '',
`dept` varchar(255) NOT NULL default '',
`position` varchar(255) NOT NULL default '',
`address_1` varchar(255) NOT NULL default '',
`address_2` varchar(255) NOT NULL default '',
`address_3` varchar(255) NOT NULL default '',
`zip` varchar(15) NOT NULL default '',
`country` varchar(50) NOT NULL default '',
`phone` varchar(255) NOT NULL default '',
`fax` varchar(50) NOT NULL default '',
`email` varchar(255) NOT NULL default '',
`entry_date` datetime default NULL,
`follow_up` date default NULL,
`action` varchar(50) NOT NULL default '',
`stage_id` int(11) default '0',
`status` varchar(50) NOT NULL default '',
`exp_price` decimal(8,2) default '0.00',
`input_source` varchar(255) NOT NULL default '',
`input_date` date default NULL,
`interest_category` varchar(255) NOT NULL default '',
`interest_subcategory` varchar(255) NOT NULL default '',
`interest_keyword` varchar(255) NOT NULL default '',
`db_entry` tinyint(3) NOT NULL default,
`hidden` tinyint(1) NOT NULL default '0',
`email_status` set('Y','N') NOT NULL default 'Y',
`dm_status` set('Y','N') NOT NULL default 'Y',
`num_products_purchased` smallint(6) default NULL,
`key_account` set('Y','N') NOT NULL default 'N',
`phone2` varchar(255) NOT NULL default '',
PRIMARY KEY (`customer_id`),
KEY `account_id` (`account_id`),
KEY `priority_id` (`priority`),
KEY `stage_id` (`stage_id`),
KEY `email` (`email`),
KEY `last_name_index` (`last_name`)
) ENGINE=MyISAM AUTO_INCREMENT=73717 DEFAULT CHARSET=utf8
***
Table: stage
Create Table: CREATE TABLE `stage` (
`stage_id` int(11) NOT NULL auto_increment,
`stage_name` varchar(15) default NULL,
PRIMARY KEY (`stage_id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8
**
Table: cust_topics
Create Table: CREATE TABLE `cust_topics` (
`cust_topic_id` int(11) NOT NULL auto_increment,
`topic_code` varchar(15) NOT NULL default '',
`customer_id` int(11) NOT NULL default '0',
PRIMARY KEY (`cust_topic_id`),
KEY `topic_id` (`customer_id`),
KEY `topic_code` (`topic_code`)
) ENGINE=MyISAM AUTO_INCREMENT=143201 DEFAULT CHARSET=utf8
**
Table: account
Create Table: CREATE TABLE `account` (
`account_id` int(11) NOT NULL auto_increment,
`account_name` char(2) character set latin1 default NULL,
`real_name` varchar(30) NOT NULL default '' COMMENT 'Real name associated
with account',
PRIMARY KEY (`account_id`),
UNIQUE KEY `account_name` (`account_name`)
) ENGINE=MyISAM AUTO_INCREMENT=20 DEFAULT CHARSET=utf8
**
Table: consolidated_customer_data
Create Table: CREATE TABLE `consolidated_customer_data` (
`customer_id` int(11) NOT NULL default '0',
`acct_name` varchar(6) default NULL,
`email` varchar(60) default NULL,
`email_status` set('Y','N') NOT NULL default 'Y',
`dm_status` set('Y','N') NOT NULL default '',
`status` varchar(50) NOT NULL default '""',
`last_name` varchar(125) default NULL,
`first_name` varchar(125) default NULL,
`sal` varchar(50) default NULL,
`company` varchar(255) default NULL,
`address_1` varchar(255) default NULL,
`address_2` varchar(255) default NULL,
`address_3` varchar(255) default NULL,
`country` varchar(50) default NULL,
`zip` varchar(15) default NULL,
`input_source` varchar(255) default NULL,
`interest_category` varchar(255) NOT NULL default '""',
`interest_subcategory` varchar(255) NOT NULL default '""',
`topic_list` text,
`stage` varchar(255) NOT NULL default '""',
PRIMARY KEY (`customer_id`),
KEY `acct_name` (`acct_name`),
KEY `email` (`email`),
KEY `last_name` (`last_name`),
KEY `company` (`company`),
KEY `country` (`country`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
***
Before putting the data into the tabl
Re: trigger that calls a webservice??
On 20 Jun 2008, at 06:43, James wrote: On Fri, June 20, 2008 9:12 am, robert rottermann wrote: Hi there, is it possible to define an update trigger that calls a webservice (or just some external method that would do it). we have a web frontent, that does the indexing of data in its own catalog (zope/plone). so I would like to be able to "push" an update to the frontend. thanks robert I think the answer is no (at least it was last year) but I found work arounds. Google for "mysql external command trigger". You can declare a stored proc as an XMLRPC request and use that as a trigger. Such functionality already works in our experimental tree. http://forge.mysql.com/wiki/ProjectPage_External_Language_Stored_Procedures Regards Antony. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: trigger that calls a webservice??
James wrote: On Fri, June 20, 2008 9:12 am, robert rottermann wrote: Hi there, is it possible to define an update trigger that calls a webservice (or just some external method that would do it). we have a web frontent, that does the indexing of data in its own catalog (zope/plone). so I would like to be able to "push" an update to the frontend. thanks robert I think the answer is no (at least it was last year) but I found work arounds. Google for "mysql external command trigger". Well, you can create a UDF, and should be able to call the UDF within a trigger.. Check out some of the memcached UDFs that were created: http://capttofu.livejournal.com/8078.html These should give a good idea of A) how to create a UDF and B) how to talk to another process within them to send / update data etc. You might even choose to cache your stuff in memcached and just use these anyway! ;) Cheers, Mark -- Mark Leith MySQL Regional Support Manager, Americas Sun Microsystems, Inc., http://www.sun.com/mysql/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: trigger that calls a webservice??
On Fri, June 20, 2008 9:12 am, robert rottermann wrote: > Hi there, > is it possible to define an update trigger that calls a webservice (or > just some external method that would do it). > > we have a web frontent, that does the indexing of data in its own catalog > (zope/plone). > so I would like to be able to "push" an update to the frontend. > > thanks robert I think the answer is no (at least it was last year) but I found work arounds. Google for "mysql external command trigger". -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
trigger that calls a webservice??
Hi there, is it possible to define an update trigger that calls a webservice (or just some external method that would do it). we have a web frontent, that does the indexing of data in its own catalog (zope/plone). so I would like to be able to "push" an update to the frontend. thanks robert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reset of Status Parameters
I would also add Baron's maakit http://www.maatkit.org/ ( innotop ) for innodb details to the arsenal. On Fri, Jun 20, 2008 at 3:11 PM, Ian Simpson <[EMAIL PROTECTED]> wrote: > I tend to use the 'mytop' program, which shows the average > queries/second for the entire lifetime and for the last 5 seconds, as > well as showing a bunch of other statistics and a list of running > queries. It's a handy little monitoring tool. > > On Fri, 2008-06-20 at 12:17 +0530, Venu Madhav Padakanti wrote: > > I am using MySQL version 5.0.22, I am interested in knowing the current > > performance on the MySQL. > > > > With the status command we can get the queries per second but it will > > average since the beginning of time when SQL was up and running and not > > the current rate? > > > > Is there any way to reset that parameter so that the data can reflect > > current without restarting the MySQL > > > > Thanks in advance > > ..venu > -- > Ian Simpson > System Administrator > MyJobGroup > > This email may contain confidential information and is intended for the > recipient(s) only. If an addressing or transmission error has misdirected > this email, please notify the author by replying to this email. If you are > not the intended recipient(s) disclosure, distribution, copying or printing > of this email is strictly prohibited and you should destroy this mail. > Information or opinions in this message shall not be treated as neither > given nor endorsed by the company. Neither the company nor the sender > accepts any responsibility for viruses or other destructive elements and it > is your responsibility to scan any attachments. -- Thanks Alex http://alexlurthu.wordpress.com
Re: Reset of Status Parameters
I tend to use the 'mytop' program, which shows the average queries/second for the entire lifetime and for the last 5 seconds, as well as showing a bunch of other statistics and a list of running queries. It's a handy little monitoring tool. On Fri, 2008-06-20 at 12:17 +0530, Venu Madhav Padakanti wrote: > I am using MySQL version 5.0.22, I am interested in knowing the current > performance on the MySQL. > > With the status command we can get the queries per second but it will > average since the beginning of time when SQL was up and running and not > the current rate? > > Is there any way to reset that parameter so that the data can reflect > current without restarting the MySQL > > Thanks in advance > ..venu -- Ian Simpson System Administrator MyJobGroup This email may contain confidential information and is intended for the recipient(s) only. If an addressing or transmission error has misdirected this email, please notify the author by replying to this email. If you are not the intended recipient(s) disclosure, distribution, copying or printing of this email is strictly prohibited and you should destroy this mail. Information or opinions in this message shall not be treated as neither given nor endorsed by the company. Neither the company nor the sender accepts any responsibility for viruses or other destructive elements and it is your responsibility to scan any attachments.
Re: Reset of Status Parameters
if using innodb do show innodb status\G. Here u will see real time insert,delete,update and selects. On 6/20/08, Venu Madhav Padakanti <[EMAIL PROTECTED]> wrote: > > > I am using MySQL version 5.0.22, I am interested in knowing the current > performance on the MySQL. > > With the status command we can get the queries per second but it will > average since the beginning of time when SQL was up and running and not the > current rate? > > Is there any way to reset that parameter so that the data can reflect > current without restarting the MySQL > > Thanks in advance > ..venu >
