quick question on mysql memory usage
Hi, My understanding is that the memory utilization of mysql can be calculated roughly using the formula like: (All global memory related server variables + max_connections * session memory related server variables) As I noticed that most global variables like key_buffer_size, innodb_buffer_szie will not return back the memory to OS since they are using (mt)malloc/free, My question is: will mysql return the memory to the OS after closing some connections? Such as, when one connection executing a query which located sort buffer, and created tempory table, after complete the query or close the query, will the memory used by this query release to OS? Thanks in advance for your info. Regards, jenny
question on mysql proxy's load balancing
Hi, When I read Jk's document on MySQL proxy: http://jan.kneschke.de/projects/mysql/mysql-proxy/ In Load balancing part, he mentioned that: We use SQF (shortest queue first) to distribute the load across the backends equally. Each backend will get the same number of connections. My understanding is that if there are 100 concurrent client connecting to the myql proxy with two backend mysql server(master and slave), the mysql proxy will send 50 connections to the master server, and the other 50 connections to the slave server, is my understanding correct? But I did observed very different in the simple mysqlslap test: mysqlslap -hproxy-host -P4040 -q ./simple.sql -c 100 -i ( ./mysql-proxy --proxy-backend-addresses=master-host:3306 --proxy-backend-addresses=slave-host:3306) on the master server, from the processlist, I saw Threads-creates arround 166, but threads running/connected only 12; on the slave server, Threads-creates arround 166, but threads running/connected only 1. Does anyone can please explain why this happening? (And the mysqlslap test result was very poor compared to the same test without mysql proxy and connecting to only one mysql server instance.) Thanks in advance for your information.
quick question on innodb_log_file_size
Hi, According to the reference manual, it was said that the combined lnnodb log file size is less than 4G on 32-bit system. But I'm running on my 64-bit solaris, I still got the error complaining the innodb log file 4G for my 64-bit MySQL. So I'm wondering is this 4G limit apply on 64-bit system as well? Why? Thanks in advance for your information. Jenny
best practice MySQl backup onto tape
Hi, I'm planing to do hot backup MySQL(innodb db) onto tape drive, and propose the following solution/script: # delete old mysql dumps rm -r -r /backup/mysql mkdir /backup/mysql # Dump all mysql databases mysqldump --all-databases -single-transaction --flush-logs all_databases.sql mysqldump --database=mysql --lock-all-tables --flush-logs system.sql # Do backup tar cvf /dev/rmt/0 /backup/mysql My question is: is there any other better solutions(including commercial solutions) to do hot MySQL backup to tape that can provide better performance, or can backup directly to the tape(no need to dump to disk first), etc. comparing to use mysqldump. Thanks in advance for your information, or comment on the above solution. Regards, Jenny
Re: best practice MySQl backup onto tape
Hi, Keith, In my case, it is just single MySQL server(no replication). Thanks for your info, I'll check with mk-parallel-dump tools, since the speed of backup is important considering the database is locked for the duration of the backup. Regards, Jenny On 12/14/07, B. Keith Murphy [EMAIL PROTECTED] wrote: Jenny Chen wrote: Hi, I'm planing to do hot backup MySQL(innodb db) onto tape drive, and propose the following solution/script: # delete old mysql dumps rm -r -r /backup/mysql mkdir /backup/mysql # Dump all mysql databases mysqldump --all-databases -single-transaction --flush-logs all_databases.sql mysqldump --database=mysql --lock-all-tables --flush-logs system.sql # Do backup tar cvf /dev/rmt/0 /backup/mysql My question is: is there any other better solutions(including commercial solutions) to do hot MySQL backup to tape that can provide better performance, or can backup directly to the tape(no need to dump to disk first), etc. comparing to use mysqldump. Thanks in advance for your information, or comment on the above solution. Regards, Jenny You didn't specify if this was a master or slave. I certainly wouldn't dump off a master server. It will lock the database for the duration of the backup. One of the ways we do backups is to do an lvmsnapshot of the data partition on the slave and then just mount and rsync the snaphsot of the data off to the backup server. Inefficient for space, but would be vastly faster restoring a backup like this then running a restore from a mysldump. You will still need to tar this take it acceptable for tape. Another option would be to replace mysqldump with Baron Schwartz's mk-parallel-dump and mk-parallel-restore tools. They are faster than a traditional dump/restore and are much more compact. These tools (and a number of others) are located at maatkit.sourceforge.net.net. Hope that helps. Keith -- B. Keith Murphy Paragon Consulting Services http://www.paragon-cs.com 850-637-3877
Question on mysql-test-run sp failed
Hi, Recently, I built mysql5.0 on Solaris 10 amd64, but when running make test, the bundled sp test failed with the following error: mysqltest: At line 1527: query 'call fib(20)' failed: 1436: Thread stack overrun: 186944 bytes used of a 262144 byte stack, and 81920 bytes needed. Use 'mysqld -O thread_stack=#' to specify a bigger stack. (the last lines may be the most important ones) Looking at the test source code, I saw the following lines: # Enable recursion set @@max_sp_recursion_depth= 20| # Minimum test: recursion of 3 levels insert into fib values (0), (1)| call fib(3)| select * from fib order by f asc| delete from fib| # Original test: 20 levels (may run into memory limits!) insert into fib values (0), (1)| call fib(20)| When calling a recursive procedure many times with a limited stack, it's quite possible to overrun the stack, and looks like that authors of the test wanted to note exactly this - could you please let me know if my understanding is correct, and let me know how to increase the stack limit for the mysqld process? Thanks much for your information in advance. Regards, Jenny
how to start mysql instanct manager on Solaris platform?
Hi, I'm trying to start mysql with mysql IM, by following the steps as bellow: 1. change the use_mysqld_safe = 0 in the mysql.server 2.set /etc/my.cnf as: [client] #password = your_password port= 3306 socket = /tmp/mysql.sock # Here follows entries for some specific programs [manager] default-mysqld-path = /usr/local/mysql/bin/mysqld socket=/tmp/manager.sock pid-file=/tmp/manager.pid monitoring-interval = 2 port = 1999 bind-address = 209.128.126.155 # The MySQL server [mysqld] mysqld-path=/usr/local/mysql5018_mmap_2/bin/mysqld socket=/tmp/mysql.sock port=3307 server_id=1 skip-stack-trace core-file skip-bdb log-bin log-error log=mylog log-slow-queries [mysqld2] port=3308 server_id=2 mysqld-path= /tests/jc_data/mysql- standard-5.0.15-solaris10-x86_64/bin/mysqld socket = /tmp/mysql.sock5 pid-file = /tmp/hostname.pid5 datadir= /tests/jc_data/mysql-standard-5.0.15-solaris10-x86_64/data log-bin log=/tmp/fordel.log 3. Then I start mysql by running mysql.server start 4. I noticed that a few problems: a. only the first mysqld(under /usr/local/mysql5018_mmap_2/bin/mysqld)started, the mysqld2 didn't start 5. If I run the client from /usr/local/mysql/bin/mysql mysql show instances It complained wrong sysntax,that make me believe that commands for MySQL IM accepts are not available for some reason. Is there anything wrong with my above steps causing the problems? Thanks in advance for your information! Jenny
How to port Oracle anonymous PL/SQL block to MySQL
Hi, As I know that MySQL supports stored procedure from 5.0. Does anyone have the info on if there is any plan to support anonymous PL/SQL block in MySQL in later MySQL version? So that when port Oracle anonymous PL/SQL block, it doesn't need to change to procedure/function? Thanks, Jenny
MySQL slow query log
Hi, Could anyone explain what might be the possible reasons that in the slow query log(running read-only queries) the most very slow queries(taking 200-300sec) were the queries: 1. create table ... type = MyISAM 2. show slave status Thanks, Jenny
can't connect to the MySQL server from remote machine?
Hi, I'm trying to connect to the MySQL server on the RedHat AS 4 from a remote Solaris SPARC box. When I run the command: mysql -h HOSTNAME I got the error as ERROR 2003 (HY000): Can't connect to MySQL server on 'HOSTNAME' (145) Could anyone please explain what the mean of the error message and how to solve it? Thanks, Jenny
Re: can't connect to the MySQL server from remote machine?
Yes, I did try both hostname and IP, but can't work although I can ping to the two boxes from each other. Just wondering if it is because of the port(3306) got disabled?? Thanks, Jenny On 12/2/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Jenny Chen [EMAIL PROTECTED] wrote on 12/02/2005 12:27:33 PM: Hi, I'm trying to connect to the MySQL server on the RedHat AS 4 from a remote Solaris SPARC box. When I run the command: mysql -h HOSTNAME I got the error as ERROR 2003 (HY000): Can't connect to MySQL server on 'HOSTNAME' (145) Could anyone please explain what the mean of the error message and how to solve it? Thanks, Jenny The value after the -h must either be a valid hostname in your organization or an IP address. If you can ping the server you want to reach by name, that's the name to use. Otherwize use the IP address of the machine you are trying to reach. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: can't connect to the MySQL server from remote machine?
Hi, I tried with telnet localhost 3306 locally, and got the message as: Connected to localhost.localdomain (127.0.0.1). Escape character is '^]'. = 5.0.16-standard!QS//?.e,7~9iw;]E!r=Connection closed by foreign host. What is this meaning? Thanks, Jenny On 12/2/05, Fernando Antunes [EMAIL PROTECTED] wrote: To test if the 3306 is listening, try this command from client : telnet hostname 3306 If MySQL return some string, it´s there and listening If not, two possibilities : 1) A firewall between or on client and server 2) MYSQL is stopped or listening in a different port. What is the version of your MYSQL ? On 12/2/05, Jenny Chen [EMAIL PROTECTED] wrote: Yes, I did try both hostname and IP, but can't work although I can ping to the two boxes from each other. Just wondering if it is because of the port(3306) got disabled?? Thanks, Jenny On 12/2/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Jenny Chen [EMAIL PROTECTED] wrote on 12/02/2005 12:27:33 PM: Hi, I'm trying to connect to the MySQL server on the RedHat AS 4 from a remote Solaris SPARC box. When I run the command: mysql -h HOSTNAME I got the error as ERROR 2003 (HY000): Can't connect to MySQL server on 'HOSTNAME' (145) Could anyone please explain what the mean of the error message and how to solve it? Thanks, Jenny The value after the -h must either be a valid hostname in your organization or an IP address. If you can ping the server you want to reach by name, that's the name to use. Otherwize use the IP address of the machine you are trying to reach. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
any alias for MySQL cluster question?
Hi, I'd ask some questions on MySQL cluster. Does anyone know if there is any email alias for the cluster questions? Thanks, Jenny
A few questions about triggers in MySQL 5
Hi, Does the current latest MySQL 5.0 support: 1. column based triggers 2. trigger restriction 3. multiple triggers of same type of the table 4. triggers reference another table And any information on in which release they will be supported? Thanks, Jenny
