You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@mahout.apache.org by Karl Eigengrund <ka...@ymail.com> on 2010/11/08 22:29:19 UTC
Usage of MySQL as DataSource with connection pooling
Hello,
I am new to Mahout. I am using it to get recommendations out of my data stored in a MySQL database. I have started by configuring the DataSource programmatically, and followed the performace hints in the JavaDoc for MySQLJDBCDataModel. But without connection pooling the performance is with just 3000 preferences extremely bad.
I am using Tomcat and I have done the configuration as described here: http://dev.mysql.com/doc/refman/5.0/en/connector-j-usagenotes-j2ee.html,
under 20.3.5.2.2. Using Connector/J with Tomcat
My Web.xml:
<resource-ref>
<res-ref-name>jdbc/ MySQLDB</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
<res-sharing-scope>Shareable</res-sharing-scope>
</resource-ref>
In the context.xml I have done the same configuration as described under point 20.3.5.2.2. Using Connector/J with Tomcat (see link above)
The usual way for retrieving the DataSource via JNDI doesn’t work for me, since Mahout requires a MySQLJDBCDataModel. The following cast doesn’t work:
InitialContext ctx = new InitialContext();
dataSource = (MysqlDataSource)ctx.lookup(
“java:comp/env/jdbc/MySQLDB");
ERROR:
java.lang.ClassCastException: org.apache.tomcat.dbcp.dbcp.BasicDataSource cannot be cast to com.mysql.jdbc.jdbc2.optional.MysqlDataSource
What is the correct way to retrieve the datasource via JNDI and use pooling?
Thank you.
Re: Usage of MySQL as DataSource with connection pooling
Posted by Sean Owen <sr...@gmail.com>.
It is not being configured -- class and URL are null. You might try my
style of configuring it as I know it works.
On Tue, Nov 9, 2010 at 2:46 PM, Karl Eigengrund
<ka...@ymail.com> wrote:
> Thank you for your help!!
> I have tried it as you suggested and also on a different system (Ubuntu instead of Windows), but I can't get it work. Before I have started Tomcat always through Eclipse, now I tried it directly and now I get the following exception by accessing it through the browser:
>
> javax.servlet.ServletException: org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC driver of class '' for connect URL 'null'
>
Re: Usage of MySQL as DataSource with connection pooling
Posted by Karl Eigengrund <ka...@ymail.com>.
Thank you for your help!!
I have tried it as you suggested and also on a different system (Ubuntu instead of Windows), but I can't get it work. Before I have started Tomcat always through Eclipse, now I tried it directly and now I get the following exception by accessing it through the browser:
javax.servlet.ServletException: org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC driver of class '' for connect URL 'null'
org.apache.mahout.cf.taste.web.RecommenderServlet.init(RecommenderServlet.java:76)
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:174)
org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:879)
org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:689)
java.lang.Thread.run(Unknown Source)
That is really strange to me, since I can see the JNDI Name in the admin webapplication. I am already quite frustrated :(
--- Sean Owen <sr...@gmail.com> schrieb am Di, 9.11.2010:
> Von: Sean Owen <sr...@gmail.com>
> Betreff: Re: Usage of MySQL as DataSource with connection pooling
> An: user@mahout.apache.org
> Datum: Dienstag, 9. November, 2010 14:19 Uhr
> I take it back -- I think the way you
> are configuring it is also valid.
> However the problem is that for whatever reason it is not
> configuring
> and deploying JNDI correctly. You should look in the server
> logs for a
> clue to the problem.
>
> On Tue, Nov 9, 2010 at 11:03 AM, Sean Owen <sr...@gmail.com>
> wrote:
> > This really isn't a question about Mahout itself, but
> I believe I can
> > still help.
> >
> > I don't see anywhere that you've actually configured
> the DataSource!
> > You need something like this in server.xml; this is my
> standard
> > "recipe":
>
Re: Usage of MySQL as DataSource with connection pooling
Posted by Sean Owen <sr...@gmail.com>.
I take it back -- I think the way you are configuring it is also valid.
However the problem is that for whatever reason it is not configuring
and deploying JNDI correctly. You should look in the server logs for a
clue to the problem.
On Tue, Nov 9, 2010 at 11:03 AM, Sean Owen <sr...@gmail.com> wrote:
> This really isn't a question about Mahout itself, but I believe I can
> still help.
>
> I don't see anywhere that you've actually configured the DataSource!
> You need something like this in server.xml; this is my standard
> "recipe":
Re: Usage of MySQL as DataSource with connection pooling
Posted by Sean Owen <sr...@gmail.com>.
This really isn't a question about Mahout itself, but I believe I can
still help.
I don't see anywhere that you've actually configured the DataSource!
You need something like this in server.xml; this is my standard
"recipe":
<GlobalNamingResources>
<Resource
name="jdbc/MySQLDB" auth="Container" type="javax.sql.DataSource"
removeAbandoned="true" logAbandoned="true" maxActive="16"
validationQuery="SELECT 1"
username="username" password="password"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://yourhost:3306/yourDB?autoReconnect=true&autoReconnectForPools=true"/>
</GlobalNamingResources>
Re: Usage of MySQL as DataSource with connection pooling
Posted by Karl Eigengrund <ka...@ymail.com>.
Thank you for your answer! I am using indexes as you suggest in your book, but I have problems with the configuration for connection pooling.
I have used the following code:
DataSource dataSource=null;
try {
dataSource = (DataSource)ctx.lookup("java:/comp/env/jdbc/MySQLDB");
} catch (NamingException e) {
e.printStackTrace();
}
System.out.println("DataSource found: "+dataSource.toString());
return dataSource;
I added the following part to my web.xml:
<resource-ref>
<res-ref-name>jdbc/MySQLDB</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
<res-sharing-scope>Shareable</res-sharing-scope>
</resource-ref>
Below you can find my settings in the context.xml of my tomcat. Somewhere must be a problem since I get the following error when I try to start Tomcat via Eclipse:
09.11.2010 10:47:55 org.apache.catalina.core.StandardHost start
INFO: XML validation disabled
DataSource found: org.apache.tomcat.dbcp.dbcp.BasicDataSource@10045eb
09.11.2010 10:47:55 org.slf4j.impl.JCLLoggerAdapter warn
WARNING: You are not using ConnectionPoolDataSource. Make sure your DataSource pools connections to the database itself, or database performance will be severely reduced.
09.11.2010 10:47:55 org.slf4j.impl.JCLLoggerAdapter warn
WARNING: Exception while retrieving number of items
09.11.2010 10:47:55 org.apache.catalina.core.StandardContext loadOnStartup
SEVERE: Servlet /Recommender threw load() exception
java.lang.NullPointerException
at sun.jdbc.odbc.JdbcOdbcDriver.getProtocol(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcDriver.knownURL(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcDriver.acceptsURL(Unknown Source)
at java.sql.DriverManager.getDriver(Unknown Source)
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createConnectionFactory(BasicDataSource.java:1437)
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1371)
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
at org.apache.mahout.cf.taste.impl.model.jdbc.AbstractJDBCDataModel.getNumThings(AbstractJDBCDataModel.java:507)
at org.apache.mahout.cf.taste.impl.model.jdbc.AbstractJDBCDataModel.getNumItems(AbstractJDBCDataModel.java:475)
at org.apache.mahout.cf.taste.impl.similarity.AbstractSimilarity.<init>(AbstractSimilarity.java:67)
at org.apache.mahout.cf.taste.impl.similarity.AbstractSimilarity.<init>(AbstractSimilarity.java:53)
at org.apache.mahout.cf.taste.impl.similarity.PearsonCorrelationSimilarity.<init>(PearsonCorrelationSimilarity.java:60)
at org.apache.mahout.cf.taste.example.grouplens.ArtRecommender.<init>(ArtRecommender.java:64)
at org.apache.mahout.cf.taste.example.grouplens.ArtRecommender.<init>(ArtRecommender.java:53)
my context.xml:
<?xml version="1.0" encoding="UTF-8"?>
<!--
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements. See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
-->
<!-- The contents of this file will be loaded for each web application -->
<Context>
<!-- Default set of monitored resources -->
<WatchedResource>WEB-INF/web.xml</WatchedResource>
<!-- Uncomment this to disable session persistence across Tomcat restarts -->
<!--
<Manager pathname="" />
-->
<Resource name="jdbc/MySQLDB"
auth="Container"
type="javax.sql.DataSource"/>
<!-- The name you used above, must match _exactly_ here!
The connection pool will be bound into JNDI with the name
"java:/comp/env/jdbc/MySQLDB"
-->
<ResourceParams name="jdbc/MySQLDB">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<!-- Don't set this any higher than max_connections on your
MySQL server, usually this should be a 10 or a few 10's
of connections, not hundreds or thousands -->
<parameter>
<name>maxActive</name>
<value>10</value>
</parameter>
<!-- You don't want to many idle connections hanging around
if you can avoid it, only enough to soak up a spike in
the load -->
<parameter>
<name>maxIdle</name>
<value>5</value>
</parameter>
<!-- Don't use autoReconnect=true, it's going away eventually
and it's a crutch for older connection pools that couldn't
test connections. You need to decide whether your application
is supposed to deal with SQLExceptions (hint, it should), and
how much of a performance penalty you're willing to pay
to ensure 'freshness' of the connection -->
<parameter>
<name>validationQuery</name>
<value>SELECT 1</value> <-- See discussion below for update to this option -->
</parameter>
<!-- The most conservative approach is to test connections
before they're given to your application. For most applications
this is okay, the query used above is very small and takes
no real server resources to process, other than the time used
to traverse the network.
If you have a high-load application you'll need to rely on
something else. -->
<parameter>
<name>testOnBorrow</name>
<value>true</value>
</parameter>
<!-- Otherwise, or in addition to testOnBorrow, you can test
while connections are sitting idle -->
<parameter>
<name>testWhileIdle</name>
<value>true</value>
</parameter>
<!-- You have to set this value, otherwise even though
you've asked connections to be tested while idle,
the idle evicter thread will never run -->
<parameter>
<name>timeBetweenEvictionRunsMillis</name>
<value>10000</value>
</parameter>
<!-- Don't allow connections to hang out idle too long,
never longer than what wait_timeout is set to on the
server...A few minutes or even fraction of a minute
is sometimes okay here, it depends on your application
and how much spikey load it will see -->
<parameter>
<name>minEvictableIdleTimeMillis</name>
<value>60000</value>
</parameter>
<!-- Username and password used when connecting to MySQL -->
<parameter>
<name>username</name>
<value>artmaster</value>
</parameter>
<parameter>
<name>password</name>
<value>00art</value>
</parameter>
<!-- Class name for the Connector/J driver -->
<parameter>
<name>driverClassName</name>
<value>com.mysql.jdbc.Driver</value>
</parameter>
<!-- The JDBC connection url for connecting to MySQL, notice
that if you want to pass any other MySQL-specific parameters
you should pass them here in the URL, setting them using the
parameter tags above will have no effect, you will also
need to use & to separate parameter values as the
ampersand is a reserved character in XML -->
<parameter>
<name>url</name>
<value>jdbc:mysql://localhost:3306/artuserdata</value>
</parameter>
</ResourceParams>
</Context>
--- Sean Owen <sr...@gmail.com> schrieb am Mo, 8.11.2010:
> Von: Sean Owen <sr...@gmail.com>
> Betreff: Re: Usage of MySQL as DataSource with connection pooling
> An: user@mahout.apache.org
> Datum: Montag, 8. November, 2010 22:24 Uhr
> On Mon, Nov 8, 2010 at 9:29 PM, Karl
> Eigengrund
> <ka...@ymail.com>
> wrote:
> > I am new to Mahout. I am using it to get
> recommendations out of my data stored in a MySQL database. I
> have started by configuring the DataSource programmatically,
> and followed the performace hints in the JavaDoc for
> MySQLJDBCDataModel. But without connection pooling the
> performance is with just 3000 preferences extremely bad.
>
> I think we exchanged a message before -- the issue was
> perhaps you
> were not using connection pools or indexes?
>
>
> > I am using Tomcat and I have done the configuration
> as described here: http://dev.mysql.com/doc/refman/5.0/en/connector-j-usagenotes-j2ee.html,
> > under 20.3.5.2.2. Using Connector/J with Tomcat
>
> Looks right.
>
> > The usual way for retrieving the DataSource via JNDI
> doesn’t work for me, since Mahout requires a
> MySQLJDBCDataModel. The following cast doesn’t work:
> >
> > InitialContext ctx = new InitialContext();
> > dataSource = (MysqlDataSource)ctx.lookup(
> > “java:comp/env/jdbc/MySQLDB");
> >
>
> It does not require MysqlDataSource; not sure what you
> mean. It does
> not for the reason you find. Just remove the cast.
>
Re: Usage of MySQL as DataSource with connection pooling
Posted by Sean Owen <sr...@gmail.com>.
On Mon, Nov 8, 2010 at 9:29 PM, Karl Eigengrund
<ka...@ymail.com> wrote:
> I am new to Mahout. I am using it to get recommendations out of my data stored in a MySQL database. I have started by configuring the DataSource programmatically, and followed the performace hints in the JavaDoc for MySQLJDBCDataModel. But without connection pooling the performance is with just 3000 preferences extremely bad.
I think we exchanged a message before -- the issue was perhaps you
were not using connection pools or indexes?
> I am using Tomcat and I have done the configuration as described here: http://dev.mysql.com/doc/refman/5.0/en/connector-j-usagenotes-j2ee.html,
> under 20.3.5.2.2. Using Connector/J with Tomcat
Looks right.
> The usual way for retrieving the DataSource via JNDI doesn’t work for me, since Mahout requires a MySQLJDBCDataModel. The following cast doesn’t work:
>
> InitialContext ctx = new InitialContext();
> dataSource = (MysqlDataSource)ctx.lookup(
> “java:comp/env/jdbc/MySQLDB");
>
It does not require MysqlDataSource; not sure what you mean. It does
not for the reason you find. Just remove the cast.