RE: How to dump MySQL data on remote server using mysqldump
Install the mysql client on the web server and run the same mysqldump command from within the web server. Give it a try !!! Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM Skype : RolandoLogicWorx redwa...@logicworks.net http://www.linkedin.com/in/rolandoedwards -Original Message- From: James Corteciano [mailto:ja...@linux-source.org] Sent: Thursday, September 02, 2010 1:19 AM To: mysql@lists.mysql.com Subject: How to dump MySQL data on remote server using mysqldump Hi, I have mysql server separated from web server. My problem is, when I dump the data from database using the command mysqldump -uuser -p -h mysql_server -t -T /tmp/dbname database_name on web server, all data were dumped to the /tmp/dbname of mysql server and not inside of web server. [web]$ mysqldump -uuser -p -h mysql_server -t -T /tmp/dbname database_name [web]$ ll /tmp/dbname total 0 [mysql]$ ll /tmp/dbname total 2 -rw-rw-rw- 1 mysql mysql table_name.txt How could I put the dump data on web server and not the local disk on mysql server? Thanks. Regards, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Execution Time
Hi, I have doubt on the execution time. Can any one help me! Which operation will take much time INSERT or UPDATE ? Thank you Vikram
Re: Does innodb have a temp table space?
I suspect he is talking about the Temp Tablespace concept from Oracle, which is different from a temporary table or a memory table. MySQL will allocate a memory table for sort operation and the like, up until that table exceeds a preset limit, at which point it will automatically (and costly !) be converted to a disk table. On Thu, Sep 2, 2010 at 3:58 AM, Daevid Vincent dae...@daevid.com wrote: InnoDB is one of MANY engines in the RDBMS mySQL. There IS in fact a few ways to store in temporary tables (both RAM and DISK based) http://dev.mysql.com/doc/refman/5.1/en/create-table.html Look at: TABLESPACE PARTITIONS ENGINE -Original Message- From: neutron [mailto:neutronsh...@gmail.com] Sent: Wednesday, September 01, 2010 6:05 PM To: mysql@lists.mysql.com Subject: Does innodb have a temp table space? Hi all, As far as I know, some DB has a separate table space to store temp data (such as for external sort). My questions are: 1. Does innodb also has a separate temp-tablespace? 2. If I don't use innodb_file_per_table, where is innodb's temporary tablespace? Is it in the shared tablespace? Thanks all! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: How to dump MySQL data on remote server using mysqldump
From the mysqldump manpage, on the -T option: Note This option should be used only when mysqldump is run on the same machine as the mysqld server. You must have the FILE privilege, and the server must have permission to write files in the directory that you specify. In other words, you've probably found a loophole that allows you do do serverside dumps remotely using mysqldump :-) Given that this is not what you intended, I suggest you either remove the -T and get SQL-format dumps; or go through the documentation again and find another way to do what you want :-) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Logs not working
Did u check the logs on the db server, to see what the issue was. regards anandkl On Thu, Sep 2, 2010 at 6:25 AM, monloi perez mlp_fol...@yahoo.com wrote: All, I'm not sure if this is the right mailing list since the specific mailing lists doesn't seem to meet my concern. For some reason mysql client stops logging to mysqd.log. We had an issue on Aug 20. But when I checked the log, the latest was on Aug 2. Any idea on how to resolve this or what caused it? Thanks, Mon
Re: Execution Time
On 02/09/2010 10:53 a, Vikram A wrote: Hi, I have doubt on the execution time. Can any one help me! Which operation will take much time INSERT or UPDATE ? Thank you Vikram Depends on the type of data, but generally (and I say this lightly) inserts are faster then updates; alot depends on the storage engines, type of data indexes on the table and so on. I suggest you see which one makes more logic, as speed usually becomes a problem when data becomes very large. -- Jangita | +256 76 91 8383 | Y! MSN: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to dump MySQL data on remote server using mysqldump
On 02/09/2010 7:19 a, James Corteciano wrote: How could I put the dump data on web server and not the local disk on mysql server? Log into the web server and do it from there? or copy the file onto the web server? -- Jangita | +256 76 91 8383 | Y! MSN: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Replication VS Cluster
Hi Guys, We have a system that has been running along nicely for the past three months on a pc (4gb 1,8ghz,debian lenny pc). It is a telecom-financal system; slightly 2 hits per minute but growing exponentally as customers increase. We have now bought two servers 12Gb RAM RAID blah blah; and we want to set the servers up such that one is an exact duplicate of the other; to guard against hardware failiure (in case for example one motherboard is fried for some reason). We want to be able to switch from one server to the next and continue with minimum downtime. Switching will be manual until I figure out how to do an automatic switch (probably continuously ping the main server from the hot backup and if the ping fails the hot backup can change its ip automatically or something!) Anyway, what method of keeping the two servers in sync would the experts recommend between replication and setting up a cluster (or something else)? which will also give me a painless (and later maybe automatic) changeover? Both servers are connected to the same switch. -- Jangita | +256 76 91 8383 | Y! MSN: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Performance problems on MySQL
Hi list, I'm having some performance problems on my 5.0.45-log DB running on Solaris 8 (V240). We only have one table and two apps selecting, updating, inserting and deleting massively and randomly from this table. The table is very simple. All SELECTs,INSERTs,UPDATEs and DELETEs have only one condition on an unique varchar indexed column. The table has 500k records and has been OPTIMIZED 32h ago. I've ran some sampling and: A SELECT costs between 400ms and 600ms. An UPDATE costs between 800ms and 1300ms. A DELETE costs between 900ms and 1300ms An INSERT costs always 900ms 2000ms. At any given time the DB is handling 60-80 operations every second. It does not scale any more than this because all the application connections to the DB are being used and waiting for the DB to move. Our application queues requests and it lags our clients. The perl mysqltuner only whines about Query cache disabled but since I get an ~20 updates every second I can't get any query cache hits, so I disabled it. If it makes any difference, we're replicating everything to another server that don't serve any queries. The DB has a 32 hour uptime. Any help is most welcome. You can find my.cnf, show status and show innodb status below. Kind regards Alex ### my.cnf: sql-mode=STRICT_ALL_TABLES old_passwords =1 skip-bdb max_connections =100 max_allowed_packet =1M table_cache =512 sort_buffer_size=2M read_buffer_size=4M read_rnd_buffer_size=8M thread_cache_size =16 query_cache_limit =32M thread_concurrency =8 max_heap_table_size =28M tmp_table_size =12M innodb_buffer_pool_size =350M innodb_additional_mem_pool_size =15M innodb_log_buffer_size =6M innodb_flush_log_at_trx_commit =1 innodb_lock_wait_timeout=50 ### mysql show status where Value NOT LIKE 0; +---++ | Variable_name | Value | +---++ | Aborted_clients | 88 | | Aborted_connects | 37590 | | Binlog_cache_use | 2148392| | Bytes_received| 1117 | | Bytes_sent| 8772 | | Com_change_db | 1 | | Com_delete| 4 | | Com_insert| 3 | | Com_select| 2 | | Com_show_databases| 1 | | Com_show_fields | 3 | | Com_show_status | 2 | | Com_show_tables | 1 | | Compression | OFF| | Connections | 276096 | | Created_tmp_files | 5 | | Created_tmp_tables| 4 | | Flush_commands| 1 | | Handler_commit| 14 | | Handler_prepare | 14 | | Handler_read_key | 8 | | Handler_read_rnd_next | 263| | Handler_write | 395| | Innodb_buffer_pool_pages_data | 6019 | | Innodb_buffer_pool_pages_dirty| 1858 | | Innodb_buffer_pool_pages_flushed | 593993 | | Innodb_buffer_pool_pages_free | 15784 | | Innodb_buffer_pool_pages_misc | 597| | Innodb_buffer_pool_pages_total| 22400 | | Innodb_buffer_pool_read_ahead_rnd | 1 | | Innodb_buffer_pool_read_requests | 42797013 | | Innodb_buffer_pool_reads | 3497 | | Innodb_buffer_pool_write_requests | 19096507 | | Innodb_data_fsyncs| 4319683| | Innodb_data_pending_fsyncs| 1 | | Innodb_data_read | 60231680 | | Innodb_data_reads | 3514 | | Innodb_data_writes| 4496721| | Innodb_data_written | 1259458560 | | Innodb_dblwr_pages_written| 593993 | | Innodb_dblwr_writes | 12967 | | Innodb_log_write_requests | 2111208| | Innodb_log_writes | 4285654| | Innodb_os_log_fsyncs | 4303114| | Innodb_os_log_pending_fsyncs | 1 | | Innodb_os_log_written | 3264897024 | | Innodb_page_size | 16384 | | Innodb_pages_created | 2476 | | Innodb_pages_read | 3543 | | Innodb_pages_written | 593993 | | Innodb_row_lock_time | 1339668| | Innodb_row_lock_time_avg | 379| | Innodb_row_lock_time_max | 10631 | | Innodb_row_lock_waits | 3531 | | Innodb_rows_deleted |
Re: Performance problems on MySQL
Can you show us the table structure and sample queries? On Thursday, September 2, 2010, Alexandre Vieira nul...@gmail.com wrote: Hi list, I'm having some performance problems on my 5.0.45-log DB running on Solaris 8 (V240). We only have one table and two apps selecting, updating, inserting and deleting massively and randomly from this table. The table is very simple. All SELECTs,INSERTs,UPDATEs and DELETEs have only one condition on an unique varchar indexed column. The table has 500k records and has been OPTIMIZED 32h ago. I've ran some sampling and: A SELECT costs between 400ms and 600ms. An UPDATE costs between 800ms and 1300ms. A DELETE costs between 900ms and 1300ms An INSERT costs always 900ms 2000ms. At any given time the DB is handling 60-80 operations every second. It does not scale any more than this because all the application connections to the DB are being used and waiting for the DB to move. Our application queues requests and it lags our clients. The perl mysqltuner only whines about Query cache disabled but since I get an ~20 updates every second I can't get any query cache hits, so I disabled it. If it makes any difference, we're replicating everything to another server that don't serve any queries. The DB has a 32 hour uptime. Any help is most welcome. You can find my.cnf, show status and show innodb status below. Kind regards Alex ### my.cnf: sql-mode =STRICT_ALL_TABLES old_passwords =1 skip-bdb max_connections =100 max_allowed_packet =1M table_cache =512 sort_buffer_size =2M read_buffer_size =4M read_rnd_buffer_size =8M thread_cache_size =16 query_cache_limit =32M thread_concurrency =8 max_heap_table_size =28M tmp_table_size =12M innodb_buffer_pool_size =350M innodb_additional_mem_pool_size =15M innodb_log_buffer_size =6M innodb_flush_log_at_trx_commit =1 innodb_lock_wait_timeout =50 ### mysql show status where Value NOT LIKE 0; +---++ | Variable_name | Value | +---++ | Aborted_clients | 88 | | Aborted_connects | 37590 | | Binlog_cache_use | 2148392 | | Bytes_received | 1117 | | Bytes_sent | 8772 | | Com_change_db | 1 | | Com_delete | 4 | | Com_insert | 3 | | Com_select | 2 | | Com_show_databases | 1 | | Com_show_fields | 3 | | Com_show_status | 2 | | Com_show_tables | 1 | | Compression | OFF | | Connections | 276096 | | Created_tmp_files | 5 | | Created_tmp_tables | 4 | | Flush_commands | 1 | | Handler_commit | 14 | | Handler_prepare | 14 | | Handler_read_key | 8 | | Handler_read_rnd_next | 263 | | Handler_write | 395 | | Innodb_buffer_pool_pages_data | 6019 | | Innodb_buffer_pool_pages_dirty | 1858 | | Innodb_buffer_pool_pages_flushed | 593993 | | Innodb_buffer_pool_pages_free | 15784 | | Innodb_buffer_pool_pages_misc | 597 | | Innodb_buffer_pool_pages_total | 22400 | | Innodb_buffer_pool_read_ahead_rnd | 1 | | Innodb_buffer_pool_read_requests | 42797013 | | Innodb_buffer_pool_reads | 3497 | | Innodb_buffer_pool_write_requests | 19096507 | | Innodb_data_fsyncs | 4319683 | | Innodb_data_pending_fsyncs | 1 | | Innodb_data_read | 60231680 | | Innodb_data_reads | 3514 | | Innodb_data_writes | 4496721 | | Innodb_data_written | 1259458560 | | Innodb_dblwr_pages_written | 593993 | | Innodb_dblwr_writes | 12967 | | Innodb_log_write_requests | 2111208 | | Innodb_log_writes | 4285654 | | Innodb_os_log_fsyncs | 4303114 | | Innodb_os_log_pending_fsyncs | 1 | | Innodb_os_log_written | 3264897024 | | Innodb_page_size | 16384 | | Innodb_pages_created | 2476 | | Innodb_pages_read | 3543 | | Innodb_pages_written | 593993 | |
Re: Performance problems on MySQL
What is the hardware spec? Anything else running on the box? Why are you replicating but not making use of the slave? Can you post the output of SHOW CREATE TABLE? Regards John On 2 September 2010 12:50, Alexandre Vieira nul...@gmail.com wrote: Hi list, I'm having some performance problems on my 5.0.45-log DB running on Solaris 8 (V240). We only have one table and two apps selecting, updating, inserting and deleting massively and randomly from this table. The table is very simple. All SELECTs,INSERTs,UPDATEs and DELETEs have only one condition on an unique varchar indexed column. The table has 500k records and has been OPTIMIZED 32h ago. I've ran some sampling and: A SELECT costs between 400ms and 600ms. An UPDATE costs between 800ms and 1300ms. A DELETE costs between 900ms and 1300ms An INSERT costs always 900ms 2000ms. At any given time the DB is handling 60-80 operations every second. It does not scale any more than this because all the application connections to the DB are being used and waiting for the DB to move. Our application queues requests and it lags our clients. The perl mysqltuner only whines about Query cache disabled but since I get an ~20 updates every second I can't get any query cache hits, so I disabled it. If it makes any difference, we're replicating everything to another server that don't serve any queries. The DB has a 32 hour uptime. Any help is most welcome. You can find my.cnf, show status and show innodb status below. Kind regards Alex ### my.cnf: sql-mode=STRICT_ALL_TABLES old_passwords =1 skip-bdb max_connections =100 max_allowed_packet =1M table_cache =512 sort_buffer_size=2M read_buffer_size=4M read_rnd_buffer_size=8M thread_cache_size =16 query_cache_limit =32M thread_concurrency =8 max_heap_table_size =28M tmp_table_size =12M innodb_buffer_pool_size =350M innodb_additional_mem_pool_size =15M innodb_log_buffer_size =6M innodb_flush_log_at_trx_commit =1 innodb_lock_wait_timeout=50 ### mysql show status where Value NOT LIKE 0; +---++ | Variable_name | Value | +---++ | Aborted_clients | 88 | | Aborted_connects | 37590 | | Binlog_cache_use | 2148392| | Bytes_received| 1117 | | Bytes_sent| 8772 | | Com_change_db | 1 | | Com_delete| 4 | | Com_insert| 3 | | Com_select| 2 | | Com_show_databases| 1 | | Com_show_fields | 3 | | Com_show_status | 2 | | Com_show_tables | 1 | | Compression | OFF| | Connections | 276096 | | Created_tmp_files | 5 | | Created_tmp_tables| 4 | | Flush_commands| 1 | | Handler_commit| 14 | | Handler_prepare | 14 | | Handler_read_key | 8 | | Handler_read_rnd_next | 263| | Handler_write | 395| | Innodb_buffer_pool_pages_data | 6019 | | Innodb_buffer_pool_pages_dirty| 1858 | | Innodb_buffer_pool_pages_flushed | 593993 | | Innodb_buffer_pool_pages_free | 15784 | | Innodb_buffer_pool_pages_misc | 597| | Innodb_buffer_pool_pages_total| 22400 | | Innodb_buffer_pool_read_ahead_rnd | 1 | | Innodb_buffer_pool_read_requests | 42797013 | | Innodb_buffer_pool_reads | 3497 | | Innodb_buffer_pool_write_requests | 19096507 | | Innodb_data_fsyncs| 4319683| | Innodb_data_pending_fsyncs| 1 | | Innodb_data_read | 60231680 | | Innodb_data_reads | 3514 | | Innodb_data_writes| 4496721| | Innodb_data_written | 1259458560 | | Innodb_dblwr_pages_written| 593993 | | Innodb_dblwr_writes | 12967 | | Innodb_log_write_requests | 2111208| | Innodb_log_writes | 4285654| | Innodb_os_log_fsyncs | 4303114| | Innodb_os_log_pending_fsyncs | 1 | | Innodb_os_log_written | 3264897024 | | Innodb_page_size | 16384 | | Innodb_pages_created | 2476
Re: Replication VS Cluster
On Thu, Sep 2, 2010 at 1:10 PM, Jangita jang...@jangita.com wrote: Hi Guys, We have a system that has been running along nicely for the past three months on a pc (4gb 1,8ghz,debian lenny pc). It is a telecom-financal system; slightly 2 hits per minute but growing exponentally as customers increase. Growth should be linear to the growth of customers, no ? :-) We have now bought two servers 12Gb RAM RAID blah blah; RAID setup is important :-) Datafiles preferably on raid 10. and we want to set the servers up such that one is an exact duplicate of the other; to guard against hardware failiure (in case for example one motherboard is fried for some reason). We want to be able to switch from one server to the next and continue with minimum downtime. Switching will be manual until I figure out how to do an automatic switch (probably continuously ping the main server from the hot backup and if the ping fails the hot backup can change its ip automatically or something!) Have a look at Ultramonkey for that. Anyway, what method of keeping the two servers in sync would the experts recommend between replication and setting up a cluster (or something else)? which will also give me a painless (and later maybe automatic) changeover? Both servers are connected to the same switch. Standard setup would be replication, yes. If you setup automatic failover, make sure you prevent automatic failback - that's the best way to mess up your dataset. I also hear MMM is pretty good, although I have no personal experience with it. Another route you might want to investigate, is Xen (or VMWare, if so inclined). Build a single virtual host on your hardware, allocate everything and the kitchen sink to it, and run your MySQL in it. You'll have a slight performance loss, obviously, but here's the benefit: you can set up the second server so that it keeps a bit-perfect copy of your primary machine. The moment your primary machine dies, the second takes over; and since it has the EXACT same state down to the last bit of ram, you don't even lose a ping. Under Xen this feature is called Remus I believe, VMWare calls it Live Migration or something similar. -- Jangita | +256 76 91 8383 | Y! MSN: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Replication VS Cluster
Clustering is a general term, do you know which one you are comparing with replication? Clustering most typically refers to high availability clustering or high performance clustering, which wouldnt necessarily/normally imply any copy of the actual data. If you want a copy of your data on another server, replication is the obvious choice. Assuming the realities of replication arent a problem with respect to your requirements, ie replication is asynchronous... Andy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replication VS Cluster
On 02/09/2010 4:32 p, Johan De Meersman wrote: On Thu, Sep 2, 2010 at 1:10 PM, Jangita jang...@jangita.com mailto:jang...@jangita.com wrote: ... Growth should be linear to the growth of customers, no ? :-) I thought so too; but one customer = 1 customer record, plus all his transactions, and also weirdly enough (common for us Africans) customers tend to use the service more as more customers enroll (did that make any sence?) :):) ... RAID setup is important :-) Datafiles preferably on raid 10. Thanks a bunch on that generous tip! ... Have a look at Ultramonkey for that. Thanks again! Standard setup would be replication, yes. If you setup automatic failover, make sure you prevent automatic failback - that's the best way to mess up your dataset. I also hear MMM is pretty good, although I have no personal experience with it. Another route you might want to investigate, is Xen (or VMWare, if so inclined). Build a single virtual host on your hardware, allocate everything and the kitchen sink to it, and run your MySQL in it. You'll have a slight performance loss, obviously, but here's the benefit: you can set up the second server so that it keeps a bit-perfect copy of your primary machine. The moment your primary machine dies, the second takes over; and since it has the EXACT same state down to the last bit of ram, you don't even lose a ping. Under Xen this feature is called Remus I believe, VMWare calls it Live Migration or something similar. Thanks! -- Jangita | +256 76 91 8383 | Y! MSN: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Performance problems on MySQL
John, Johnny,
Thanks for the prompt answer.
mysql SHOW CREATE TABLE clientinfo;
++--+
| Table | Create
Table
|
++--+
| clientinfo | CREATE TABLE `clientinfo` (
`userid` varchar(21) NOT NULL default '',
`units` float default NULL,
`date_last_query` datetime default NULL,
`last_acc` int(10) unsigned default NULL,
`date_last_units` datetime default NULL,
`notification` int(10) unsigned NOT NULL default '0',
`package` char(1) default NULL,
`user_type` varchar(5) default NULL,
PRIMARY KEY (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
++--+
1 row in set (0.00 sec)
mysql SHOW INDEX FROM clientinfo;
+++--+--+-+---+-+--++--++-+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+++--+--+-+---+-+--++--++-+
| clientinfo | 0 | PRIMARY |1 | userid |
A | 460056 | NULL | NULL | | BTREE | |
+++--+--+-+---+-+--++--++-+
1 row in set (0.00 sec)
SELECT * FROM clientinfo WHERE userid='182106617';
UPDATE clientinfo SET
units=0.0,date_last_query=now(),user_type='POS',last_acc=167,date_last_units=now(),notification=0
WHERE userid='152633876';
INSERT INTO clientinfo VALUES ('171918726',101.0,now(),1,now(),0,'D','PRE')
ON DUPLICATE KEY UPDATE units=101.0, date_last_query=now(), last_acc=1,
date_last_units=now(), notification=0, package='D', user_type='PRE';
DELETE FROM clientinfo WHERE units='155618918';
There are no other type of queries.
We're running this DB on a Sun-Fire V240. 2xUIIIi 1.5ghz with 2GB of RAM.
We also run some other applications in the server, but nothing that consumes
all the CPU/Memory. The machine has almost 1GB of free memory and 50% of
idle CPU time at any time.
TIA
BR
Alex
On Thu, Sep 2, 2010 at 1:52 PM, John Daisley daisleyj...@googlemail.comwrote:
What is the hardware spec? Anything else running on the box?
Why are you replicating but not making use of the slave?
Can you post the output of SHOW CREATE TABLE?
Regards
John
cant establish connection at application side.
Hi .. I am facing a problem in mysql. I am getting error 'Can not open connection' at the application side which happens at random timings. What i found was at whenever the problem occurs it takes 70 secs for connecting to mysql server. The machine is a high end quad core, 16 GB ram, dedicated for mysql I guess its problem is with my configurations. My guess is like if i ve configured some buffer to x and when when mysql is being used it keeps coming down and slows down connecting time for clients ( its a wild guess ) .. So can I do something to monitor the resources/ connections/ or something else to see what does mysql lacks when connection time reaches 70 secs or my application gets a 'Can not open connection' problem so that I give a larger value for that thing .. Certainly changing (timout's .. or something else ) in application would solve but thats not a solution for my situation .. i have configured max number of connections to 500 .. and the total number of connections hardly reach 100 .. Any help please .. Thanks Karthik.
Re: Replication VS Cluster
On 02/09/2010 4:35 p, a.sm...@ukgrid.net wrote: Clustering is a general term, do you know which one you are comparing with replication? Clustering most typically refers to high availability clustering or high performance clustering, which wouldnt necessarily/normally imply any copy of the actual data. If you want a copy of your data on another server, replication is the obvious choice. Assuming the realities of replication arent a problem with respect to your requirements, ie replication is asynchronous... Andy. True. Simply put: I want a solution that ensures that server 2 has all the data at server 1 at any point in time; say server 1 suddenly fell into a pond :) . I wouldnt want to open server 2 and find the last insert/update/delete missing... -- Jangita | +256 76 91 8383 | Y! MSN: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: cant establish connection at application side.
You might want to have a good close look at the state of your network before you consider mysql as a cause.. - michael dykman On Thu, Sep 2, 2010 at 10:57 AM, karthik kumar kumarkarth...@gmail.com wrote: Hi .. I am facing a problem in mysql. I am getting error 'Can not open connection' at the application side which happens at random timings. What i found was at whenever the problem occurs it takes 70 secs for connecting to mysql server. The machine is a high end quad core, 16 GB ram, dedicated for mysql I guess its problem is with my configurations. My guess is like if i ve configured some buffer to x and when when mysql is being used it keeps coming down and slows down connecting time for clients ( its a wild guess ) .. So can I do something to monitor the resources/ connections/ or something else to see what does mysql lacks when connection time reaches 70 secs or my application gets a 'Can not open connection' problem so that I give a larger value for that thing .. Certainly changing (timout's .. or something else ) in application would solve but thats not a solution for my situation .. i have configured max number of connections to 500 .. and the total number of connections hardly reach 100 .. Any help please .. Thanks Karthik. -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Replication VS Cluster
Simply put: I want a solution that ensures that server 2 has all the data at server 1 at any point in time If server 1 and 2 are on the same local network, I would use a cluster. If they are located on physically separate networks, I would use master-master replication. Neil -- Neil Aggarwal, (281)846-8957 FREE trial: Wordpress VPS with unmetered bandwidth http://UnmeteredVPS.net/wordpress -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: cant establish connection at application side.
I am running a tool which just makes connection and closes it (using JDBC) .. The JDBC connection never says 'Can not establish connection' .. I just says ' at this time ' took 70 secs to establish a connection .. So my guess was network was not a problem,( since connection was established but takes extra time ) .. The application says 'Can not establish connection' = cant establish connection in a specified timeout.. Am i guessing anything wrong or can I get any ideas please ? Thanks Karthik. On Thu, Sep 2, 2010 at 8:32 PM, Michael Dykman mdyk...@gmail.com wrote: You might want to have a good close look at the state of your network before you consider mysql as a cause.. - michael dykman On Thu, Sep 2, 2010 at 10:57 AM, karthik kumar kumarkarth...@gmail.com wrote: Hi .. I am facing a problem in mysql. I am getting error 'Can not open connection' at the application side which happens at random timings. What i found was at whenever the problem occurs it takes 70 secs for connecting to mysql server. The machine is a high end quad core, 16 GB ram, dedicated for mysql I guess its problem is with my configurations. My guess is like if i ve configured some buffer to x and when when mysql is being used it keeps coming down and slows down connecting time for clients ( its a wild guess ) .. So can I do something to monitor the resources/ connections/ or something else to see what does mysql lacks when connection time reaches 70 secs or my application gets a 'Can not open connection' problem so that I give a larger value for that thing .. Certainly changing (timout's .. or something else ) in application would solve but thats not a solution for my situation .. i have configured max number of connections to 500 .. and the total number of connections hardly reach 100 .. Any help please .. Thanks Karthik. -- - michael dykman - mdyk...@gmail.com May the Source be with you.
Re: Performance problems on MySQL
On 02/09/2010 4:46 p, Alexandre Vieira wrote: John, Johnny, Thanks for the prompt answer. ... We also run some other applications in the server, but nothing that consumes all the CPU/Memory. The machine has almost 1GB of free memory and 50% of idle CPU time at any time. TIA BR Alex Increase innodb_buffer_pool_size say to 1GB? -- Jangita | +256 76 91 8383 | Y! MSN: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: cant establish connection at application side.
On 02/09/2010 4:57 p, karthik kumar wrote: Hi .. I am facing a problem in mysql. I am getting error 'Can not open connection' at the application side which happens at random timings. What i found was at whenever the problem occurs it takes 70 secs for connecting to mysql server. The machine is a high end quad core, 16 GB ram, dedicated for mysql I guess its problem is with my configurations. My guess is like if i ve configured some buffer to x and when when mysql is being used it keeps coming down and slows down connecting time for clients ( its a wild guess ) .. So can I do something to monitor the resources/ connections/ or something else to see what does mysql lacks when connection time reaches 70 secs or my application gets a 'Can not open connection' problem so that I give a larger value for that thing .. Certainly changing (timout's .. or something else ) in application would solve but thats not a solution for my situation .. i have configured max number of connections to 500 .. and the total number of connections hardly reach 100 .. Any help please .. Thanks Karthik. Try adding skip-name-resolve on your mysql.conf file; long connection times sometimes are caused by the server trying to resolve, and with many people connecting can sometimes slow the server to a halt! Make sure that your users table has IP addresses instead of host names on the user table eg. 127.0.0.1 instead of localhost (since mysql wount resolve anymore) -- Jangita | +256 76 91 8383 | Y! MSN: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: cant establish connection at application side.
I had a similar problem and found a solution. My WAMP server could not serve pages because the port that it was configured to utilize was in use by my Skype app, which loaded first. All that I had to do, was quit out of Skype, then start WAMP server, then restart Skype (which, I think, uses a different port if it's first choice is in use). Good luck. -Leslie On Thu, Sep 2, 2010 at 8:29 AM, karthik kumar kumarkarth...@gmail.comwrote: I am running a tool which just makes connection and closes it (using JDBC) .. The JDBC connection never says 'Can not establish connection' .. I just says ' at this time ' took 70 secs to establish a connection .. So my guess was network was not a problem,( since connection was established but takes extra time ) .. The application says 'Can not establish connection' = cant establish connection in a specified timeout.. Am i guessing anything wrong or can I get any ideas please ? Thanks Karthik. On Thu, Sep 2, 2010 at 8:32 PM, Michael Dykman mdyk...@gmail.com wrote: You might want to have a good close look at the state of your network before you consider mysql as a cause.. - michael dykman On Thu, Sep 2, 2010 at 10:57 AM, karthik kumar kumarkarth...@gmail.com wrote: Hi .. I am facing a problem in mysql. I am getting error 'Can not open connection' at the application side which happens at random timings. What i found was at whenever the problem occurs it takes 70 secs for connecting to mysql server. The machine is a high end quad core, 16 GB ram, dedicated for mysql I guess its problem is with my configurations. My guess is like if i ve configured some buffer to x and when when mysql is being used it keeps coming down and slows down connecting time for clients ( its a wild guess ) .. So can I do something to monitor the resources/ connections/ or something else to see what does mysql lacks when connection time reaches 70 secs or my application gets a 'Can not open connection' problem so that I give a larger value for that thing .. Certainly changing (timout's .. or something else ) in application would solve but thats not a solution for my situation .. i have configured max number of connections to 500 .. and the total number of connections hardly reach 100 .. Any help please .. Thanks Karthik. -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- Leslie Doak Classic Page Works 619-222-3625 | leslied...@gmail.com | http://www.ClassicPageWorks.com Twitter: http://twitter.com/lesliedoak | LinkedIn: http://www.linkedin.com/in/lesliedoak
Re: Replication VS Cluster
Quoting Jangita jang...@jangita.com: Simply put: I want a solution that ensures that server 2 has all the data at server 1 at any point in time; say server 1 suddenly fell into a pond :) . I wouldnt want to open server 2 and find the last insert/update/delete missing... Ok so that rules out any asynchronous replication (MySQL replication for example). So options available would include, HA clustering with a shared fibre channel RAID array. Or synchronous replication over the network using something like DRBD or HAST (on FreeBSD), plus any other suggestions from others... :P -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Replication VS Cluster
On Thu, Sep 2, 2010 at 5:12 PM, Neil Aggarwal n...@jammconsulting.comwrote: If server 1 and 2 are on the same local network, I would use a cluster. As in NDB ? I've no personal experience with it - save for a sales talk by MySQL guys some years back where we decided it was useless to us - but I understand it has quite a few specific limitations that make it suited for a rather specific range of applications. If they are located on physically separate networks, I would use master-master replication. If you're on separate networks, you're gonna have trouble maintaining both performance and perfect replication, regardless of what you do; not to mention you'll be in performance hell as soon as you want to switch to the remote master. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Replication VS Cluster
On Thu, Sep 2, 2010 at 5:51 PM, a.sm...@ukgrid.net wrote: Quoting Jangita jang...@jangita.com: Simply put: I want a solution that ensures that server 2 has all the data at server 1 at any point in time; say server 1 suddenly fell into a pond :) . I wouldnt want to open server 2 and find the last insert/update/delete missing... Ok so that rules out any asynchronous replication (MySQL replication for example). Actually, recent 5.1 servers do have 'semi-synchronous' replication, where replication is synchronous until the slave happens to timeout, where it reverts to asynchronous until you fix it. Incidentally, this is default Oracle behaviour, too, if you're not talking RAC. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: cant establish connection at application side.
Wow .. Cool that solved our problem... Thanks a lott .. On Thu, Sep 2, 2010 at 9:20 PM, Jangita jang...@jangita.com wrote: On 02/09/2010 4:57 p, karthik kumar wrote: Hi .. I am facing a problem in mysql. I am getting error 'Can not open connection' at the application side which happens at random timings. What i found was at whenever the problem occurs it takes 70 secs for connecting to mysql server. The machine is a high end quad core, 16 GB ram, dedicated for mysql I guess its problem is with my configurations. My guess is like if i ve configured some buffer to x and when when mysql is being used it keeps coming down and slows down connecting time for clients ( its a wild guess ) .. So can I do something to monitor the resources/ connections/ or something else to see what does mysql lacks when connection time reaches 70 secs or my application gets a 'Can not open connection' problem so that I give a larger value for that thing .. Certainly changing (timout's .. or something else ) in application would solve but thats not a solution for my situation .. i have configured max number of connections to 500 .. and the total number of connections hardly reach 100 .. Any help please .. Thanks Karthik. Try adding skip-name-resolve on your mysql.conf file; long connection times sometimes are caused by the server trying to resolve, and with many people connecting can sometimes slow the server to a halt! Make sure that your users table has IP addresses instead of host names on the user table eg. 127.0.0.1 instead of localhost (since mysql wount resolve anymore) -- Jangita | +256 76 91 8383 | Y! MSN: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=kumarkarth...@gmail.com
Re: Performance problems on MySQL
Hi Jangita, I'm 15779 innodb_buffer_pool_pages_free from a total of 22400. That's 246MB of 350MB free. | Innodb_buffer_pool_pages_data | 6020 | | Innodb_buffer_pool_pages_dirty| 1837 | | Innodb_buffer_pool_pages_flushed | 673837 | | Innodb_buffer_pool_pages_free | 15779 | | Innodb_buffer_pool_pages_latched | 0 | | Innodb_buffer_pool_pages_misc | 601| | Innodb_buffer_pool_pages_total| 22400 | | Innodb_buffer_pool_read_ahead_rnd | 1 | | Innodb_buffer_pool_read_ahead_seq | 0 | | Innodb_buffer_pool_read_requests | 48471963 | | Innodb_buffer_pool_reads | 3497 | | Innodb_buffer_pool_wait_free | 0 | | Innodb_buffer_pool_write_requests | 21700478 | Why would I need to increase? Thanks BR AJ On Thu, Sep 2, 2010 at 4:47 PM, Jangita jang...@jangita.com wrote: On 02/09/2010 4:46 p, Alexandre Vieira wrote: John, Johnny, Thanks for the prompt answer. ... We also run some other applications in the server, but nothing that consumes all the CPU/Memory. The machine has almost 1GB of free memory and 50% of idle CPU time at any time. TIA BR Alex Increase innodb_buffer_pool_size say to 1GB? -- Jangita | +256 76 91 8383 | Y! MSN: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=nul...@gmail.com -- Alexandre Vieira - nul...@gmail.com
RE: Performance problems on MySQL
Have you considered adding a secondary index on the units column for your
delete queries?
DELETE FROM clientinfo WHERE units='155618918';
-Original Message-
From: Alexandre Vieira [mailto:nul...@gmail.com]
Sent: Thursday, September 02, 2010 8:46 AM
To: John Daisley; joh...@pixelated.net
Cc: mysql@lists.mysql.com
Subject: Performance problems on MySQL
John, Johnny,
Thanks for the prompt answer.
mysql SHOW CREATE TABLE clientinfo;
++--
+
| Table | Create
Table
|
++--
+
| clientinfo | CREATE TABLE `clientinfo` (
`userid` varchar(21) NOT NULL default '',
`units` float default NULL,
`date_last_query` datetime default NULL,
`last_acc` int(10) unsigned default NULL,
`date_last_units` datetime default NULL,
`notification` int(10) unsigned NOT NULL default '0',
`package` char(1) default NULL,
`user_type` varchar(5) default NULL,
PRIMARY KEY (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
++--
+
1 row in set (0.00 sec)
mysql SHOW INDEX FROM clientinfo;
+++--+--+-+-
--+-+--++--++-+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+++--+--+-+-
--+-+--++--++-+
| clientinfo | 0 | PRIMARY |1 | userid |
A | 460056 | NULL | NULL | | BTREE | |
+++--+--+-+-
--+-+--++--++-+
1 row in set (0.00 sec)
SELECT * FROM clientinfo WHERE userid='182106617';
UPDATE clientinfo SET
units=0.0,date_last_query=now(),user_type='POS',last_acc=167,date_last_units
=now(),notification=0
WHERE userid='152633876';
INSERT INTO clientinfo VALUES ('171918726',101.0,now(),1,now(),0,'D','PRE')
ON DUPLICATE KEY UPDATE units=101.0, date_last_query=now(), last_acc=1,
date_last_units=now(), notification=0, package='D', user_type='PRE';
DELETE FROM clientinfo WHERE units='155618918';
There are no other type of queries.
We're running this DB on a Sun-Fire V240. 2xUIIIi 1.5ghz with 2GB of RAM.
We also run some other applications in the server, but nothing that consumes
all the CPU/Memory. The machine has almost 1GB of free memory and 50% of
idle CPU time at any time.
TIA
BR
Alex
On Thu, Sep 2, 2010 at 1:52 PM, John Daisley
daisleyj...@googlemail.comwrote:
What is the hardware spec? Anything else running on the box?
Why are you replicating but not making use of the slave?
Can you post the output of SHOW CREATE TABLE?
Regards
John
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Performance problems on MySQL
Hi Travis,
Sorry, bad copy/paste. That DELETE statement is wrong.
The application executes:
DELETE FROM clientinfo WHERE userid='x';
BR
AJ
On Thu, Sep 2, 2010 at 5:23 PM, Travis Ard travis_...@hotmail.com wrote:
Have you considered adding a secondary index on the units column for your
delete queries?
DELETE FROM clientinfo WHERE units='155618918';
-Original Message-
From: Alexandre Vieira [mailto:nul...@gmail.com]
Sent: Thursday, September 02, 2010 8:46 AM
To: John Daisley; joh...@pixelated.net
Cc: mysql@lists.mysql.com
Subject: Performance problems on MySQL
John, Johnny,
Thanks for the prompt answer.
mysql SHOW CREATE TABLE clientinfo;
++--
+
| Table | Create
Table
|
++--
+
| clientinfo | CREATE TABLE `clientinfo` (
`userid` varchar(21) NOT NULL default '',
`units` float default NULL,
`date_last_query` datetime default NULL,
`last_acc` int(10) unsigned default NULL,
`date_last_units` datetime default NULL,
`notification` int(10) unsigned NOT NULL default '0',
`package` char(1) default NULL,
`user_type` varchar(5) default NULL,
PRIMARY KEY (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
++--
+
1 row in set (0.00 sec)
mysql SHOW INDEX FROM clientinfo;
+++--+--+-+-
--+-+--++--++-+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+++--+--+-+-
--+-+--++--++-+
| clientinfo | 0 | PRIMARY |1 | userid |
A | 460056 | NULL | NULL | | BTREE | |
+++--+--+-+-
--+-+--++--++-+
1 row in set (0.00 sec)
SELECT * FROM clientinfo WHERE userid='182106617';
UPDATE clientinfo SET
units=0.0,date_last_query=now(),user_type='POS',last_acc=167,date_last_units
=now(),notification=0
WHERE userid='152633876';
INSERT INTO clientinfo VALUES ('171918726',101.0,now(),1,now(),0,'D','PRE')
ON DUPLICATE KEY UPDATE units=101.0, date_last_query=now(), last_acc=1,
date_last_units=now(), notification=0, package='D', user_type='PRE';
DELETE FROM clientinfo WHERE units='155618918';
There are no other type of queries.
We're running this DB on a Sun-Fire V240. 2xUIIIi 1.5ghz with 2GB of RAM.
We also run some other applications in the server, but nothing that
consumes
all the CPU/Memory. The machine has almost 1GB of free memory and 50% of
idle CPU time at any time.
TIA
BR
Alex
On Thu, Sep 2, 2010 at 1:52 PM, John Daisley
daisleyj...@googlemail.comwrote:
What is the hardware spec? Anything else running on the box?
Why are you replicating but not making use of the slave?
Can you post the output of SHOW CREATE TABLE?
Regards
John
--
Alexandre Vieira - nul...@gmail.com
Re: Does innodb have a temp table space?
Hello Johan, Thanks for the reply. On Thu, Sep 2, 2010 at 3:34 AM, Johan De Meersman vegiv...@tuxera.be wrote: I suspect he is talking about the Temp Tablespace concept from Oracle, which is different from a temporary table or a memory table. MySQL will allocate a memory table for sort operation and the like, up until that table exceeds a preset limit, at which point it will automatically (and costly !) be converted to a disk table. == How to define the memory table limit? When the temp table is converted to a disk table, where is this disk table stored? In the same shared tablespace file if I don't use innodb_file_per_table? On Thu, Sep 2, 2010 at 3:58 AM, Daevid Vincent dae...@daevid.com wrote: InnoDB is one of MANY engines in the RDBMS mySQL. There IS in fact a few ways to store in temporary tables (both RAM and DISK based) http://dev.mysql.com/doc/refman/5.1/en/create-table.html Look at: TABLESPACE PARTITIONS ENGINE -Original Message- From: neutron [mailto:neutronsh...@gmail.com] Sent: Wednesday, September 01, 2010 6:05 PM To: mysql@lists.mysql.com Subject: Does innodb have a temp table space? Hi all, As far as I know, some DB has a separate table space to store temp data (such as for external sort). My questions are: 1. Does innodb also has a separate temp-tablespace? 2. If I don't use innodb_file_per_table, where is innodb's temporary tablespace? Is it in the shared tablespace? Thanks all! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Best method to keep totals
From: Jerry Schwartz je...@gii.co.jp IMNSHO, never store dynamic data in a field unless you absolutely have to. I agree, and yet, it's so darned handy if it's a calculation you need quite often. In FileMaker Pro (hold the boos, please :-) you can have calculated fields -- a pseudo field that holds references to other fields in an equation. To do something similar in MySQL, I've used views (which is somewhat clumsy), but is there a better way? Or is this necessarily part of domain knowledge programming in your interface language? Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
question about VIEWS in 5.1.x
Simple question about views: I have a view such as: create view combo as select * from table1 union select * from table2; Where table1 and table2 are very large and identical and have a non-unique key on field id.. when I do a: select * from combo where id='value' ; the system seems to be doing a table scan of one or both tables.. I can't even do an: explain select * from combo where field='value' ; the system seems to hang on the explain. SHOW PROCESSLIST says the explain is Sending data . Issuing either one of the view components with the where clause returns results in a fraction of a second (pretty much a full indexed lookup) I know when I used to use Oracle, the where clause would be applied to all parts of the view, but in this case, I can't even figure out what MySQL is trying to do. (I've also tried UNION ALL with the same results). Any suggestions on how to query both tables using the indexed and the view at the same time? That was my intention. -Hank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Symlinks not working when pointing to another table.
Hello, I've done lot of researches and tests but can't find any answer. I need to share one table between two db, those two db are in the same path ( /var/lib/mysql/db1 db2 ). I created symbolic links for db2 pointing to the table in db1. When I query the table from db2 I get this error : 'ERROR 1030 (HY000): Got error 140 from storage engine' I'm sure it was working in previous version of MySQL, but from the last version it's no longer working. This is how it looks : test-lan:/var/lib/mysql/test3# ls -alh drwx-- 2 mysql mysql 4.0K 2010-08-30 13:28 . drwxr-xr-x 6 mysql mysql 4.0K 2010-08-30 13:29 .. lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.frm - /var/lib/mysql/test/blbl.frm lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.MYD - /var/lib/mysql/test/blbl.MYD lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.MYI - /var/lib/mysql/test/blbl.MYI -rw-rw 1 mysql mysql 65 2010-08-30 13:24 db.opt I really need those symlinks, is there a way to make them working like before ? ( old MySQL-server is fine ) Thanks, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Symlinks not working when pointing to another table.
-Original Message- From: Julien Lory [mailto:julien.l...@gmail.com] Sent: Thursday, September 02, 2010 12:31 PM To: mysql@lists.mysql.com Subject: Symlinks not working when pointing to another table. Hello, I've done lot of researches and tests but can't find any answer. I need to share one table between two db, those two db are in the same path ( /var/lib/mysql/db1 db2 ). I created symbolic links for db2 pointing to the table in db1. When I query the table from db2 I get this error : 'ERROR 1030 (HY000): Got error 140 from storage engine' I'm sure it was working in previous version of MySQL, but from the last version it's no longer working. This is how it looks : test-lan:/var/lib/mysql/test3# ls -alh drwx-- 2 mysql mysql 4.0K 2010-08-30 13:28 . drwxr-xr-x 6 mysql mysql 4.0K 2010-08-30 13:29 .. lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.frm - /var/lib/mysql/test/blbl.frm lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.MYD - /var/lib/mysql/test/blbl.MYD lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.MYI - /var/lib/mysql/test/blbl.MYI -rw-rw 1 mysql mysql 65 2010-08-30 13:24 db.opt I really need those symlinks, is there a way to make them working like before ? ( old MySQL-server is fine ) Just out of curiosity. If you're using apparmor and this new directory is on another partion or mount point or anything, you might have to add a rule in apparmor's config for them... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Symlinks not working when pointing to another table.
I'm using Debian Lenny ( up to date ), so there is no app armor / selinux / grsec :/ ii mysql-client-5.0 5.0.51a-24+lenny4 MySQL database client binaries ii mysql-common 5.0.51a-24+lenny4 MySQL database common files ii mysql-server-5.0 5.0.51a-24+lenny4 MySQL database server binaries On 02/09/2010 16:18, Daevid Vincent wrote: -Original Message- From: Julien Lory [mailto:julien.l...@gmail.com] Sent: Thursday, September 02, 2010 12:31 PM To: mysql@lists.mysql.com Subject: Symlinks not working when pointing to another table. Hello, I've done lot of researches and tests but can't find any answer. I need to share one table between two db, those two db are in the same path ( /var/lib/mysql/db1 db2 ). I created symbolic links for db2 pointing to the table in db1. When I query the table from db2 I get this error : 'ERROR 1030 (HY000): Got error 140 from storage engine' I'm sure it was working in previous version of MySQL, but from the last version it's no longer working. This is how it looks : test-lan:/var/lib/mysql/test3# ls -alh drwx-- 2 mysql mysql 4.0K 2010-08-30 13:28 . drwxr-xr-x 6 mysql mysql 4.0K 2010-08-30 13:29 .. lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.frm - /var/lib/mysql/test/blbl.frm lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.MYD - /var/lib/mysql/test/blbl.MYD lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.MYI - /var/lib/mysql/test/blbl.MYI -rw-rw 1 mysql mysql 65 2010-08-30 13:24 db.opt I really need those symlinks, is there a way to make them working like before ? ( old MySQL-server is fine ) Just out of curiosity. If you're using apparmor and this new directory is on another partion or mount point or anything, you might have to add a rule in apparmor's config for them... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Does innodb have a temp table space?
On 9/2/2010 1:39 PM, neutron wrote: Hello Johan, Thanks for the reply. On Thu, Sep 2, 2010 at 3:34 AM, Johan De Meersman vegiv...@tuxera.be wrote: I suspect he is talking about the Temp Tablespace concept from Oracle, which is different from a temporary table or a memory table. MySQL will allocate a memory table for sort operation and the like, up until that table exceeds a preset limit, at which point it will automatically (and costly !) be converted to a disk table. == How to define the memory table limit? When the temp table is converted to a disk table, where is this disk table stored? In the same shared tablespace file if I don't use innodb_file_per_table? The automatically-converted tables produced by the system as part of SQL command processing start off as MEMORY tables unless they contain data that the MEMORY storage engine does not support. If they do contain unsupported data types or if they exceed the size of the smaller of --max-heap-table-size or --tmp-table-size, then the table is converted to a MYISAM table. The folder for temporary tables is controlled by the --tmpdir parameter. This behavior and the configuration variables I discussed are covered in more detail in these links: http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html http://dev.mysql.com/doc/refman/5.1/en/memory-storage-engine.html http://dev.mysql.com/doc/refman/5.1/en/temporary-files.html http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_tmpdir http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_max_heap_table_size http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_tmp_table_size -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Best method to keep totals
-Original Message- From: Jan Steinman [mailto:j...@bytesmiths.com] Sent: Thursday, September 02, 2010 1:52 PM To: mysql@lists.mysql.com Subject: RE: Best method to keep totals From: Jerry Schwartz je...@gii.co.jp IMNSHO, never store dynamic data in a field unless you absolutely have to. I agree, and yet, it's so darned handy if it's a calculation you need quite often. [JS] You run the risk of introducing inconsistencies. In FileMaker Pro (hold the boos, please :-) you can have calculated fields -- a pseudo field that holds references to other fields in an equation. [JS] I've used similar products. I suspect that the calculations are done on demand just as they would be with MySQL. To do something similar in MySQL, I've used views (which is somewhat clumsy), but is there a better way? Or is this necessarily part of domain knowledge programming in your interface language? [JS] Wouldn't the appropriate place for the calculation be in whatever queries retrieve the data, or in a store procedure that they share? That's basically the same as a calculated field. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Best method to keep totals
On 2 Sep 10, at 13:58, Jerry Schwartz wrote: From: Jan Steinman [mailto:j...@bytesmiths.com] From: Jerry Schwartz je...@gii.co.jp IMNSHO, never store dynamic data in a field unless you absolutely have to. ... To do something similar in MySQL, I've used views (which is somewhat clumsy), but is there a better way? [JS] Wouldn't the appropriate place for the calculation be... in a store procedure that they share? That's basically the same as a calculated field. I've never played with stored procedures. Can you point me to a tutorial? Or should I just Google for it? Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Symlinks not working when pointing to another table.
On 9/2/2010 3:31 PM, Julien Lory wrote: Hello, I've done lot of researches and tests but can't find any answer. I need to share one table between two db, those two db are in the same path ( /var/lib/mysql/db1 db2 ). I created symbolic links for db2 pointing to the table in db1. When I query the table from db2 I get this error : 'ERROR 1030 (HY000): Got error 140 from storage engine' I'm sure it was working in previous version of MySQL, but from the last version it's no longer working. This is how it looks : test-lan:/var/lib/mysql/test3# ls -alh drwx-- 2 mysql mysql 4.0K 2010-08-30 13:28 . drwxr-xr-x 6 mysql mysql 4.0K 2010-08-30 13:29 .. lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.frm - /var/lib/mysql/test/blbl.frm lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.MYD - /var/lib/mysql/test/blbl.MYD lrwxrwxrwx 1 mysql mysql 28 2010-08-30 13:28 blbl.MYI - /var/lib/mysql/test/blbl.MYI -rw-rw 1 mysql mysql 65 2010-08-30 13:24 db.opt I really need those symlinks, is there a way to make them working like before ? ( old MySQL-server is fine ) You cannot share one set of files (one table) between two different MySQL instances. That also means that you cannot share a table between the same instance by pretending it's something else by a symlink. The storage engines are designed with the premise that they have exclusive domain over the files they manage. Sharing is not part of their code. If you need to expose a table from within a different database, you must use a view. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
