Suggested tools to benchmark configuration changes
I have a server that has several hundred table in a few different databases comprising almost a gig of data, all running on a rather old (3.23) version of mysql. I have used the slow query log to identify queries and have optimized the queries significantly. At this point the entries in the slow-query log (with long query time set around at 3 seconds) usually examine between 1k and 10k rows. When I run the query to test them the query time is generally under .1 second. The server is basically running with a my-small.cnf and I think that most of the rest of performance I can pull out of the server will come from tuning the mysql server variables for table cache and temporary table size. I am concerned that I might have issues with ram usage. With this in mind: 1. Is there some way to use the general query log to test different server configurations with a real world assortment of queries? Perhaps some way to use mysqlslap? Any other suggestions for benchmarking tools? 2. I see entries in the slow query log where the number of row examined does not correlate with EXPLAIN's that I run of the queries on the production server. Is this likely a situation where mysql needs index hints, or could something else be in play? Thank you in advance for any help. RTFM welcomed, just point out what page ;) -- Rob Wultsch
Re: Question: table schema optimization
Hailiang Ji wrote: Folks, A help needed. My manager's pushed me to optimize the tables that I created in distributed in several DBs. I have tried best to explain to him that I have followed the strict formula design to do the Join, Search and so on. However, the system performance is not good enough yet when our system get thousands' users visiting in the same time. Maybe you're missing some indexes. Enable slow-query-log and see where your time is being spent. http://dev.mysql.com/doc/refman/4.1/en/slow-query-log.html Once you have some data, use explain to work out whether a query is using an index. Or try mysql_explain_log: http://dev.mysql.com/doc/refman/4.1/en/mysql-explain-log.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question: table schema optimization
You should read up on the "show status" and "show variables" output. That will give you a start as to perhaps some obvious issues. For instances, your opened_tables and threads_created should not be large. Using the right table types is also a very big issue, although you may have gone down that path already. You can try doing a search on remember.yahoo.com and mysql, they ran into some interesting performance bottlenecks they had to solve. Installing mytop may also be helpful to determine what's going on. Finally, you should also consider what is going on in the OS, I like vmstat for a quick overview. I don't know what you definition of "performance is not good". Following a "strict formula" may be part of your problem. Nobody normalizes their database to fifth normal form, it would be too slow and complicated. Check this link out: http://dev.mysql.com/books/hpmysql-excerpts/ch06.html The last paragraph has a bit on yahoo. On Sep 11, 2007, at 2:10 AM, Hailiang Ji wrote: Folks, A help needed. My manager's pushed me to optimize the tables that I created in distributed in several DBs. I have tried best to explain to him that I have followed the strict formula design to do the Join, Search and so on. However, the system performance is not good enough yet when our system get thousands' users visiting in the same time. Could anyone point me something so that I can tune the system performance? I don't think my db tables have problems. I guess I should investigate on the deployment, for example, cluster, load-balancing and so on? Our system hs big traffic at daytime, usually at noon time or at evening. Thanks in advance, Hailiang Ji, Developer Email: [EMAIL PROTECTED] Web: www.mydanwei.com myDanwei, Inc. --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mail not being accepted
When I try to email to this list my email is reject with the following message? Error: 552 Mail with no Date header not accepted here My Mail has a date header in it...?
Re: Copying InnoDB files to remote server -> remote server won't start
Baron Schwartz wrote: Whil Hentzen wrote: Michael Dykman wrote: if you see no errors, check your config... are you sure InnoDB is enabled ? I can create a new database and add InnoDB tables to it without problems. Ack, I lied. No error in the /var/lib/mysql, but there IS an error file in /var/logs, and it contains: "./ibdata1: error 13 in a file operation" "mysqld does not have access rights to the directory" But. I'm sitting here looking and I can't see any difference between the ibdata1 and the ibdata1_orig files, nor the folders that contain the .frm files either. Ownership and permissions are both identical? As best as I can tell - here's the remote machine list: drwxr-xr-x 11 mysql mysql 4096 Sep 11 18:29 . drwxr-xr-x 33 root root 4096 May 10 11:17 .. drwx-- 2 mysql mysql 4096 Sep 11 13:22 delme drwx-- 2 mysql mysql 4096 Sep 11 14:19 delmeinno -rw-r- 1 mysql mysql 10485760 Sep 11 17:10 ibdata1 -rw-rw 1 mysql mysql 10485760 Sep 11 14:19 ibdata1_5 -rw-r- 1 mysql mysql 5242880 Sep 11 17:10 ib_logfile0 -rw-rw 1 mysql mysql 5242880 Sep 11 14:20 ib_logfile0_5 There are two databases here. delme is MyISAM. Works fine. delmeinno is the InnoDB. The original works fine. ibdata1 and ib_logfile0 are the original InnoDB files. They work fine. ibdata1_5 and ib_logfile0_5 are the InnoDB files that were copied from the local box. Yes, i see that these have 'w' permissions for mysql group but I can't see why that would matter or cause the error that is occurring. When I get rid of the ibdata1 and ib_logfile0 files (move them elsewhere), and then rename the '5's so that MySQL tries to access those, the MySQL server won't start. Whil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Copying InnoDB files to remote server -> remote server won't start
Whil Hentzen wrote: Michael Dykman wrote: if you see no errors, check your config... are you sure InnoDB is enabled ? I can create a new database and add InnoDB tables to it without problems. Ack, I lied. No error in the /var/lib/mysql, but there IS an error file in /var/logs, and it contains: "./ibdata1: error 13 in a file operation" "mysqld does not have access rights to the directory" But. I'm sitting here looking and I can't see any difference between the ibdata1 and the ibdata1_orig files, nor the folders that contain the .frm files either. Ownership and permissions are both identical? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Copying InnoDB files to remote server -> remote server won't start
Michael Dykman wrote: if you see no errors, check your config... are you sure InnoDB is enabled ? I can create a new database and add InnoDB tables to it without problems. Ack, I lied. No error in the /var/lib/mysql, but there IS an error file in /var/logs, and it contains: "./ibdata1: error 13 in a file operation" "mysqld does not have access rights to the directory" But. I'm sitting here looking and I can't see any difference between the ibdata1 and the ibdata1_orig files, nor the folders that contain the .frm files either. There must be something but I sure can't see it... Whil On 9/11/07, Whil Hentzen <[EMAIL PROTECTED]> wrote: Hi gang, After reading through two years of 'how do I back up my database' threads, I'm trying out the various backup mechanisms offered up. I've read through these steps: http://dev.mysql.com/doc/refman/5.0/en/innodb-backup.html Running a local box and a remote box, both with Fedora 6, MySQL 5. Trying to copy a local /var/lib/mysql/mydata InnoDB. The problem: After I copy InnoDB files to a remote box, the MySQL server on the remote box won't restart. Here are the steps I've followed: 1. Stop the local and remote servers 2. Rename the remote ibdata and ib_logfile0 files 3. Copy the local ibdata and ib_logfile0 files to the remote /var/lib/mysql folder 4. Copy the local /var/lib/mysql/mydata/* to the remote /var/lib/mysql/mydata/* folder (contains one .frm and db.opt) 5. Change the ownership of the remote files and folders to 'mysql' 6. Restart the remote server (service mysqld start) 7. Failure, with a 'timeout' error There is no error log created in the remote /var/lib/mysql folder. When I rid the copied files and folders, and then rename the originals back to their initial names, the server starts up nice and neat again. I can copy a MyISAM database from the local box to the remote box and it is accessible via the remote MySQL server just fine. Obviously, there is something wrong in the way I'm copying the InnoDB files. What am I missing? Whil -- 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]
Copying InnoDB files to remote server -> remote server won't start
Hi gang, After reading through two years of 'how do I back up my database' threads, I'm trying out the various backup mechanisms offered up. I've read through these steps: http://dev.mysql.com/doc/refman/5.0/en/innodb-backup.html Running a local box and a remote box, both with Fedora 6, MySQL 5. Trying to copy a local /var/lib/mysql/mydata InnoDB. The problem: After I copy InnoDB files to a remote box, the MySQL server on the remote box won't restart. Here are the steps I've followed: 1. Stop the local and remote servers 2. Rename the remote ibdata and ib_logfile0 files 3. Copy the local ibdata and ib_logfile0 files to the remote /var/lib/mysql folder 4. Copy the local /var/lib/mysql/mydata/* to the remote /var/lib/mysql/mydata/* folder (contains one .frm and db.opt) 5. Change the ownership of the remote files and folders to 'mysql' 6. Restart the remote server (service mysqld start) 7. Failure, with a 'timeout' error There is no error log created in the remote /var/lib/mysql folder. When I rid the copied files and folders, and then rename the originals back to their initial names, the server starts up nice and neat again. I can copy a MyISAM database from the local box to the remote box and it is accessible via the remote MySQL server just fine. Obviously, there is something wrong in the way I'm copying the InnoDB files. What am I missing? Whil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: archive engine potential bug
also when it hits a dupl. and i skip the record, and continue slave it hits another duplicate entry almost instantly, when i then drop the table on the slave, and recreate it manually and set it to myisam at that time, the slave continues without a problem. On Tue, 2007-09-11 at 16:42 +0200, WiNK / Rob wrote: > W00ps forgot to update subject of my email, > > WiNK / Rob wrote: > > Hi , > > > > I think i might have hit a bug, posted on forums.mysql.com but > > apparently nobody really reads that i think. > > > > my table: > > > > CREATE TABLE `clog` ( `cID` int(20) NOT NULL auto_increment, `lID` > > int(10) default NULL, `ip` int(10) default NULL, `timestamp` int(11) > > NOT NULL, PRIMARY KEY (`clickID`) ) ENGINE=MYISAM; or i use ARCHIVE > > > > I have a bit of a problem that occurs only when i change my really > > simple log table to the archive engine. The replication breaks. Any > > thoughts? The row number of the error is variable. When the table is > > set to myisam, the replication does not break on duplicate key, and > > runs as expected. > > > > Can't write; duplicate key in table 'clog'' on query. > > > > Is it possible that due to the stress of the benchmark, my slave > > cannot compute the next cID or creates a duplicate (cId is the only > > variable that changes, on bench query)? > > > > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Really strange index/speed issues
Baron Schwartz wrote: Chris Hemmings wrote: Chris Hemmings wrote: Baron Schwartz wrote: Hi Chris, Chris Hemmings wrote: Dan Buettner wrote: Chris, a couple of thoughts - First, your index on the section is doing you no good (at this time) since all the values are the same. You may already know that, but thought I'd mention it. Second, my theory on why query #1 is faster - if all your prices range from 1 up, and you're querying for prices greater than 0, then MySQL can just return the first 30 rows after sorting them. The second query, where you are looking for prices greater than 1, MySQL has to sort and then examine a number of rows until it finds enough matching rows (price > 1) to satisfy your query. This likely takes a little bit of time. How many rows do you have with price = 1? It would have to scan over that many before it could start satisfying your query, if you think about it. HTH, Dan On 9/10/07, Chris Hemmings <[EMAIL PROTECTED]> wrote: Hello, I have a table, currently holding 128,978 rows... In this table, I have a section column (int) and a price column (int). Every row has a section of 1 currently, every row has a price, ranging from 1 to 10,000. I have an index on both columns separately. Have a look at these two queries, can someone tell me why there is such a difference in speed of execution? (Note difference in price qualifier) SELECT * FROM `table1` WHERE price >0 AND section =1 ORDER BY price LIMIT 0 , 30 Showing rows 0 - 29 (128,978 total, Query took 0.9462 sec) Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734 Using where; Using filesort SELECT * FROM `table1` WHERE price >1 AND section =1 ORDER BY price LIMIT 0 , 30 Showing rows 0 - 29 (128,949 total, Query took 0.0008 sec) Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734 Using where; Using filesort Other info: Query cacheing = off MySQL version = 5.0.32 OS = Debian Sarge Sure, the second query returns 29 fewer records than the first, but should that make the difference in time? Hope you can shed some light onto this :-) Ta! Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Thanks Dan, I've got you on the section index... I was going to use that later, when I get somre real data in there. Anyway, I agree with your logic, but, the inverse is happening. The one where it has to actually exclude some rows (because price>1) is actually faster. Thats really why this has me baffled, I would presume that the price>1 would be slower as it does have to filter rows out first. There's an easy way to find out: FLUSH STATUS, run the query, SHOW STATUS LIKE 'handler%'. Do this on an otherwise quiet server if possible.Or use MySQL Query Profiler -- it does a lot of math for you :-)Baron Thanks Baron! I think you have hit upon something, doing what you said on a 'silent' server, I get the following: SELECT * FROM `table1` WHERE price >1 AND section =1 ORDER BY price LIMIT 0 , 30; 30 rows in set (0.00 sec) mysql> SHOW STATUS LIKE 'handler%'; ++---+ | Variable_name | Value | ++---+ | Handler_commit | 0 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare| 0 | | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_next | 29| | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 14| ++---+ 15 rows in set (0.00 sec) SELECT * FROM `table1` WHERE price >0 AND section =1 ORDER BY price LIMIT 0 , 30; 30 rows in set (0.95 sec) mysql> SHOW STATUS LIKE 'handler%'; +++ | Variable_name | Value | +++ | Handler_commit | 0 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare| 0 | | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_next | 128978 | | Handler_read_prev | 0 | | Handler_read_rnd | 30 | | Handler_read_rnd_next | 0 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 14 | ++
Re: Really strange index/speed issues
The results of an EXPLAIN have a lot to do with the data which is actually on the system. In this case, it seems to hinge on the distribution of your 'price' attribute.. how many records on your system? and what is the general distribution of the price attribute? (how many distinct values) On 9/11/07, Chris Hemmings <[EMAIL PROTECTED]> wrote: > Chris Hemmings wrote: > > Baron Schwartz wrote: > >> Hi Chris, > >> > >> Chris Hemmings wrote: > >>> Dan Buettner wrote: > Chris, a couple of thoughts - > > First, your index on the section is doing you no good (at this time) > since > all the values are the same. You may already know that, but thought > I'd > mention it. > > Second, my theory on why query #1 is faster - if all your prices > range from > 1 up, and you're querying for prices greater than 0, then MySQL can > just > return the first 30 rows after sorting them. > > The second query, where you are looking for prices greater than 1, > MySQL has > to sort and then examine a number of rows until it finds enough > matching > rows (price > 1) to satisfy your query. This likely takes a little > bit of > time. How many rows do you have with price = 1? It would have to > scan over > that many before it could start satisfying your query, if you think > about > it. > > HTH, > Dan > > > > On 9/10/07, Chris Hemmings <[EMAIL PROTECTED]> wrote: > > Hello, > > > > I have a table, currently holding 128,978 rows... In this table, I > > have a > > section column (int) and a price column (int). Every row has a > > section of > > 1 > > currently, every row has a price, ranging from 1 to 10,000. > > > > I have an index on both columns separately. > > > > Have a look at these two queries, can someone tell me why there is > > such a > > difference in speed of execution? (Note difference in price > > qualifier) > > > > > > > > SELECT * > > FROM `table1` > > WHERE price >0 > > AND section =1 > > ORDER BY price > > LIMIT 0 , 30 > > > > Showing rows 0 - 29 (128,978 total, Query took 0.9462 sec) > > > > Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734 > > Using > > where; Using filesort > > > > > > > > SELECT * > > FROM `table1` > > WHERE price >1 > > AND section =1 > > ORDER BY price > > LIMIT 0 , 30 > > > > > > Showing rows 0 - 29 (128,949 total, Query took 0.0008 sec) > > > > Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734 > > Using > > where; Using filesort > > > > > > > > Other info: > > > > Query cacheing = off > > MySQL version = 5.0.32 > > OS = Debian Sarge > > > > Sure, the second query returns 29 fewer records than the first, but > > should > > that make the difference in time? > > > > Hope you can shed some light onto this :-) > > > > Ta! > > > > Chris. > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > >>> > >>> Thanks Dan, > >>> > >>> I've got you on the section index... I was going to use that later, > >>> when I get somre real data in there. > >>> > >>> Anyway, I agree with your logic, but, the inverse is happening. The > >>> one where it has to actually exclude some rows (because price>1) is > >>> actually faster. Thats really why this has me baffled, I would > >>> presume that the price>1 would be slower as it does have to filter > >>> rows out first. > >> > >> There's an easy way to find out: FLUSH STATUS, run the query, SHOW > >> STATUS LIKE 'handler%'. Do this on an otherwise quiet server if > >> possible.Or use MySQL Query Profiler -- it does a lot of math for you > >> :-)Baron > >> > >> > >> > >> > >> > >> > > > > Thanks Baron! > > > > I think you have hit upon something, doing what you said on a 'silent' > > server, I get the following: > > > > SELECT * FROM `table1` WHERE price >1 AND section =1 ORDER BY price > > LIMIT 0 , 30; > > > > 30 rows in set (0.00 sec) > > > > mysql> SHOW STATUS LIKE 'handler%'; > > ++---+ > > | Variable_name | Value | > > ++---+ > > | Handler_commit | 0 | > > | Handler_delete | 0 | > > | Handler_discover | 0 | > > | Handler_prepare| 0 | > > | Handler_read_first | 0 | > > | Handler_read_key | 1 | > > | Handler_read_next | 29| > > | Handler_read_prev
Re: Really strange index/speed issues
Chris Hemmings wrote: Chris Hemmings wrote: Baron Schwartz wrote: Hi Chris, Chris Hemmings wrote: Dan Buettner wrote: Chris, a couple of thoughts - First, your index on the section is doing you no good (at this time) since all the values are the same. You may already know that, but thought I'd mention it. Second, my theory on why query #1 is faster - if all your prices range from 1 up, and you're querying for prices greater than 0, then MySQL can just return the first 30 rows after sorting them. The second query, where you are looking for prices greater than 1, MySQL has to sort and then examine a number of rows until it finds enough matching rows (price > 1) to satisfy your query. This likely takes a little bit of time. How many rows do you have with price = 1? It would have to scan over that many before it could start satisfying your query, if you think about it. HTH, Dan On 9/10/07, Chris Hemmings <[EMAIL PROTECTED]> wrote: Hello, I have a table, currently holding 128,978 rows... In this table, I have a section column (int) and a price column (int). Every row has a section of 1 currently, every row has a price, ranging from 1 to 10,000. I have an index on both columns separately. Have a look at these two queries, can someone tell me why there is such a difference in speed of execution? (Note difference in price qualifier) SELECT * FROM `table1` WHERE price >0 AND section =1 ORDER BY price LIMIT 0 , 30 Showing rows 0 - 29 (128,978 total, Query took 0.9462 sec) Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734 Using where; Using filesort SELECT * FROM `table1` WHERE price >1 AND section =1 ORDER BY price LIMIT 0 , 30 Showing rows 0 - 29 (128,949 total, Query took 0.0008 sec) Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734 Using where; Using filesort Other info: Query cacheing = off MySQL version = 5.0.32 OS = Debian Sarge Sure, the second query returns 29 fewer records than the first, but should that make the difference in time? Hope you can shed some light onto this :-) Ta! Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Thanks Dan, I've got you on the section index... I was going to use that later, when I get somre real data in there. Anyway, I agree with your logic, but, the inverse is happening. The one where it has to actually exclude some rows (because price>1) is actually faster. Thats really why this has me baffled, I would presume that the price>1 would be slower as it does have to filter rows out first. There's an easy way to find out: FLUSH STATUS, run the query, SHOW STATUS LIKE 'handler%'. Do this on an otherwise quiet server if possible.Or use MySQL Query Profiler -- it does a lot of math for you :-)Baron Thanks Baron! I think you have hit upon something, doing what you said on a 'silent' server, I get the following: SELECT * FROM `table1` WHERE price >1 AND section =1 ORDER BY price LIMIT 0 , 30; 30 rows in set (0.00 sec) mysql> SHOW STATUS LIKE 'handler%'; ++---+ | Variable_name | Value | ++---+ | Handler_commit | 0 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare| 0 | | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_next | 29| | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 14| ++---+ 15 rows in set (0.00 sec) SELECT * FROM `table1` WHERE price >0 AND section =1 ORDER BY price LIMIT 0 , 30; 30 rows in set (0.95 sec) mysql> SHOW STATUS LIKE 'handler%'; +++ | Variable_name | Value | +++ | Handler_commit | 0 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare| 0 | | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_next | 128978 | | Handler_read_prev | 0 | | Handler_read_rnd | 30 | | Handler_read_rnd_next | 0 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 14 | +++ 15 rows in set (0.01
Re: Really strange index/speed issues
Chris Hemmings wrote: Baron Schwartz wrote: Hi Chris, Chris Hemmings wrote: Dan Buettner wrote: Chris, a couple of thoughts - First, your index on the section is doing you no good (at this time) since all the values are the same. You may already know that, but thought I'd mention it. Second, my theory on why query #1 is faster - if all your prices range from 1 up, and you're querying for prices greater than 0, then MySQL can just return the first 30 rows after sorting them. The second query, where you are looking for prices greater than 1, MySQL has to sort and then examine a number of rows until it finds enough matching rows (price > 1) to satisfy your query. This likely takes a little bit of time. How many rows do you have with price = 1? It would have to scan over that many before it could start satisfying your query, if you think about it. HTH, Dan On 9/10/07, Chris Hemmings <[EMAIL PROTECTED]> wrote: Hello, I have a table, currently holding 128,978 rows... In this table, I have a section column (int) and a price column (int). Every row has a section of 1 currently, every row has a price, ranging from 1 to 10,000. I have an index on both columns separately. Have a look at these two queries, can someone tell me why there is such a difference in speed of execution? (Note difference in price qualifier) SELECT * FROM `table1` WHERE price >0 AND section =1 ORDER BY price LIMIT 0 , 30 Showing rows 0 - 29 (128,978 total, Query took 0.9462 sec) Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734 Using where; Using filesort SELECT * FROM `table1` WHERE price >1 AND section =1 ORDER BY price LIMIT 0 , 30 Showing rows 0 - 29 (128,949 total, Query took 0.0008 sec) Explain output: 1 SIMPLE table1 ALL section,price NULL NULL NULL 96734 Using where; Using filesort Other info: Query cacheing = off MySQL version = 5.0.32 OS = Debian Sarge Sure, the second query returns 29 fewer records than the first, but should that make the difference in time? Hope you can shed some light onto this :-) Ta! Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Thanks Dan, I've got you on the section index... I was going to use that later, when I get somre real data in there. Anyway, I agree with your logic, but, the inverse is happening. The one where it has to actually exclude some rows (because price>1) is actually faster. Thats really why this has me baffled, I would presume that the price>1 would be slower as it does have to filter rows out first. There's an easy way to find out: FLUSH STATUS, run the query, SHOW STATUS LIKE 'handler%'. Do this on an otherwise quiet server if possible.Or use MySQL Query Profiler -- it does a lot of math for you :-)Baron Thanks Baron! I think you have hit upon something, doing what you said on a 'silent' server, I get the following: SELECT * FROM `table1` WHERE price >1 AND section =1 ORDER BY price LIMIT 0 , 30; 30 rows in set (0.00 sec) mysql> SHOW STATUS LIKE 'handler%'; ++---+ | Variable_name | Value | ++---+ | Handler_commit | 0 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare| 0 | | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_next | 29| | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_next | 0 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 14| ++---+ 15 rows in set (0.00 sec) SELECT * FROM `table1` WHERE price >0 AND section =1 ORDER BY price LIMIT 0 , 30; 30 rows in set (0.95 sec) mysql> SHOW STATUS LIKE 'handler%'; +++ | Variable_name | Value | +++ | Handler_commit | 0 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_prepare| 0 | | Handler_read_first | 0 | | Handler_read_key | 1 | | Handler_read_next | 128978 | | Handler_read_prev | 0 | | Handler_read_rnd | 30 | | Handler_read_rnd_next | 0 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_update | 0 | | Handler_write | 14 | +++ 15 rows in set (0.01 sec) So, the slower query
Re: Implement a logging table; avoiding conflicting inserts
Fan, Wellington wrote: > > Given: MySQL 4.0.12, I need to implement a pageview log with a resolution of 1 day. If you want to brute-force it, I think I would go this route: create table hits ( day date not null primary key, hitcount int unsigned not null, ); insert ignore into hits(day, hitcount) values (current_date, 0); update hits set hitcount = hitcount + 1 where day = current_date; No transactions. Your application logic can perhaps be smart and avoid the first query. But the transactional method with locking in share mode is probably going to have a lot more overhead and lower concurrency than my suggestion. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Implement a logging table; avoiding conflicting inserts
> > Given: MySQL 4.0.12, I need to implement a pageview log with a
> > resolution of 1 day.
..
>
> Would the "REPLACE" method work?
>
> David
Hmmm...as I read the docs, the "LOCK IN SHARED MODE" seemed to be the
real key to this.
I created a test script and ran:
$ ab -n100 -c100 localhost/hits.php
Where hits.php looks like:
http://dev.mysql.com/doc/refman/4.1/en/innodb-next-key-locking.html
$sql = "SELECT views FROM pageviews WHERE
date='".mysql_escape_string($date)."' AND
url='".mysql_escape_string($url)."' LOCK IN SHARE MODE";
/**
* If NO records are returned, we need to INSERT with our first pageview
*/
$rs = mysql_query($sql,$dblink);
if( mysql_num_rows($rs) == 0 ) {
$sql = "INSERT INTO pageviews SET views=1,
date='".mysql_escape_string($date)."',
url='".mysql_escape_string($url)."'";
} else {
$sql = "REPLACE INTO pageviews SET
views=".(intval(mysql_result($rs,0,'views'))+1).",
date='".mysql_escape_string($date)."',
url='".mysql_escape_string($url)."'";
//$sql = "UPDATE pageviews SET views=views+1 WHERE
date='".mysql_escape_string($date)."' AND
url='".mysql_escape_string($url)."'";
}
echo $sql;
$rs = mysql_query($sql,$dblink);
/**
* Barely error-checking...
*/
if ( mysql_affected_rows($dblink) != 1 ) {
$err = mysql_error($dblink);
error_log ($err."\n", 3, '/tmp/errors.log');
}
mysql_query('COMMIT',$dblink);
?>
As you can see, I tried *both* the "REPLACE INTO" and "UPDATE" queries
and received *very* strange results. I sum(views) and get roughly 115
views!! I expected 100 or less, but maybe I do NOT understand 'ab'.
So, I added this:
error_log('foo'."\n", 3, '/tmp/errors.log');
exit;
At the top of my script, and ran:
$ ab -n100 -c100 localhost/hits.php
Again, expecting 100 'foo's -- I get roughly 160!
What the hell? I guess I really *don't* understand ab...
Thoughts?
--
Wellington
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with ORDER BY
Thing 1: your auto_increment key MUST be your primary key. Thing 2: the timestamp field will be updated with the current epochal timestamp which only increments every second.. as you have a timestamp field as you primary (and therefore unique) key, you will never be able to perform more than one INSERT/UPDATE within the span of any given second. you need to redign the table, I'm afraid. On 9/11/07, WiNK / Rob <[EMAIL PROTECTED]> wrote: > Hi , > > I think i might have hit a bug, posted on forums.mysql.com but > apparently nobody really reads that i think. > > my table: > > CREATE TABLE `clog` ( `cID` int(20) NOT NULL auto_increment, `lID` > int(10) default NULL, `ip` int(10) default NULL, `timestamp` int(11) NOT > NULL, PRIMARY KEY (`clickID`) ) ENGINE=MYISAM; or i use ARCHIVE > > I have a bit of a problem that occurs only when i change my really > simple log table to the archive engine. The replication breaks. Any > thoughts? The row number of the error is variable. When the table is set > to myisam, the replication does not break on duplicate key, and runs as > expected. > > Can't write; duplicate key in table 'clog'' on query. > > Is it possible that due to the stress of the benchmark, my slave cannot > compute the next cID or creates a duplicate (cId is the only variable > that changes, on bench query)? > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
archive engine potential bug
W00ps forgot to update subject of my email, WiNK / Rob wrote: Hi , I think i might have hit a bug, posted on forums.mysql.com but apparently nobody really reads that i think. my table: CREATE TABLE `clog` ( `cID` int(20) NOT NULL auto_increment, `lID` int(10) default NULL, `ip` int(10) default NULL, `timestamp` int(11) NOT NULL, PRIMARY KEY (`clickID`) ) ENGINE=MYISAM; or i use ARCHIVE I have a bit of a problem that occurs only when i change my really simple log table to the archive engine. The replication breaks. Any thoughts? The row number of the error is variable. When the table is set to myisam, the replication does not break on duplicate key, and runs as expected. Can't write; duplicate key in table 'clog'' on query. Is it possible that due to the stress of the benchmark, my slave cannot compute the next cID or creates a duplicate (cId is the only variable that changes, on bench query)? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with ORDER BY
Hi , I think i might have hit a bug, posted on forums.mysql.com but apparently nobody really reads that i think. my table: CREATE TABLE `clog` ( `cID` int(20) NOT NULL auto_increment, `lID` int(10) default NULL, `ip` int(10) default NULL, `timestamp` int(11) NOT NULL, PRIMARY KEY (`clickID`) ) ENGINE=MYISAM; or i use ARCHIVE I have a bit of a problem that occurs only when i change my really simple log table to the archive engine. The replication breaks. Any thoughts? The row number of the error is variable. When the table is set to myisam, the replication does not break on duplicate key, and runs as expected. Can't write; duplicate key in table 'clog'' on query. Is it possible that due to the stress of the benchmark, my slave cannot compute the next cID or creates a duplicate (cId is the only variable that changes, on bench query)? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Implement a logging table; avoiding conflicting inserts
> Given: MySQL 4.0.12, I need to implement a pageview log with a > resolution of 1 day. > > I propose this table: > > CREATE TABLE `pageviews` ( > `id` int(11) NOT NULL auto_increment, > `date` date NOT NULL default '-00-00', > `url` char(120) NOT NULL default '', > `views` mediumint(9) NOT NULL default '0', > PRIMARY KEY (`id`), > UNIQUE KEY `date` (`date`,`url`), > KEY `url` (`url`) >) TYPE=InnoDB;>> > > > So that an update will look like: > > UPDATE pageviews SET views=views+1 WHERE date='' AND > url='' > > Of course I need to INSERT the record if one does not match my WHERE. > This would be easy if I had 4.1 -- "INSERT ... ON DUPLICATE KEY UPDATE", > I think -- but I do not. Would the "REPLACE" method work? David
help with ORDER BY
Hi all!
I need some help with ORDER BY in the following example. I want to order by
selected category, then by subcategories of the selected category, then by
categories with the same parent_id of the selected category, then by random
if possible, or random within the categories if possible, but the first
order by part is not working because is not returning products of the
selected category first, instead returns products from a top category
(parent_id = 0).
table categories
id | parent_id | category
- where parent_id is 0 for top categories.
table products
id | id_category | product
SELECT
products.id,
(SELECT
CASE WHEN CHAR_LENGTH(products.product) > 40 THEN
CONCAT(SUBSTRING(products.product,1,37),'...')
ELSE products.product END) AS product,
(SELECT
CASE WHEN CHAR_LENGTH(products.description) > 70 THEN
CONCAT(SUBSTRING(products.description,1,67),'...')
ELSE products.description END) AS description
FROM
products
WHERE
products.id_stock = 1
ORDER BY
products.id_category IN (".$id_selected_category."
, (SELECT categories.id
FROM categories
WHERE categories.parent_id = ".$id_selected_category.")
, (SELECT categories.id
FROM categories
WHERE categories.parent_id = ".$parent_id."))
, RAND() LIMIT 11
(the php vars have correct values)
Please apologise my bad English.
Thanks in advance.
Pedro.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Big SELECT: ordering results by where matches are found
At 13:34 -0400 10/9/07, Baron Schwartz wrote: Looks like you've found the solution you need. The only other suggestion I have is to use UNION ALL if you don't need to eliminate duplicate rows in the UNION, because there's some overhead for checking for them. Hi Baron Thanks for this, and I did try it, but the difference in time taken to execute the query was negligible (I tested it multiple times) - it was around 0.02 seconds whichever way I did it, and when I used EXPLAIN, the results were identical except for one detail: The number of rows in the first row of the EXPLAIN result was lower with plain UNION than if I used UNION ALL. As far as I can tell from my relatively limited experience with all this, the first row refers to my outer 'wrapper' select from the derived table (the table in the first row is given as '' and the Extra column shows 'Using temporary'). For a given query, with UNION ALL that has 45 rows, with UNION it's 31. So I guess I'll stick to plain UNION. As far as my desire to cope with multiple search terms is concerned, I realise now that fulltext handles that anyway! So I've changed the few non-numeric fields that weren't indexed that way (fore, sur and topic) to fulltext and bingo! Not only that, but it all happens fully FOUR TIMES as quickly! So many thanks, Baron - mainly due to you, yesterday was a very good MySQL day for me. It's not often I get two 'lightbulb moments' on the same day! -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ Good people will do good things, and bad people will do bad things. But for good people to do bad things - that takes religion. -- Steven Weinberg, physicist and Nobel Laureate -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What should be a simple query...
Try this: SELECT RMAs.rma_id FROM RMAs, rma_line_items WHERE TO_DAYS(date_settled) = 733274 AND RMAs.rma_id = rma_line_items.rma_id GROUP BY RMAs.rma_id HAVING COUNT(*) > 1 On Sep 10, 2007, at 11:36 PM, Mike Mannakee wrote: I have two tables, one called RMAs and the other called rma_line_items. The first one has the general details of the RMA (Return Merchandise Authorization) , the second holds the details of each item being returned. What I want is a listing of the RMA ids (which are unique in the RMAs table) which have more than one line item in the corresponding table. So I'm using: SELECT * FROM RMAs, rma_line_items WHERE TO_DAYS(date_settled) = 733274 AND RMAs.rma_id IN (SELECT rma_id FROM rma_line_items HAVING COUNT(*) > 1) and it's netting me nothing, which I know is not true. So to investigate I just ran the subselect: SELECT rma_id FROM rma_line_items HAVING COUNT(*) > 1 and I find it's not giving me but one row, the first one to match having more than one item. But there are plenty more RMAs that have more than one entry in the rma_line_items table and I need to get at them. What am I doing wrong? Any ideas? Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
