Percona Live
Hey you lot, I'm currently being brainwashed with Oracle internals, so to keep a semblance of sanity I'm throwing this out here :-) I'd just like to thank the nice Percona people for successfully throwing together the second edition of Percona Live London. It was, if anything, even more interesting than the first edition, and I had great fun discussing all the techie stuff with the right people. Not to mention trying to figure out what the hell this other Johan dude was talking about when he was explaining how to optimize the entire stack from CPU to instance. The man clearly knows what he's talking about. Me, not so much :-p So, again, thank you for making this happen, and I'll definitely be there again next year :-) Anyways, off to analyze selfish reads and other mayhem again. /johan -- My love for you is like a cockroach: It's mostly active at night, It frightens the elderly, And it is completely impervious to nuclear weapons.
Re: Help with left outer join
On 12/11/2012 7:22 PM, h...@tbbs.net wrote: ... (Are all the distinct ids really needed? When one joins on a field with the same name in both tables, one may use 'USING', and only the common field, with neither NULL, shows up in the output.) This is a perfectly acceptable naming convention to use. For example if you have a field on the `art` table that references the ID column of the `person` table to indicate the owner and another field to indicate the person who created the art, you might want to use the names `owner_person_id` and `artist_person_id` to keep them separate from the `id` column used to uniquely identify the work of art itself. In this design pattern, each table has a numeric ID column (string-based primary keys are perfectly legal but have their drawbacks and should be used with care) and to reference it from another table you can use the pattern parenttable_id. It keeps your naming conventions clean and predictable. If I were to try to use a USING operator in my opening example, I would be trying to match the PK fields of two separate types of data. (the USING example) SELECT ... FROM art INNER JOIN person USING(id)... Let's say that I renamed the id fields to art_id and person_id to make them table-specific. This still fails because a person's identifier as an owner is not the same as a work of art's creator. It also does not allow me to use the `person` table more than once in a single query. (FAIL: a renamed USING example) SELECT ... FROM art INNER JOIN person USING(person_id) --- does this refer to the owner or the creator of the art? (the name template example) SELECT ... FROM art INNER JOIN person owner on art.owner_person_id = owner.id INNER JOIN person artist on art.artist_person_id = artist.id ... I admit I am a control freak when it comes to the accuracy of my queries, the integrity of my data, and the ease of maintenance for my SQL statements. Because of this, I much prefer the regular predictability of the ANSI JOIN syntax (demonstrated above) for any JOIN except for an intentional Cartesian product. For that case alone, I prefer a comma join (Cartesian product example) SELECT ... FROM table1, table2 ... Not only is the ANSI syntax the only way to specify an OUTER join (such as LEFT JOIN or RIGHT JOIN) it forces the author of the statement to recognize that they do or do not have an ON clause for this table relationship. Separating the same conditions into a WHERE clause makes it very easy to overlook a table relationship and accidentally create a Cartesian product which can often devastate query performance. It is perfectly legal to use the comma-join syntax with MySQL but I strongly recommend against it just because it can only be used for INNER joins or CROSS joins and because it forces you to put your relationship conditions in the WHERE clause. Another strike (to me, anyway) against the comma join is that in order to process joins more like the specifications in the SQL standards, we demoted the precedence of the comma operator with 5.0.12. These changes also affected the behavior of the USING and NATURAL JOIN operators. http://dev.mysql.com/doc/refman/5.5/en/join.html Regards, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Handlersockets - mutex_delay contention
Hello, Just for the record, I found the problem and overcome the contention. The key was: innodb_thread_concurrency Quoting the documentation: innodb_thread_concurrency is the variable that limits the number of operating system threads that can run concurrently inside the InnoDB engine. Rest of the threads have to wait in a FIFO queue for execution. Also, threads waiting for locks are not counted in the number of concurrently executing threads. When the tests started, we set it to the recommended value: 2xNumber of Cores. I tried also setting it up with a crazy number Only when I disabled it (set global innodb_thread_concurrency = 0;) the reads went crazy and I was able to do 1.1M reads per second :-) The CPU is now the bottleneck (which makes sense) and reached around 93-94%, at that point I am not able to go over 1.1M r/s. The mutex_delay never appeared again after disabling the transactions limit. Manuel. 2012/12/7 Manuel Arostegui man...@tuenti.com Hello all, I am testing handlersockets performance in a 5.5.28-29.1-log (Percona) server. These are the enabled options: loose_handlersocket_port = 9998 loose_handlersocket_port_wr = loose_handlersocket_threads = 48 loose_handlersocket_threads_wr = 1 innodb_spin_wait_delay=0 The machine has 24 (Xeon - 2.00GHz) cores and 64GB RAM. We are using bonding to make sure the ethernets aren't limiting us here (we get around 90Mbps) We are able to handler around 500K requests per second using handler socket plugin. Even though it looks pretty impressive number, it's still not close to the 750K ones Yoshinori is able to get ( http://yoshinorimatsunobu.blogspot.com.es/2010/10/using-mysql-as-nosql-story-for.html ) The machine is acting a normal slave in a cluster - receiving normal traffic from our site (we do this on purpose to see how many requests we can handle in a normal workload environment) Obviously we're not expecting to get similar numbers as our tests aren't the same. However, doing a bit of profiling to try to determine what's the bottleneck here we've seen this: CPU: Intel Architectural Perfmon, speed 2000.26 MHz (estimated) Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (No unit mask) count 10 samples %image name symbol name *2163868 24.2065 mysqld mutex_delay* 6945407.7696 mysqld build_template(row_prebuilt_struct*, THD*, TABLE*, unsigned int) 6264057.0074 mysqld buf_page_get_gen 6076646.7978 mysqld rec_get_offsets_func 4809375.3801 mysqld cmp_dtuple_rec_with_match 4120814.6098 mysqld btr_cur_search_to_nth_level 3654024.0876 mysqld rec_init_offsets 3560643.9832 mysqld page_cur_search_with_match 3108193.4770 mysqld row_search_for_mysql 2742483.0679 mysqld row_sel_store_mysql_rec 2084662.3320 mysqld my_pthread_fastmutex_lock 1858532.0791 mysqld ha_innobase::index_read(unsigned char*, unsigned char const*, unsigned int, ha_rkey_function) 1829392.0465 mysqld pfs_mutex_enter_func 1543691.7269 mysqld mtr_memo_slot_release 1385581.5500 mysqld page_check_dir 1316221.4724 mysqld dict_index_copy_types 1011621.1317 mysqld srv_conc_force_exit_innodb 72754 0.8139 mysqld ha_innobase::change_active_index(unsigned int) 65191 0.7293 mysqld my_long10_to_str_8bit 62574 0.7000 mysqld btr_pcur_store_position 61900 0.6925 mysqld pfs_mutex_exit_func 51889 0.5805 mysqld Field_long::pack_length() const 49073 0.5490 mysqld srv_conc_enter_innodb 44079 0.4931 mysqld ha_innobase::init_table_handle_for_HANDLER() 38998 0.4363 mysqld Field_tiny::pack_length() const 38868 0.4348 mysqld rec_copy_prefix_to_buf 36292 0.4060 mysqld ha_innobase::innobase_get_index(unsigned int) That mutex_delay is eating quite a big % of the time. I have not been able to find what is that related to. Does anyone has a clue about what's it and if there's a way to improve and overcome it? Cheers Manuel.
Re: Help with left outer join
On Wed, Dec 12, 2012 at 8:25 AM, Shawn Green shawn.l.gr...@oracle.com wrote: On 12/11/2012 7:22 PM, h...@tbbs.net wrote: ... (Are all the distinct ids really needed? When one joins on a field with the same name in both tables, one may use 'USING', and only the common field, with neither NULL, shows up in the output.) This is a perfectly acceptable naming convention to use. For example if you have a field on the `art` table that references the ID column of the `person` table to indicate the owner and another field to indicate the person who created the art, you might want to use the names `owner_person_id` and `artist_person_id` to keep them separate from the `id` column used to uniquely identify the work of art itself. In this design pattern, each table has a numeric ID column (string-based primary keys are perfectly legal but have their drawbacks and should be used with care) and to reference it from another table you can use the pattern parenttable_id. It keeps your naming conventions clean and predictable. If I were to try to use a USING operator in my opening example, I would be trying to match the PK fields of two separate types of data. (the USING example) SELECT ... FROM art INNER JOIN person USING(id)... Let's say that I renamed the id fields to art_id and person_id to make them table-specific. This still fails because a person's identifier as an owner is not the same as a work of art's creator. It also does not allow me to use the `person` table more than once in a single query. (FAIL: a renamed USING example) SELECT ... FROM art INNER JOIN person USING(person_id) --- does this refer to the owner or the creator of the art? (the name template example) SELECT ... FROM art INNER JOIN person owner on art.owner_person_id = owner.id INNER JOIN person artist on art.artist_person_id = artist.id ... I admit I am a control freak when it comes to the accuracy of my queries, the integrity of my data, and the ease of maintenance for my SQL statements. Because of this, I much prefer the regular predictability of the ANSI JOIN syntax (demonstrated above) for any JOIN except for an intentional Cartesian product. For that case alone, I prefer a comma join (Cartesian product example) SELECT ... FROM table1, table2 ... Not only is the ANSI syntax the only way to specify an OUTER join (such as LEFT JOIN or RIGHT JOIN) it forces the author of the statement to recognize that they do or do not have an ON clause for this table relationship. Separating the same conditions into a WHERE clause makes it very easy to overlook a table relationship and accidentally create a Cartesian product which can often devastate query performance. It is perfectly legal to use the comma-join syntax with MySQL but I strongly recommend against it just because it can only be used for INNER joins or CROSS joins and because it forces you to put your relationship conditions in the WHERE clause. Another strike (to me, anyway) against the comma join is that in order to process joins more like the specifications in the SQL standards, we demoted the precedence of the comma operator with 5.0.12. These changes also affected the behavior of the USING and NATURAL JOIN operators. http://dev.mysql.com/doc/refman/5.5/en/join.html Thanks very much Shawn for this very informative post. I learned SQL in the early 1980's and I was not taught the JOIN syntax, only the comma join and WHERE. It's really just force of habit that I write queries that way. I will try very hard in the future to break that habit and use the JOIN syntax. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Help with left outer join
On Tue, Dec 11, 2012 at 8:48 PM, Peter Brawley peter.braw...@earthlink.net wrote: ERROR 1054 (42S22): Unknown column 'data_tool.category_id' in 'on clause' But category_id is a column in data_tool. Then a bit of reordering is required ... SELECT data_target.name, ep, wafer_id, lot_id, date_time, data_file_id, data_cstimage.name, bottom, wf_file_path_id, data_measparams.name, vacc, data_category.name FROM data_cst RIGHT JOIN data_cstimage ON data_cstimage.id = data_cst.image_measurer_id INNER JOIN data_tool ON data_tool.id = data_cst.tool_id INNER JOIN data_target ON data_target.id = data_cst.target_name_id INNER JOIN data_measparams ON data_measparams.id = data_cst.meas_params_name_id INNER JOIN data_category ON data_category.id = data_tool.category_id WHERE data_cst.target_name_id IN (38018, 29947, 28330) AND data_cst.date_time BETWEEN '2012-09-01 00:00:00' AND '2012-09-07 00:00:00' ORDER BY target_name_id, ep, wafer_id, lot_id, date_time; Your query worked exactly like my original one - i.e. when data_cst.image_measurer_id is NULL I don't get that data_cst row. But I changed the RIGHT JOIN to a LEFT JOIN and then it was doing exactly what I wanted. Thanks for all the help! On 2012-12-11 5:11 PM, Larry Martell wrote: SELECT data_target.name, ep, wafer_id, lot_id, date_time, data_file_id, data_cstimage.name, bottom, wf_file_path_id, data_measparams.name, vacc, data_category.name FROM data_cst RIGHT JOIN data_cstimage ON data_cstimage.id = data_cst.image_measurer_id INNER JOIN data_target ON data_target.id = data_cst.target_name_id INNER JOIN data_measparams ON data_measparams.id = data_cst.meas_params_name_id INNER JOIN data_category ON data_category.id = data_tool.category_id INNER JOIN data_tool ON data_tool.id = data_cst.tool_id WHERE data_cst.target_name_id IN (38018, 29947, 28330) AND data_cst.date_time BETWEEN '2012-09-01 00:00:00' AND '2012-09-07 00:00:00' ORDER BY target_name_id, ep, wafer_id, lot_id, date_time; Thanks very much for the reply. This is giving me: ERROR 1054 (42S22): Unknown column 'data_tool.category_id' in 'on clause' But category_id is a column in data_tool. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Help with left outer join
On Tue, Dec 11, 2012 at 7:22 PM, h...@tbbs.net wrote: 2012/12/11 16:19 -0500, Larry Martell I have this query: SELECT data_target.name, ep, wafer_id, lot_id, date_time, data_file_id, data_cstimage.name, bottom, wf_file_path_id, data_measparams.name, vacc, data_category.name FROM data_cst, data_target, data_cstimage, data_measparams, data_category, data_tool WHERE data_cst.target_name_id IN (38018, 29947, 28330) AND data_cst.date_time BETWEEN '2012-09-01 00:00:00' AND '2012-09-07 00:00:00' AND data_target.id = data_cst.target_name_id AND data_cstimage.id = data_cst.image_measurer_id AND data_measparams.id = data_cst.meas_params_name_id AND data_category.id = data_tool.category_id AND data_tool.id = data_cst.tool_id ORDER BY target_name_id, ep, wafer_id, lot_id, date_time My problem is that when data_cst.image_measurer_id is NULL I don't get that data_cst row even though all the other part of the where clause are TRUE. I understand why that is, but in that case I want the row, but with NULL in the data_cstimage.name column. I think I need a left outer join, but I've been messing with this for hours, and I can't get the syntax right. I've googled it, but all the examples are simple with just 2 tables. Can someone help me with this? Modern forms do not give a left join if one uses WHERE-clause to reduce a full cross-join to an inner join. It is better to start with something like this, FROM data_cst JOIN data_target ON data_target.id = data_cst.target_name_id JOIN data_cstimage ON data_cstimage.id = data_cst.image_measurer_id JOIN data_measparams ON data_measparams.id = data_cst.meas_params_name_id JOIN (data_category JOIN data_tool ON data_category.id = data_tool.category_id) ON data_tool.id = data_cst.tool_id but I am not too sure where to bracket data_tool. When you have put it into a 'FROM'-clause with 'JOIN', not comma, separating the tables, with the same outcome as now, then you have to decide before which 'JOIN' to put the 'LEFT'. Maybe you want it between data_cstimage and data_measparams. (Are all the distinct ids really needed? When one joins on a field with the same name in both tables, one may use 'USING', and only the common field, with neither NULL, shows up in the output.) I'm not familiar with the USING clause. I'll have to look into that. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Foreign-key naming
When I wrote my comment after Larry Martell s problem, I already suspected it was somewhat out of place because to his problem it did not apply. 2012/12/12 08:25 -0500, Shawn Green This is a perfectly acceptable naming convention to use. For example if you have a field on the `art` table that references the ID column of the `person` table to indicate the owner and another field to indicate the person who created the art, you might want to use the names `owner_person_id` and `artist_person_id` to keep them separate from the `id` column used to uniquely identify the work of art itself. In this design pattern, each table has a numeric ID column (string-based primary keys are perfectly legal but have their drawbacks and should be used with care) and to reference it from another table you can use the pattern parenttable_id. It keeps your naming conventions clean and predictable. If I were to try to use a USING operator in my opening example, I would be trying to match the PK fields of two separate types of data. (the USING example) SELECT ... FROM art INNER JOIN person USING(id)... Let's say that I renamed the id fields to art_id and person_id to make them table-specific. This still fails because a person's identifier as an owner is not the same as a work of art's creator. It also does not allow me to use the `person` table more than once in a single query. (FAIL: a renamed USING example) SELECT ... FROM art INNER JOIN person USING(person_id) --- does this refer to the owner or the creator of the art? (the name template example) SELECT ... FROM art INNER JOIN person owner on art.owner_person_id = owner.id INNER JOIN person artist on art.artist_person_id = artist.id ... Well, you're right, a work can refer to people in at least two different aspects, there is the work's author, and the work s owner. Neither is appropriate for the same name as found in a list of people, because now a distinction is made in the undifferentiated mass. And, yes, in general I suspect that if in one table there are more foreign-key references to the same key in another table, there is enough difference in aspect that none of them is fittingly so named as in the original table. What if neither author nor owner directly referred to people, but, instead, author referred to a table of artists, with their training style listed, and owner referred to a table of owners, with preferred styles of work listed? These tables in the end would refer to people; shall their references bear a name distinct from the key in the original table's? I believe that for every chain of foreign-key references from one table to another, if there is no other chain of foreign-key references from that one table to that other table (and no design-change that changes this is likely!), it is quite all right if along the chain each foreign-key reference and the key to which each refers have the same name. The nice thing about USING and NATURAL is that in a query only one coalesced field is yielded. I find it not quite right to pick between one field or another to yield when both are alike unless one of them is NULL. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: using LIMIT without ORDER BY
I am not sure, but if its a MyISAM table, it should be ordered by the records insertion order, and in case of InnoDB it should be ordered by the clustered index, not necessarily it should be a defined one. On Thu, Dec 13, 2012 at 12:58 PM, jiangwen jiang jiangwen...@gmail.comwrote: hi, all: There's a confusion. I want to get all the data in table t by pages, using Limit SQL without ORDER BY: SELECT * FROM t Limit 0,10 SELECT * FROM t Limit 10, 10 ... Is it right without ORDER BY? Is there any default order in table t, to make suer I can get all data in the table? Thanks Regards! White
