Hi Krishna,
I have one more question for you. My table schema is as follows
create table RequestDO (
country VARCHAR(256),
device VARCHAR(256),
devicemanufacturer VARCHAR(256),
entryPage INTEGER,
exitPage INTEGER,
logicalPageName VARCHAR(3999),
logtime TIMESTAMP,
newrepeat VARCHAR(2),
operator VARCHAR(256),
pagename VARCHAR(3999),
referer VARCHAR(256),
requestid VARCHAR(256) [primary key],
requesttype INTEGER,
responseduration INTEGER,
responsesize INTEGER,
revenue INTEGER,
sessionid VARCHAR(256),
sitename VARCHAR(3999),
source VARCHAR(3999),
subscriberid VARCHAR(256),
subscribermsisdn VARCHAR(256),
subscribertype VARCHAR(2)
) engine=innodb;
My current query is as follows:
SELECT DATE_FORMAT(A.LOGTIME, '%Y-%m-%d %H') AS LOGTIME,
COUNT(DISTINCT A.REQUESTID) AS PAGE_VIEWS
FROM RequestDO A
WHERE A.LOGTIME BETWEEN DATE_FORMAT('2008-09-15 00', '%Y-%m-%d %H') AND
DATE_FORMAT('2008-09-15 23', '%Y-%m-%d %H')
GROUP BY DATE_FORMAT(A.LOGTIME, '%Y-%m-%d %H')
This table has about 200Million records and it takes hours to come out.
In this table requestid is unique. What other indexes can I create to
speed up the response time of my query? What should I configure in
mysql? Can you help me with this?
I have 6GB RAM, 2 cores and 7200 RPM disk with 1TB size.
Thanks in advance
Karthik
From: Krishna Chandra Prajapati [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 15, 2008 3:43 PM
To: Karthik Pattabhiraman
Cc: mysql@lists.mysql.com
Subject: Re: Trouble with large data in MySql
Hi,
It depends on your query using join and sort. Also, on how much ram you
have. You can refer to huge_my.cnf
You can find huge_my.cnf in the complied mysql-server binary.
join 12 MB
sort 8 MB
On Wed, Oct 15, 2008 at 3:29 PM, Karthik Pattabhiraman
<[EMAIL PROTECTED]> wrote:
I have not specified any values for join_buffer_size and
sort_buffer_size. What should be the optimal values for this?
-Karthik
From: Krishna Chandra Prajapati [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 14, 2008 10:03 AM
To: Karthik Pattabhiraman
Cc: mysql@lists.mysql.com
Subject: Re: Trouble with large data in MySql
Hi Karthik,
You have many columns in a single table (although you can). But, if you
break the below table into two tables ( Vertical partitioning). It will
help you to run your query faster. You haven't shown join_buffer_size
and sort_buffer_size.
Secondly, check your all the queries with explain.
On Wed, Oct 8, 2008 at 4:00 PM, Karthik Pattabhiraman
<[EMAIL PROTECTED]> wrote:
Hi,
We have 4 tables in which we have approximately 40 Million records
per month. We are having trouble getting results from MySql as it takes
about 4-5 hours to complete for each query. We are using this primarily
for reporting purposes.
My table schema is as follows
SMAS Table:
Column Name
Type
Key
adnetwork
adnetworkResponse
campaignName
clientRequest
logkey
loggingTime
logmodule
pageName
propertyName
requestId
requestSystemId
serverResponse
sessionId
siteName
sucess
systemId
varchar(3999)
text
varchar(3999)
varchar(3999)
varchar(3999)
timestamp
varchar(3999)
varchar(3999)
varchar(3999)
varchar(3999)
varchar(3999)
text
varchar(256)
varchar(3999)
int(11)
varchar(3999)
MUL
MUL
Currently, SMAS table has 40Million records and our query takes 5 hours
to execute.
My my.cnf file is as follows and all tables are InnoDB.
[mysqld]
datadir=/mnt/data-store/mysql/data
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
innodb_data_home_dir=/mnt/data-store/mysql/data
innodb_data_file_path=ibdata1:15G:autoextend
innodb_buffer_pool_size=3G
max_connections=200
tmpdir=/mnt/data-store/tmp
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
My query is as follows on SMAS table
select campaignName, siteName, adnetwork,date_format(loggingTime
,'%d/%m/%Y') logDate, count(distinct requestid) adpages
from SMAS where sucess = 1 GROUP BY 1,2,3,4;
Any help will be highly appreciated.
-Karthik
--
Krishna Chandra Prajapati
MySQL DBA,
Ed Ventures e-Learning Pvt.Ltd.
1-8-303/48/15, Sindhi Colony
P.G.Road, Secunderabad.
Pin Code: 500003
Office Number: 040-66489771
Mob: 9912924044
URL: ed-ventures-online.com
Email-id: [EMAIL PROTECTED]
--
Krishna Chandra Prajapati
MySQL DBA,
Ed Ventures e-Learning Pvt.Ltd.
1-8-303/48/15, Sindhi Colony
P.G.Road, Secunderabad.
Pin Code: 500003
Office Number: 040-66489771
Mob: 9912924044
URL: ed-ventures-online.com
Email-id: [EMAIL PROTECTED]
