RE: MySQL cache problem - innodb_buffer_pool_size and FS cache
Hi, This question is about tuning the innodb_buffer_pool_size in an experiment related to my earlier post. I am running MySQL 4.1.11 with innoDB on RedHatEnterprise Linux, kernel 2.6.9-22. The table contains 50M records, with a total data_length of 9GB and index_length of 9.5GB. I measured repeatedly the query latency of 5 randomly selected records. The latency remains relatively small and are about the same regardless of whether I restart MySQL or not. As pointed out earlier by Philippe, this could be caused by hitting the FS cache. 1) Does that mean the MySQL innodb_buffer_pool_size setting will virtually have no effect at all? (if it is true, then it should happen quite commonly). In my test, I tried to change the innodb_buffer_pool_size from its default value (8MB) to half of the physical memory (4GB). I found almost no difference in latency in these two cases. 2) The free command shows the following information. Is it valid to infer that the FS caches about 6.6G of the total 9.5G index file for the innodb table? (assuming the server is dedicated to mysql) %free total used free sharedbuffers cached Mem: 81620487589836 572212 0 1845726591900 -/+ buffers/cache: 8133647348684 Swap: 20971441602096984 Thanks! Charles -Original Message- From: Philippe Poelvoorde [mailto:[EMAIL PROTECTED] Sent: Friday, April 07, 2006 2:43 AM To: MySQL General Subject: Re: MySQL 4.1.11 innodb cache can't be flushed after restart ? 2006/4/7, Charles Q. Shen [EMAIL PROTECTED]: Hi all, I am running MySQL 4.1.11 with an innoDB table holding about 17GB of records. I took a few hundreds of randomly selected records from the table and measured the average access time: 1st test: average access time is 600ms 2nd test: average access time is 30ms 3rd test: average access time is 15ms Stop and restart MySQL 4th test: average access time is 15ms Note that I stopped and restarted mysql between the 3rd and 4th test but the average access time does not change. What OS do you use ? It's quiet likely you hit the FS cache, not the MySQL one. -- 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]
RE: MySQL cache problem - innodb_buffer_pool_size and FS cache
Hi Dan, Thanks! Please see comments inline. -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: Saturday, April 15, 2006 2:51 PM To: Charles Q. Shen Cc: 'Philippe Poelvoorde'; 'MySQL General' Subject: Re: MySQL cache problem - innodb_buffer_pool_size and FS cache In the last episode (Apr 15), Charles Q. Shen said: This question is about tuning the innodb_buffer_pool_size in an experiment related to my earlier post. I am running MySQL 4.1.11 with innoDB on RedHatEnterprise Linux, kernel 2.6.9-22. The table contains 50M records, with a total data_length of 9GB and index_length of 9.5GB. I measured repeatedly the query latency of 5 randomly selected records. The latency remains relatively small and are about the same regardless of whether I restart MySQL or not. As pointed out earlier by Philippe, this could be caused by hitting the FS cache. 1) Does that mean the MySQL innodb_buffer_pool_size setting will virtually have no effect at all? (if it is true, then it should happen quite commonly). In my test, I tried to change the innodb_buffer_pool_size from its default value (8MB) to half of the physical memory (4GB). I found almost no difference in latency in these two cases. For a 5-record test using the primary key to look up values, it may not make a difference at all. Sorry I did not make it clear, the 5-record tests are based on a secondary-index, not the the primary one. And I repeated the tests for the same 5-records set several times. You can basically ignore the index_length, since for innodb that only applies to secondary indexes. The primary index is counted as data. If the primary index is counted as data and become part of the data_length and as I understand the secondary indexes are stored with the primary key value for the row. Could you please explain why the index_length could be larger than the data_length? ( Does that mean you have everything, data, primary, secondary index mixed together??) thanks. If you were to graph lookup latency verses buffer_pool size, you would probably see two bends: the first happens when your cache is large enough to store most of the levels of the index you're using (since you have to seek for each level), Is this the size of index_length or is this merely the size of index itself ( for example, 600M primary keys as you've shown below, plus whatever size of secondary keys)? and the second happens when your cache is large enough to store the data pages as well (so you go to 0 seeks per record). So given that my data_length is 9G and index_length is 9.5G, what do you think would be the memory threshold for this to happen (9G, 9.5G, 18.5G) ? I don't know how big your primary key is, It is an INT. but assuming 8 bytes (and a 4-byte pointer to the next index page), that'd need 50MB*(8+4)=600MB. The nice thing about indexes, though, is the first levels get hit an awful lot, so it doesn't take many random lookups to fetch them. You would need 9GB of cache to reach the next bend for your particular table, and even then you would have to run a lot of 5-record tests in sequence before the cache filled up enough to show it (or do a full table scan beforehand to pull everything into memory). I tested the same 5-record repeatedly. I also tried a smaller database with a data_length 87M and index_length 96M. In that case I tested after a full table scan, but encountered the same problem as reported. Probably the reason is still the OS cache you also mentioned below. Thanks! Charles Your testing is made more difficult by your 8GB RAM size, since even if your make your innodb cache ridiculously small, you still have a 50% chance of the lookup taking 0ms because it's in the OS cache. It's always more efficient to cache inside mysql, though, since read syscalls aren't free. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.1.11 innodb cache can't be flushed after restart ?
The OS used are Mandriva and Fedora. Can you explain more? Thanks. Charles -Original Message- From: Philippe Poelvoorde [mailto:[EMAIL PROTECTED] Sent: Friday, April 07, 2006 2:43 AM To: MySQL General Subject: Re: MySQL 4.1.11 innodb cache can't be flushed after restart ? 2006/4/7, Charles Q. Shen [EMAIL PROTECTED]: Hi all, I am running MySQL 4.1.11 with an innoDB table holding about 17GB of records. I took a few hundreds of randomly selected records from the table and measured the average access time: 1st test: average access time is 600ms 2nd test: average access time is 30ms 3rd test: average access time is 15ms Stop and restart MySQL 4th test: average access time is 15ms Note that I stopped and restarted mysql between the 3rd and 4th test but the average access time does not change. What OS do you use ? It's quiet likely you hit the FS cache, not the MySQL one. -- 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]
MySQL 4.1.11 innodb cache can't be flushed after restart ?
Hi all, I am running MySQL 4.1.11 with an innoDB table holding about 17GB of records. I took a few hundreds of randomly selected records from the table and measured the average access time: 1st test: average access time is 600ms 2nd test: average access time is 30ms 3rd test: average access time is 15ms Stop and restart MySQL 4th test: average access time is 15ms Note that I stopped and restarted mysql between the 3rd and 4th test but the average access time does not change. I also tried another set of random records that are not in the table, the average access time is about 2s for the first test and 115ms for the second test. After stop and restart MySQL, I still got the 115ms access time. Clearly MySQL have both positive and negtive caching. But does anyone know why the cache is not flushed after MySQL restart?? I understand that MySQL has a query_cache, but it is turned off by default and I do NOT have it on. There is also an innodb_buffer_pool_size variable, which in my case is at the default value 1048576 (and can't seem to be set smaller). My data file path in my.cnf file is: # Configure the datafile to be auto expanding innodb_data_file_path = ibdata1:10M:autoextend Thanks a lot! Charles -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ordering rows whit a select from where in ( exp )
Hi Adrian,
you can do
SELECT field_name FROM meta WHERE id IN
('13','11','7','8','9','10','12')
ORDER BY FIELD(id,'13','11','7','8','9','10','12')
/Johan
Adrian wrote:
Hi everyone,
Here is my issue:
I have this Query : SELECT field_name FROM meta WHERE id IN
('13','11','7','8','9','10','12')
I want the rows to be display in the same order as the in list of
ids.Any ideas? Should I use order by? Whit witch option ?
Thanks for your help.
Adrian.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mygrating from 3.23 to 4.1
Hi, For years i've been using MySQL 3.23, but now that i'm about to reinstall my company's web server, i've decided that it's time to go to 4.1 I've read quite a few things in the web about migrating the databases, *including the mysql one*, but each article i read, pointed me in a different direction. Tryed to do the migration at my home computer, but... no good. What is indeed the right way to do this *full* databases migration from 3.3.28 to 4.1.11 ? Any help would be apreciated. Warm Regards, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
auto increment using even numbers
I am creating a table and would like to use auto increment, but I would like one column to increment in only odd numbers 1,3,5... and another column of the same table to increment in even numbers 2,4,6... Is it possible to do this? Thanks, KB __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB crash - recovery
Greetings all. I have a bit of a problem here, a database i'm administering was somehow corrupted, and i'm unable to recover it in any way. Is there any way at all to recover a corrupt InnoDB database? (I read on innodb.com that it is impossible, but hope it is not) When I run a query from any InnoDB table in the database MySQL crashes with the following stack trace and errors. I'm running a GNU/Linux system and MySQL 4.0.13 from the Debian unstable. Error: trying to access field 4294967295 in rec 030807 13:53:24 InnoDB: Assertion failure in thread 180234 in file rem0rec.c line 111 InnoDB: Failing assertion: 0 ... thd=0x86e3990 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbe7fe898, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x8102bc3 0x401ad75a 0x82b9a60 0x8230d50 0x822e42c 0x816952f 0x8169c84 0x816bf6a 0x816c2be 0x815e77f 0x8178c60 0x810f8e8 0x8112a15 0x810db3d 0x810d6cc 0x810d059 0x401a7d53 0x4038a3f7 New value of fp=(nil) failed sanity check, terminating stack trace! ... 0x8102bc3 mysql_binlog_send__FP3THDPcUxUs + 1419 0x401ad75a _end + 936375294 0x82b9a60 _tr_flush_block + 640 0x8230d50 page_cur_delete_rec + 5780 0x822e42c page_copy_rec_list_end_to_created_page + 392 0x816952f yyparse + 3855 0x8169c84 yylex + 1572 0x816bf6a opt_search_plan_for_table + 742 0x816c2be opt_search_plan_for_table + 1594 0x815e77f row_upd_clust_step + 431 0x8178c60 btr_compress + 3852 0x810f8e8 srv_master_thread + 172 0x8112a15 innobase_start_or_create_for_mysql + 1297 0x810db3d srv_sprintf_innodb_monitor + 425 0x810d6cc srv_suspend_mysql_thread + 1372 0x810d059 srv_table_reserve_slot_for_mysql + 473 0x401a7d53 _end + 936352247 0x4038a3f7 _end + 938328219 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Second Auto-increment
I would like to auto-increment within an auto-incremented value. For example, if I have a rental agreement(auto-incremented) and that rental agreement becomes month-to-month. I was thinking that I would want to track the receipts as such: Contract # | Type | Second Auto # | Duration |payment total 1 | 1 | 1 | 6 months| 6,000 1 | 2 | 1 | 1 month | 1,000 1 | 2 | 2 | 1 month | 1,000 As you can see, the contract stays the same (1), but the Contract Type changes to 2(month-to-month). When this happens, the first transaction is reset to auto-increment from that point forward. The third record illustrates the second auto-incremented #, within the Type #2. Although I have seen many auto-incremented examples, I have yet to see one that auto-increments within another auto-incremented value. Any suggestions or examples would be appreciated. Thanks. - Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo.
Securing a Website driven with MySQL/PHP/Apache
I am in the process of developing a MySql database driven website but I need to know that it is secure. Should the User Authentication Password stored in the database be encryted? And if so, how? Any other security tips are welcome..! Thanks. - Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo.
RE: Decode to Case
Try a function called coaleace It will return the non null value. But i dont have the exact syntax with me nor the manual. Hope it helps Tommy -Original Message- From: Arul [mailto:[EMAIL PROTECTED]] Sent: Wed, 05 Jun 2002 13:26 To: MySQL Subject: Decode to Case Hi All I am currently in the process of converting our database from Oracle to MySql. I Use 3.23.50 Max on Win 2k Well..here's a query where we use decode function in oracle...How do i change it to MYSQL The Query is -- SELECT C.Company_Name, DECODE(U.CompanyID, Null, U.USERID, (SELECT DISTINCT A.UserID FROM Users AWHERE A.User_TypeID = 2))as UserID,U.COMPANYID, U.PASSWORD FROM Users U, Company C WHERE U.CompanyID = C.CompanyID (+) ; --- Well...as per the query , if the company id in the user table is null , we need to select the UserID from the usertable and if the company id has any values , we need to select the distinct of the users in the company table I Thought of using CASE...so it went like this Select C.Company_Name , If(U.CompanyID is null , U,UserID,11) as UserID , U.CompanyID , U.Password From users left join company on u.companyid = c.company id... This by default assigns userid as 11 when companyid is nullBut how do we get the value from the user table instead of 11 Any ideas for replacing subqueries inside decode function...MySql doesnt support sub queries ...so how do i write queries inside a CASE condition Regards, -Arul - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: a bug or intentional? -- problem with multiple table query
Thanks! I altered my statements to use left joins and now it all looks like
it's working!
On Thursday 01 November 2001 09:51 pm, Quentin Bennett wrote:
Hi,
You need to read up on Left Joins, which will insert a 'NULL' row in the
results for B where there isn't a real one.
Regards
Quentin
-Original Message-
From: Q [mailto:[EMAIL PROTECTED]]
Sent: Friday, 2 November 2001 4:40 p.m.
To: [EMAIL PROTECTED]
Subject: a bug or intentional? -- problem with multiple table query
I am having a problem with a query and I don't know if it is a bug or
intentional. Anyway any input is greatly appreciated. Here is a basic
query
that is simpler than what I am dealing with but should demonstrate my
problem.
Lets say:
SELECT A.1, A.2 FROM A
yeilds:
test1 test2
then:
SELECT A.1, A.2 FROM A, B
yeilds nothing, unless B has something in it. my problem is that b does not
always have something in it. I need to have B in the FROM list because I
use
it in the WHERE statement.
I am curious if this is intentional, but mainly I would like to know if
there
is a way around it other than putting a dummy entry in the tables.
If anyone cares here is the real query:
SELECT DISTINCT services.serviceid, services.name from admin_permissions,
pop_permissions, admin_pops, services WHERE
(concat('service_',services.serviceid)=admin_permissions.object AND
(admin_permissions.can_change=1 OR admin_permissions.can_grant=1) AND
admin_permissions.userid=1) OR (concat('service_',services.serviceid)=
pop_permissions.object AND (pop_permissions.can_change=1 OR
pop_permissions.can_grant=1) AND pop_permissions.popid=admin_pops.popid AND
admin_pops.userid=1) ORDER BY services.name
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
The information contained in this email is privileged and confidential
and intended for the addressee only. If you are not the intended
recipient, you are asked to respect that confidentiality and not
disclose, copy or make use of its contents. If received in error
you are asked to destroy this email and contact the sender immediately.
Your assistance is appreciated.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
a bug or intentional? -- problem with multiple table query
I am having a problem with a query and I don't know if it is a bug or
intentional. Anyway any input is greatly appreciated. Here is a basic query
that is simpler than what I am dealing with but should demonstrate my problem.
Lets say:
SELECT A.1, A.2 FROM A
yeilds:
test1 test2
then:
SELECT A.1, A.2 FROM A, B
yeilds nothing, unless B has something in it. my problem is that b does not
always have something in it. I need to have B in the FROM list because I use
it in the WHERE statement.
I am curious if this is intentional, but mainly I would like to know if there
is a way around it other than putting a dummy entry in the tables.
If anyone cares here is the real query:
SELECT DISTINCT services.serviceid, services.name from admin_permissions,
pop_permissions, admin_pops, services WHERE
(concat('service_',services.serviceid)=admin_permissions.object AND
(admin_permissions.can_change=1 OR admin_permissions.can_grant=1) AND
admin_permissions.userid=1) OR (concat('service_',services.serviceid)=
pop_permissions.object AND (pop_permissions.can_change=1 OR
pop_permissions.can_grant=1) AND pop_permissions.popid=admin_pops.popid AND
admin_pops.userid=1) ORDER BY services.name
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
joining/merging a query?
I have a query question that I can't seem to find an answer to. Perhaps someone can suggest something. I will try to explain what I am trying to do: I have what is essentially 2 queries that I would like to sum into the same result set. I do not know if it is possible, but it would make my life a lot easer if it were. Basically I would have something similar to the following: TABLE user_objects userid object 1 A 1 B 2 D TABLE group_objects groupid object 1 B 1 C 2 E TABLE user_groups userid groupid 1 1 with the above I can do a query SELECT object FROM user_objects WHERE userid=1: object A B then I can do SELECT group_objects.object FROM group_objects, user_groups WHERE group_objects.groupid = user_groups.groupid AND user_groups.userid=1: object B C now somehow I would like to get an output like: A B C in one query. Is this possible somehow? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
