Dear R community,
I tried to optimized my R code by using Rprof. In my R code I'm using MySQL
database connections intensively. After a bunch of queries R fails with the
following error message:
Error in .Call("RS_MySQL_newConnection", drvId, con.params, groups,
PACKAGE = .MySQLPkgName) :
RS-DBI driver: (could not connect mylogin@mydatabase on dbname
"myDB"
Without the R profiler this code runs very stable since weeks.
Do you have any ideas or suggestions?
I tried the following R versions:
___________________________
platform i386-pc-solaris2.8
arch i386
os solaris2.8
system i386, solaris2.8
status
major 1
minor 9.1
year 2004
month 06
day 21
language R
___________________________
platform sparc-sun-solaris2.8
arch sparc
os solaris2.8
system sparc, solaris2.8
status
major 2
minor 1.1
year 2005
month 06
day 20
language R
___________________________
platform sparc-sun-solaris2.8
arch sparc
os solaris2.8
system sparc, solaris2.8
status
major 1
minor 9.1
year 2004
month 06
day 21
language R
Thank you in advance and kind regards,
Lutz Thieme
AMD Saxony/ Product Engineering AMD Saxony Limited Liability Company & Co.
KG
phone: + 49-351-277-4269 M/S E22-PE, Wilschdorfer Landstr. 101
fax: + 49-351-277-9-4269 D-01109 Dresden, Germany
[[alternative HTML version deleted]]
I think you're barking up the wrong tree. Optimize the MySQL code separately from optimizing the R code. A very nice reference about the former is http://highperformancemysql.com/. Also, if possible, do everything in MySQL. hth, b.> -----Original Message----- > From: Thieme, Lutz [mailto:lutz.thieme at amd.com] > Sent: Thursday, July 21, 2005 10:11 AM > To: Rhelp (E-mail) > Subject: [R] Rprof fails in combination with RMySQL > > > Dear R community, > > I tried to optimized my R code by using Rprof. In my R code > I'm using MySQL > database connections intensively. After a bunch of queries R > fails with the > following error message: > Error in .Call("RS_MySQL_newConnection", drvId, con.params, > groups, PACKAGE = .MySQLPkgName) : > RS-DBI driver: (could not connect mylogin at mydatabase > on dbname "myDB" > > Without the R profiler this code runs very stable since weeks. > > Do you have any ideas or suggestions? > > I tried the following R versions: > ___________________________ > platform i386-pc-solaris2.8 > arch i386 > os solaris2.8 > system i386, solaris2.8 > status > major 1 > minor 9.1 > year 2004 > month 06 > day 21 > language R > ___________________________ > platform sparc-sun-solaris2.8 > arch sparc > os solaris2.8 > system sparc, solaris2.8 > status > major 2 > minor 1.1 > year 2005 > month 06 > day 20 > language R > ___________________________ > platform sparc-sun-solaris2.8 > arch sparc > os solaris2.8 > system sparc, solaris2.8 > status > major 1 > minor 9.1 > year 2004 > month 06 > day 21 > language R > > > Thank you in advance and kind regards, > > Lutz Thieme > AMD Saxony/ Product Engineering AMD Saxony Limited > Liability Company & Co. KG > phone: + 49-351-277-4269 M/S E22-PE, > Wilschdorfer Landstr. 101 > fax: + 49-351-277-9-4269 D-01109 Dresden, Germany > > > [[alternative HTML version deleted]] > > ______________________________________________ > R-help at stat.math.ethz.ch mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide! > http://www.R-project.org/posting-guide.html >
Hello Bogdan, thanks for you reply. My MySQL is always optimized oustide from R (but many thanks for the interesting link!). I'm very sure that I have to optimize the R code which uses the data from my queries for calculations. To get more in- formation which R function is the main speed limiter I tried Rprof. Because I'm always opening and closing the connection for every query I have never opened more than one connection. And again: The same R code runs without Rprof stable since weeks multiple times a day. I can exclude by 99% that the error comes from the database. Maybe it comes from large number of opening closing cycles?... Regards, Lutz> -----Original Message----- > From: bogdan romocea [mailto:br44114 at gmail.com] > Sent: Thursday, July 21, 2005 5:05 PM > To: Thieme, Lutz > Cc: R-help at stat.math.ethz.ch > Subject: RE: [R] Rprof fails in combination with RMySQL > > > I think you're barking up the wrong tree. Optimize the MySQL code > separately from optimizing the R code. A very nice reference about the > former is http://highperformancemysql.com/. Also, if possible, do > everything in MySQL. > hth, > b. > > > > -----Original Message----- > > From: Thieme, Lutz [mailto:lutz.thieme at amd.com] > > Sent: Thursday, July 21, 2005 10:11 AM > > To: Rhelp (E-mail) > > Subject: [R] Rprof fails in combination with RMySQL > > > > > > Dear R community, > > > > I tried to optimized my R code by using Rprof. In my R code > > I'm using MySQL > > database connections intensively. After a bunch of queries R > > fails with the > > following error message: > > Error in .Call("RS_MySQL_newConnection", drvId, con.params, > > groups, PACKAGE = .MySQLPkgName) : > > RS-DBI driver: (could not connect mylogin at mydatabase > > on dbname "myDB" > > > > Without the R profiler this code runs very stable since weeks. > > > > Do you have any ideas or suggestions? > > > > I tried the following R versions: > > ___________________________ > > platform i386-pc-solaris2.8 > > arch i386 > > os solaris2.8 > > system i386, solaris2.8 > > status > > major 1 > > minor 9.1 > > year 2004 > > month 06 > > day 21 > > language R > > ___________________________ > > platform sparc-sun-solaris2.8 > > arch sparc > > os solaris2.8 > > system sparc, solaris2.8 > > status > > major 2 > > minor 1.1 > > year 2005 > > month 06 > > day 20 > > language R > > ___________________________ > > platform sparc-sun-solaris2.8 > > arch sparc > > os solaris2.8 > > system sparc, solaris2.8 > > status > > major 1 > > minor 9.1 > > year 2004 > > month 06 > > day 21 > > language R > > > > > > Thank you in advance and kind regards, > > > > Lutz Thieme > > AMD Saxony/ Product Engineering AMD Saxony Limited > > Liability Company & Co. KG > > phone: + 49-351-277-4269 M/S E22-PE, > > Wilschdorfer Landstr. 101 > > fax: + 49-351-277-9-4269 D-01109 Dresden, Germany > > > > > > [[alternative HTML version deleted]] > > > > ______________________________________________ > > R-help at stat.math.ethz.ch mailing list > > https://stat.ethz.ch/mailman/listinfo/r-help > > PLEASE do read the posting guide! > > http://www.R-project.org/posting-guide.html > > > >
I think the opposite is applicable too - optimize R outside of MySQL.
Exclude the MySQL queries completely and use instead the same data
frames (prepared beforehand) with Rprof. Then, if you really want to
run the full code with Rprof, wrap the queries in try():
data <- try(fetch(dbSendQuery(connection,query),n=-1))
if (class(data) == "try-error") for (i in 1:100) {
data <- try(fetch(dbSendQuery(connection,query),n=-1))
if (class(data) != "try-error") break
}
Also, why do you close the connection after each query? Open one
connection and use it for the whole R session. (I never close the
connection after a query.)
hth,
b.
> -----Original Message-----
> From: Thieme, Lutz [mailto:lutz.thieme at amd.com]
> Sent: Friday, July 22, 2005 2:04 AM
> To: bogdan romocea
> Cc: R-help at stat.math.ethz.ch
> Subject: Re: [R] Rprof fails in combination with RMySQL
>
>
> Hello Bogdan,
>
> thanks for you reply. My MySQL is always optimized oustide
> from R (but many thanks for the interesting link!).
> I'm very sure that I have to optimize the R code which uses
> the data from my queries for calculations. To get more in-
> formation which R function is the main speed limiter I tried
> Rprof.
> Because I'm always opening and closing the connection for every
> query I have never opened more than one connection.
> And again: The same R code runs without Rprof stable since weeks
> multiple times a day. I can exclude by 99% that the error comes
> from the database. Maybe it comes from large number of opening
> closing cycles?...
>
> Regards,
>
> Lutz
>
>
>
> > -----Original Message-----
> > From: bogdan romocea [mailto:br44114 at gmail.com]
> > Sent: Thursday, July 21, 2005 5:05 PM
> > To: Thieme, Lutz
> > Cc: R-help at stat.math.ethz.ch
> > Subject: RE: [R] Rprof fails in combination with RMySQL
> >
> >
> > I think you're barking up the wrong tree. Optimize the MySQL code
> > separately from optimizing the R code. A very nice
> reference about the
> > former is http://highperformancemysql.com/. Also, if possible, do
> > everything in MySQL.
> > hth,
> > b.
> >
> >
> > > -----Original Message-----
> > > From: Thieme, Lutz [mailto:lutz.thieme at amd.com]
> > > Sent: Thursday, July 21, 2005 10:11 AM
> > > To: Rhelp (E-mail)
> > > Subject: [R] Rprof fails in combination with RMySQL
> > >
> > >
> > > Dear R community,
> > >
> > > I tried to optimized my R code by using Rprof. In my R code
> > > I'm using MySQL
> > > database connections intensively. After a bunch of queries R
> > > fails with the
> > > following error message:
> > > Error in .Call("RS_MySQL_newConnection", drvId,
con.params,
> > > groups, PACKAGE = .MySQLPkgName) :
> > > RS-DBI driver: (could not connect mylogin at mydatabase
> > > on dbname "myDB"
> > >
> > > Without the R profiler this code runs very stable since weeks.
> > >
> > > Do you have any ideas or suggestions?
> > >
> > > I tried the following R versions:
> > > ___________________________
> > > platform i386-pc-solaris2.8
> > > arch i386
> > > os solaris2.8
> > > system i386, solaris2.8
> > > status
> > > major 1
> > > minor 9.1
> > > year 2004
> > > month 06
> > > day 21
> > > language R
> > > ___________________________
> > > platform sparc-sun-solaris2.8
> > > arch sparc
> > > os solaris2.8
> > > system sparc, solaris2.8
> > > status
> > > major 2
> > > minor 1.1
> > > year 2005
> > > month 06
> > > day 20
> > > language R
> > > ___________________________
> > > platform sparc-sun-solaris2.8
> > > arch sparc
> > > os solaris2.8
> > > system sparc, solaris2.8
> > > status
> > > major 1
> > > minor 9.1
> > > year 2004
> > > month 06
> > > day 21
> > > language R
> > >
> > >
> > > Thank you in advance and kind regards,
> > >
> > > Lutz Thieme
> > > AMD Saxony/ Product Engineering AMD Saxony Limited
> > > Liability Company & Co. KG
> > > phone: + 49-351-277-4269 M/S E22-PE,
> > > Wilschdorfer Landstr. 101
> > > fax: + 49-351-277-9-4269 D-01109 Dresden, Germany
> > >
> > >
> > > [[alternative HTML version deleted]]
> > >
> > > ______________________________________________
> > > R-help at stat.math.ethz.ch mailing list
> > > https://stat.ethz.ch/mailman/listinfo/r-help
> > > PLEASE do read the posting guide!
> > > http://www.R-project.org/posting-guide.html
> > >
> >
> >
>
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide!
> http://www.R-project.org/posting-guide.html
>