MySQL 5.1: Views, queries, updates and performance issues
Hi all, I've got some semi-general questions on the topics in the title. What I'm looking for is more in the line of theory than query specifics. I am but a poor peasant boy. What I have is an application that makes heavy use of views. If I understand views correctly (and I may not), views are representations of queries themselves. The guy who wrote the app chose to do updates and joins against the views instead of against the underlying tables themselves. I've tuned to meet the gross memory requirements and mysqltuner.pl is saying that 45% of the joins are without indexes. With the slow query logs on and queries_without_indexes, I'm frequently seeing updates that often take more that 2 seconds to complete... Often MUCH longer (how does 157 seconds grab you?). So, with that background, what would you do next and is it possible this use of views, in this way is a significant contributor to the problem? Bruce Ferrell -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: MySQL 5.1: Views, queries, updates and performance issues
At first blush, your problem would appear to concern the lack of index-use. That's where I would begin my investigation. It might be painstaking, but I would do something like this: For each view Look at the Join(s) and see what columns are being joined Look at the tables and see what columns are being indexed If any columns are not indexed that should be, create said index(es) Next view Of course, this process could be optimized by looking at the views in terms of their frequency of use. Finally, you didn't mention what sort of front end you're using. It's possible that you might benefit by using stored procedures rather than views. That switch would of course cost you some time invested in changing the front end to pass explicit parameters. Hope this helps, Arthur On Thu, Dec 29, 2011 at 12:50 PM, Bruce Ferrell bferr...@baywinds.orgwrote: Hi all, I've got some semi-general questions on the topics in the title. What I'm looking for is more in the line of theory than query specifics. I am but a poor peasant boy. What I have is an application that makes heavy use of views. If I understand views correctly (and I may not), views are representations of queries themselves. The guy who wrote the app chose to do updates and joins against the views instead of against the underlying tables themselves. I've tuned to meet the gross memory requirements and mysqltuner.pl is saying that 45% of the joins are without indexes. With the slow query logs on and queries_without_indexes, I'm frequently seeing updates that often take more that 2 seconds to complete... Often MUCH longer (how does 157 seconds grab you?). So, with that background, what would you do next and is it possible this use of views, in this way is a significant contributor to the problem? Bruce Ferrell -- Cell: 647.710.1314 Thirty spokes converge on a hub but it's the emptiness that makes a wheel work -- from the Daodejing
Re: why does basic MySQLdump on db table fail with Permission denied, or else it creates a zero KB file
[snip] that is because Mac OSX is missing a package-managment and so you need a little knowledge about your OS to fix the PATH or you have to use full-qualified calls or configure/install your software to locations which are already in the path which mysqldump as normal user wil tell you where it is really [harry@srv-rhsoft:~]$ which mysqldump /usr/bin/mysqldump Thank you Richard, Andy, and Reindl ! Of course you all nailed it. Reindl, I especially appreciate you addressing each point, as it pointed me in the right direction to fill in the understanding-holes on all those topics! Some things I (partially) knew, but did not remember today because I don't have to deal in these areas much. For right now, I just used full paths both for the command and for the output. Just a side note, that: Govind% which mysqldump mysqldump: Command not found. Govind% which /usr/local/mysql/bin/mysqldump /usr/local/mysql/bin/mysqldump kind of defeats the purpose of having to know the path in advance in order to use the command to detect the path ;-) -Govinda -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: why does basic MySQLdump on db table fail with Permission denied, or else it creates a zero KB file
Am 29.12.2011 19:21, schrieb Govinda: Just a side note, that: Govind% which mysqldump mysqldump: Command not found. Govind% which /usr/local/mysql/bin/mysqldump /usr/local/mysql/bin/mysqldump kind of defeats the purpose of having to know the path in advance in order to use the command to detect the path ;-) /usr/local/mysql/bin/ is nowhere in the path which can only help you if your standard-user has the directory in his PATH and another user not in my opinion this is a configure/compile/install-problem /usr/local/ is intended to have the stahtdard unix-hirarchy like /usr/loca/bin, /usr/local/share, /usr/local/lib and normally /usr/local/bin IS in the PATH so the problem here is that mysql has the unix-hirarchy inside instead directly install into /usr/lcoal/ on the other hand doing this without a package-managment it would be better over the long to keep it chaind all in one directory as it is because you can uninstall it with simply remove the folder for the hadnful things on my linux-machines where such non-default locations are existing i usually set symlinks unter /usr/local/bin/ to the binarys, so they are seperated and from the user point of view in the PATh and all wroks fine additionally a ls -l /usr/local/bin/ shows where all the stuff is physically installed instead haveing all details in mind or notice them somewhere you forget also :-) signature.asc Description: OpenPGP digital signature
why does basic MySQLdump on db table fail with Permission denied, or else it creates a zero KB file
Hi Everyone This should be quick and simple, but after researching on Google quite a bit I am still stumped. I am mostly newbie with: server admin, CLI, MySQL. I am developing my PHP site locally, and now need to move some new MySQL tables from my local dev setup to the remote testing site. First step for me is just to dump the tables, one at a time. I successfully login to my local MySQL like so: Govind% /usr/local/mysql/bin/mysql -uroot but while in this dir (and NOT logged into MySQL): /usr/local/mysql/bin ...when I try this: mysqldump -uroot -p myDBname myTableName myTestDumpedTable.sql ..then I keep getting this: myTestDumpedTable.sql: Permission denied. Same result if I do any variation on that (try to dump the whole db, drop the '-p', etc.) On StackOverflow I asked this question [1], and replies there led me to trying being logged in as root user, and then (the same): mysqldump -uroot -p myDBname myTableName myTestDumpedTable.sql produces: sh: mysqldump: command not found ...which is odd because it does produce a zero-KB file named myTestDumpedTable.sql in that dir. So then I try (in Mac OS X Terminal, while logged in as me (not root)): mysqldump -uroot -p myDBname myTableName ~/myTestDumpedTable.sql ...and again it produces: sh: mysqldump: command not found... and again a zero-KB file named myTestDumpedTable.sql, in ~/ I am embarrassed as I am sure this is going to be incredibly simple, or just reveal a gaping (basic) hole in my knowledge. .. but I would appreciate any help ;-) [1] http://stackoverflow.com/questions/8663454/why-does-basic-mysqldump-on-db-table-fail-with-permission-denied Thanks -Govinda -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: mysql_secure_installation
Hi Shawn, I would assume that MySQL is installed mostly on production servers rather than in class room environments. Wouldn't it make more sense for MySQL to be secure by default rather than insecure by default? It would make more sense to me if there was a 'mysql_insecure_installation' script that did the opposite and the steps done by 'mysql_secure_installation' were implemented by default. I suspect many developers are not even aware of mysql_secure_installation or the steps that it takes and the vast majority do not run it or the do the steps at all. In my opinion, additional steps shouldn't be taken to make MySQL more secure, instead additional steps should be taken to make it insecure if that is what is needed in certain environments. Thank you for the reply. Ryan On Tue, Dec 27, 2011 at 4:08 PM, Shawn Green (MySQL) shawn.l.gr...@oracle.com wrote: Hello Ryan, On 12/18/2011 15:36, Ryan Dewhurst wrote: Hi, Does anyone know why what's done in 'mysql_secure_installation' [0] isnot part of the default mysql installation? [0] http://dev.mysql.com/doc/refman/5.0/en/mysql-secure-installation.html Thank you,Ryan Dewhurst P.S. I also asked this question on the forums:http://forums.mysql.com/read.php?30,506069,506069#msg-506069 The script simply automates the steps documented in our manual, here: http://dev.mysql.com/doc/refman/5.0/en/default-privileges.html If you want to see the script in action: * repeat a fresh install * enable the General Query Log * run the script. The General Query Log stores a copy of every command sent to a MySQL server before the server even parses it. It's there as a diagnostic tool and should not be enabled on a production machine unless there is a specific need to do so. The steps of 'mysql_secure_installation' are not performed by default because many people want to just get to know MySQL before putting it into full production. This is most easily performed (especially in a classroom setting) with an unsecured installation. Also the steps to secure the installation can be leveraged as an excellent teaching tool for: a) How MySQL accounts are authenticated b) Where the account information is stored c) The different levels of authentication supported by MySQL. For those who don't want to read or learn, or for those who simply want to automate their installation, there is the script. -- 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
puzzled issue for deadlock due to locking upgrade from LOCK_S to LOCK_X
Hi Guys, I got a deadlock problem, and it puzzled me days. Hope some body could help with some explanation for the reason of deadlock, better if some extra advises. * * *DeadLock Info:* * * - - - (1) TRANSACTION: TRANSACTION 13D947E32, ACTIVE 0 sec, process no 10928, OS thread id 1470925120 starting index read mysql tables in use 1, locked 1 LOCK WAIT 5 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1 MySQL thread id 2343068, query id 874146900 xxx.yyy.zzz.183 feel Updating UPDATE feed_daily_sum_1124 SET gmt_modified = now() ,GOOD_SELLER = IFNULL(GOOD_SELLER,0)+1 WHERE USER_ID = 376169572 AND RATEDATE = '2011-11-28 00:00:00' - (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 704 page no 220 n bits 736 index `uk_daily_sum_userid` of table `feel_17`.`feed_daily_sum_1124` trx id 13D947E32 lock_mode X locks rec but not gap waiting Record lock, heap no 537 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 8000166be464; asc k d;; 1: len 3; hex 8fb77c; asc |;; 2: len 8; hex 443449df; asc D4I ;; - - - (2) TRANSACTION: TRANSACTION 13D947E3B, ACTIVE 0 sec, process no 10928, OS thread id 1538029888 starting index read, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 5 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1 MySQL thread id 2309035, query id 874146901 xxx.yyy.zzz.56 feel Updating UPDATE feed_daily_sum_1124 SET gmt_modified = now() ,GOOD_SELLER = IFNULL(GOOD_SELLER,0)+1 WHERE USER_ID = 376169572 AND RATEDATE = '2011-11-28 00:00:00' - (2) HOLDS THE LOCK(S): RECORD LOCKS space id 704 page no 220 n bits 736 index `uk_daily_sum_userid` of table `feel_17`.`feed_daily_sum_1124` trx id 13D947E3B lock mode S Record lock, heap no 537 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 8000166be464; asc k d;; 1: len 3; hex 8fb77c; asc |;; 2: len 8; hex 443449df; asc D4I ;; - - - (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 704 page no 220 n bits 736 index `uk_daily_sum_userid` of table `feel_17`.`feed_daily_sum_1124` trx id 13D947E3B lock_mode X locks rec but not gap waiting Record lock, heap no 537 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 8000166be464; asc k d;; 1: len 3; hex 8fb77c; asc |;; 2: len 8; hex 443449df; asc D4I ;; - - - WE ROLL BACK TRANSACTION (2) TRANSACTIONS *Problem:* * * It's a typical deadlock problem, and the two SQL shown are the same. uk_daily_sum_userid is the index that contains user_id and ratedate. Seen from the innodb provided information, we could deduce that: T1 Waiting for a LOCK_X for Record R1 T2 Hods a LOCK_S of Record R1, and waiting for a LOCK_X for R1 As T2 needs a lock upgrade but that is a LOCK_X waiting before T2 requests LOCK_X for the same record, that caused the deadlock. OK, it's all ok for me to understand the deadlock from innodb deadlock info. But my question is: WHY LOCK_S is existed in T2? Let's me show you the SQL in Transaction. T1 and T2 are the same logic SQL assembles as: sql1: INSERT INTO feed_receive values (xxx, yyy); sql2: UPDATE feed_daily_sum sql3: UPDATE feed_all_sum sql4: commit There is no FK constrains in the three tables, all are depended tables!!! WHY LOCK_S should occur in this case? For my understanding of LOCK_S, FK contains (update child but parent related record should have LOCK_S), or SELECT xxx LOCK in shared mode. Any idea?
Re: why does basic MySQLdump on db table fail with Permission denied, or else it creates a zero KB file
I would suggest trying: mysqldump -uroot -p myDBname myTableName /tmp/myTestDumpedTable.sql Maybe you don't have permission (or space) to write into /usr/local/mysql/bin. That would be an unusual place for such files. On 12/29/11 9:15 AM, Govinda wrote: Hi Everyone This should be quick and simple, but after researching on Google quite a bit I am still stumped. I am mostly newbie with: server admin, CLI, MySQL. I am developing my PHP site locally, and now need to move some new MySQL tables from my local dev setup to the remote testing site. First step for me is just to dump the tables, one at a time. I successfully login to my local MySQL like so: Govind% /usr/local/mysql/bin/mysql -uroot but while in this dir (and NOT logged into MySQL): /usr/local/mysql/bin ...when I try this: mysqldump -uroot -p myDBname myTableName myTestDumpedTable.sql ..then I keep getting this: myTestDumpedTable.sql: Permission denied. Same result if I do any variation on that (try to dump the whole db, drop the '-p', etc.) On StackOverflow I asked this question [1], and replies there led me to trying being logged in as root user, and then (the same): mysqldump -uroot -p myDBname myTableName myTestDumpedTable.sql produces: sh: mysqldump: command not found ...which is odd because it does produce a zero-KB file named myTestDumpedTable.sql in that dir. So then I try (in Mac OS X Terminal, while logged in as me (not root)): mysqldump -uroot -p myDBname myTableName ~/myTestDumpedTable.sql ...and again it produces: sh: mysqldump: command not found... and again a zero-KB file named myTestDumpedTable.sql, in ~/ I am embarrassed as I am sure this is going to be incredibly simple, or just reveal a gaping (basic) hole in my knowledge. .. but I would appreciate any help ;-) [1] http://stackoverflow.com/questions/8663454/why-does-basic-mysqldump-on-db-table-fail-with-permission-denied Thanks -Govinda -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- There are two ways to build software: Make it so simple that there are obviously no bugs, or make it so complex that there are no obvious bugs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: mysql_secure_installation
Am 29.12.2011 18:42, schrieb Ryan Dewhurst: I would assume that MySQL is installed mostly on production servers rather than in class room environments. Wouldn't it make more sense for MySQL to be secure by default rather than insecure by default? yes and no yes because there are way too much peopole in positions without the minimal knowledge of their job no because if someone is a trustable admin he does not only a default install without verify and audits signature.asc Description: OpenPGP digital signature
Re: why does basic MySQLdump on db table fail with Permission denied, or else it creates a zero KB file
Am 29.12.2011 18:15, schrieb Govinda: ...when I try this: mysqldump -uroot -p myDBname myTableName myTestDumpedTable.sql ..then I keep getting this: myTestDumpedTable.sql: Permission denied. your unix-user has no write permissions to myTestDumpedTable.sql this has nothing to do wirh mysql what about considering use a target-folder your user owns and generally use a full-qualified path for the dump-file instead spit it randomly in the folder where you are Same result if I do any variation on that (try to dump the whole db, drop the '-p', etc.) because no parameter can change your folder-pmermissions On StackOverflow I asked this question [1], and replies there led me to trying being logged in as root user, and then (the same): mysqldump -uroot -p myDBname myTableName myTestDumpedTable.sql produces: sh: mysqldump: command not found mysqldump is not in the path of your root-user change the PATH-variable or call mysqldump full-qualified ...which is odd because it does produce a zero-KB file named myTestDumpedTable.sql in that dir. it is not odd it is normal that myTestDumpedTable.sql creates the file since what you are doing is output redirection So then I try (in Mac OS X Terminal, while logged in as me (not root)): mysqldump -uroot -p myDBname myTableName ~/myTestDumpedTable.sql ...and again it produces: sh: mysqldump: command not found.. that is because Mac OSX is missing a package-managment and so you need a little knowledge about your OS to fix the PATH or you have to use full-qualified calls or configure/install your software to locations which are already in the path which mysqldump as normal user wil tell you where it is really [harry@srv-rhsoft:~]$ which mysqldump /usr/bin/mysqldump signature.asc Description: OpenPGP digital signature
