Re: optemizer for mySQL!
Googling for mysql profiler brings a link: http://amr.activechannel.net/myprofiler.htm Well... that looks like something really good. I have a question for all of list subscribers: has anyone use this tool? Does it really do what it pretends to do? On 5/15/06, sheeri kritzer [EMAIL PROTECTED] wrote: I'm not sure what you mean by profiler -- where are you getting your information from? It's not referenced in the article I sent. If you mean checking to see if indexes are being used during a query, check out the EXPLAIN syntax. http://dev.mysql.com/doc/refman/5.0/en/explain.html -Sheeri On 5/14/06, Jim [EMAIL PROTECTED] wrote: Thanks Sheeri, How do you run the profiler and what does it do? Have recently had to add some indexes to various tables to improve performance so does not seen to be running automatically. -Original Message- From: sheeri kritzer [mailto:[EMAIL PROTECTED] Sent: Saturday, 13 May 2006 2:05 AM To: Jim Cc: mysql@lists.mysql.com Subject: Re: optemizer for mySQL! http://www.devshed.com/c/a/MySQL/A-Technical-Tour-of-MySQL/ The MySQL server has it built in. -Sheeri On 5/8/06, Jim [EMAIL PROTECTED] wrote: Hi All, Didn't know there was an optemizer for mySQL. Where can I get it from? Thanks Jim Best regards, Jim Clark Project Manager Multilink Systems Ph: 03 9425 9400 Fax: 03 9425 9811 -- 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] -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tuning a Server with 10,000 databases
On 5/9/06, Alex [EMAIL PROTECTED] wrote: That's what I actually did now. We have got the databases start with usernames + number appended situation here so i patched sql_show.cc code to only do acl checks on databases starting with the username. Still not optimal but cuts down a show databases on a server with 60.000 databases from 15 seconds to 0.14 seconds which is ok. Same here - my database names start with username+underscore, so query now takes 0.05 instead of 6 secs (I have about 15000 DBs). -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tuning a Server with 10,000 databases
That's not a bug, that's a missing feature (or we can call it unoptimal behaviour). I think that having 1 databases and 1 users on a single mysqld and doing show databases query often isn't what developers see as intended usage for MySQL. :) Here's what happens when you do show databases query: 1) mysqld gets a list of subdirectories of mysql data dir (usually /var/lib/mysql). Each directory is a database 2) It loops through all entries, and adds an entry to result set if: a) an user has global show databases privilege b) an user has been granted access for this database Part b) is what actually takes time. For each entry the server first checks ACL cache. It's a hash table and lookups against it are very fast. But for show databases query most lookups will return a miss, and a miss means full scan of the whole acl db. So for 1 databases it scans table with 1 rows 1 times, which means 10 million scanned records. That's why it's slow. As a workaround, if one has some rules regarding which user can see which DB (for example, if usernames and database names start with same substring), they can add this check to the code (so scans will be avoided for most entries). That's still far from optimal, but at least show databases will take dozens of milliseconds instead of seconds. On 5/6/06, sheeri kritzer [EMAIL PROTECTED] wrote: Perhaps it's time to file a bug report, then? -Sheeri On 5/3/06, Alex [EMAIL PROTECTED] wrote: This problem is indeed not related to OS / Hardware Problems. Take a look at this thread: http://lists.mysql.com/mysql/197542 Read the part about show databases as root vs standard user + observed file system activity. -- 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] -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimising for many rows and returned records (de-coupling query time to record set size for range queries)
On 4/23/06, Nick Hill [EMAIL PROTECTED] wrote: 1) I can improve performance by a factor of 2-2.5 by changing the double lat/lon to an integer then selecting on an integer. 2) I have concluded that for each 10 fold increase in the number of records, select queries take twice as long. For each doubling of the number of returned records, there is a sqrt(2) increase in select query time. I've noticed a couple things. 1) Right now you're emulating spatial index. 2) In future, you're going to emulate partitioning. Why do you think that doing this stuff manually is better than using builtin capabilities? As the database grows, it would likely improve database performance by splitting an individual table into several thousand tables using the file system directory btree algorithm to effectively pre-select the data before the query is handled to the MySQL engine. This is not a neat solution. A much better way would be to improve the mysql index performance on very large numbers of records. Selects against a table use b-trees too. Splitting data into lot of tables won't help with selects at all (well, it may help on scans with concurrent large data sets if data will be spread across different physical drives, but not with regular range lookups that you're doing). It will only help with inserts. Given that there is such a strong relationship between the number of records returned, and query time, I conclude that the whole index tree is matched for every given number of root x records returned. If all records we are matching are under a single node or under a small number of nodes in the index tree, perhaps there is some way of telling the database engine to ignore the rest of the index tree. What is a 'root record'? Are you speaking about internal representation of b-tree? Could this work, or am I misunderstanding how the index tree works? Are there existing optimisations which can de-couple the relationship between number of records and query time where the records I am selecting are within a small range? For studying select query performance issues it's better think about index as simply about a sorted array with random-access, where each random access costs O(lgN) and accesses to adjanced data cost O(1). If your points are spread uniformly in space, cost of select query you've shown is O(N*lgN) -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Execution time of show databases increases when fewer databasesare visible
On 4/21/06, Nico Sabbi [EMAIL PROTECTED] wrote: Is this behaviour due to a bug or maybe my mysql tables are messed up? I'm not sure if we can call it a bug. But spending 8 seconds (and using 100% CPU) for a simple SHOW DATABASES query is a bit too much, and should be fixed. mysqld first reads directories name from its data directory, then loops over all names and does the following check: if (thd-master_access (DB_ACLS | SHOW_DB_ACL) || acl_get(thd-host, thd-ip, thd-priv_user, file_name,0) || (grant_option !check_grant_db(thd, file_name))) If a query runs faster when user has access to more DBs, then the problem most likely lies within check_grant_db function. But it all needs more careful investigation. -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Execution time of show databases increases when fewer databasesare visible
On 4/21/06, Nico Sabbi [EMAIL PROTECTED] wrote: the more databases a user has the less time it takes to execute show databases and vice versa: If no rows in `show databases` output appear because some user has some table/columns privs but no db privs for a database in question, then I think that check_grant_db I mentioned in previous email may be safely hacked away. -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie optimization question
On 4/20/06, Adam Wolff [EMAIL PROTECTED] wrote: How can I optimize the case where I filter on one key but sort on another? This is fast: SELECT * FROM contacts WHERE fullname LIKE j% ORDER BY fullname LIMIT 10; But this is slow: SELECT * FROM contacts WHERE fullname LIKE j% ORDER BY email LIMIT 10; EXPLAIN tells me that the optimizer is using filesort for the second but not the first (which makes sense.) Such things are pretty hard to optimize. If you have large number of rows, forcing usage of (email) index for this query might help, as engine will scan as many rows as required for satisfying limit. Also having (email, fullname(1)) index might save a few cycles. * Question 2: Why does introducing an extra WHERE clause make things slower? If I do this: SELECT * FROM contacts WHERE fullname LIKE j% AND user_id=1 ORDER BY fullname LIMIT 10; Probably because the engine is not using (fullname) index for this query - optimizer sees 'const' ref for user_id, retrieves all rows that have user_id=1 and then filters/sorts them all. -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert speed on table with 7M rows but small row size.
On 4/8/06, Kevin Burton [EMAIL PROTECTED] wrote: Is there anyway I can tune MySQL to improve this operation? There's a fundamental problem with B-trees - inserts in a big B-tree can be very slow, cause it may require tree rebalancing. One workaround for this problem is partitioning. MySQL 5.1 will have built-in partitioning support, but the idea itself is very simple, and you can achieve the same effect by manually spreading data among tables. -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tuning a Server with 10,000 databases
Hi! I'm facing somewhat similar problems - I've stuffed about 10 thousand databases, most of them are pretty small and not very active, and some of the queries are extremely slow. I've tried different filesystems, and currently I'm using bleeding-edge reiser4 filesystem. It handles large directories very well, commands like `find /var/lib/mysql|wc -l` complete under a second, but when for example authenticated user does SHOW DATABASES query, it takes about 15 seconds to complete. I'm pretty sure it's not OS or filesystem issue, cause during this query mysqld process consumes all available CPU time in user-land (if it was FS or OS issue, it would use kernel CPU time). I've tried pretty much anything I can think of, but can't seem to solve the problem. Splitting the databases between several mysqld instances will help of course, but it will be pretty tough to administer. I'm going to complile mysqld from sources with profiling enabled, then gather profiling info, and submit it as a bug. Seems that's the only thing that may help. On 4/1/06, Gary Huntress [EMAIL PROTECTED] wrote: I have been offering free database hosting for over 4 years and I've been doing it on a shoestring.My last MySQL server was a generic 1GHz system with 256MB RAM running Redhat 9. The performance was surprisingly good because the query loads were not typically high. One persistent problem was the initial connection times. On that old system if I had less than approx 10,000 separate databases then the connection times were fast, and on the order of 1 second or so. If I had more than 10,000 databases this dramatically changed the connection times to well over 15 seconds or more. I always attributed this connection lag to a problem with the filesystem and the large number of directories. The old server had RH9 and ext3 with no htree support which I was told could help with this problem. I recently bought a new 2.4 GHz system with 1GB of RAM and installed Fedora 4 with ext3 and htree support. All new hardware, faster drives, more RAM and updated software. I thought I was golden!Well, I have 14,000 databases on this new system and it is as slow as the old 1GHz system. The tuning articles I've read, and the sample my-*.cnf files that ship with the tarball appear to apply to the more typical installation of a single huge database rather than thousands of individual dbs. Can anyone offer any suggestions? Thanks, Gary Huntress -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tuning a Server with 10,000 databases
On 4/1/06, Greg Whalin [EMAIL PROTECTED] wrote: Not necessarily sure this is the problem. But if it is, it could be solved by switching to a different filesystem. Or, if you are using ext3fs, you could try enabling dir_index on the filesystem (tune2fs -O dir_index ... man tune2fs), which could give you a boost in performance in a large dir (this could take a long time to complete). You may also want to up your table cache so that mysql can keep more of your commonly used tables open? FWIW, I've experimented heavily with FS options, and found out that dir_index on ext3 doesn't help at all, it actually harms performance. 'noatime' and 'nodiratime' options do help a little. Also, 14000 subdirectories is not something that will cause 15 seconds delay - those 14000 subdirectories will always live in OS dentry cache anyway. -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Avoiding disk writes during CREATE TEMPORARY
Make sure you don't have dynamic-width rows. Use char instead of varchar. On 2/17/06, Eamon Daly [EMAIL PROTECTED] wrote: Hi, all. I have a machine with lots of memory, and I'm trying to avoid the disk entirely for some of our larger reports. I was under the impression that CREATE TEMPORARY would create in-memory tables and only write to disk if tmp_table_size is reached, but that doesn't seem to be the case: I've set tmp_table_size to 1G and this particular run is only creating tables under 64M. Using TYPE = MEMORY does what I want it to and only creates .frm files in /tmp: CREATE TEMPORARY TABLE revenue_data TYPE = MEMORY SELECT * FROM data ...but as we do have reports that might exceed 1G in size, and the MEMORY storage type never converts to on-disk tables, that's really not a good solution. How does MySQL determine whether a table is written to memory or to disk, and is there a way to force the former? Eamon Daly -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MSSQL-MySQL Compatibility Question
Yeah, it's perfectly ok to run both on the same machine at the same time, though with MS SQL you're limited to Microsoft OS. -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to increase query speed ?
Adding compound (hash, years) index (or even better unique index if it fits in your business logic) in both tables should speed up things. -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Should I buy commercial license?
Hi! There'a a great article about MySQL licensing: http://entwickler.com/itr/online_artikel/psecom,id,618,nodeid,114.html Basically, you don't need to purchase license unless you distribute your software. (And even if you do, it doesn't automatically mean that you should buy commercial license). -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Memory tables and INNODB have different query plans for GROUP BY with btree?
Hi Kevin, I also observed differences between query plans on InnoDB and MyISAM tables. I had a complex query, which had two possible plans, first plan included const and ref joins on bigger tables then joins on smaller tables, and second one was to do range scan of smaller table and then join all larger tables. Second plan was 20 times faster, and the only way I managed to force query optimizer to use range scan is converting tables to InnoDB. (I couldn't use straight join and force index cause I had no access to scripts that was generating queries). Regarding your example, you won't get your index used for group-by if you're selecting *. Your select against memory table is using key:TEST to skip sorting of data (group by means implicit oreder by, and it retrieves row using this key in already sorted order). When it's in-memory table, random reads are fast. Your select against disk table isn't using index cause it's faster to retrieve all rows by scanning table and then sort them. If the column you're using for group by was the first part of primary key in innodb table, it would also use it for retrieval (and that's the fastest possible way of doing this query on innodb). On 10/4/05, Kevin Burton [EMAIL PROTECTED] wrote: I was benchmarking a few of my queries tonight and I noticed that two queries had different query plans based on table type. Here's the broken query: mysql EXPLAIN SELECT * FROM FOO_LINK_MEMORY_TEST GROUP BY TARGET_NODE_ID\G *** 1. row *** id: 1 select_type: SIMPLE table: FOO_LINK_MEMORY_TEST type: index possible_keys: NULL key: TEST key_len: 18 ref: NULL rows: 1000 Extra: 1 row in set (0.00 sec) Note no index is used. mysql EXPLAIN SELECT * FROM FOO_LINK_INNODB_TEST GROUP BY TARGET_NODE_ID\G *** 1. row *** id: 1 select_type: SIMPLE table: FOO_LINK_INNODB_TEST type: index possible_keys: NULL key: TARGET_NODE_ID key_len: 9 ref: NULL rows: 1011 Extra: 1 row in set (0.00 sec) ... and here it uses TARGET_NODE_ID. The only difference is that I created an INNODB table and inserted the columns in the memory table into the INNODB table. I'm trying to follow the instructions here: http://dev.mysql.com/doc/mysql/en/loose-index-scan.html To get decent GROUP BY performance. Is this a bug? Is there a workaround? Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://www.feedblog.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Memory tables and INNODB have different query plans for GROUP BY with btree?
Oops, I was obviously wrong about your example, please ignore it. :) -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: config diff: query time went from 70 mins to 20 seconds
On 10/1/05, David Sparks [EMAIL PROTECTED] wrote: So what was the setting below that was tanking mysql performance? I suspect that innodb_log_buffer_size=32M was the culprit. I think that tripling your buffer pool did the job. :) Also I'd revert innodb_additional_mem_pool_size and innodb_log_buffer_size, cause those are allocated only once, and looks like you have enough memory for bigger values. -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Creating HUGE reports
I think there are some required indexes missing in your table structure. If you showed us your real SHOW CREATE TABLE CONTACTS \G and SHOW CREATE TABLE LISTS \G as well as text of SELECTs that kill your mysqld, you'd get better replies. 250k/60k records is not that huge tables really. On 9/25/05, Miguel Cardenas [EMAIL PROTECTED] wrote: Hello... After solving some problems with the code, now am working with the real one database. On my tests was playing with 20,000 records and worked pretty fine sorting by X or Y field and setting some 'where ...' conditions. At this moment the database has 250,000+ records, new insertions are pretty fast, searchs are pretty fast too, but generating reports is almost impossible, the computer may hang one hour and still be working with any output. My tables are something like this: CONTACTS - about 250,000 records -- | id (INT) | contact(CHAR) | active(BOOL) | ... | -- LISTS - about 60,000 records (category for some contacts) | id (INT) | category(CHAR) | Contacts may have a category, although not all of them have one, and some of them may be in more than one, so it is not option to use a 'category' field inside the CONTACTS table... perhaps, intuitively could have a 'SET' type field with categories, but there are not fixed categories and may (will) grow with usage... There are two conditions that should be both acomplished: Cond 1. CONTACTS.active = 1 Select all active contacts Cond 2. LISTS.category = (or ) mycagegory Add/remove contacts from the report that are listed in mycategory If I try a select on both tables by conditioning CONTACTS.id=LISTS.ID and LISTS.category=something the whole system becomes slow and unusable, and still do not wait enough to see how long it will take. This task will be performed once or twice a week, so it must be optimized, and don't know what should be better... 1st option: let the complex select finish (have no idea of how long will take) 2nd option: generate a temporal table with results matching Cond1, and then apply Cond2 to the temporal table. Commonly, the 'category' applies to discard contacts, more than choosing them, so I would simply remove LIST.id records from TEMPORAL table where LIST.id=TEMPORAL.id and then would have the final report. But... what should be better? a single select with complex conditions or a temporal table with multiple parses to append/discard records for every category used (one or various)? Thanks for any comment. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bad MySQL performance with LEFT JOINS in combination with BIGINT(16)Keys
Looks like you don't have index on `lad_id` field in second table. So when you do the first query, mysql first does a full scan of 2nd table, then does eqref lookup for 1st table, which is rather fast operation. But when you do the left join, it scans first table and for each value of id does a scan of second table, which is performance killer. -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Charset problems (utf-8 - latin1)
First, check if actual data in your tables after this conversion is really properly encoded. Launch your mysql command line client, execute SET NAMES latin1; and do some selects on your table data to see if it's readable. If it is, then all you need is to make PHP use latin1 when reading data from db server. One way to do this is to issue 'SET NAMES latin1' query after establishing a connection. There are also some ways to set this as global option for all websites if you can't modify scripts. -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Charset problems (utf-8 - latin1)
Also the good way of doing conversion itself is mysqldump table structure, then import the dump, launch mysql command line utility (or use phpmyadmin for that) and alter encoding on database and all needed tables and columns, then mysqldump the first database using utf8 charset (without dropping tables), and import this dump into second database. -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bind (listen) question
Theres --bind-address mysqld startup option, but I'm afraid it allows specifying only one IP to bind to. One of workarounds for your problem is to make it listening on all IPs, but configure firewall to drop all TCP packets going to port 3306 not on IPs you want. -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: switching from varchar to tinytext
Bytes IIRC. Depending on actual content it can be 127-255 characters. Why do you choose to convert varchar to tinytext? Doesn't look like good idea to me. -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Blew MySQL away by accident
However, I am trying to move my RH 9 system to Centos 4.1 and everything has gone well until I though I could just copy the mysql db into the new server. You can. Just make sure you add old-passwords option to your my.cnf file. -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query-log.html
Trouble is, I dont start mysqld with the --log= option, and yet it still insists on logging. How do I disable this? (My current method is to use --log=/dev/null but I'm sure this cant be the best way). I cant find any evidence of a my.cnf anywhere, so I dont think it is due to an unexpected config-file option Add log=OFF line to your my.cnf. -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow Queries Log and Index-less queries
I have a question regarding the slow queries log, and queries not using index. I have a small table, with say 10 entries, like that : ID | Element - 1 | One 2 | Two 3 | Three 4 | Four 5 | Five 6 | Six 7 | Seven 8 | Eight 9 | Nine 10 | Ten I want to get all those entries: SELECT ID, Element FROM tblentries; The problem is that this query, even if very fast, is logged in the slow query log because it does not use index (I activated this option in the slow query log). Woudln't it be better if such queries would be logged only in case there is a WHERE, ORDER or GROUP/HAVING clause ? Slow query log, especially with long-long-format turned on, isn't made for manual browsing anyway. You'll have to use mysqldumpslow or some custom-made script to analyze it. Queries that don't have 'where' are easy to filter then. Also, is it better to do : SELECT ID, Element FROM tblentries; or SELECT ID, Element FROM tblentries WHERE ID 0; (In this last case, it won't be logged in the slow query log beause it uses an index...) It won't be logged if it actually will use index. In your example it won't use index, full table scan will be used instead, because query optimizer is able to determine that all records match where condition. -- Alexey Polyakov
Re: Index update process 20+ hrs
I'd try setting innodb_buffer_pool_size=8G in my.cnf -- Alexey Polyakov
Re: Key Buffer Size
Yeah, it's safe. -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query
do something like: SELECT t1.date, (t1.field - t2.field) as diff FROM your_table t1, your_table t2 where t1.date = t2.date + 1; On 8/16/05, Octavian Rasnita [EMAIL PROTECTED] wrote: Hi, I define the previous record by date. For each record corresponds a date which is unique and the previous record is that that contains the yesterday date. Thank you. Teddy - Original Message - From: Felix Geerinckx [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, August 16, 2005 2:45 PM Subject: Re: query On 16/08/2005, Octavian Rasnita wrote: I want to create a query that selects the diference between the value of a field from the current record and the value of the same field from the previous record. How do you define current record and previous record? (relational databases are not spreadsheets) -- felix -- 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] -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: best practices for finding duplicate chunks
You can modify the algorithm I proposed to find groups of records that are likely to have duplicate chunks. Simply record only a part of hashes, something like: if md5(concat(word1,word2,...,word20))%32=0. Disk usage for this table will be maybe 60 bytes per record, if your average word is 8 bytes (counting whitespace), then disk space you'll need is about 25% of data size. After groups of record are found, you can do brute-force indexing to find duplicate chunks. On 8/15/05, Gerald Taylor [EMAIL PROTECTED] wrote: Thanks for your answer. It would certainly work provided having enough disk space to do that. I thought something like that but was hoping I can leverage fulltext and just record the fulltext result between a each record and each other record. Then I can group all records that highly correlate and maybe do a much smaller scale version of the brute force indexing thing that you are proposing, i.e. only do it on a group of records that we already know have a high correlation, ie a high probability of sharing a chunk in common Then when done I can throw away that data and do another group. What do you think? Processing cycles I have but easy disk space I don't. -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A little help with mysql's Query Cache configuration
query_cache_size - a total size of memory that server utilizes for query caching. query_cache_limit - maximum size of result set that can be cached. I'd suggest leaving query_cache_limit at 1 mb (the default value), and tune query_cache_size until you have reasonably low qcache_lowmem_prunes value. On 8/15/05, Maxim Vexler [EMAIL PROTECTED] wrote: Mysql's query cache has two option's [query_cache_limit] and [query_cache_size]. The documentation is not clear (for me) on their purpose and correct usage tactics. What does the [query_cache_size] sets, the maximum memory space a single query can utilize ? Is it wise to set it to the same size as [query_cache_limit] ? How much memory would you recommend setting the caching to, if I have 1GB on the system, and it runs httpd/php mysql all together ? I've also included a dump from our live server, for which I have configured the query cache. for reference (or criticism) : mysql SHOW STATUS LIKE 'Qcache%'; +-+-+ | Variable_name | Value | +-+-+ | Qcache_queries_in_cache | 1007| | Qcache_inserts | 98199 | | Qcache_hits | 97128 | | Qcache_lowmem_prunes| 49330 | | Qcache_not_cached | 671 | | Qcache_free_memory | 1063112 | | Qcache_free_blocks | 340 | | Qcache_total_blocks | 2374| +-+-+ 8 rows in set (0.00 sec) mysql SHOW global VARIABLES like 'query_cache%'; +---+-+ | Variable_name | Value | +---+-+ | query_cache_limit | 5242880 | | query_cache_size | 3145728 | | query_cache_type | ON | +---+-+ 3 rows in set (0.00 sec) Thank you. -- Cheers, Maxim Vexler (hq4ever). Do u GNU ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication, charset / collations Problem
The only idea, which came into my mind, is that the server use different default charsets or collations. I checked the configurations and can't find a difference. Is there a possibility to check the default charsets and collations on the running server ? If charsets/collations are not set explicitly in my.cnf, server could be using ones specified during compilation. To check charsets and collations on the running server do: show global variables like 'c%'; -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
