001: /*
002: Copyright (C) 2002-2004 MySQL AB
003:
004: This program is free software; you can redistribute it and/or modify
005: it under the terms of version 2 of the GNU General Public License as
006: published by the Free Software Foundation.
007:
008: There are special exceptions to the terms and conditions of the GPL
009: as it is applied to this software. View the full text of the
010: exception in file EXCEPTIONS-CONNECTOR-J in the directory of this
011: software distribution.
012:
013: This program is distributed in the hope that it will be useful,
014: but WITHOUT ANY WARRANTY; without even the implied warranty of
015: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
016: GNU General Public License for more details.
017:
018: You should have received a copy of the GNU General Public License
019: along with this program; if not, write to the Free Software
020: Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
021:
022:
023:
024: */
025: package testsuite.perf;
026:
027: import testsuite.BaseTestCase;
028:
029: /**
030: * Simplistic test for performance regression.
031: *
032: * @author Mark Matthews
033: */
034: public class RetrievalPerfTest extends BaseTestCase {
035: // ~ Static fields/initializers
036: // ---------------------------------------------
037:
038: private static final int NUM_TESTS = 10000;
039:
040: private static final int NUM_ROWS = 80;
041:
042: // ~ Constructors
043: // -----------------------------------------------------------
044:
045: /**
046: * Constructor for RetrievalPerfTest.
047: *
048: * @param name
049: * name of the test to run
050: */
051: public RetrievalPerfTest(String name) {
052: super (name);
053: }
054:
055: // ~ Methods
056: // ----------------------------------------------------------------
057:
058: /**
059: * Runs all tests.
060: *
061: * @param args
062: * ignored
063: */
064: public static void main(String[] args) {
065: new RetrievalPerfTest("testRetrievalMyIsam").run();
066: new RetrievalPerfTest("testRetrievalHeap").run();
067: new RetrievalPerfTest("testRetrievalCached").run();
068: }
069:
070: /**
071: * @see junit.framework.TestCase#setUp()
072: */
073: public void setUp() throws Exception {
074: super .setUp();
075: this .stmt
076: .executeUpdate("DROP TABLE IF EXISTS retrievalPerfTestHeap");
077: this .stmt
078: .executeUpdate("DROP TABLE IF EXISTS retrievalPerfTestMyIsam");
079: this .stmt
080: .executeUpdate("CREATE TABLE retrievalPerfTestHeap (priKey INT NOT NULL PRIMARY KEY,"
081: + "charField VARCHAR(80)) TYPE=HEAP");
082: this .stmt
083: .executeUpdate("CREATE TABLE retrievalPerfTestMyIsam (priKey INT NOT NULL PRIMARY KEY,"
084: + "charField VARCHAR(80)) TYPE=MyISAM");
085:
086: for (int i = 0; i < NUM_ROWS; i++) {
087: this .stmt
088: .executeUpdate("INSERT INTO retrievalPerfTestHeap (priKey, charField) VALUES ("
089: + i
090: + ",'abcdefghijklmnopqrstuvqxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')");
091: this .stmt
092: .executeUpdate("INSERT INTO retrievalPerfTestMyIsam (priKey, charField) VALUES ("
093: + i
094: + ",'abcdefghijklmnopqrstuvqxyzABCDEFGHIJKLMNOPQRSTUVWXYZ')");
095: }
096: }
097:
098: /**
099: * @see junit.framework.TestCase#tearDown()
100: */
101: public void tearDown() throws Exception {
102: this .stmt
103: .executeUpdate("DROP TABLE IF EXISTS retrievalPerfTestHeap");
104: this .stmt
105: .executeUpdate("DROP TABLE IF EXISTS retrievalPerfTestMyIsam");
106: super .tearDown();
107: }
108:
109: /**
110: * Tests retrieval from the query cache
111: *
112: * @throws Exception
113: * if an error occurs
114: */
115: public void testRetrievalCached() throws Exception {
116: this .stmt.executeUpdate("SET QUERY_CACHE_TYPE = DEMAND");
117:
118: double fullBegin = System.currentTimeMillis();
119: double averageQueryTimeMs = 0;
120: double averageTraversalTimeMs = 0;
121:
122: for (int i = 0; i < NUM_TESTS; i++) {
123: long queryBegin = System.currentTimeMillis();
124: this .rs = this .stmt
125: .executeQuery("SELECT SQL_CACHE * FROM retrievalPerfTestHeap");
126:
127: long queryEnd = System.currentTimeMillis();
128: averageQueryTimeMs += ((double) (queryEnd - queryBegin) / NUM_TESTS);
129:
130: long traverseBegin = System.currentTimeMillis();
131:
132: while (this .rs.next()) {
133: this .rs.getInt(1);
134: this .rs.getString(2);
135: }
136:
137: long traverseEnd = System.currentTimeMillis();
138: averageTraversalTimeMs += ((double) (traverseEnd - traverseBegin) / NUM_TESTS);
139: }
140:
141: double fullEnd = System.currentTimeMillis();
142: double fullTime = (fullEnd - fullBegin) / 1000;
143: double queriesPerSec = NUM_TESTS / fullTime;
144: double rowsPerSec = (NUM_ROWS * NUM_TESTS) / fullTime;
145: System.out.println("\nQuery Cache From Heap Retrieval\n");
146: System.out.println("Full test took: " + fullTime + " seconds.");
147: System.out.println("Queries/second: " + queriesPerSec);
148: System.out.println("Rows/second: " + rowsPerSec);
149: System.out.println("Avg. Query Exec Time: "
150: + averageQueryTimeMs + " ms");
151: System.out.println("Avg. Traversal Time: "
152: + averageTraversalTimeMs + " ms");
153:
154: // We're doing something wrong if we can't beat 45 seconds :(
155: assertTrue(fullTime < 45);
156: }
157:
158: /**
159: * Tests retrieval from HEAP tables
160: *
161: * @throws Exception
162: * if an error occurs
163: */
164: public void testRetrievalHeap() throws Exception {
165: double fullBegin = System.currentTimeMillis();
166: double averageQueryTimeMs = 0;
167: double averageTraversalTimeMs = 0;
168:
169: for (int i = 0; i < NUM_TESTS; i++) {
170: long queryBegin = System.currentTimeMillis();
171: this .rs = this .stmt
172: .executeQuery("SELECT * FROM retrievalPerfTestHeap");
173:
174: long queryEnd = System.currentTimeMillis();
175: averageQueryTimeMs += ((double) (queryEnd - queryBegin) / NUM_TESTS);
176:
177: long traverseBegin = System.currentTimeMillis();
178:
179: while (this .rs.next()) {
180: this .rs.getInt(1);
181: this .rs.getString(2);
182: }
183:
184: long traverseEnd = System.currentTimeMillis();
185: averageTraversalTimeMs += ((double) (traverseEnd - traverseBegin) / NUM_TESTS);
186: }
187:
188: double fullEnd = System.currentTimeMillis();
189: double fullTime = (fullEnd - fullBegin) / 1000;
190: double queriesPerSec = NUM_TESTS / fullTime;
191: double rowsPerSec = (NUM_ROWS * NUM_TESTS) / fullTime;
192: System.out.println("\nHEAP Table Retrieval\n");
193: System.out.println("Full test took: " + fullTime + " seconds.");
194: System.out.println("Queries/second: " + queriesPerSec);
195: System.out.println("Rows/second: " + rowsPerSec);
196: System.out.println("Avg. Query Exec Time: "
197: + averageQueryTimeMs + " ms");
198: System.out.println("Avg. Traversal Time: "
199: + averageTraversalTimeMs + " ms");
200:
201: // We're doing something wrong if we can't beat 45 seconds :(
202: assertTrue(fullTime < 45);
203: }
204:
205: /**
206: * Tests retrieval speed from MyISAM type tables
207: *
208: * @throws Exception
209: * if an error occurs
210: */
211: public void testRetrievalMyIsam() throws Exception {
212: double fullBegin = System.currentTimeMillis();
213: double averageQueryTimeMs = 0;
214: double averageTraversalTimeMs = 0;
215:
216: for (int i = 0; i < NUM_TESTS; i++) {
217: long queryBegin = System.currentTimeMillis();
218: this .rs = this .stmt
219: .executeQuery("SELECT * FROM retrievalPerfTestMyIsam");
220:
221: long queryEnd = System.currentTimeMillis();
222: averageQueryTimeMs += ((double) (queryEnd - queryBegin) / NUM_TESTS);
223:
224: long traverseBegin = System.currentTimeMillis();
225:
226: while (this .rs.next()) {
227: this .rs.getInt(1);
228: this .rs.getString(2);
229: }
230:
231: long traverseEnd = System.currentTimeMillis();
232: averageTraversalTimeMs += ((double) (traverseEnd - traverseBegin) / NUM_TESTS);
233: }
234:
235: double fullEnd = System.currentTimeMillis();
236: double fullTime = (fullEnd - fullBegin) / 1000;
237: double queriesPerSec = NUM_TESTS / fullTime;
238: double rowsPerSec = (NUM_ROWS * NUM_TESTS) / fullTime;
239: System.out.println("\nMyIsam Retrieval\n");
240: System.out.println("Full test took: " + fullTime + " seconds.");
241: System.out.println("Queries/second: " + queriesPerSec);
242: System.out.println("Rows/second: " + rowsPerSec);
243: System.out.println("Avg. Query Exec Time: "
244: + averageQueryTimeMs + " ms");
245: System.out.println("Avg. Traversal Time: "
246: + averageTraversalTimeMs + " ms");
247:
248: // We're doing something wrong if we can't beat 45 seconds :(
249: assertTrue(fullTime < 45);
250: }
251: }
|