Hallo Robert,
thanks for your commentaries. You are right. I didn't pasted the complete
SQL-statement. The correct one is:
SELECT
COUNT("ID") AS REQUESTCOUNT,
"SESSIONID" AS SESSIONID,
MIN("PSESSIONID") AS PSESSIONID,
MAX("PORTALID") AS PORTALID,
MIN("MEDIUM") AS MEDIUM,
MAX("MEDIUMNO") AS MEDIUMNO,
MIN("CREATIONDATE") AS CREATIONDATE,
"HOST" AS HOST,
MAX("COOKIEAGE") AS COOKIEAGE,
MIN("ID") AS FIRSTID,
MAX("ID") AS LASTID,
(NUM(MIN("CREATIONDATE") ) DIV 10000000000) AS DATEREF,
MIN("IPADDRESS") AS IPADDRESS
FROM "MAIN"."REQUEST"
GROUP BY "SESSIONID", "HOST"
This statement is the fist one of a process for the aggragation. In the
second step I aggragate this data again. But I need this value/ informations
before I can aggragate it. In addition I need the informations of this
statement/ table, if I want to make a sophisticated analyses. So if i use
only a view for this analyse, I worry about the response time. I think the
view-select-statement is nearly so slow as this insert-select. On the other
hand I make a incrementel update, so that I haven't to do this Insert
alldays but from time to time I have to.
So I think there is only a special parameter, which slows down this process
especially because of the minimal cpu load. Moreover the incrementel update
is fast, but if the data volume is getting bigger the total process gets
very slow.
Thanks a lot for your help.
Ralf Weisser
> --- Ursprüngliche Nachricht ---
> Von: "Robert Klemme" <[EMAIL PROTECTED]>
> An: "Ralf Udo Weisser" <[EMAIL PROTECTED]>
> Kopie: maxdb@lists.mysql.com
> Betreff: Re: Performance-Problem with 6 million rows and CPU load with 5%
> Datum: Fri, 28 Apr 2006 10:01:09 +0200
>
> Hi Ralf
>
> 2006/4/27, Ralf Udo Weisser <[EMAIL PROTECTED]>:
> > Hi,
> >
> > I am a beginner in MaxDB issues.
>
> Welcome!
>
> > My Problem is:
> > I save data of requests from a server-system in one table. This table
> has
> > nearly 6 million rows. Now I want to aggregate this data. In this task I
> > make a INSERT in another table. This statement insertes the aggregated
> data
> > from the request-table. In this table it has to be written nearly 5
> million
> > rows, too.
>
> That's not really an aggregation. I mean, your volume stays in the
> order of magintude of the original data. Depending on the nature of
> the data and what you do with it it might be more efficient to just
> define a view with your select and use that along with proper
> indexing.
>
> > In this Process the performance of the database is very slow, although
> > different index are set on this colums, where a maximum/minimum is
> > calculated. This calculation takes more than 6 hours. During this
> > aggragation I looked at the peformance of the system and I noticed, that
> the
> > system is not working to full capacity. The kernel-process of the
> database
> > was only at 5%.
>
> Your system is most likely IO bound. Do you use standard disks or do
> you have a fast RAID or SAN for your database files? Also, did you
> separate transaction log and data volumes physically (i.e. different
> hardware and IO channels)?
>
> > My system has Windows XP Prof and 2 processors with 1 GB ram. I
> allocated
> > the database 500MB ram for the kernel-process.
>
> Sounds good. Could be higher if you don't need the memory otherwise.
>
> > Now my question is: Are there special parameters for the database, which
> has
> > to be set, so that it increases more performance and more processor
> load?
> >
> > I can't invisage, that the problem arise from failure index or the
> > statement. As a precaution here is the statement:
> >
> > INSERT INTO "MAIN"."T_VISITS"
> > SELECT
> > COUNT("ID") AS REQUESTCOUNT,
> > "SESSIONID" AS SESSIONID,
> > MIN("PSESSIONID") AS PSESSIONID,
> > MIN("CREATIONDATE") AS CREATIONDATE,
> > MIN("IPADDRESS") AS IPADDRESS
> > FROM "MAIN"."REQUEST"
> >
> > with the Index to PSESSIONID, CREATIONDATE,IPADDRESS
>
> I don't see any GROUP BY. That will "aggregate" to a single record.
> Is this really the statement you are using? Is this really what you
> want?
>
> Kind regards
>
> robert
>
>
> --
> Have a look: http://www.flickr.com/photos/fussel-foto/
>
--
Echte DSL-Flatrate dauerhaft für 0,- Euro*!
"Feel free" mit GMX DSL! http://www.gmx.net/de/go/dsl
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
