How to find out which rows gets deleted by the mysqlimport
Hi, Anyone know how to find out what are the rows that are reported by mysqlimport as deleted? Thanks HT -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Repeated repair table ... quick takes same amount of time (long) under 4.1.7
Hi, If I remember correctly, if table is ok, then if one does a table repair, the command should return immediately. I have a table with 14 million rows, and I repeated table repair (quick) 3 times, it took the same amount of time (55 min). It is almost the same time as if I built the index from scratch. Any idea? The table seems ok for query as I checked. This is under MySQL 4.1.7, MyISAM table with full text indexes. Thanks Haitao -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indexes use different block sizes error with preloading fulltext indexes.
Mark It is a known problem with this feature. So far I am not aware of any solution to it. Just want to let you know that you are not alone having this problem. Haitao On Fri, 05 Nov 2004 02:16:49 +, Mark Maunder [EMAIL PROTECTED] wrote: I keep getting this error when trying to preload a fulltext index. I've checked the block size of the fulltext index using myisamchk (is there an easier way to find out block size?) and it is 2048. The block size of the primary key on the same table is 1024. Is that what it means by Indexes use different block sizes? As you can see from below, I've tried to only load the fulltext index, and the error persists. I have also tried setting the global key_cache_block_size to 2048 and that didn't work. I have also tried creating a separate key cache with it's own 2048 block size and preloading the index into that, and that didn't work either. Any help is appreciated. mysql load index into cache fttest INDEX (ft); ++--+--+---+ | Table | Op | Msg_type | Msg_text | ++--+--+---+ | workzoo.fttest | preload_keys | error| Indexes use different block sizes | | workzoo.fttest | preload_keys | status | Operation failed | ++--+--+---+ 2 rows in set (0.00 sec) Mark. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrade to 4.1.7 caused Sort Aborted errors
Hi, We were using MySQL 4.1.6g without this kind of error, but after we upgrade to 4.1.7, we are having following errors: 041103 9:35:35 [ERROR] /var/opt/mysql4/11000/pkgroot/bin/mysqld: Sort aborted 041103 9:35:35 [ERROR] /var/opt/mysql4/11000/pkgroot/bin/mysqld: Sort aborted What do these mean? How to fix it? Is it a bug? I just wondering if anyone has upgraded to 4.1.7 and has seen these errors. Thanks so much! Haitao -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL query performance test tool
Hi, We want to test our MYSQL (4.1.4g) server's query performance, and I just wondering if there is a tool that enable us sending a list of queries over HTTP or JDBC repeatedly and gather/display the statistics? Thanks HT -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimize index building
Hi, We are using following steps to re-load and re-build a table every day: truncate the table disable index load data enable index What are the important parameters I should be watching out to make the ebale index step faster? Out table is over 10million rows and has two fulltext indexes. Thanks Haitao -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slower mysqlimport in 4..0.20?
Hi, I just wondering if anyone notice or can confirm this: I tried to load 11 million records using mysqlimport using both 4.1.3b and 4.0.20. 4.1.3b took 1.5 hours, but the 4.0.20 took over 10 hours. This includes loading the data and then build the index. Thanks Haitao -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1.3beta hangs after several consective drop table
I don't know if it is a bug, but I repeated did:
DROP TABLE IF NOT EXISTS company;
Create table company {
...
...
};
create name_idx on company (name(32));
The server hangs at create name_idxthe first couple of them were
really quick.
Any idea?
Thanks a lot!
Haitao
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What would happen in these two cases?
Thanks so much for the reply! So, if I understand correctly, to swap the table like I described, I should do: LOCK TABLES A write,A2 write; FLUSH TABLES; rename table A to A1; rename table A2 to A; UNLOCK TABLES; Right? If there is no write to either A or A2, then there is no need to lock the table, right? Thanks! On Mon, 09 Aug 2004 19:21:39 +0300, Egor Egorov [EMAIL PROTECTED] wrote: Haitao Jiang [EMAIL PROTECTED] wrote: Case 1: I have a table A under a running MySQL 4.1.3b server. If I replace A.MYD and A.MYI files without stop and start the server, will data gets corrupted? It depends on many factors. Honestly, there are lots of cases where you will get correct data and nothing wrong will happen. But you have to be an experience Unix developer to understand Unix internals in order to dance like that. :) So the general answer is: don't, it's too dangerous. Case 2: I rename A to A1, and A2 to A, assume A, A2 have the same schema but different data/index. Is it bad? Will data gets corrupted? I tied this one, it seems ok. Yes, you're right, it just *SEEMS* ok. :) If you really need to replace table files, use FLUSH TABLES, LOCK TABLES: http://dev.mysql.com/doc/mysql/en/FLUSH.html http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select first row within groups
Hi, If I want to find out highest score student from each class, how can I do that in MySQL? Assume the table looks like: classId INT, studentId INT, score INT In the case of multiple students from the same class has the same highest score, I would like to get the first one whose studentId is the smallest. I tried to use sub-query, but in the case of students of same highest score in the same class, they all get returned - I only want one from each class. Is it possible? Thanks a lot HT -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What would happen in these two cases?
It would be great if there is a swap table command that is atomic. Thanks a lot Haitao On Tue, 10 Aug 2004 13:23:30 +0300, Egor Egorov [EMAIL PROTECTED] wrote: Haitao Jiang [EMAIL PROTECTED] wrote: Thanks so much for the reply! So, if I understand correctly, to swap the table like I described, I should do: LOCK TABLES A write,A2 write; FLUSH TABLES; rename table A to A1; rename table A2 to A; UNLOCK TABLES; Right? If there is no write to either A or A2, then there is no need to lock the table, right? You can't rename locked tables. RENAME is atomic anyway so you can safely use it without lock. But your software should be aware of a possible race condition that happens between two RENAME TABLEs. Thanks! On Mon, 09 Aug 2004 19:21:39 +0300, Egor Egorov [EMAIL PROTECTED] wrote: Haitao Jiang [EMAIL PROTECTED] wrote: Case 1: I have a table A under a running MySQL 4.1.3b server. If I replace A.MYD and A.MYI files without stop and start the server, will data gets corrupted? It depends on many factors. Honestly, there are lots of cases where you will get correct data and nothing wrong will happen. But you have to be an experience Unix developer to understand Unix internals in order to dance like that. :) So the general answer is: don't, it's too dangerous. Case 2: I rename A to A1, and A2 to A, assume A, A2 have the same schema but different data/index. Is it bad? Will data gets corrupted? I tied this one, it seems ok. Yes, you're right, it just *SEEMS* ok. :) If you really need to replace table files, use FLUSH TABLES, LOCK TABLES: http://dev.mysql.com/doc/mysql/en/FLUSH.html http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.0.20 vs MySQL 4.1.3b
Hi, I just wondering if there is a list of important features or bug fixes comparing these two versions. We are debating whether to go one or the another. Also, when is the 4.1.4 going to come out? Is it still beta? Thanks so much! HT -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What would happen in these two cases?
That is what I need! Thanks so much again to everyone! HT On Tue, 10 Aug 2004 14:32:13 -0700, Justin Swanhart [EMAIL PROTECTED] wrote: You can put multiple renames in one statement, and the entire rename will be atomic.. I create summary tables from some of my data, and I periodically refresh them. When refreshing them I create new tables to replace the old tables with.. Then I do: rename current_table to old_table, new_table to current_table On Tue, 10 Aug 2004 13:50:34 -0700, Haitao Jiang [EMAIL PROTECTED] wrote: It would be great if there is a swap table command that is atomic. Thanks a lot Haitao On Tue, 10 Aug 2004 13:23:30 +0300, Egor Egorov [EMAIL PROTECTED] wrote: Haitao Jiang [EMAIL PROTECTED] wrote: Thanks so much for the reply! So, if I understand correctly, to swap the table like I described, I should do: LOCK TABLES A write,A2 write; FLUSH TABLES; rename table A to A1; rename table A2 to A; UNLOCK TABLES; Right? If there is no write to either A or A2, then there is no need to lock the table, right? You can't rename locked tables. RENAME is atomic anyway so you can safely use it without lock. But your software should be aware of a possible race condition that happens between two RENAME TABLEs. Thanks! On Mon, 09 Aug 2004 19:21:39 +0300, Egor Egorov [EMAIL PROTECTED] wrote: Haitao Jiang [EMAIL PROTECTED] wrote: Case 1: I have a table A under a running MySQL 4.1.3b server. If I replace A.MYD and A.MYI files without stop and start the server, will data gets corrupted? It depends on many factors. Honestly, there are lots of cases where you will get correct data and nothing wrong will happen. But you have to be an experience Unix developer to understand Unix internals in order to dance like that. :) So the general answer is: don't, it's too dangerous. Case 2: I rename A to A1, and A2 to A, assume A, A2 have the same schema but different data/index. Is it bad? Will data gets corrupted? I tied this one, it seems ok. Yes, you're right, it just *SEEMS* ok. :) If you really need to replace table files, use FLUSH TABLES, LOCK TABLES: http://dev.mysql.com/doc/mysql/en/FLUSH.html http://dev.mysql.com/doc/mysql/en/LOCK_TABLES.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What would happen in these two cases?
I hope this is not a so dumb question: Case 1: I have a table A under a running MySQL 4.1.3b server. If I replace A.MYD and A.MYI files without stop and start the server, will data gets corrupted? Case 2: I rename A to A1, and A2 to A, assume A, A2 have the same schema but different data/index. Is it bad? Will data gets corrupted? I tied this one, it seems ok. In both cases, there will be some queries on A, but no updates. Thanks Haitao -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR 1030 (HY000): Got error 127 from storage engine
Hi, I have done following on a 8 million row table (4GB): repair table optimize table drop one of the fulltext index optimize table again Now fulltext search stop working and I am getting: ERROR 1030 (HY000): Got error 127 from storage engine What does this mean? Index corrupted? How that happen? Any place I can get a list of MySQL error code and know what they mean? Thanks a lot Haitao -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why drop a index takes so long to do?
Hi, I have a 3GB index which includes a fulltext index on 2 columns. I wanted to drop this fulltext index from the table, but it took forever. Why it is so slow? Is it because MySQL basically go through the index file (there is only one) and re-write it to another file and then copy it back? Any other fast way to drop a index like this? Thanks Haitao -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I can not figure out this J Connector problem - a bug???
Yes. Exactly! Thanks so much for pointing this out!
Haitao
On Wed, 4 Aug 2004 08:53:28 -0500 (CDT), Mark Matthews [EMAIL PROTECTED] wrote:
Hi,
I have following code to insert a row into a table - schema follows:
:
PreparedStatement insertData = targetConnection.prepareStatement(insert
into CompanyParticipationLevel
(siteId,nodeId,companyId,editionId,participation
LevelId,participationText,participationLogo) values (?, ?, ?, ?, ?, ?,
?));
Statement sourceData = sourceConnection.createStatement();
ResultSet sourceResultSet = sourceData.executeQuery(select
cpl.siteId,
isnull(cpl.nodeId,0) as nodeId, cpl.companyId, cpl.editionId,
cpl.participationL
evelId, cpl.participationText, cpl.participationLogo FROM
CompanyParticipationLe
vel cpl where cpl.siteId=8 and cpl.participationLevelTypeId=1);
Object value = null;
while (sourceResultSet.next()) {
insertData.clearParameters();
System.out.print(processing ();
for(int i = 1; i = 7; i++){
value = sourceResultSet.getObject(i);
insertData.setObject(i, value);
System.out.print((+value+));
}
System.out.println());
insertData.executeUpdate();
}
+--+-+--+-+-+---+
| Field| Type| Null | Key | Default |
Extra |
+--+-+--+-+-+---+
| siteId | int(10) unsigned| | PRI | 0 |
|
| nodeId | int(10) unsigned| | PRI | 0 |
|
| companyId| int(10) unsigned| | PRI | 0 |
|
| editionId| int(10) unsigned| | PRI | 0 |
|
| participationLevelId | tinyint(3) unsigned | | PRI | 0 |
|
| participationText| text| YES | | NULL|
|
| participationLogo| varchar(128)| YES | | NULL|
|
+--+-+--+-+-+---+
The output of the program:
processing ((8)(0)(56361)(0)(4)(null)( ))
Exception in thread main java.sql.SQLException: Column 'siteId' cannot
be null
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2551)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1443)
at
com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedSt
atement.java:1239)
at
com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPrepared
Statement.java:903)
at
com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1871)
at
com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1796)
at com.mysql.jdbc.PreparedStatement.executeUpdate
(PreparedStatement.java:1658)
It is obvious that I did setObject correctly, there are 7 columns and
I have 7 value supplied. But JDBC complains that first column is null.
Is it a bug or someone can tell me what is going on?
Thanks
Haitao
What version of Connector/J are you using? This looks like a bug that was
fixed in 3.1.3.
-Mark
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I can not figure out this J Connector problem - a bug???
Hi,
I have following code to insert a row into a table - schema follows:
:
PreparedStatement insertData = targetConnection.prepareStatement(insert
into CompanyParticipationLevel (siteId,nodeId,companyId,editionId,participation
LevelId,participationText,participationLogo) values (?, ?, ?, ?, ?, ?, ?));
Statement sourceData = sourceConnection.createStatement();
ResultSet sourceResultSet = sourceData.executeQuery(select cpl.siteId,
isnull(cpl.nodeId,0) as nodeId, cpl.companyId, cpl.editionId, cpl.participationL
evelId, cpl.participationText, cpl.participationLogo FROM CompanyParticipationLe
vel cpl where cpl.siteId=8 and cpl.participationLevelTypeId=1);
Object value = null;
while (sourceResultSet.next()) {
insertData.clearParameters();
System.out.print(processing ();
for(int i = 1; i = 7; i++){
value = sourceResultSet.getObject(i);
insertData.setObject(i, value);
System.out.print((+value+));
}
System.out.println());
insertData.executeUpdate();
}
+--+-+--+-+-+---+
| Field| Type| Null | Key | Default | Extra |
+--+-+--+-+-+---+
| siteId | int(10) unsigned| | PRI | 0 | |
| nodeId | int(10) unsigned| | PRI | 0 | |
| companyId| int(10) unsigned| | PRI | 0 | |
| editionId| int(10) unsigned| | PRI | 0 | |
| participationLevelId | tinyint(3) unsigned | | PRI | 0 | |
| participationText| text| YES | | NULL| |
| participationLogo| varchar(128)| YES | | NULL| |
+--+-+--+-+-+---+
The output of the program:
processing ((8)(0)(56361)(0)(4)(null)( ))
Exception in thread main java.sql.SQLException: Column 'siteId' cannot be null
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2551)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1443)
at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedSt
atement.java:1239)
at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPrepared
Statement.java:903)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1871)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1796)
at com.mysql.jdbc.PreparedStatement.executeUpdate
(PreparedStatement.java:1658)
It is obvious that I did setObject correctly, there are 7 columns and
I have 7 value supplied. But JDBC complains that first column is null.
Is it a bug or someone can tell me what is going on?
Thanks
Haitao
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT difficulties
Hi, there Maybe this question is not MySQL specific, but I just wondering if MySQL has any way to doing this: I have a table A like following: offerId resellerId price -- 1r1 5 2r1 10 3r2 12 4r2 4 -- How can I select only one row from each reseller with the the lowest price from this reseller? i.e. the result should be 1 r1 5 4 r2 4 Thanks in adavnce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Idea to speed up multiple jdbc connections?
Marc mysqld runs on a very powerful Operton machine with 16GB memory and barely any other application process running, it is hard to believe that a simple select that runs under 2 second will utilize all the resources...that is why I tend to think there is something in the mysql set up that caused this...any idea where I should look? BTW: the numbers are in milliseconds Thanks Haitao --- Marc Slemko [EMAIL PROTECTED] wrote: On Mon, 7 Jun 2004, Haitao Jiang wrote: Yes. The time I measure like I said is purely around statement.execQuery() call. Connection creation is not a factor here at all. My database has 1.64 million rows and 4 queries are all selects, which are identical in both serial and parallel cases. In serial cases: Query 0 took 590 Query 1 took 431 Query 2 took 461 Query 3 took 440 In parallel cases: Queryer 3 query took 1552 Queryer 1 query took 1632 Queryer 2 query took 1783 Queryer 0 query took 1923 I don't understand why in 4 concurrent connection cases (already created not included in the timing) it takes more than 3 times longer to exec. a query. Umh... if your queries are limited by some bottleneck on the server (such as, for example, CPU) then why would running them in parallel make it any faster? It seems that in the sequential case they are taking a total of 1922 (whatever those units are) while in the parallel case they are taking 1923. What this is telling you is that, in this case, a single query is able to fully utilize the resources (likely CPU given these numbers, although it is possible it could be disk) on the server. If a single query can fully utilize the server, all that adding more concurrency can possibly do is slow the total throughput down. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Idea to speed up multiple jdbc connections?
Each of 4 individual query only took 0.6 seconds, there is no other clients, it hardly to believe taht mysql query performance will degrade 300% (from 0.6s to ~1.9s) if we have 4 concurrent connections... As far as I know, MySQL should be able to handle hundreds of connections on a single CPU box without degrading performance like above. Thanks HT --- Marc Slemko [EMAIL PROTECTED] wrote: On Mon, 7 Jun 2004, Haitao Jiang wrote: Marc mysqld runs on a very powerful Operton machine with 16GB memory and barely any other application process running, it is hard to believe that a simple select that runs under 2 second will utilize all the resources...that is why I tend to think there is something in the mysql set up that caused this...any idea where I should look? How many processors? If there is only one and the query is CPU bound (as it probably is if everything is cached, given 16 gigs of ram), then why shouldn't it use all the CPU? Or, to phrase the question differently: why should the query take 2 seconds to run if there are free resources? Now, on a multiprocessor box it clearly starts to get more complicated. mysql has no capability to spread one query across multiple CPUs in parallel, and while it can spread multiple queries across CPUs the scalability has its limits. The fact that is a simple query is irrelevant (some of the simplest can be the slowest if it has to do a full table scan). From the fact that it takes 2 seconds it is clear it is not an entirely trivial query. __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Idea to speed up multiple jdbc connections?
Hi, I would appreciate any help on this: I got approximate same timing on following two: case 1: create 1 jdbc connection and issue 4 queries sequentially case 2: create 4 jdbc connections and issue 4 queries via 4 different threads at the same time The timing is done around statement.execQuery(query), so overhead of multithreading can be ignored. I would think case 2 should be faster, but it was not. Any idea? Thanks a lot PS: the mysqld server 4.1.1a is running with 16 threads __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Idea to speed up multiple jdbc connections?
Yes. The time I measure like I said is purely around statement.execQuery() call. Connection creation is not a factor here at all. My database has 1.64 million rows and 4 queries are all selects, which are identical in both serial and parallel cases. In serial cases: Query 0 took 590 Query 1 took 431 Query 2 took 461 Query 3 took 440 In parallel cases: Queryer 3 query took 1552 Queryer 1 query took 1632 Queryer 2 query took 1783 Queryer 0 query took 1923 I don't understand why in 4 concurrent connection cases (already created not included in the timing) it takes more than 3 times longer to exec. a query. Thanks Haitao --- Mark Matthews [EMAIL PROTECTED] wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 [EMAIL PROTECTED] wrote: AFAIK, creation of connection from DB is expensive. This is one of the reasons why we need connection pooling. Jonathan, While that might be true for other databases, it's not true for MySQL (connections are a few ms. to create). The real reason to use connection pooling is as a resource limiter so that you do not waste MySQL server-side resources for threads that are effectively doing nothing. Haitao's issue might be due to some locking in the database server, thus effectively serializing his four connections, or he might not be actually producing enough load to actually be able to measure any difference between his two approaches. If he could post his DDL, the relative size(s) of his data set(s) and the queries, that would be somewhere to start. -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com MySQL Guide to Lower TCO http://www.mysql.com/it-resources/white-papers/tco.php -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAxRgLtvXNTca6JD8RAjiSAJ0R5b6MNW0SdY5z4eJtmfgAV0ZMtgCgtGyn 037apgXT972UAR3Khkg7ITI= =4bja -END PGP SIGNATURE- __ Do you Yahoo!? Friends. Fun. Try the all-new Yahoo! Messenger. http://messenger.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can one found out why a query is not cached?
Thanks Paul. But that my query doesn't violate any of these conditions. Here is more detail: mysql show status like %qcache%; +-+---+ | Variable_name | Value | +-+---+ | Qcache_queries_in_cache | 0 | | Qcache_inserts | 0 | | Qcache_hits | 0 | | Qcache_lowmem_prunes| 0 | | Qcache_not_cached | 163 | | Qcache_free_memory | 511982024 | | Qcache_free_blocks | 1 | | Qcache_total_blocks | 1 | +-+---+ 8 rows in set (0.00 sec) mysql show variables like %cache%; +--+--+ | Variable_name| Value| +--+--+ | bdb_cache_size | 8388600 | | binlog_cache_size| 32768| | have_query_cache | YES | | key_cache_block_size | 1024 | | key_cache_division_limit | 100 | | key_cache_age_threshold | 300 | | max_binlog_cache_size| 18446744073709551615 | | query_cache_limit| 134217728| | query_cache_min_res_unit | 1024 | | query_cache_size | 51200| | query_cache_type | ON | | table_cache | 256 | | thread_cache_size| 16 | +--+--+ 13 rows in set (0.00 sec) Now issue the query: mysql select SQL_CACHE UPC from PIMuzeIndex where UPC = 08616218713; +-+ | UPC | +-+ | 08616218713 | | 08616218713 | | 08616218713 | | 08616218713 | | 08616218713 | | 08616218713 | | 08616218713 | +-+ 7 rows in set (0.00 sec) Now check the qcache status, we can see no query is cached: mysql show status like %qcache%; +-+---+ | Variable_name | Value | +-+---+ | Qcache_queries_in_cache | 0 | | Qcache_inserts | 0 | | Qcache_hits | 0 | | Qcache_lowmem_prunes| 0 | | Qcache_not_cached | 164 | | Qcache_free_memory | 511982024 | | Qcache_free_blocks | 1 | | Qcache_total_blocks | 1 | +-+---+ 8 rows in set (0.01 sec) Any idea? Please help. Thanks HT --- Paul DuBois [EMAIL PROTECTED] wrote: At 17:02 -0700 5/3/04, Haitao Jiang wrote: I have a complex query which took 4 seconds, I set the query cache size to 512MB. BUt the query is not cached, it only returns 7 rows, so the cache size is not a problem here. Anyway I can find out why MySQL 4.1 is not caching my queries? The criteria for caching are given on this page: http://dev.mysql.com/doc/mysql/en/Query_Cache_How.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How can one found out why a query is not cached?
I have a complex query which took 4 seconds, I set the query cache size to 512MB. BUt the query is not cached, it only returns 7 rows, so the cache size is not a problem here. Anyway I can find out why MySQL 4.1 is not caching my queries? Thanks a lot Haitao __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can not get an answer here - how to force the index block size to be the same?
Paul I wanted to pre load keys into cache which is larger than the total index size: --- Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 99 to server version: 4.1.1-alpha-Max Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql load index into cache MuzeTable; +-+--+--+--- + | Table | Op | Msg_type | Msg_text | +-+--+--+--- + | mysql.MuzeTable | preload_keys | error| Indexes use different block sizes | | mysql.MuzeTable | preload_keys | status | Operation failed | +-+--+--+--- + 2 rows in set (0.00 sec) -- I check the blocksize of the indexes, some of them are 1024, some are 2048. Any idea how to solve this? Thanks a lot! Haitao --- Paul DuBois [EMAIL PROTECTED] wrote: At 20:25 -0700 4/15/04, Haitao Jiang wrote: Paul Because I want to use the load index into cache... to pre-load the indexes into cache. But I can not use this feature since the block size of indexes are different (1024 vs. 2048), and the load index into cache.. complains about it. Any idea? Thanks a lot! I'm afraid that's pretty vague. What does your table structure look like? What does your LOAD INDEX statement look like? What is the result of the statement? Haitao --- Paul DuBois [EMAIL PROTECTED] wrote: At 17:34 -0700 4/15/04, Haitao Jiang wrote: It seems to be either a hard question or stupid question:). Is there anyway in Version 4.1.1 I can force all the indexes to have same block size? Say 2048? If it is plain impossible without changing the source code, please let me know. Why do you care about this? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com __ Do you Yahoo!? Yahoo! Tax Center - File online by April 15th http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can not get an answer here - how to force the index block size to be the same?
It seems to be either a hard question or stupid question:). Is there anyway in Version 4.1.1 I can force all the indexes to have same block size? Say 2048? If it is plain impossible without changing the source code, please let me know. I really appreciate any help on this. Thanks HT __ Do you Yahoo!? Yahoo! Tax Center - File online by April 15th http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can not get an answer here - how to force the index block size to be the same?
Paul Because I want to use the load index into cache... to pre-load the indexes into cache. But I can not use this feature since the block size of indexes are different (1024 vs. 2048), and the load index into cache.. complains about it. Any idea? Thanks a lot! Haitao --- Paul DuBois [EMAIL PROTECTED] wrote: At 17:34 -0700 4/15/04, Haitao Jiang wrote: It seems to be either a hard question or stupid question:). Is there anyway in Version 4.1.1 I can force all the indexes to have same block size? Say 2048? If it is plain impossible without changing the source code, please let me know. Why do you care about this? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com __ Do you Yahoo!? Yahoo! Tax Center - File online by April 15th http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can not get an answer here - how to force the index block size to be the same?
Because if you want to pre-load indexes into cache, you need to have same block size for all indexes of a table. --- Rhino [EMAIL PROTECTED] wrote: I don't know the answer to your question but I'm curious to know why you would want to do this. Normally, that sort of detail is handled by the database and should not be a consideration for any user of the database. In other words, your queries should work regardless of whether the block sizes for indexes are the same or differ. I would be quite surprised if MySQL - or any decent relational database - gave you a way to control those block sizes. Or is this really a locking question? I'm not sure how MySQL handles the management of index pages and the corresponding data pages that they represent but DB2 (mainframe) gives you some control over whether indexes use full pages or subpages to let you minimize the number of rows locked by a given user. The more subpages you use, the fewer data pages get locked. However, you also tend to need more index space than if you didn't use subpages. Rhino - Original Message - From: Haitao Jiang [EMAIL PROTECTED] To: mysql [EMAIL PROTECTED] Sent: Thursday, April 15, 2004 8:34 PM Subject: Can not get an answer here - how to force the index block size to be the same? It seems to be either a hard question or stupid question:). Is there anyway in Version 4.1.1 I can force all the indexes to have same block size? Say 2048? If it is plain impossible without changing the source code, please let me know. I really appreciate any help on this. Thanks HT __ Do you Yahoo!? Yahoo! Tax Center - File online by April 15th http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Tax Center - File online by April 15th http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql MAtch against query help
+ is not part of the definition of a word in MySQL.
One solution is to normalize C++ into CPLUSPLUS
both during index and query time.
Haitao
--- electroteque [EMAIL PROTECTED] wrote:
Hehe I've also had a problem with searching for
something like it's ,
anything with a single quote doesnt return anything,
maybe try adding a
slash , C\+\+ ?? heheh maybe i'm wrong, it could be
a limitation.
-Original Message-
From: David Taiwo
[mailto:[EMAIL PROTECTED]
Sent: Tuesday, April 13, 2004 9:32 PM
To: [EMAIL PROTECTED]
Subject: Mysql MAtch against query help
I have a query that searches my database for
people
with C++ on their resume .
Query
=
select * ,match(Res_resume) AGAINST ('C++') as
kewyordscore from member,memberprofile,resume left
join stateprovince on stateid = Res_state
where mem_id = mempf_memid and
match(Res_resume) AGAINST ('+C++' IN BOOLEAN
MODE)
I have the ft_min_word_len set to 2 , but for some
reason , it still does not return a record when i
search for C++. Is there a way to escape the ++
when i
do a search , or is there something i am not doing
right.
The query works when i do a search for 'PR' or
'P*' .
so i am sure that i reindexed my database.
I am using Mysql version 4.1.1 on windows.
Any help owuld be great ... thx
Dave
__
Do you Yahoo!?
New DSL Internet Access from SBC Yahoo!
http://sbc.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
__
Do you Yahoo!?
Yahoo! Small Business $15K Web Design Giveaway
http://promotions.yahoo.com/design_giveaway/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Useless index key cache
Hi, Thanks to the reply from Sergei, I understand why MySQL requires SAME block size on all indexes when load index into cache However, this feature basically useless for the table that has any fulltext index. Based on what I've seen, fulltext indexes use 2048 block size, whereas others use 1024. So, at current time, we can not pre-load index anyway...am I right? Anyway to make all index block size all the same, no matter what type of indexes? Thanks so much for any help! Haitao __ Do you Yahoo!? Yahoo! Tax Center - File online by April 15th http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Impossible to pre-load the MySQL fulltext indexes?
Hi, I tried to load a table's indexes into cache, however I noticed that full-text indexes have a different block-size than others (2048 vs. 1024), which caused load index command to fail. Any idea on how to solve this? Thanks a lot __ Do you Yahoo!? Yahoo! Small Business $15K Web Design Giveaway http://promotions.yahoo.com/design_giveaway/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How can I make index block size all the same?
Why Mysql load index ... into cache require the same size index block when it creates indexes in different block sizes (1k or 2k)? I don't understand. Please help! __ Do you Yahoo!? Yahoo! Small Business $15K Web Design Giveaway http://promotions.yahoo.com/design_giveaway/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why do we need ~ in BOOLEAN MODE?
~ are supposed to adjust the weight of a word to the relevancy score, but they only work in BOOLEAN MODE and BOOLEAN MODE doesn't care about relevancy! In my opinion, they should be only allowed in the regular fulltext search in which we DO compute the relevancy. Am I missing something? Thanks so much! Haitao Jiang __ Do you Yahoo!? Get better spam protection with Yahoo! Mail. http://antispam.yahoo.com/tools -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can MySQL give different column different weight when compute
I want to give some columns of my table more weight when it comes to compute the relevancy score. For example, if a word appears in Title column, it should weight more if it appears in Body column. Does MySQL support this? Thanks for your help! Haitao Jiang __ Do you Yahoo!? Get better spam protection with Yahoo! Mail. http://antispam.yahoo.com/tools -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange bug(?) with Phrase search in BOOLEAN MODE
Could anyone explain why
match (keywords) against ('16-bit Touch' IN BOOLEAN
MODE)
returns results, but not
match (keywords) against ('16-bit' IN BOOLEAN MODE)?
Is it a known bug?
Thanks a lot!
Haitao
__
Do you Yahoo!?
Get better spam protection with Yahoo! Mail.
http://antispam.yahoo.com/tools
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange bug(?) with Phrase search in BOOLEAN MODE
Thanks! That was what I guessed. But how to explain
16-bit Touch doesn't match records with 32-bit
Touch in the keywords? It just returned all the
records with 16-bit Touch, i.e. 16-bit seems does
count.
Thanks again!
Haitao
--- Michael Stassen [EMAIL PROTECTED]
wrote:
It's expected behavior, not a bug.
16-bit Touch is parsed as 16, bit and touch.
The first two are
ignored because they are too short. So, this
searches for rows with
touch, then selects the ones which contain your
phrase.
16-bit contains no words to search for, so it
returns nothing.
Michael
Haitao Jiang wrote:
Could anyone explain why
match (keywords) against ('16-bit Touch' IN
BOOLEAN
MODE)
returns results, but not
match (keywords) against ('16-bit' IN BOOLEAN
MODE)?
Is it a known bug?
Thanks a lot!
Haitao
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
__
Do you Yahoo!?
Get better spam protection with Yahoo! Mail.
http://antispam.yahoo.com/tools
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How does ~ work in BOOLEAN MODE?
Please help me understand this: ~ are supposed to adjust the weight of a word to the relevancy score, but they only work in BOOLEAN MODE and BOOLEAN MODE doesn't care about relevancy! I am really confused. Could anyone kindly explain how they supposed to work to me? Thanks so much! Haitao Jiang __ Do you Yahoo!? Get better spam protection with Yahoo! Mail. http://antispam.yahoo.com/tools -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
