-
TTD #121: JDBC resource for MySQL and Oracle sample database in GlassFish v3
This blog clearly explains how to configure the MySQL sample database (sakila) with GlassFish. Even though the instructions use a specific database but should work for other databases (such as Oracle, JavaDB, PostgreSQL, and others) as well. The second half of the blog provide specific syntax for the Oracle sample database.
Download sakila sample database and unzip the archive.
Install the database as described here - basically load and run "sakila-schema.sql" and "sakila-data.sql" extracted from the archive.
Create a new MySQL user account using MySQL CLI Admin and assign the privileges
Using "root" user (sudo mysql --user root)
CREATE USER glassfish IDENTIFIED BY 'glassfish';
GRANT ALL PRIVILEGES ON *.* TO 'glassfish'@'localhost' IDENTIFIED BY 'glassfish';
FLUSH PRIVILEGES;
Using "glassfish" user (sudo mysql --user glassfish)
source sakila-schema.sql;
source sakila-data.sql;
Download Connector/J, unzip and copy "mysql-connector-java-5.x.x-bin.jar" to "glassfish/domains/domain1/lib/ext" directory.
Start GlassFish server as:
asadmin start-domain
Create a JDBC resource
Create JDBC connection pool as:
asadmin create-jdbc-connection-pool --datasourceclassname com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource --restype javax.sql.DataSource --property "User=glassfish:Password=glassfish:URL=jdbc\:mysql\://localhost/sakila" jdbc/sakilaPool
Test the JDBC connection pool as:
asadmin ping-connection-pool jdbc/sakilaPool
Create the JDBC resource as:
asadmin create-jdbc-resource --connectionpoolid jdbc/sakilaPool jdbc/sakila
That's it!
Creating a JDBC resource for any other database requires the following updates to the steps mentioned above. Lets consider modifying these steps for the Oracle sample database.
Use the client interface SQL*PLus and connect as:
sqlplus "/ as sysdba"
create user and grant the privileges as:
CREATE USER glassfish IDENTIFIED BY glassfish DEFAULT tablespace users TEMPORARY tablespace temp;
GRANT CONNECT TO glassfish IDENTIFIED BY glassfish;
GRANT UNLIMITED TABLESPACE TO glassfish;
GRANT CREATE TABLE TO glassfish;
GRANT CREATE SEQUENCE TO glassfish;
Copy the appropriate JDBC driver (ojdbc6.jar).
Create the JDBC resource as:
asadmin create-jdbc-connection-pool --datasourceclassname oracle.jdbc.pool.OracleDataSource --restype javax.sql.DataSource --property "User=hr:Password=hr:URL=jdbc\:oracle\:thin\:@localhost\:1521\:orcl" jdbc/hr
asadmin ping-connection-pool jdbc/hr
asadmin create-jdbc-resource --connectionpoolid jdbc/hr jdbc/hr
as explained in TOTD #108.
Here are a few other related entries:
RESTful representation of sakila using NetBeans and GlassFish
JPA + Servlet 3.0 Application using Sakila in Eclipse
JSF + JPA + EJB Application using Oracle, NetBeans, and GlassFish
Technorati: totd javaee glassfish v3 jpa mysql sakila oracle
-
Top Speed - Queries per Second
Today I reached 109k Queries per Second. I was quite impressed by it.
Some background on the situation.
I developed some stored procedures to process some rather large tables we had in our database.
I managed to get the stored procedures to be very efficient and quick.
I then wanted to test it out and tried to overload the server to see how much it could take.
Normally, the server would do around 1k at best with these kinds of tasks. I have recently been able to tweak it to 20k QPS. But today, for some reason, the cache managed to get itself in the right position and produced this result.
The Server:
A 4+ year old Dell server, with SAS drives, 1 Quad-core CPU and 16Gbs of memory.
Database:
MySQL 5.0.48 - with MyISAM tables only
The Tasks:
Reference a 101 million row table (12+ Gbs) to fill in a column in three 8-9 million row tables (2-5Gbs).
Reference a 700k row table to fill in a 7 million row table.
So 4 tasks at the same time.
Click on the picture to see the full screenshot
Applications used: Mtop & Htop
-
Don't forget the COMMIT in MySQL
Yes, MySQL has transactions if you use InnoDB or NDB Cluster for example. Using these transactional storage engines, you'll have to commit (or roll back) your inserts, deletes or updates.I've seen it a few times now with people being surprised that no data is going into the tables. It's not so a silly problem in the end. If you are used to the defaults in MySQL you don't have to commit anything since it is automatically done for you.Take the Python Database Interfaces for MySQL. PEP-249 says that, by default, auto-commit should be turned off. You could turn it back on, but it's good practice to be explicit and commit in your code. Remember the Zen of Python!Here is just a small example to show it. Uses MySQL Connector/Python, but it does work also with others:import mysql.connectorcnx = mysql.connector.connect(db='test')cur = cnx.cursor()cur.execute("""CREATE TABLE innodb_t1 ( id INT UNSIGNED NOT NULL, c1 VARCHAR(128), PRIMARY KEY (id)) ENGINE=InnoDB""")ins = "INSERT INTO innodb_t1 (id,c1) VALUES (%s,%s)"cur.execute(ins, (1,'MySQL Support Team _is_ already the best',))cnx.commit()cur.close()cnx.close()
-
How PostgreSQL protects against partial page writes and data corruption
I explored two interesting topics today while learning more about Postgres.
Partial page writes
PostgreSQL’s partial page write protection is configured by the following setting, which defaults to “on”:
full_page_writes (boolean)
When this parameter is on, the PostgreSQL server writes the entire content of each disk page to WAL during the first modification of that page after a checkpoint… Storing the full page image guarantees that the page can be correctly restored, but at a price in increasing the amount of data that must be written to WAL. (Because WAL replay always starts from a checkpoint, it is sufficient to do this during the first change of each page after a checkpoint. Therefore, one way to reduce the cost of full-page writes is to increase the checkpoint interval parameters.)
Trying to reduce the cost of full-page writes by increasing the checkpoint interval highlights a compromise. If you decrease the interval, then you’ll be writing full pages to the WAL quite often. This should in theory lead to surges in the number of bytes written to the WAL, immediately following each checkpoint. As pages are revisited over time for further changes, the number of bytes written should taper off gradually until the next checkpoint. Hopefully someone who knows more can confirm this. Does anyone graph the number of bytes written to their WAL? That would be a nice illustration to see how dramatic this surging is.
Decreasing the checkpoint interval seems a bit scary, and is bound to have its own costs, for all the usual reasons. A massive checkpoint once in a while should be really expensive, and would lead to a bad worst-case time for recovery. Does the new bgwriter implementation in 8.3 fix any of this? In theory it could, but I don’t know enough yet to say. I have heard conflicting opinions on this point. I have a lot more to read about it before I form my own opinion.
Storing full pages might not really be that expensive. It could bloat the WAL, but is the cost (in terms of time) really that high? InnoDB (in MySQL) protects against partial page writes with a double-write strategy: a region in the tablespace is called the doublewrite buffer. Page writes are first sent to the doublewrite buffer, then to their actual location in the data file. I don’t remember where, but I’ve seen benchmarks showing that this doesn’t hurt performance, even though it seems counter-intuitive. Modern disks can do a lot of sequential writes, and the way InnoDB writes its data makes a lot of things sequential. I doubt that putting full pages into the PostgreSQL WAL is forced to cost a lot, unless there is an implementation-specific aspect that makes it expensive.
The TODO has some items on the WAL, which look interesting — “Eliminate need to write full pages to WAL before page modification” and a couple more items. I need to understand PostgreSQL’s recovery process better before I know what these really mean.
Detecting data corruption
I was able to verify that the WAL entries have a checksum. It is a CRC32. This is in xlog.c.
However, as far as I can understand, the answer for detecting data corruption in normal data pages is “Postgres doesn’t do that.” I was told on the IRC channel that normal data pages don’t have checksums. I am not sure how to verify that, but if it’s true it seems like a weakness. I’ve seen hardware-induced corruption on InnoDB data many times, and it could sometimes only be detected by page checksums.
What happens when a page is corrupt? It probably depends on where the corruption is. If a few bytes of the user’s data is changed, then I assume you could just get different data out of the database than you inserted into it. But if non-user data is corrupted then do you get bizarre behavior, or do you get a crash or error? I need to learn more about PostgreSQL’s data file layout to understand this. Imagining (I haven’t verified this) that a page has a pointer to the next page, what happens if that pointer is flipped to refer to some other page, say, a page from a different table? If TABLE1 and TABLE2 have identical structures but different data, could SELECT * FROM TABLE1 suddenly start showing rows from TABLE2 partway through the results? Again I need to learn more about this.
Related posts:The Ma.gnolia data might not be permanently lost I keep reaWhat data types does your innovative storage engine NOT support? I’vePostgreSQL Conference East 2009, Day Three As I said
Related posts brought to you by Yet Another Related Posts Plugin.
-
News, Jacob's leaves, Assay to Canonical
News Monday! Matt Assay to JOIN Canonical as COOThis took me a bit by surprise at first. I don't find myself often agreeing with Matt. Most of what he tends to write/argue for is what I have referred to in the past as "crippleware". Canonical in recent time has taken to opening up their platform. I've been a strong advocate for Launchpad, it is a great service. I love that they opened it up in recent time. When it comes to infrastructure software on the size of LP, I don't believe that many others will ever install it. Slash, G-Forge, and the Livejournal software are examples of infrastructure software that approach the size or outweigh the LP codebase. They have rarely been successfully deployed by others. The advantage in the Launchpad software being open source is the potential for others to audit the code. I suspect that they will receive some patches, but I doubt that the number of patches will ever out pace what the conical staff itself creates. This morning I got a number of worried pieces of email over Matt's new position at Canonical. Do I find that I am worried about Assay joining Canonical? Not really. The job of the COO is too keep the company moving on a day to day basis. With his background at Alfresco, the COO role makes sense. Canonical has a lot of strong open source advocates so I wouldn't expect change in a direction that would create issue. Canonical's Ubuntu One is their longterm play. Service based revenue work hand in hand with open source go well together (...how many online services can you name that aren't based on open source?). The COO position is one of the key positions that a company will hire for, yet, many smaller companies tend to pass over the creation of this position in lieu of having the CEO also fill this role. This is a real shame since you can often have a great CEO, who makes for a poor COO. Ken Jacobs leaves OracleWhen Innodb was first acquired by Oracle there was a lot of shock and dismay within the MySQ Ecosystem. MySQL INC's reaction to the acquisition, which then rippled to the community, created a mistrust of Oracle. Ken Jacobs really changed that reaction in the community. There has been a number of times over the years that I found myself on the same side of the fence as Ken when it came to both leadership and technical vision about MySQL. I am sure Oracle has other competent executives to fill his shoes, but Ken has been a real asset to Oracle over the years. I am sad to see him leave the ecosystem, he played a very positive role in the community. Oracle buying Innodb was never the killer move most envisioned at the time. It kick started engine development around MySQL, which was the only real innovation we saw for many years. Around the time of the acquisition all but one of the engineers who knew MySQL well, worked for MySQL. Having multiple companies working on engines re-invogorated outside development in the project. Without Oracle buying Innodb, the MySQL ecosystem would have never been forced into an innovators cycle again.
|
News Feeds