Hi,
1. Since requestid is a primary key then why you are using distinct in your
query. It's not required.
2. Index needs to be created on logtime.
3. Explain will best describe where indexing is required.
4. In your query use group by a.logtime (date_format is not required)
Your system must have more rams and high processor (quad core).
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.
On Wed, Oct 15, 2008 at 4:21 PM, Karthik Pattabhiraman <
[EMAIL PROTECTED]> wrote:
> 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]
>
--
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]
