Re: USING WHERE; USING TEMPORARY; USING filesort
- Original Message - From: Daevid Vincent dae...@daevid.com d.`date_release`!='-00-00' AND sd.`scene_id`=s.`scene_id` GROUP BY sg.`scene_id` ORDER BY num DESC, sd.`count_dl_monthly` DESC LIMIT 30; Could you put an index on sg.scene_id? Not sure, but it might help the grouping be more efficient. In general, though, you mqy be running into the limit of how big a temporary result table in memory can get before it gets moved to disk. There's two parameters that govern that iirc, max_tmp_table_size and another. Have a look at the manual. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: USING WHERE; USING TEMPORARY; USING filesort
On Thu, Feb 23, 2012 at 5:50 PM, Daevid Vincent dae...@daevid.com wrote: Anyone have any thoughts on how I might optimize this query? As always, it's all about the indexes. The index it chose on your main table looks pretty weak. You probably should move those non-joining columns out of your join condition in case their location is influencing the plan, and try experimenting with multi-column indexes. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
two 5.1 servers, different behaviour
I am using DBvisualizer to inspect two MySQL 5.1 databases on two
different systems. The two different systems are nearly identical
in the O/S and the version of MySQL (5.1.58 vs 5.1.60). Both the
systems have MySQL configured in the same way (see below) and are
successfully using MySQL underneath a Perl CGI application.
mysql5.1.60% cat /usr/local/etc/mysql/my.cnf
[mysqld]
max_allowed_packet = 16M
#skip-networking
mysql5.1.58% cat /usr/local/etc/mysql/my.cnf
[mysqld]
max_allowed_packet = 16M
#skip-networking
I can successfully connect to the 5.1.60 server using DBvisualizer,
but I get the following well-known error when I try to connect to the
5.1.58 server on the other system:
com.mysql.jdbc.PacketTooBigException: Packet for query is too
large (7696217 1048576). You can change this value on the server
by setting the max_allowed_packet' variable.
I have contacted the support folks at DBvisualizer sending them the
following stack trace when the above exception occurs. They have no
clue and suggested I contact the MySQL community which I am now doing.
=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
Product: DbVisualizer Personal 8.0
Build: #1689 (2011/06/16 12:10)
Java VM: OpenJDK Client VM
Java Version: 1.6.0
Java Vendor: Sun Microsystems Inc.
OS Name: FreeBSD
OS Arch: i386
OS Version: 8.2-STABLE
13:32:29 [DEBUG AWT-EventQueue-1 DbVisualizerGUI.?] Init Seconds: 4.630 splash
display: 1.238 window display: 4.629
13:32:39 [DEBUG AWT-EventQueue-1 L.?] Loading class using dynamic ClassLoader:
com.mysql.jdbc.Driver
13:32:39 [DEBUG AWT-EventQueue-1 L.?] Loading class using dynamic ClassLoader:
com.mysql.jdbc.Driver
13:32:39 [DEBUG Thread-6 G.?] Connecting: myapp test/development server
13:32:39 [DEBUG Thread-6 L.?] Loading class using dynamic ClassLoader:
com.mysql.jdbc.Driver
13:32:39 [DEBUG Thread-6 L.?] Loading class using dynamic ClassLoader:
com.mysql.jdbc.Driver
13:32:39 [DEBUG pool-1-thread-1 D.?] RootConnection:
Driver.acceptsURL(jdbc:mysql://xxx.yyy.zzz.www:3306/myapp)
13:32:39 [DEBUG pool-1-thread-2 D.?] RootConnection:
Driver.connect(jdbc:mysql://xxx.yyy.zzz.www:3306/myapp, {user=,
password=})
13:32:39 [DEBUG pool-1-thread-2 D.?] RootConnection: EXCEPTION -
com.mysql.jdbc.PacketTooBigException: Packet for query is too large (7696217
1048576). You can change this value on the server by setting the
max_allowed_packet' variable.
=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
I could easily upgrade to 5.1.60 on the failing system, but I'd rather
not have to do that unless that is the called for solution.
I would be greatful for any hints or suggestions as to how I might go
about correcting this problem. Thank you in advance.
Regards,
web...
--
William Bulley Email: w...@umich.edu
72 characters width template -|
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: two 5.1 servers, different behaviour
That error is coming from neither the MySQL server nor from
DBVisualizer. That is coming from your JDBC driver. Check the
version of that and research the effect of configuration options.
http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html
- michael dykman
On Fri, Feb 24, 2012 at 10:44 AM, William Bulley w...@umich.edu wrote:
I am using DBvisualizer to inspect two MySQL 5.1 databases on two
different systems. The two different systems are nearly identical
in the O/S and the version of MySQL (5.1.58 vs 5.1.60). Both the
systems have MySQL configured in the same way (see below) and are
successfully using MySQL underneath a Perl CGI application.
mysql5.1.60% cat /usr/local/etc/mysql/my.cnf
[mysqld]
max_allowed_packet = 16M
#skip-networking
mysql5.1.58% cat /usr/local/etc/mysql/my.cnf
[mysqld]
max_allowed_packet = 16M
#skip-networking
I can successfully connect to the 5.1.60 server using DBvisualizer,
but I get the following well-known error when I try to connect to the
5.1.58 server on the other system:
com.mysql.jdbc.PacketTooBigException: Packet for query is too
large (7696217 1048576). You can change this value on the server
by setting the max_allowed_packet' variable.
I have contacted the support folks at DBvisualizer sending them the
following stack trace when the above exception occurs. They have no
clue and suggested I contact the MySQL community which I am now doing.
=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
Product: DbVisualizer Personal 8.0
Build: #1689 (2011/06/16 12:10)
Java VM: OpenJDK Client VM
Java Version: 1.6.0
Java Vendor: Sun Microsystems Inc.
OS Name: FreeBSD
OS Arch: i386
OS Version: 8.2-STABLE
13:32:29 [DEBUG AWT-EventQueue-1 DbVisualizerGUI.?] Init Seconds: 4.630
splash display: 1.238 window display: 4.629
13:32:39 [DEBUG AWT-EventQueue-1 L.?] Loading class using dynamic
ClassLoader: com.mysql.jdbc.Driver
13:32:39 [DEBUG AWT-EventQueue-1 L.?] Loading class using dynamic
ClassLoader: com.mysql.jdbc.Driver
13:32:39 [DEBUG Thread-6 G.?] Connecting: myapp test/development server
13:32:39 [DEBUG Thread-6 L.?] Loading class using dynamic ClassLoader:
com.mysql.jdbc.Driver
13:32:39 [DEBUG Thread-6 L.?] Loading class using dynamic ClassLoader:
com.mysql.jdbc.Driver
13:32:39 [DEBUG pool-1-thread-1 D.?] RootConnection:
Driver.acceptsURL(jdbc:mysql://xxx.yyy.zzz.www:3306/myapp)
13:32:39 [DEBUG pool-1-thread-2 D.?] RootConnection:
Driver.connect(jdbc:mysql://xxx.yyy.zzz.www:3306/myapp, {user=,
password=})
13:32:39 [DEBUG pool-1-thread-2 D.?] RootConnection: EXCEPTION -
com.mysql.jdbc.PacketTooBigException: Packet for query is too large (7696217
1048576). You can change this value on the server by setting the
max_allowed_packet' variable.
=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=
I could easily upgrade to 5.1.60 on the failing system, but I'd rather
not have to do that unless that is the called for solution.
I would be greatful for any hints or suggestions as to how I might go
about correcting this problem. Thank you in advance.
Regards,
web...
--
William Bulley Email: w...@umich.edu
72 characters width template -|
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
--
- michael dykman
- mdyk...@gmail.com
May the Source be with you.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
Re: two 5.1 servers, different behaviour
According to Michael Dykman mdyk...@gmail.com on Fri, 02/24/12 at 11:42: That error is coming from neither the MySQL server nor from DBVisualizer. That is coming from your JDBC driver. Check the version of that and research the effect of configuration options. http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html Thanks for the reply. I understood that the error came from the JBDC driver because of the Java class path given in the exception text: com.mysql.jdbc.PacketTooBigException But that isn't the issue. The same DbVisualizer instance works just fine when connecting to the MySQL 5.1.60 server on system A, but fails with the query too large exception when trying to connect to the MySQL 5.1.58 server on system B. It strains credulity to think that the same DbVisualizer instance would use two different JBDC drivers when the DbVisualizer connect string begins with jdbc:mysql://... in both cases. I found this file in my DbVisualizer installation directory: unix% cat /usr/local/share/dbvis/jdbc/mysql/README MySQL-AB JDBC Driver Version:5.1.16 Files: mysql.jar Reference: http://www.mysql.com DbVis Software AB is a MySQL Network Certified Partner and have the right to distribute the Connector/J driver. So what I can't explain is why it works for one and not the other. Regards, web... -- William Bulley Email: w...@umich.edu 72 characters width template -| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: two 5.1 servers, different behaviour
I see your point. Configuration files aside, what do you get when you query the servers themselves with: show variables like 'max_allowed_packet' There *might* be something in your start-up scripts overriding that config setting. - md On Fri, Feb 24, 2012 at 11:57 AM, William Bulley w...@umich.edu wrote: According to Michael Dykman mdyk...@gmail.com on Fri, 02/24/12 at 11:42: That error is coming from neither the MySQL server nor from DBVisualizer. That is coming from your JDBC driver. Check the version of that and research the effect of configuration options. http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html Thanks for the reply. I understood that the error came from the JBDC driver because of the Java class path given in the exception text: com.mysql.jdbc.PacketTooBigException But that isn't the issue. The same DbVisualizer instance works just fine when connecting to the MySQL 5.1.60 server on system A, but fails with the query too large exception when trying to connect to the MySQL 5.1.58 server on system B. It strains credulity to think that the same DbVisualizer instance would use two different JBDC drivers when the DbVisualizer connect string begins with jdbc:mysql://... in both cases. I found this file in my DbVisualizer installation directory: unix% cat /usr/local/share/dbvis/jdbc/mysql/README MySQL-AB JDBC Driver Version: 5.1.16 Files: mysql.jar Reference: http://www.mysql.com DbVis Software AB is a MySQL Network Certified Partner and have the right to distribute the Connector/J driver. So what I can't explain is why it works for one and not the other. Regards, web... -- William Bulley Email: w...@umich.edu 72 characters width template -| -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: two 5.1 servers, different behaviour
According to Michael Dykman mdyk...@gmail.com on Fri, 02/24/12 at 12:58: I see your point. Configuration files aside, what do you get when you query the servers themselves with: show variables like 'max_allowed_packet' There *might* be something in your start-up scripts overriding that config setting. The query on the 5.1.60 server returned this: mysql show variables like 'max_allowed_packet'; ++--+ | Variable_name | Value| ++--+ | max_allowed_packet | 16777216 | ++--+ 1 row in set (0.00 sec) mysql And from the 5.1.58 server this: mysql show variables like 'max_allowed_packet'; ++--+ | Variable_name | Value| ++--+ | max_allowed_packet | 16777216 | ++--+ 1 row in set (0.01 sec) mysql This is what I exepected since the my.cnf files are configured identically on the two servers as I indicated earlier. BTW, this query was run by us yesterday in an attempt to debug this issue. At this point we were stumped and called it a day. :-( Today, I got the less than helpful from the support folks at DbVisualizer and then I contacted the MySQL community. :-) Regards, web... -- William Bulley Email: w...@umich.edu 72 characters width template -| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: two 5.1 servers, different behaviour
At this point, I would not know what else to do except fire up wireshark and start debugging the packets. Are both servers on the same subnet? Is your DBVisualizer client local to either of these or on the same subnet as one and not another? You mentioned a minor version difference between the servers.. Have you read the relevant release notes between those versions? This looks like an ugly one. I don't envy you. - michael dykman On Fri, Feb 24, 2012 at 1:09 PM, William Bulley w...@umich.edu wrote: According to Michael Dykman mdyk...@gmail.com on Fri, 02/24/12 at 12:58: I see your point. Configuration files aside, what do you get when you query the servers themselves with: show variables like 'max_allowed_packet' There *might* be something in your start-up scripts overriding that config setting. The query on the 5.1.60 server returned this: mysql show variables like 'max_allowed_packet'; ++--+ | Variable_name | Value | ++--+ | max_allowed_packet | 16777216 | ++--+ 1 row in set (0.00 sec) mysql And from the 5.1.58 server this: mysql show variables like 'max_allowed_packet'; ++--+ | Variable_name | Value | ++--+ | max_allowed_packet | 16777216 | ++--+ 1 row in set (0.01 sec) mysql This is what I exepected since the my.cnf files are configured identically on the two servers as I indicated earlier. BTW, this query was run by us yesterday in an attempt to debug this issue. At this point we were stumped and called it a day. :-( Today, I got the less than helpful from the support folks at DbVisualizer and then I contacted the MySQL community. :-) Regards, web... -- William Bulley Email: w...@umich.edu 72 characters width template -| -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: two 5.1 servers, different behaviour
According to Michael Dykman mdyk...@gmail.com on Fri, 02/24/12 at 13:23: At this point, I would not know what else to do except fire up wireshark and start debugging the packets. Well, doesn't that beat all, sigh... Now I'm back to square zero... :-( Are both servers on the same subnet? Negative. Is your DBVisualizer client local to either of these or on the same subnet as one and not another? The DbVisualizer client tool is local to the 5.1.60 server. You mentioned a minor version difference between the servers.. Have you read the relevant release notes between those versions? I think that might be a next step, but even more expedient would be upgrading the 5.1.58 server to 5.1.60 or 5.1.61 version and retest. This looks like an ugly one. I don't envy you. Gee, thanks for those words of encouragement - NOT! :-) Regards, web... -- William Bulley Email: w...@umich.edu 72 characters width template -| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: two 5.1 servers, different behaviour
To rule out a version issue have you tried another host with the problematic version and same/similar config? On Feb 24, 2012 6:47 PM, William Bulley w...@umich.edu wrote: According to Michael Dykman mdyk...@gmail.com on Fri, 02/24/12 at 13:23: At this point, I would not know what else to do except fire up wireshark and start debugging the packets. Well, doesn't that beat all, sigh... Now I'm back to square zero... :-( Are both servers on the same subnet? Negative. Is your DBVisualizer client local to either of these or on the same subnet as one and not another? The DbVisualizer client tool is local to the 5.1.60 server. You mentioned a minor version difference between the servers.. Have you read the relevant release notes between those versions? I think that might be a next step, but even more expedient would be upgrading the 5.1.58 server to 5.1.60 or 5.1.61 version and retest. This looks like an ugly one. I don't envy you. Gee, thanks for those words of encouragement - NOT! :-) Regards, web... -- William Bulley Email: w...@umich.edu 72 characters width template -| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: two 5.1 servers, different behaviour
On Fri, Feb 24, 2012 at 1:46 PM, William Bulley w...@umich.edu wrote: Are both servers on the same subnet? Negative. *maybe* your router is doing some packet mangling? Wild shot in the dark.. Is your DBVisualizer client local to either of these or on the same subnet as one and not another? The DbVisualizer client tool is local to the 5.1.60 server. Perhaps try hitting that 'local' server' from a remote client to see if the effect is the same? Is this the one misbehaving? You mentioned a minor version difference between the servers.. Have you read the relevant release notes between those versions? I think that might be a next step, but even more expedient would be upgrading the 5.1.58 server to 5.1.60 or 5.1.61 version and retest. Agreed! This looks like an ugly one. I don't envy you. Gee, thanks for those words of encouragement - NOT! :-) What I meant to say was: I can't think of a better way to spend a weekend. Have fun! -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: two 5.1 servers, different behaviour
According to Andrew Moore eroomy...@gmail.com on Fri, 02/24/12 at 14:03: To rule out a version issue have you tried another host with the problematic version and same/similar config? No, for two reasons: a) I just tumbled onto the idea of doing a version upgrade today, and b) the amount of work to do as you suggest is greater than simply upgrading the 5.1.58 server to 5.1.60 or 5.1.61 server Thanks for the reply! Regards, web... -- William Bulley Email: w...@umich.edu 72 characters width template -| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: two 5.1 servers, different behaviour
According to Michael Dykman mdyk...@gmail.com on Fri, 02/24/12 at 14:10: *maybe* your router is doing some packet mangling? Wild shot in the dark.. That is worth investigating... :-) Thanks. Perhaps try hitting that 'local' server' from a remote client to see if the effect is the same? Is this the one misbehaving? This is also worth trying, but both of these attempts will have to wait until Monday when I can arrange the tests. What I meant to say was: I can't think of a better way to spend a weekend. Have fun! Weekend, no, but come Monday, I will be able to try out some of these concepts. I do, however, plan to have fun this weekend... ;^) Regards, web... -- William Bulley Email: w...@umich.edu 72 characters width template -| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: exclusive write lock for innodb tbl
Hello, Unless I misunderstood the task, the exclusive lock would be one way to solve it. What you want to do, is have both parent and children start their activities with locking the table in exclusive mode and then performing their operations. The parent and children will then all serialize their operations and can do pretty much anything with the table in their own turn. The exclusive lock is not, however, a requirement for a task like this. The same serialization could be achieved by using transactions for each operation and starting each operation with a write. The subtle requirement for that is disabling autocommit and of course using Innodb tables. For example, each parent could insert rows with the status of PENDING and each child would then do something like update tblA set status = 'READ' where status = 'PENDING' limit 1. What this does, it picks the first unprocessed row and locks it. After the row is locked, the child can read whatever it wants from the row, knowing that all other children will lock on the same row and be suspended. The idea above could further be extended and enhanced depending on the requirement. The ordering by timestamp could be added, the parallel processing of records and so on. Peace, Karen On Feb 24, 2012, at 6:27 PM, bruce wrote: hi. trying to get my head around how to create an exclusive read/write lock for a tblA. tblA is updated by a parent process. the test system then has multiple children who on a 1st come basis attempt to read the 1st unread row. something like:: parentApp (writes/updates) tblA ^ ^ childA -^ childB -^ childC -^ so when childA reads, childB/childC need to wait, so they can't read the same row as childA. childA updates the tblA row with a uniqueID so the other tbls don't read the row, after childA releases the tblA... as i understand it, this should be doable using innodb, but none of the articles i've seen describe how to accomplish this. the shared mode refers to the write process the exclusive mode might be useful, but i seem to be missing something.. thoughts on this would be useful. thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
