Memory Maxed Out And Slow Performance
We have two main query types running against a table of some 2 million
rows and have gotten query response down to well under a second by
using the right indexes.
Problem is that we are running an advertising campaign that brings a
concentrated block of users to the site. When this happens one of the
queries which relies on a particluar index comes severely of the rails
and can take up to 2 minutes filling the slow query log for 15 to 20
minutes. The other query type never appears in the slow log at these
times.
Can anyone explain why this is happening?
The server has 2Gb of ram and a key_buffer of 1Gb. When the problems
occur mysql uses all of the key_buffer but the total value of the MYI
files is under 250Mb. What is in memory - thread related buffers?
It seems as though the index required for the query is not in memory
and the disks are coming into play. Why is only one index affected?
I would really appreciate some pointers, what can I check to pinpoint
the problem and what settings should I implement to preserve
performance?
Front end is 4 cold fusion web servers using persistent connections.
max_connections is currently at 100.
View Complete Forum Thread with Replies
Related Forum Messages:
Slow Performance
On my index.php page, I have a simple query that checks the session_id against a table where I store other session_id'. If it's not there, it records it (unique hit). If it's there, it doesn't record it (not a unique hit.) This usually goes off without a hitch, and every month or so I empty the table. Right now I only have about 2500 rows, and it's taking forever to load the page. Is there something possibly server related that could be causing this? My host charges an arm and a leg just to see if there's something wrong if I bring up an issue, so I'd like some insight as to whether there's a commonly known server-side issue that can bog down performance.
View Replies !
Server Performance Slow
I have designed a service on my site which requires about 30 -40 SQL queries per page to run. Everything works without problems apart from the slowness. The page will load very quickly until it gets to the first query, then it pauses..... and carries on slowly producing the results. (When I say slowly, I mean relative slow compared to the speed this forum loads for instance)All the other web apps with mySQL work without slowing down, but they don't take up as much resources. Even the VB board is very fast. I'm running a 2.8Ghz 1GB Win2003 server, so I doubt that is a problem. Is there a guide to optimizing mySQL server or a key "setting" I should be changing?
View Replies !
Slow Performance While Mysqldump
i have a large database with many tables. I was using it fine on HP Proliant server (4gb ram). Now i did the same database to a mirror HP Proliant (2Gb ram). After recovery the database, query is very very slower then Machine 1, does it damage the index while dumping? How do you recover indexes if some way it damage and cause the slow performance? Machine 1: >mysqldump --user=root --password=root -R --ignore-table=database.table0 --ignore-table=database.able1 database > pathsql.sql Machine 2: >mysql --user=root --password=root database < pathsql.sql
View Replies !
Slow Performance After Falling Back To MySQL 3
Yesterday I have upgraded my linux box with a pair of Xeon 1G CPU and reinstalled everything (it was a sinle Xeon 700). The default RH9 installation comes with MySQL 3.23.54. After the reinstallation I found the machine was not as responsive as before. (It's not very slow, but some lagging is noticed) I was running Mysql-max 4.0.11 and the performance was ok, except the slowness during the peak hours, which was regarded as normal. I would like to know the differences between Mysql 3 and 4. Is the new version making a significant jump in performance? I also noticed the system eats up memory faster than before. With the old setting the system never used up 60% of physical memory, even at extremely high loading (30+). Now it easily used up 70% of memory. Code:
View Replies !
Slow Performance :: Navicat To Import Tables From An ODBC Connection
have been using Navicat to import tables from an ODBC connection I have. The import goes extremely fast but once the data is locally stored and I try to manipulate it by joining tables, the program goes slower and sometimes even crashes it. If the same data is however imported into a table in MS Access, and a query is created it runs fine. I would like to know if there is a way to improve the performance of my MySQL database as the reason I was using it is because of its scalability. I was wondering if there was something I was doing wrong or could do better. The Server the data is downloaded to right now is has the following specs: Windows XP Pro Intel Core 2 CPU 2.13 Ghz 2 GB RAM
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 !
Large Table Performance Problem With MYSQL Performance
I am doing some benchmarking of an application using MYSQL under Windows and I confused about the results. Environment Emachine 6417 with amd3200+ 64 bit processor 3.3GB ram mysql 4.1 with odbc 3.51 MYISAM windows 2003 server std edition date, account and invoice number are indexed Database size 18 million rows I am querying (selecting) columns of a date and an account our tester program that opens a socket to the Mysql database and does a select for the above n times each time the date and the account is randomized to minimize hits on records closeby. This program will perform over 1000 queries per second. At the end, the Mysql socket will be closed Even when the socket is closed each time, I still get 400 queries per second. When I enter a similar query manually a web interface, I get about 3-9 second response time. This program opens/closes a socket for each query when using EMS I get similar 9 second results. Does anyone have any suggestions Also in production, this table will be accesse for both read and write will I have problems. My testing showed that Innodb is much slower.
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 !
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 !
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 !
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 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 !
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 !
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 !
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 !
Memory Table Full
I've read that memory tables are supposed to gracefully 'fallback' to the default engine when they reach a certain size, but I'm not finding that they behave that way. Within my application, I had run into situations where CREATE TEMPORARY TABLE xxxx ENGINE=Memory SELECT xxx... would result in a "The table 'xxxx' is full" error. I had to change from memory to the default engine to ensure they would work, but most of the queries were much faster when using in memory temp tables where appropriate. Is there a way to ensure MySQL will gracefully switch to another engine when the temporary table becomes too large for the heap engine?
View Replies !
Mysqlimport Out Of Memory Error
I am trying to import some tables into a database. During the importing I come across a 12MB file that results in the following error message: mysqlimport: Out of memory (Needed 76164 bytes) *note - the number of bytes is not exact*
View Replies !
Bringing Records Into Memory/RAM
I am working in a database project in which some tables in the database is having around 2 Million rows. It is taking quite long time(more than 30 minutes) for order by & group by queries though index is used on those specific columns. Is there any other way to optimize the query retrieval speed? I heard there is a concept of moving all the records into the memory or RAM to increase the performance. I didn't understand what does it mean of moving data into memory/RAM.
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 !
Client Ran Out Of Memory Error
I am trying to insert about 10,000,000 records from a table (MYISAM MYSQL 5.0.22) into a Sybase ASA database table. I have done this many times from Sybase to Sybase. I also get the same error when mearly trying a select query returning the rows (WINSQL). Is there a parameter on the ODBC driver to prevent this? Is there a parameter on the MYSQL server to prevent this? I am already using the allow big results flag on the 3.5x ODBC driver. Both servers have plenty of ram 4 Gb with 8 Gb swap files.
View Replies !
Memory Usage Overhead
I've been analysing the memory usage of our server and each SQL thread seems to be taking up 10mb -since our site is basically a search site that means we're using 10mb per user which means with our 2gb RAM we can take around 120-200 simultaneous visitors. Other than buying more RAM, what can I do to increase the number of simultaneous connections (ie - how can I reduce memory usage?) By my Calculations we need to be able to accept around 500 simultaneous searchers to meet our target. We will also be increasing the amount of data we have - currently it is around 1.5 million records but that will be gradually increasing to around 10 million - which I'm sure will also increase the memory load on the server. Right now we have 1.4Gb Data and 380Kb overhead.
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 !
|
TRACKER
