From: Karthik Pattabhiraman (karthikpjulysystems.com)
Date: Wed Oct 08 2008 - 05:30:03 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]