RE: Should we petition GoDaddy?
> I am slowly considering leaving GoDaddy, who has a very good bandwidth and > ok tech support (I have seen better but much much worse) and acceptable > prices, but unfortunately does not support MySQL 5 and PHP 5 either. SNIP > Who think we could make them make the right move and pretty quickly? I'm guessing that you are young and haven't had enough experience with idiots. Simply change vendors. GoDaddy will not bend to accommodate you. Dave. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Should we petition GoDaddy?
I am slowly considering leaving GoDaddy, who has a very good bandwidth and ok tech support (I have seen better but much much worse) and acceptable prices, but unfortunately does not support MySQL 5 and PHP 5 either. I wonder if I could give these people a chance but one thing I'd like to do is see if we could petition these guys and simply threaten to close out our accounts if they do not get on with the music. The current version of MySQL is 3.23. I have no time upgrading my dedicated server to a RedHat machine with custom PHP and MySQL, I don't have the time to do that. Who think we could make them make the right move and pretty quickly? -- 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
In the last episode (Apr 15), Charles Q. Shen said: > From: Dan Nelson [mailto:[EMAIL PROTECTED] > > 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 have multiple indexes, it's possible that their total size could exceed the size of the original data. InnoDB B-tree pages try to stay less than 15/16ths full, and if you haven't optimized the table in a while, they'll end up between 1/2 and 15/16ths, so you may have a lot of slack space in there. None of the Mysql engines tell you how big a particular index is, and you can only get a packing percentage for myisam tables (with myisamchk -i). If you have the disk space to spare, you can make a copy of the table and create just the primary and secondary index used in the query. Then index_length will tell you how big the secondary index is. > > 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)? Just the size of the index you're interested in. > > 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) ? Depends on how much of that 9.5G is the index you're using in the query. It's usually a losing race to actually want to cache the entire table, though, since I assume your table's going to grow. -- 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 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: copying table fo other database
[EMAIL PROTECTED] wrote: > Hi to all! > > I have to transfer copy of the one table from one to other database. > I was working with phpMyAdmin. I exported table in sql file but, when > tried to Import it to other database I got > Fatal error: Allowed memory size of 8388608 bytes exhausted (tried to > allocate 806882 bytes) > Any idea how to do this? > > Thanks for any help > > -afan > Afan, The error you see is coming from PHP, which allows to grow one instance only this big. Apparently your SQL dump is quite big and apparently phpmyadmin is buffering it all in memory before executing. The way I see it, there are are three solutions: 1.) You set the memory limits high enough in php.ini (You don't want to do that.) 2.) You split the SQL dump in enough pieces to stay within the memory constraints of PHP. You then have to import every part seperately. 3.) Instead of using phpmyadmin, you connect with a mysql client and import the sql dump with the client. If you ask me, option 3 seems like the most reasonable and is by far the easiest. I hope that helps. -Jorrit -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Prepared Statement, Select and Calls in C/C++ UDF
Hi, I want to make "C" User Defined Function where to do some data processing using the parameters and the data in the database and to return some BIGINT result after that. My questions are: Q1. Is it possible to use Prepared Statement, Select and Calls in C/C++ UDF? Q2. If, Yes, which MySQL version support such functionallity? Q3. The big problem is how to use the data in the Database - how to get MYSQL structure with which to use Prepared Statements, Selects and Calls? Thank you in advance. -- Best Regards, Miroslav Nachev -- 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
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. You can basically ignore the index_length, since for innodb that only applies to secondary indexes. The primary index is counted as data. 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), 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). I don't know how big your primary key is, 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). 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: Show Grants problem
--- Todd Smith <[EMAIL PROTECTED]> wrote: > Hello > Does anyone know why a user would show up in a select from user > query but > not in a show grants command. What I am trying to say is I tried > SHOW > GRANTS FOR 'user1'@'%' and received that there is no such grant > defined for > 'user1'... But when I run SELECT * FROM user WHERE User = 'user1' I > do get > results. Could the user have been added using an insert causing the > SHOW > GRANTS to fail? > > > Any Ideas > > Todd > Todd, Users are identified by more than just their login name. Users are a combination of login AND a "host" specifier. When you do SELECT * from user where user = 'user1'; a) how many entries are there b) what is in the host column for those entries. For example if the host column shows "fizzle.shizzle.com" then you have to use SHOW GRANTS FOR 'user1'@'fizzle.shizzle.com'; HTH! Shawn Green Database Administrator Unimin Corporation - Spruce Pine __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- 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, 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]
localhost, wildcards and external connections
Hi, Just upgraded my mysql installation from 4.0.18 to 4.1.14, and i'm seeing some odd things with mysql authentication. We use some scripts to change/add mysql users. In this script there's an option to allow 'external' connections, this is implemented by using Host="%" in the user and db table. Previously this would allow both localhost and external network connections, however after the upgrade this now only allows external connections. Could someone comment if this is a regression or if I should use another method ? Thanks, Frido -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
copying table fo other database
Hi to all! I have to transfer copy of the one table from one to other database. I was working with phpMyAdmin. I exported table in sql file but, when tried to Import it to other database I got Fatal error: Allowed memory size of 8388608 bytes exhausted (tried to allocate 806882 bytes) Any idea how to do this? Thanks for any help -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ~ How to install 3 instances of mysql~
Hi, Also a read of http://dev.mysql.com/doc/refman/5.0/en/multiple-servers.html and http://dev.mysql.com/doc/refman/5.0/en/mysqld-multi.html would probably be quite helpful. The second gives you a good idea on how to setup the my.cnf file for multiple servers. The concepts are the same for version 4.1 Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Saturday, 15 April 2006 10:45 PM To: mysql@lists.mysql.com Subject: Re: ~ How to install 3 instances of mysql~ On Sat, 15 Apr 2006, Mohammed Abdul Azeem wrote: > To: [EMAIL PROTECTED] > From: Mohammed Abdul Azeem <[EMAIL PROTECTED]> > Subject: Re: ~ How to install 3 instances of mysql~ > > Hello Keith, > > Thank you very much for your guidence. > > This is my existing my.cnf file > > > [mysqld] > port= 3306 > socket = /tmp/mysql.sock > skip-locking > key_buffer = 384M > max_allowed_packet = 1M > table_cache = 512 > sort_buffer_size = 2M > read_buffer_size = 2M > read_rnd_buffer_size = 8M > thread_cache = 8 > query_cache_size = 32M > log_bin_trust_routine_creators = 1 > # Try number of CPU's*2 for thread_concurrency > thread_concurrency = 8 > #specify the storage engine > default-storage-engine = InnoDB > #specify the table type > default-table-type = InnoDB > #enable the full query log > log > #Print warnings to error log file > log_warnings > #specify max connections > max_connections = 30 > #specify max user connections > max_user_connections = 12 > # Uncomment the following if you are using InnoDB tables > innodb_data_home_dir = /mysql-system/mysql/data/ > innodb_data_file_path = ibdata1:10M:autoextend > innodb_log_group_home_dir = /mysql-system/mysql/data/ > innodb_log_arch_dir = /mysql-system/mysql/data/ > # You can set .._buffer_pool_size up to 50 - 80 % > # of RAM but beware of setting memory usage too high > innodb_buffer_pool_size = 384M > innodb_additional_mem_pool_size = 20M > # Set .._log_file_size to 25 % of buffer pool size > innodb_log_file_size = 5242880 > innodb_log_buffer_size = 8M > innodb_flush_log_at_trx_commit = 1 > innodb_lock_wait_timeout = 100 > > The variables key_buffer = 384M, innodb_buffer_pool_size = 384M, > innodb_additional_mem_pool_size = 20M, innodb_log_buffer_size = 8M > > are set for 1 instance. > > My RAM memory is 1GB. Can i have the same values for the above variables > set for all the 3 instances ? if yes, will that not exceed the available > RAM memory. Please help me set the correct values for these variables > for each instance. Well, if you are running multiple instances of mysqld concurrently, then I guess you will have to share the system resorces as well, such as memory and file handles. Note that if you are using qps to monitor processes, for each mysqld you have running, the memory usage will increase appropriately. I only use MyISAM tables the moment, so I cannot help you with your InnoDB table settings. Kind Regards Keith Roberts -- 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: ~ How to install 3 instances of mysql~
On Sat, 15 Apr 2006, Mohammed Abdul Azeem wrote: > To: [EMAIL PROTECTED] > From: Mohammed Abdul Azeem <[EMAIL PROTECTED]> > Subject: Re: ~ How to install 3 instances of mysql~ > > Hello Keith, > > Thank you very much for your guidence. > > This is my existing my.cnf file > > > [mysqld] > port= 3306 > socket = /tmp/mysql.sock > skip-locking > key_buffer = 384M > max_allowed_packet = 1M > table_cache = 512 > sort_buffer_size = 2M > read_buffer_size = 2M > read_rnd_buffer_size = 8M > thread_cache = 8 > query_cache_size = 32M > log_bin_trust_routine_creators = 1 > # Try number of CPU's*2 for thread_concurrency > thread_concurrency = 8 > #specify the storage engine > default-storage-engine = InnoDB > #specify the table type > default-table-type = InnoDB > #enable the full query log > log > #Print warnings to error log file > log_warnings > #specify max connections > max_connections = 30 > #specify max user connections > max_user_connections = 12 > # Uncomment the following if you are using InnoDB tables > innodb_data_home_dir = /mysql-system/mysql/data/ > innodb_data_file_path = ibdata1:10M:autoextend > innodb_log_group_home_dir = /mysql-system/mysql/data/ > innodb_log_arch_dir = /mysql-system/mysql/data/ > # You can set .._buffer_pool_size up to 50 - 80 % > # of RAM but beware of setting memory usage too high > innodb_buffer_pool_size = 384M > innodb_additional_mem_pool_size = 20M > # Set .._log_file_size to 25 % of buffer pool size > innodb_log_file_size = 5242880 > innodb_log_buffer_size = 8M > innodb_flush_log_at_trx_commit = 1 > innodb_lock_wait_timeout = 100 > > The variables key_buffer = 384M, innodb_buffer_pool_size = 384M, > innodb_additional_mem_pool_size = 20M, innodb_log_buffer_size = 8M > > are set for 1 instance. > > My RAM memory is 1GB. Can i have the same values for the above variables > set for all the 3 instances ? if yes, will that not exceed the available > RAM memory. Please help me set the correct values for these variables > for each instance. Well, if you are running multiple instances of mysqld concurrently, then I guess you will have to share the system resorces as well, such as memory and file handles. Note that if you are using qps to monitor processes, for each mysqld you have running, the memory usage will increase appropriately. I only use MyISAM tables the moment, so I cannot help you with your InnoDB table settings. Kind Regards Keith Roberts -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump
Anago Chima wrote: Please, I want to know if there is a way I can back up all the databases in my MySQL server to a text file just with a single mysqldump query. I run MySQL Server Version 4.1.14 on Windows XP pro __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com Hi, From the manual, http://dev.mysql.com/doc/refman/4.1/en/mysqldump.html use -A or --all-databases as an option Regards David Logan South Australia When in trouble, or in doubt Run in circles, scream and shout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump
Please, I want to know if there is a way I can back up all the databases in my MySQL server to a text file just with a single mysqldump query. I run MySQL Server Version 4.1.14 on Windows XP pro __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to remove muiltiple queries to a table at the same time
1. A single query that fetvhes a value, increases it and save it : UPDATE `table_name` SET `field`=`field`+1; 2. You can lock tables, make updates an then unlock them, the other processes will wait the table to be unlocked before running their queries : LOCK TABLES `table_name` WRITE; {SQL statements here} UNLOCK TABLES; See also : http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html ""abhishek jain"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] Dear Friends, I run several processes and they need to query the mysql 5.0.8 database simultaneously .I have a config table which have the record id. I need to fetch that and increment that .What I feel that the same record id is fetched by different simultaneosly before i update .Can anyone help me in either: 1)telling me a single query which will fetch and incr. in the same query. so the problem of simultaneously queries are solved. 2)A system by which delaying the other queries are done, I use PHP . Expecting a quick reply. Thanks, Abhishek Jain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to remove muiltiple queries to a table at the same time
Dear Friends, I run several processes and they need to query the mysql 5.0.8 database simultaneously .I have a config table which have the record id. I need to fetch that and increment that .What I feel that the same record id is fetched by different simultaneosly before i update .Can anyone help me in either: 1)telling me a single query which will fetch and incr. in the same query. so the problem of simultaneously queries are solved. 2)A system by which delaying the other queries are done, I use PHP . Expecting a quick reply. Thanks, Abhishek Jain
How to host multiple data nodes on a single computer having two processors
In mysql-5.1-reference manual: Each data node is usually located on a separate computer. However, it is also possible to host multiple data nodes on a single computer having more than one processor. In such cases, it is feasible to run one instance of ndbd per physical CPU. (Note that a processor with multiple cores is still a single processor.) How to two data nodes on a single computer having two processors? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 3 to 5 upgrade
At 19:14 +0100 14/4/06, Philippe Poelvoorde wrote: Have a serious look at : http://dev.mysql.com/doc/refman/4.1/en/upgrading-from-3-23.html http://dev.mysql.com/doc/refman/4.1/en/upgrading-from-4-0.html http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html I will definitely do this. Meanwhile though, it seems to me that my best option is to set up a similar system on my local machine - where I do all my development - as soon as I can. So maybe this is where it gets /slightly/ off-topic... Can anyone recommend - bearing in mind that I'm no great Unix expert but can follow instructions slavishly! - the best combination of MySQL 5, PHP 5 and Apache 2 for Mac OS X 10.4? I do currently run PHP 5, but only Apache 1.3 and MySQL 4.1. -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ A professor is one who talks in someone else's sleep. -- W.H. Auden -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ~ How to install 3 instances of mysql~
Hello Keith, Thank you very much for your guidence. This is my existing my.cnf file [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M thread_cache = 8 query_cache_size = 32M log_bin_trust_routine_creators = 1 # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 #specify the storage engine default-storage-engine = InnoDB #specify the table type default-table-type = InnoDB #enable the full query log log #Print warnings to error log file log_warnings #specify max connections max_connections = 30 #specify max user connections max_user_connections = 12 # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /mysql-system/mysql/data/ innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /mysql-system/mysql/data/ innodb_log_arch_dir = /mysql-system/mysql/data/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 384M innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 5242880 innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 100 The variables key_buffer = 384M, innodb_buffer_pool_size = 384M, innodb_additional_mem_pool_size = 20M, innodb_log_buffer_size = 8M are set for 1 instance. My RAM memory is 1GB. Can i have the same values for the above variables set for all the 3 instances ? if yes, will that not exceed the available RAM memory. Please help me set the correct values for these variables for each instance. Thanks in advance, Abdul. On Sat, 2006-04-15 at 09:11 +0100, [EMAIL PROTECTED] wrote: > Hi Mohammed - yes it is possible to install multiple > instances of mysqld on one machine. > > Under SuSE Linux 9.2 I have had 3 instances of mysqld > running. > > I would suggest the following. > > Use the generic static pre-compiled distibution. > > Linux (non RPM package) downloads (platform notes) > Linux (x86, glibc-2.2, "standard" is static, gcc) > Standard 5.0.20 30.3M Pick a mirror > MD5: 5b0471380db88b03267bbabde500b7e0 | Signature > > For each server you want to run, install a copy of the above > distribution into a seperate base directory. > > eg. > > /usr/local/mysql-5.0.20-srv1 > /usr/local/mysql-5.0.20-srv2 > /usr/local/mysql-5.0.20-srv3 > > You will need to install a seperate data directory for each > server, as it could corrupt the database if multiple > mysqld's are writing to the same database. > > Each server can be started using a shell script that > lives in it's own server directory, in this case; > > /usr/local/mysql-5.0.20-srv1/bin/start-mysql-5.0.20 > > contents of start-mysql-5.0.20-srv1 would be something like: > > #! /bin/sh > # > # start the MySQL database server srv1 instance > > /usr/local/mysql-5.0.20-srv1/bin/mysqld \ > --defaults-file=/usr/local/mysql-5.0.20-srv1/my.cnf \ > --port=7000 \ > --socket=/usr/local/mysql-5.0.20-srv1/data/mysql.sock \ > --pid=/usr/local/mysql-5.0.20-srv1/data/srv1.pid \ > --user=mysql \ > --datadir=/usr/local/mysql-5.0.20-srv1 & > > > Each server instance can have it's own my.conf file, such as > > **you will need to change /usr/local/mysql-5.0.18 to match > your own installations** > > # /usr/local/mysql-/my.cnf > > # MySQL server configuration file > > # last updated 2006-03-08 > > # mysql client program configuration file lives in /etc/my.cnf > > # > # mysqld server configuration options > # > > [mysqld] > basedir=/usr/local/mysql-5.0.18 > > ## use for testing multiple instances of mysqld > ## these parameters are normally passed to mysqld > ## from the start-mysql-5.0.18 script > ## > ##basedir=/usr/local/mysql-5.0.18 > ##port=7005 > ##socket=/usr/local/mysql-5.0.18/data/mysql.sock > ##pid-file=/usr/local/mysql-5.0.18/data/laptop.pid > ##datadir=/usr/local/mysql-5.0.18/data > ##user=mysql > > server-id=1 > > #skip-networking > skip-name-resolve > skip-locking > > set-variable = key_buffer=16M > set-variable = max_allowed_packet=1M > set-variable = table_cache=64 > set-variable = sort_buffer=512K > set-variable = net_buffer_length=8K > set-variable = myisam_sort_buffer_size=8M > > # logging options > log=5-0-18.log > log-bin=laptop-bin > log-error=5-0-18.error-log > log-queries-not-using-indexes > log-slow-admin-statements > log-slow-queries=5-0-18.slow-log > log-warnings > > # > > # end of mysqld server configuration file > # /usr/local/mysql-/my.cnf > > > The /etc/my.cnf can be used to set parameters for all the > mysql clients. > > # /etc/my.cnf > > # MySQL client program co
Right column type for OLE objects
Dear friends, I need to store OLE objects such as Word, Excel & OpenOffice, Autocad documents in the database. Which will be the correct column type for it? Thanks and regards, CPK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ~ How to install 3 instances of mysql~
Create 3 my.cnf files with different ports and sockets: [mysqld] port = 3306 socket=/var/lib/mysql/mysql1.sock . Launch server with: bin/safe_mysqld --defaults-file=/usr/local/mysql/bin/my1.cnf client: /usr/local/mysql/bin/mysql -S /var/lib/mysql/mysql1.sock stop: mysqladmin -S /var/lib/mysql/mysql1.sock shutdown I run MySql 4 & 5 in the same CPU. (different databases). Santino Cusimano At 11:23 +0530 15-04-2006, Mohammed Abdul Azeem wrote: Hi, I need to install 3 instances of mysqld server on a single machine. Can anyone let me know how this can be acheived ? It would be helpful if someone can send me some links and suggestions regarding the same. Also pls lemme know what kind of a configuration file i need to have in order to acheive the same. Thanks in advance, Abdul. This email has been Scanned for Viruses! www.newbreak.com -- 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: ~ How to install 3 instances of mysql~
Hi Mohammed - yes it is possible to install multiple instances of mysqld on one machine. Under SuSE Linux 9.2 I have had 3 instances of mysqld running. I would suggest the following. Use the generic static pre-compiled distibution. Linux (non RPM package) downloads (platform notes) Linux (x86, glibc-2.2, "standard" is static, gcc) Standard5.0.20 30.3M Pick a mirror MD5: 5b0471380db88b03267bbabde500b7e0 | Signature For each server you want to run, install a copy of the above distribution into a seperate base directory. eg. /usr/local/mysql-5.0.20-srv1 /usr/local/mysql-5.0.20-srv2 /usr/local/mysql-5.0.20-srv3 You will need to install a seperate data directory for each server, as it could corrupt the database if multiple mysqld's are writing to the same database. Each server can be started using a shell script that lives in it's own server directory, in this case; /usr/local/mysql-5.0.20-srv1/bin/start-mysql-5.0.20 contents of start-mysql-5.0.20-srv1 would be something like: #! /bin/sh # # start the MySQL database server srv1 instance /usr/local/mysql-5.0.20-srv1/bin/mysqld \ --defaults-file=/usr/local/mysql-5.0.20-srv1/my.cnf \ --port=7000 \ --socket=/usr/local/mysql-5.0.20-srv1/data/mysql.sock \ --pid=/usr/local/mysql-5.0.20-srv1/data/srv1.pid \ --user=mysql \ --datadir=/usr/local/mysql-5.0.20-srv1 & Each server instance can have it's own my.conf file, such as **you will need to change /usr/local/mysql-5.0.18 to match your own installations** # /usr/local/mysql-/my.cnf # MySQL server configuration file # last updated 2006-03-08 # mysql client program configuration file lives in /etc/my.cnf # # mysqld server configuration options # [mysqld] basedir=/usr/local/mysql-5.0.18 ## use for testing multiple instances of mysqld ## these parameters are normally passed to mysqld ## from the start-mysql-5.0.18 script ## ##basedir=/usr/local/mysql-5.0.18 ##port=7005 ##socket=/usr/local/mysql-5.0.18/data/mysql.sock ##pid-file=/usr/local/mysql-5.0.18/data/laptop.pid ##datadir=/usr/local/mysql-5.0.18/data ##user=mysql server-id=1 #skip-networking skip-name-resolve skip-locking set-variable = key_buffer=16M set-variable = max_allowed_packet=1M set-variable = table_cache=64 set-variable = sort_buffer=512K set-variable = net_buffer_length=8K set-variable = myisam_sort_buffer_size=8M # logging options log=5-0-18.log log-bin=laptop-bin log-error=5-0-18.error-log log-queries-not-using-indexes log-slow-admin-statements log-slow-queries=5-0-18.slow-log log-warnings # # end of mysqld server configuration file # /usr/local/mysql-/my.cnf The /etc/my.cnf can be used to set parameters for all the mysql clients. # /etc/my.cnf # MySQL client program configuration file # last updated 2006-03-05 # mysqld server configuration file lives in # /usr/local/mysql-/my.cnf #--- # mysql client program configuration options #--- [mysql] no-auto-rehash # needed for security - to stops multiple deletes/updates # without a where clause safe-updates # # The following options will be passed to all MySQL clients [client] socket = /var/lib/mysql/mysql.sock port = # [myisamchk] set-variable = key_buffer=20M set-variable = sort_buffer=20M set-variable = read_buffer=2M set-variable = write_buffer=2M # [mysqldump] quick set-variable = max_allowed_packet=16M # # available programs/scripts are: #my_print_defaults mysqladmin #myisamchk mysqlbinlog #myisamlog mysqlbug - n/a #myisampack mysqlcheck #mysql mysqld #mysql_convert_table_format - .plmysqld_multi #mysql_find_rows mysqldump #mysql_fix_privilege_tables n/a mysqlhotcopy - .pl #mysql_install_dbmysqlimport #mysql_setpermission - .pl mysqlshow #mysql_zap mysqltest #mysqlaccess - .pl safe_mysqld # # end of mysql client program configurations # /etc/my.cnf To monitor the mysql instances and the parameters passed to each one, you could use a process monitoring tool like: http://www.student.nada.kth.se/~f91-men/qps/ To cleanly shut down the servers, use a script for