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 Complete Forum Thread with Replies
Related Forum Messages:
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 !
SELECT Count(something) As Amount WHERE Amount = 'this'
SELECT search_query, COUNT(search_query) as count FROM hr_search WHERE count >= 2 GROUP BY search_query ORDER BY search_query ASC I'm building a search cloud and this is the query I want to use to grab all results from my database with more than two entries... however SQL doesnt like me using 'count' in the WHERE statement. Is there any way around this?
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 !
Mysql Process
I am working on bulk mailing panel. I want to send 70,000 mails to the member I am using following process for sending the email id to the members: fetch the email id; send email; change the flag; Next email id; But this mysql process taking log time. Is there any way, to control it the process or stop the process for some time and resend it again.
View Replies !
MySQL Process Terminated
wanted to put this together with an earlier thread of mine...although I decided to split it coz it seems this issue might not be related to my earlier thread on maximum memory MySQL can use in Windows 2000.. We have had a number of incidents lately in which the MySQL process terminated unexpectedly, and did not restart. Logs were enabled except the slow and general query log. But whenever the process terminates, there's totally nothing in the logs regarding what's the cause of the crash...it does not even mention a shutdown. Was wondering if anyone encountered this before? Hope to hear your experience on this and perhaps a tip or two on what to look at next?
View Replies !
MySQL Development Process
I am a student working from a university and at present part of my research involves figuring out the development model for MySQL. Who actually does the coding for MySQL and if MySQL development is open to all how does one get involved?
View Replies !
How To Kill A MySQL Process?
How do I find which mysql process has crashed, or is stuck and not running to completion, and then how to KILL it? FYI, if via mytop I see that process ID 2987 has been running for 78000 seconds, I am assuming that this process has crashed. Then how to kill it?
View Replies !
Mysql + Pid / Process Count + Increases
I have mysql 4.1.22 compiled on RHEL 4 and running fine. Over the time the threads / pids of mysql keep on increasing. For eg : ps -ef |grep mysql |wc -l 63 ps -ef |grep mysql |wc -l 101 The system is Intel Dual Core, RHEL4, 300 GB SATA HDD with 16GB RAM. Also I have observed that I cannot login into mysql shell when the process count goes higher beyond 100 meaning that it takes lots of time to enter into the mysql> shell I am using the my-innodb-heavy-4G.cnf as my.cnf with couple of modifications like max_connections=1000 skip-name-resolve innodb_file_per_table innodb_flush_method=O_DIRECT lower_case_table_names=1 expire_logs_days = 10 net_read_timeout=60 net_write_timeout=120 max_connect_errors = 100 innodb_locks_unsafe_for_binlog=1 innodb_flush_log_at_trx_commit=2 innodb_lock_wait_timeout=300 myisam_repair_threads = 10 innodb_file_io_threads = 20 innodb_thread_concurrency = 16 innodb_table_locks = 0 wait_timeout = 480 Rest of the settings remain same. The configure command used for compiling mysql is CFLAGS="-static -O3 -march=nocona -funroll-loops" CXX=gcc CXXFLAGS="-static -O3 -march=nocona -pipe -mmmx -msse -msse2 -mfpmath=sse,387 -funroll-loops -fno-exceptions -fno-rtti -fomit-frame-pointer -felide-constructors -mtune=nocona" ./configure --prefix=/usr/local/mysql --with-mysqld-user=mysql --libexecdir=/usr/sbin --sysconfdir=/etc --mandir=/usr/share/man --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static --with-thread-safe-client --with-unix-socket-path=/tmp/mysql.sock --localstatedir=/usr/local/mysql --enable-assembler --disable-shared --without-readline --without-debug --without-docs --without-bench and then make, make install and strip /usr/sbin/mysqld
View Replies !
Dynamically Settings MySQL Process Priority
I have written a very complex and highly CPU intensive PHP script that relies on numerous complex MySQL database calls. As this script takes nearly 48 hours to finish execution with a large database, and will sometimes be run on a live server, I need to be able to dynamically adjust the process priority of the script's MySQL thread from within the PHP code if other server activity must be given priority. Here is my question, and I know this may sound ridiculous, but is it possible to do this without having to resort to the usage of PHP's Process Control Functions? Unfortunately this is a shared host so those will not be enabled.
View Replies !
Debian Mysql Autostart: Line 159: Kill: (####) - No Such Process
I've just recently compiled MySQL 5.0.41 on a Debian box, and am trying to install the init script. I've copied support-files/mysql.server over to the /etc/init.d directory and set up the symlinks, but cannot seem to run it. Running: /etc/rc3.d/S90mysql start displays: Starting MySQL/etc/rc3.d/S90mysql: line 159: kill: (8076) - No such process ....
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 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 Replies !
Could Not Start The MySQL Service On Local Computer Error 1067: The Process Terminated Unexpectedly
We have an existing install of MySQL Server 5 on a Windows Server 2003 server. The MySQL service is configured to start automatically and to be restarted if it stops. With the exception of one hiccough about 6 weeks ago the service has been starting and running properly. Last night I installed critical updates and rebooted the server. This morning I realized that the MySQL service was not running. I atmysqlted to start it. It appeared to start. However, just a few seconds later it was not running. Atmysqlted to start it again. Same thing... Rebooted server. Service did not start. Now, following that reboot, I get "Could not start the MySQL service on local computer. Error 1067: the process terminated unexpectedly"
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 !
Max Amount &
i have a table of salaries that includes year, salary and id of employee. what i want is an output of who was the highest paid employee for each year. how in the world do i approach this? i've attempted some queries from the manual section 3.6.4 but have come up extremely short. i get results where the employee id does not match the employee who was the highest paid for that year.
View Replies !
Variable Amount
i'm developping a php-application that needs to be highly customizable. On the one side, the user has a input-mask, where he can edit database records. on the other side, the user should be given the possibility to alter this database mask on his own, probably in a special "settings tab" - e.g. adding or deleting fields. My approach so far: I have to tables: one, which holds the names of the fields to be offered AND another one, which holds the entries for each "record". These two are linked together by a "right-join" statement.
View Replies !
Certain Amount Of Rows
guess it is a simple question. let's assume i have a certain amount of rows in my resultset, sorted by their id. but actually i would like to retreive only a part of it, let's say the first time the first 20 rows, next time the next 20 rows and so on. think of a forum where you would see a maximum of 20 replies at once in the same thread. below you would have a forward/backward button which would let you flip through the pages with the answers, 20 each. how would such an sql-statement look like? p.s.: one solution would be to retreive e.g. all answers, and just to read out the needed ones from the resultset - but I guess there is a more sophisticated way, which would need less sources
View Replies !
Amount Of Columns
Does the amount of columns in a table matter? My member table has around 30 different columns and i'm wondering whether this is too much.
View Replies !
ORDER BY :: Amount
The following query does not seem to order by "bid_amount". It will simply take the first entry in the database. Any idea how to make it order by "bid_amount" (DESC)? ......
View Replies !
Display Amount/No Duplicates
How do you select only the first #(10, 20 ,30... just some set number) rows in a table. Then if you want to continue, how can you select the next ten. I already know how to set them to display in desc order... but I don't know if adding this code will change it. In case you want to know, I am trying to make a news table and have it display on my web page, but I only want 10 headlines to display at a time, then click a link and the next ten will display. The other question is: How do you test to see if someone has already entered the data you are about to enter. An example would be, if someone entered the data(aa,bb,cc,dd) then tried to enter that exact same data again, it would not allow it.
View Replies !
Percentages And Dollar Amount
what is the best practice for using percentages and dollar amounts? I'm setting up my schema. I will have percentages and revenue to enter into the database. What happens if I have 33% how should this be reprecented .
View Replies !
Storing Dollar Amount With .00
I want to save dollar amounts with two zeros at the end ($5.00). I have used the decimal type and it works if I have numbers at the end ($5.25) but it will not keep zeros. I need this because I want the info to display with two zeros. I saw somewhere about using a FORMAT option, but I can't seem to find in the Manual how to do this.
View Replies !
Variable Amount Of Data
I'm very new to MySQL. I've searched and searched and have been unable to find an answer to my problem. I'm setting up a database that will store photos (not the actual photo but the path) and the names of the people in the photos. I plan on two tables: 1) People * ID * fname * mname * lname 2) Picture * People * Location * Date I cannot figure out how to handle photos that have multiple people in them. The number of people could vary from 1 to 100. How do I create a table where each entry will have a varying number of people listed?
View Replies !
Displaying Certain Amount Of Data
I have created a script. This script of mine takes data off of a table and displays it on a web page. What I want is for the script to display only data that matches the id of another section of the web page. For example, the script would first point to another factor in the database, perhaps the value of a row in another table. Let's call that value 025. In a second table, I have a few rows of data. Their ids were 025, 026, 027 and 028. The web page would only display rows with the ids of 025. However, if that value in the other table was 026, then in the second table, the rows with id 026 would only be displayed.
View Replies !
Code To Pull Amount
I'm certain there is a function to do this but I forget what it is... Say I am pulling values from the database, but I only want to pull like the first 10 chars of the value.. how do I do that?
View Replies !
Limiting Amount Of Returned Rows?
How do I limit the amount of returned rows? Lets say, that I want only the latest 400 rows (of e.g. 4034634 rows) - is there a parameter for that like rownum in Oracle? e.g. select * from someplace where rownum < 400 order by sometimefield?
View Replies !
Limit Amount Of Rows In Table?
I would like to have a table which only holds the last 50 added rows. Table: userips id, userip 4, 22.22.22.22 5, 23.22.22.25 6, 25.22.22.26 | 53, 22.55.55.112 Now if a new ip is INSERTed the last one has to be removed so only 50 rows will be stored at all time.I can do this by first inserting a new ip and then deleting a row which requires 2 query's but is there a more efficient way?
View Replies !
|
TRACKER
