MySQL memory problem
Hi all, I am facing a big problem and see if anyone know how to solve it. I am using freebsd 4.4 with mysql-4.0.12. The database server has 2G RAM in it. Here is the content of my.cnf. [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock port=3306 set-variable = thread_cache_size=550 set-variable = table_cache=4200 set-variable = key_buffer_size=500M set-variable = record_buffer=10 set-variable = max_connect_errors=99 set-variable = max_connections=750 set-variable = tmp_table_size=10M transaction-isolation = READ-UNCOMMITTED skip-name-resolve skip-locking skip-innodb set-variable = query_cache_type=1 set-variable = query_cache_size=52428800 log-slow-queries memlock set-variable = max_binlog_size=10M log-bin=/var/lib/mysql/auth1-1-log-bin log-bin-index=/var/lib/mysql/auth1-1-log-bin.index server-id=1 [mysql.server] user=mysql [safe_mysqld] err-log=/var/lib/mysql/mysqld.log pid-file=/var/lib/mysql/mysqld.pid The server is running completely fine before. Today I found that one table is corrupted without any reason. MySQL cannot show the table when I use that database. Then I ran myisamchk on that table. Here is my command. myisamchk -f -o -v -q -O key_buffer_size=800M -O sort_buffer_size=800M -O read_buffer_size=80M -O write_buffer_size=80M kaming_table.* The table 'kaming_table' has 1G MYD data file and 300M MYI index file. Then after the checking, the table seems being fixed and then I start the MySQL again. I can run query in that table after that. Then suddenly I saw this error message keeps coming in the mysqld.log Error Message: Out of memory; Check if mysqld or some other process uses all available memory. If not you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space I use 'top' to see how many RAM the mysql using. Just about 500M memory to using at that time and I still have at least 1G RAM in inactive. I have no idea what problem is. And I didn't see any error message from the system I couldn't login to mysql. I just saw that it use a lot of CPU time and so I strace the mysql process and I found that the mysql process kept output Cannot allocate memory. At last, I tried to upgrade the mysql software to 4.0.13 and the problem still exist... Do anyone have idea about that. Really need help. Thanks a lot. Kaming. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL memory problem
Hi Jeremy, here is the value when I typed 'ulimit -a'. core file size (blocks) unlimited data seg size (kbytes) 524288 file size (blocks) unlimited max locked memory (kbytes) unlimited max memory size (kbytes)unlimited open files 32768 pipe size (512 bytes) 1 stack size (kbytes) 65536 cpu time (seconds) unlimited max user processes 8211 virtual memory (kbytes) 589824 Thanks a lot for your help. Kaming. Jeremy Zawodny wrote: On Tue, Jun 10, 2003 at 07:37:19PM +0800, Kaming wrote: Then after the checking, the table seems being fixed and then I start the MySQL again. I can run query in that table after that. Then suddenly I saw this error message keeps coming in the mysqld.log Error Message: Out of memory; Check if mysqld or some other process uses all available memory. If not you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space So, what does `ulimit' tell you? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 7 days, processed 223,310,311 queries (351/sec. avg) -- 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]
Innodb memory problem
Hi all, I have a problem about the innodb memory usage. The following is my config file for mysql and the version I am using is 4.0.12 in FreeBSD 4.7 stable. my.cnf [mysqld] port=3306 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock set-variable = thread_cache_size=700 set-variable = table_cache=1000 set-variable = key_buffer_size=16M set-variable = max_connect_errors=99 set-variable = max_connections=850 skip-name-resolve skip-locking memlock ### INNODB CONFIG ### innodb_data_home_dir = /var/lib/mysql/innodbdata innodb_data_file_path = datafile:4096M:autoextend set-variable = innodb_buffer_pool_size=24M set-variable = innodb_additional_mem_pool_size=8M innodb_log_group_home_dir = /var/lib/mysql/innodblog innodb_log_arch_dir = /var/lib/mysql/innodblog set-variable = innodb_log_file_size=10M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=0 # [mysql.server] user=mysql basedir=/usr/local/site/mysql [safe_mysqld] err-log=/var/lib/mysql/mysqld.log pid-file=/var/lib/mysql/mysqld.pid When I start the mysqld, the memory usage is about 85M. Then the mysqld will use more and more memory and doesn't release. It will use all the memory and the performance will be dropped when it starts to use swap space... Do anyone know how to calculate the memory mysqld use when it starts? And also any method can be used to limit the memory usage for innodb? I don't have memory problem when using MyISAM before... The memory usage of mysqld reachs certain level and will not continue to grow. Thanks a lot. Kaming. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql permission problem
Hi all, I am using mysql version 3.23.47 on RedHat linux 7.2. I found that the permission I have granted before, for example, login the mysql server and execute 'grant select on *.* to [EMAIL PROTECTED] identified by test; flush privileges'. Then I can access to the mysql server from the client with IP 192.168.10.1. but I received the access denied error after a few days later I have checked that there is a entry in the 'user' table of 'mysql' database in the mysql server. So I run flush privileges again. After that, I can access to the mysql server from the client with IP 192.168.10.1 again. Do anyone face this problem before?? Many thanks. Kaming. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
database permission
Hi all, I have a problem about the access permission of mysql. I found that the mysql client will be denied by the mysql server even I have granted the permission already. The mysql client has already run and can connect to the mysql server before... I am not sure what the problem is and so I just run flush privileges again and everything work fine after that. I have repeated this action several times I tried to use myisamchk to check the mysql database or even delete and recreate it again But the problem still exist after I run the mysql server for a period of time... the mysql client will be denied to connect the server again... Did anyone faced that problem before?? Can anyone please give me some advices about that?? Many thanks. Kaming. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
log rotation
Hi all, Do anyone know how can I rotate the error log of mysql?? I just know that flush logs can do but I am also doing replication in that mysql server. I hope to rotate the error log but not affect the binlog If anyone know about that. Please give me some advice. Many thanks. Kaming. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
C programming to connect mysql
Hi all, Do anyone know that how to write a C programming to connect to mysql?? Many thanks. Kaming. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
grant permission
Hi all, Do anyone have this experience before?? I have granted the permission for a machine to access mysql databases several times and then flush privileges. I use perl DBI module to connect to databases through Web. But I found that after a day or even several hours later. I found that the apache log said that the DBI cannot connect to the mysql. It is strange cause I can connect to the mysql from the mysql client of that machine So I set another machine with the same configuration of hardware, system and program setting and I found that this problem didn't occur If anyone know about that. Can you please advise me please. Many thanks. Kaming. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
