Mysql Eating Big Chunks Of Memory
I'm running version 4.0.16 on solaris 8 as the backend for a web site
serving around 12 million pages impressions a month. The performance
of the server is monitored using orcollator.
I'm finding that the memory usage on the server remains pretty
constant for weeks at a time at around 300 Meg but then every once in
a while the size of the mysqld process jumps anywhere between another
50 and 250 Meg. It then stays at this new size until mysql is
restared (or jumps again). The memory increase appears to remain as
resident according to the output of top.
The site has a content management system which connects directly to
the production data base as well which while it is used daily has some
functionality which is only used as frequently as this problem occurs
making me suspect it is the culprit.
What I could really do with some advice on is
- What are the most likely causes of the memory loss.
- whether there are tools that allow you to see a breakdown of how the
current memory is being used by mysql, or even better monitor it over
time, in order to establish the source of the problem.
- And if there aren't any such tools how best to approach this.
View Complete Forum Thread with Replies
Related Forum Messages:
Writing Large Chunks Of Binary Data To MySQL With ODBC
I'm having a lot of trouble writing large chunks of binary data (tests are in the range of 16-512K, but we need support for large longblobs) to MySQL using ODBC. Database is local on a W2K system, but I have to support all modern Windows systems, and a variety of ODBC configurations. (I'll be testing against multiple ODBC databases soon - but development is against MySQL) I've been able to adapt some example code that executes a statement (SQLExecute) and uses SQLBindParameter to mark the longblob field as SQL_DATA_AT_EXEC. That works. However I really need to use a result set (opened for update), and to add new rows using SQLBulkOperation( hStmt, SQL_ADD) This works for the normally bound fields, but fails if I bind a variable using something like: Code:
View Replies !
Command Line Client Eating Up Chars
Hello, I'm using version 5.0.51 of MySQL on Windows XP. I'm having a bizarre problem regarding string values: After importing some values using a small PHP script an SQL sentence executed from the command line client such as this: .....
View Replies !
Out Of Memory; Check If Mysqld Or Some Other Process Uses All Available Memory;” Error
We have found that the MYSQL on all Email DB servers starts throwing “Out of memory; check if mysqld or some other process uses all available memory;” error. The error goes once we restart MySQL. But after a week again the same problem occurs. We have 4 GB of physical memory on the server but Mysql utilizes only up to 2.5 GB & starts throwing Out of memory error. Can someone from Database team look into it?
View Replies !
Memory Usage :: Swap Memory Not Released?
We have Mysql 5.0 running on RH linux. We have noticed that the swap memory increases but never gets released unless we reboot the server every 2-3 weeks. How would I know whats using this swap memory? Second question which I think maybe related to the first is that mysql is using a lot of memory when I do a top. Is there a way to limit the memory usage of mysql? I have a large table in mysql about 7 GB and I have 8 GB physical memory and swam is set to use 2GB.
View Replies !
Retreiving Large Results In Chunks
I'm running queries with MySql 4.0.17 that return thousands of records. Because I need to present them in GUI, I returieve the results in chunks using LIMIT, for example - get first 100, then the range 100-2000 and so on. The problem is as follows: in the first chunk, MySQL uses one strategy to fetch the results, and in the following chunks - a different strategy. This means that records from the subsequent queries might have records that already appeared in the first query or that some records will be left out. For performance issues it is a problem to add a unique secondary sorting criteria (like id) to the query. Is there a clean way to force MySQL to relate to the first (initial) query result set?
View Replies !
Retreiving Large Query Results In Chunks
I'm running queries with MySql 4.0.17 that return thousands of records. Because I need to present them in GUI, I returieve the results in chunks using LIMIT, for example - get first 100, then the range 100-2000 and so on. The problem is as follows: in the first chunk, MySQL uses one strategy to fetch the results, and in the following chunks - a different strategy. This means that records from the subsequent queries might have records that already appeared in the first query or that some records will be left out. For performance issues it is a problem to add a unique secondary sorting criteria (like id) to the query. Is there a clean way to force MySQL to relate to the first (initial) query result set?
View Replies !
Mysql On Flash-memory
I have a question about flash-memory lifespan. My database consists of 1 table, the table has 50 fields of 10 bytes each, thus each record will have 500bytes. There are 1000 records in the table, so roughly the table will have a size of 0.5MByte. The system will update the record sequentially, one record is updated per second. That is 3600 update per hour or 86400 per day. If I store this database on a 64MB flash-memory card then how long does the card last before wearing out.
View Replies !
MySQL 4.0.13 Memory Problem
I want to get your opinions on how to increase available/free memory and performance on a heavy volume database server. I have MySQL 4.0.13 running on RH 7.2 replicated to another RH 7.2 using same MySQL version. Recently our master database server (2 AMD Cpu + 2Gb memory + 2Gb swap space) started to suffer from memory outages because of heavy load. During day available free memory is changing from 200Mb to 5Mb and when available memory reaches to 5Mb MySQL starts to give 'Too many connections' messages. Db server is working with 45-70 query/second and more than 25,712 connection per hour. There are active 13-18 threads serving databases. To increase available free memory I've did following : 1- Optimized all tables 2- Removed unneccessary/old indexes 3- Moved unused databases to replication server 4- Increased key_buffer_size from 8Mb to 16Mb This changes helped a bit but still memory is a problem for MySQL. Average MySQL thread used to consume 100-120 Mb memory before changes, now it is consuming 60-70Mb per thread. What should I do to prevent 'too many connections' messages and have more memory available on database servers? Should I remove more indexes from tables? Should I increase key_buffer_size to 32Mb or more? Key_buffer_size doesn't look like a problem since key efficiency looks 100% most of the time.
View Replies !
Memory Alignment In MySQL
CREATE TABLE my table (coll_a INT NOT NULL, coll_b SMALLINT, coll_c SMALLINT, INDEX b_index (coll_b) ); Above is the table I will created. I declare coll_b and coll_c, which together will cose me 4 bytes in one row. Is it? What if I declare both of them as INT, will it cost me more memory? How about the memory needed for the index b_index? Not sure how memory alignment will affect MySQL.
View Replies !
MySQL And Memory Consumption
I've done a new install on a Freebsd server (5.1), using the latest ports of MySql 4.1. Everything seems to work great. However when I do a "top", I notice that Mysql is consuming around 321M of memory. This is is a virtually empty database with nothing loaded into it yet. Everything seems to be performing fine. Its just that I don't ever remember MySql using this much memory just after a new installation.
View Replies !
Mysql Is Not Using ENOUGH Memory On Linux
Running mysql 3.23.56-1.9 (yes , upgrading soon!) on red hat. Our database: - data is 5GB or so - has approx 2GB of indices We have 4GB of memory on the machine. At least 2.5GB of that is free+cached [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock set-variable = key_buffer=500M set-variable = myisam_sort_buffer_size = 64M set-variable = table_cache=600 set-variable = join_buffer=1M set-variable = thread_cache = 8 set-variable = sort_buffer =4M set-variable = record_buffer=4M set-variable = thread_cache_size = 64 set-variable = thread_concurrency=8 set-variable = max_allowed_packet = 16M set-variable = tmp_table_size = 64M But, no matter how much memory I set for key_buffer in /etc/my.cnf, mysql seems to use very little of it after running millions of queries: 14951 mysql 15 0 226M 221M 1308 S 3.3 5.5 89:55 0 mysqld
View Replies !
MySQL Client Ran Out Of Memory
I'm using a mysql server with my own TCP/IP server on the same FreeBSD machine. MySQL stores a users database, like logins and passwords. It works fine for a couple of days, but after that my simple queries with one row of result stops working. It says "MySQL Client ran out of memory". If I connect to mysql using other tools or simply restart my server - it works fine again under the same queries. So please don't support I use a huge amount of data, whole database can be selected in one query under normal conditions, its pretty small. My Server Info: sr10# mysql --version mysql Ver 14.7 Distrib 4.1.16, for portbld-freebsd5.4 (i386) using 4.3
View Replies !
MySQL With C++ Causes Memory Leak?
The garbage collector tells me it's using the same amount of memory, but the Task Manager shows it using 4-10KB more each second. Any ideas? If i remove all references to the actual datareader then the mem usage is stable, but as soon as it starts to return records then the leak appears. Code:
View Replies !
MySQL Memory Leak
I appear to have a memory leak problem caused by mysql I currently have 10 instances of /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file ... running but not actually doing anything. These have been running since the 9th (last time I restarted mysql) There are 2 that if you trace process on them are doing something... Process 565 has a subprocess of 567 and then 567 has the rest as subprocesses. But none of those subprocesses are running mysql commands - or doing anything other than eating memory. When I restart the server, applications are using 0.5Gb Memory then that creeps up to currently using 1.1Gb. So what are those dead process and why are they hanging around?
View Replies !
Compiled Mysql++ Memory Leak
I compiled mysql++ with Visual Studio 2003, the problem is I got i memory leak when i debug the application, the leak occurs even when i only use the mysqlpp::Connection class. Note I linked mysqlpp with mysql 5.0
View Replies !
MySQL 4.0.13 Memory Problem Under Heavy Load
How to increase available/free memory and performance on a heavy volume database server. I have MySQL 4.0.13 running on RH 7.2 replicated to another RH 7.2 using same MySQL version. Recently our master database server (2 AMD Cpu + 2Gb memory + 2Gb swap space) started to suffer from memory outages because of heavy load. During day available free memory is changing from 200Mb to 5Mb and when available memory reaches to 5Mb MySQL starts to give 'Too many connections' messages. Db server is working with 45-70 query/second and more than 25,712 connection per hour. There are active 10-13 threads serving databases. To increase available free memory I've did the following : 1- Optimized all tables 2- Removed unneccessary/old indexes 3- Moved unused databases to replication server 4- Increased key_buffer_size from 8Mb to 128Mb 5- Have increased max_connection from 100 to 150 6- Have increased thread_cache to 5 This changes helped a bit but still memory is a problem for MySQL. What should I do to prevent 'too many connections' messages and have more memory available on database servers? Should I remove more indexes from tables? Should I increase key_buffer_size to 256Mb or more? Key_buffer_size doesn't look like a problem since key efficiency looks 100% most of the time.
View Replies !
MySQL 4.0.13 Memory Problem On Heavy Load
I want to get your opinions on how to increase available/free memory and performance on a heavy volume database server. I have MySQL 4.0.13 running on RH 7.2 replicated to another RH 7.2 using same MySQL version. Recently our master database server (2 AMD Cpu + 2Gb memory + 2Gb swap space) started to suffer from memory outages because of heavy load. During day available free memory is changing from 200Mb to 5Mb and when available memory reaches to 5Mb MySQL starts to give 'Too many connections' messages. Db server is working with 45-70 query/second and more than 25,712 connection per hour. There are active 13-18 threads serving databases. To increase available free memory I've did following : 1- Optimized all tables 2- Removed unneccessary/old indexes 3- Moved unused databases to replication server 4- Increased key_buffer_size from 8Mb to 16Mb This changes helped a bit but still memory is a problem for MySQL. Average MySQL thread used to consume 100-120 Mb memory before changes, now it is consuming 60-70Mb per thread. What should I do to prevent 'too many connections' messages and have more memory available on database servers? Should I remove more indexes from tables? Should I increase key_buffer_size to 32Mb or more? Key_buffer_size doesn't look like a problem since key efficiency looks 100% most of the time.
View Replies !
Memory Allocation Limit On Mysql 4.0.16 Binary For AIX 5.1
I downloaded the mysql 4.0.16 binary for AIX 5.1 from Mysql.com website. How much memory can this binary's mysqld handle? Is it a 32-bit or 64-bit process? It seems that it can only allocate a maximum of 2GB of memeory (by setting the maxdata as suggested on the AIX note in the documentation). I am using mainly innodb tables. Occasionaly, I have encountered "out of memory error". We have 4GB of memeory on our RS6000 (AIX 5.1). It is used as a dedicated mysql server. Is there a way I could allocate more memory for mysqld?
View Replies !
MySQL Consuming Lots Of Memory During Transactions W/ Many Statements
I am having a problem with MySQL consuming a lot of memory and eventually throwing an Out of Memory error and restarting itself. The symptoms are that swap usage continues to rise until some breaking point. The application is a typical web application w/ 2 web servers running Apache/Tomcat connecting to a dedicated DB server running only MySQL. This seems to occur as a result of running many statements in a single transaction, both against InnoDB tables and MyISAM tables. In one case, I'm writing all user actions to an audit table (MyISAM), all from a single application thread doing approximately 5K inserts (as single INSERTs on a single connection) every few seconds. In the other, I'm doing a variety of select/update/insert/delete statements against InnoDB tables, but again totaling several thousand in one transaction. Both of these problems occur even when there is relatively low activity elsewhere on the system. Code:
View Replies !
Command That Lowers The Amount Of Memory That The Process MySQL?
Someone knows a command that lowers the amount of memory that the process MySQL (mysqld, mysqld-nt...) uses??? My work is to execute many INSERT instructions based on SELECT instructions. When the execution starts, all be fine, but the time passes and this process "mysqld-nt" start grow and grow and then the performance be really bad. I tried to use "FLASH" and "RESET" between the INSERTS but no sucess. Someone knows?
View Replies !
Error: MySQL Client Ran Out Of Memory (was "Urgently Need Help")
I'm having a big trouble with this query select *, UNIX_TIMESTAMP(date_submitted) as postedon, UNIX_TIMESTAMP(DATE_ADD(date_submitted, INTERVAL auction_period DAY)) as expiry from bidsnbuys_products where approved='yes' It works before but lately we've got a huge records and if our customers search a product it will give them an error, I even tested by myself and the error is MySQL client ran out of memory Is there a better mysql query with my query above?
View Replies !
Memory Use?
I'm looking for minimal memory use on a Windows XP machine. Which version would be best for this? And can anyone tell me if there's any favored links to MySQL tutorials?
View Replies !
Out Of Memory
I'm running 5.0.41 on a dual quad core xeon beast of a machine that has 8 gigs of ram. The OS is FreeBSD 6.2 STABLE. When I run top I MySQL is only using 512 mb of ram. No matter what I change in the my.cnf file, I can't seem to increase that. Can someone take a look at my my.cnf file and give me some suggestions? Here is the contents of my.cnf: # Example MySQL config file for very large systems. # # This is for a large system with memory of 1G-2G where the system runs mainly # MySQL. # # You can copy this file to # /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is /var/db/mysql) or # ~/.my.cnf to set user-specific options. # # In this file, you can use all long options that a program supports. # If you want to know which options a program supports, run the program # with the "--help" option. # The following options will be passed to all MySQL clients [client] #password= your_password port= 3306 socket= /tmp/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port= 3306 socket= /tmp/mysql.sock skip-locking interactive_timeout = 180 wait_timeout = 180 key_buffer = 512M max_allowed_packet = 2M table_cache = 1024 sort_buffer_size = 4M read_buffer_size = 4M read_rnd_buffer_size = 16M myisam_sort_buffer_size = 128M thread_cache_size = 16 query_cache_size = 256M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8
View Replies !
Max Memory
how much memory in a worst case mysql (a default install) consumes? I have programs blowing out of memory in a 750MB laptop and can't find the culprit.
View Replies !
Memory Error
I'm running mysql 4.1 on Intel 4 Linux gcc 2.4. Have found serious problem with memory leak - can't call calloc repeatedly without a seg fault. problem comes after a connect call has been made.
View Replies !
Memory Leaks
I am using the mysqlclient library 4.0.14 on win2k. It works fine but there are memory leaks I have no solutions for. I have tried this: int _tmain(int argc, TCHAR* argv[], TCHAR* envp[]) { MYSQL MySQL; mysql_init(&MySQL); mysql_close(&MySQL); mysql_thread_end(); return 0; } .... but in the debug mode I get results like this: Detected memory leaks! Dumping objects -> {52} normal block at 0x00974088, 556 bytes long.....
View Replies !
My.cnf Memory Specifications
In my my.cnf file, these are some of the things that are specified in [mysqld]: set-variable = key_buffer=256M set-variable = table_cache=64 set-variable = sort_buffer=512K set-variable = max_connections=1000 set-variable = thread_concurrency=10 set-variable = innodb_buffer_pool_size=40M set-variable = innodb_additional_mem_pool_size=40M On FreeBSD, I only have one process running which makes this information pretty straight forward. On my linux boxes, there are many different processes running (on one box, there is 31). Does this mean that *each* of those 31 processes attempts to allocate the above amount of resources, or that the above resources are being shared between the 31 processes?
View Replies !
Mysqldump Out Of Memory
I have some some large tables, 30 mil rows, some rows has long blogs size (300M). I used mysqldump -q to dump it, but i still get out of memory error.
View Replies !
Client Ran Out Of Memory
i got an error while my site gets loaded Warning: mysql_query() [function.mysql-query]: Unable to save result set in /path../includes/mysql.class.php on line 39 Error in Selection MySQL client ran out of memory My site didnt get loaded and was stuck by displaying this error .,
View Replies !
Out Of Memory Errors
Several times a day we're seeing in the error log: 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 can't find any documentation to explain describing what 'ulimit' is or exactly where to find it. Where using version 5.0.15-nt-max.
View Replies !
My.cnf File And RAM Memory
Our company trying to migrate to mySQL 5.0 and I'm having some problems setting it up on our first new server. System: 8 GB Ram, Dual-core. OS: Red Hat Enterprise Linux 4 I'm trying to change the directory the database is stored. I understand this is the datadir value in the my.cnf. This file doesn't exists so I found the my-large.cnf, my-huge.cnf, etc. First I assume I should use the my-hug.cnf since we have over 2 GB RAM. Second I can't find a datadir variable in any of the optional .cnf files to chage. How should I specify the directory change Also do I change all the /var/lib/mysql/ to the new directory or the one value.
View Replies !
Out Of Memory After Mysqldump
Using all InnoDB tables v5.0.16 under Linux, Get this message: [ERROR] 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 It only happens after all databases have been dumped from the instance. Any ideas?
View Replies !
Memory Leak?
I am writing a program using the C API which slows down then finally crashes because there is a memory leak. I have determined that the memory leak happens because the API function mysql_free_result() doesn't actually free the result. The memory usage immediately before and after the call to mysql_free_result is identical. I am using MySQL 5.0 on Ubuntu Linux. In order to reproduce the error, compile the following C program using either gcc or g++ using the following commands: Code:
View Replies !
Memory Leak
I have developed a high intensity database driven smpp gateway and clients the problem is that I am experiancing a memory leak...My app starts out using about 0.1% memory and after a couple of hours sits at 10%I think it has something to do with the mysql_store_results() function
View Replies !
Server Out Of Memory
After a few requests, my server answers to me 'out of memory (Needed 1048548)', and i need to restart the server.I changed the variable 'query_cache_limit' to 20M, but the result is the same.
View Replies !
Can't Allocate Memory
I'm using mysql 4.1.12-standard-log on a HP-UX11.11 with 8G Memory. I'm using the InnoDB storage system. If I try to configure mysql to use more than 700M, I get the following error: InnoDB: Error: cannot allocate 943734784 bytes of memory with malloc! Total allocated memory by InnoDB 30553264 bytes. Operating system errno: 12 Check if you should increase the swap file or ulimits of your operating system. I check the ulimits and is set to unlimited. There is over 2G free swap memory. Any ideas on how to avoid this problem?
View Replies !
Memory Issues
We have recently installed beta version of our application on IBM - Pentium Xeon - dual processor, 1 GB RAM, 80 GB HDD on Fedora Core 3 OS. The kernel version which I currently have is: 2.6.9-1.667 and Mysql Server version: 4.0.20-standard. I have noticed that mysql processes (using 'top' command) take up substantial amount of memory over period of time. Please take a look at mysql processes output of 'top' command below: Code:
View Replies !
Memory Tables
Memory tables get wiped when the server restarts so, for those with experience of memory tables - what happens then? How are the tables rebuilt?
View Replies !
Using Up Memory Or Resources Or Both Or Neither?
I am doing a call where I go into a directory and open up all the files found and call my database each time to check for either the existence or lack of.. The database gets called each time with a $DBlink = new DB; and opens up a connection to the database each time. The directory originally had 200000 files in it and the program kept halting up. I split the directory up into 10000 file chunks but it still halts up after a while. My question is... am I using up all my memory or am I using up all my resources or none of the above and it's something else? I do a mysql_free_result after every query and I do a mysql_close on the link after I'm done.
View Replies !
Cannot Allocate Memory
This error suddenly showed up about 3 days ago and it has steadily gotten worse to the point that our log files are now filled with this error and the site is effectively dysfunctional. I am seeing the following show up in the PHP logs:mysql_connect() [function.mysql-connect]: Can't create a new thread (errno 12); if you are not out of available m Any idea what could be causing it and how I could fix it? This never happened when we had 1 GB of RAM installed but now it is suddenly happening after doubling the RAM to 2 GB about a week after adding that new RAM.
View Replies !
Lowering Memory Usage?
I was wondering if there's any way I can lower MySQL's = memory useage? It often uses more than 50000kb which I consider to much.. I'm guessing I should play with my.cnf or whatever it's called (located at = C:/ and windows calls it cardnumber or something)...
View Replies !
Lowering Memory Usage
there's any way I can lower MySQL's = memory useage? It often uses more than 50000kb which I consider to much.. I'm guessing I should play with my.cnf or whatever it's called (located at = C:/ and windows calls it cardnumber or something)
View Replies !
Memory Allocation Limit
I downloaded the mysql 4.0.16 binary for AIX 5.1 from Mysql.com website. How much memory can this binary's mysqld handle? Is it a 32-bit or 64-bit process? It seems that it can only allocate a maximum of 2GB of memeory (by setting the maxdata as suggested on the AIX note in the documentation). I am using mainly innodb tables. Occasionaly, I have encountered "out of memory error". We have 4GB of memeory on our RS6000 (AIX 5.1). It is used as a dedicated mysql server. Is there a way I could allocate more memory for mysqld?
View Replies !
Libmysql.dll V5.1 Memory Leak
In working with large files and libmysql.dll (not libmysqld.dll) I am discovering that resources are not released when MySQL_Close() is called for the last thread. My Application reads from one table and writes to another using a single TRANSACTION. The target table size is about 600MB. Memory increases steadily up to about 1.2 GB as this process progresses, and is only released when the dll is unloaded from memory by closing the app. I have triple checked my application and it is not responsible. What could the dll be holding on to and how can I get it to release that memory if not a memory leak?
View Replies !
|
TRACKER
