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 Complete Forum Thread with Replies
Related Forum Messages:
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 Heavy Load On Databases And Connections?
I'm preparing for a lot of potential traffic to a web app. My question is. Is there any restriction on the amount of databases you have on a single mysql installation on a linux server. Could there be any performance issue if for example I had 100,000 databases all being connected to at different times? What is the upper limits of a standard LAMP dedicated server? What i mean is how many simultaneous connection can it handle before it starts to slow down for the users?
View Replies !
MySQL Handling Heavy Loads
Has anyone used MySQL in a public web site environment that receives 1 - 1.5 million sessions/day? Sessions are mostly content and light commerce. Site spikes to 3 million sessions/day about 4 times/year. Trying to find a good example of MySQL holding up under this kind of load.
View Replies !
Mysql Version Is More Able To Handle Heavy Traffic
we are currently running mysql 4.1. we have problems with a php/mysql website that generates a lot of traffic and relies heavily on mysql (it's a "find your old schoolmates" community website). Sometimes it just says "too many connections", or just takes a long while before rendering the page. Would upgrading to mysql 5 help ? What are the best mysql settings in order to optimize the performance?
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 !
Update Heavy Data
We have an os commerce application with a database of 7mb is working fine on our local server but when we are trying to update the data from local to online server via phpmyadmin we are unable to upload it there. Is any other way or idea to upload the file to server? "we can do it by root access of server but we dont have that acess in our hosting"
View Replies !
Heavy Traffic System
I am developing a very heavy traffic site. It may happen that at same time (exactly same) two user will try to register with the same username. What will be the approach to prevent this situation. Which user will be able to register and which one will be denied? Do I need to use any locking facility? If so is it a wise decision to lock entire table?
View Replies !
InnoDB: Right For Write-heavy Table? Server Tuning?
I've decided to merge 4500 identical tables into one. They were previously partitioned with one table per user of the system, but I'm imagining I may not need to do this anymore for this particular table. I'd like to do so to reduce the number of files in this database's directory (currently over 15,000), to reduce backup complexity, and maybe improve performance. That's where I'm still unsure. This is the summary table for W3Counter with fields website_id, date, unique_visits, return_visits, page_views. The website_id and date uniquely identify each row. It is a write-heavy table (probably more than 99% INSERT/UPDATE compared to SELECT). I may be making things up, but I believe I remember InnoDB showing superior performance for this type of table. I'd imagine row-level locking would decrease lock contention when being hit by dozens of threads concurrently -- queries trying to update different rows would be allowed since that row isn't locked, unlike MyISAM where the entire table is locked by each query. Merging all the tables into this one would mean starting with about 1.5 million rows and growing about 4500 per day. So what do you think? Is InnoDB the right way to go for this table? Should I expect better performance than MyISAM for the inserts/updates? Should I expect the same or better performance on SELECTs that SUM() those counts over various date ranges, and sometimes group by WEEK(), MONTH() or YEAR() on the date column? I'm not sure whether the primary key is sufficient for that type of query, if a key on just the website_id would help those, or what. Any opinions? My other reason for this thread is to get some pointers on tuning the server variables for InnoDB tables. There's much less written about this than those mainly applicable to MyISAM tables. Which are most important for a table like this? Right now no other tables will be InnoDB as the rest benefit greatly from MyISAM-specific features (like prefix compression on indexes and maintenance of row counts).
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 !
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 !
Load Data Infile :: Load Key
My question is this I need to load a file (b/c of the size I don't want to use insert statements) with load data local infile. The problem is I need to add a static value to each record created by the load that will serve as the key. In version 5 of MySql this is no problem because I can use set w/ load data infile to accomplish what I need. However I need to do this in an older version. Any thoughts on how I could pull this off?
View Replies !
Could Not Load MySQL Driver! Compile Php --with-mysql Or Install The Mysql.so Module.
I am trying to install Media Wiki 1.5.2 on Windows XP and I have already installed apache_2.0.55-win32-x86-no_ssl ,mysql-5.0.15-win32 and php-5.1.1-Win32 . When I am trying to install Media Wiki 1.5.2 I receive the following message : Warning: dl() [function.dl]: Not supported in multithreaded Web servers - use extension=mysql.so in your php.ini in C:Program FilesApache GroupApache2htdocsmediawikimediawiki-1.5.2install-utils.inc on line 17 Could not load MySQL driver! Please compile php --with-mysql or install the mysql.so module.
View Replies !
Load CSV->MySql
I have a CSV file and want to load it into mysql. However, when I used the related query to input the file, an error occurs: Data too long for column 'Name' at row 2 I tried to ignore the line, but the same problem ocuur again. The content of the field is not English.
View Replies !
Cause Of MySQL Load
I have a server and periodically the load skyrockets to like 30. When I do a "top" all I see are mysql processes on the top half. How do I find out which user is causing this?
View Replies !
Load Testing MySQL
I know this is quite a bit off topic, but i was wondering if anyone else out there had any experience with Segue's Silk Performer load testing tools. More specifically, i am trying to compile a test script to allow me to perform some transactional database testing to load test a MySQL 4.0.13 database server running behind some apache webservers. I have obtained an ODBC driver from MySQL and have some specific queries i would like to use in the test. I am have used SP to load test the webservers specifically, but i am not 100% sure how to run this against a database server now. If anyone has any experience they can share, please feel free to email me off list.
View Replies !
LOAD Command In MySQL
I am ChandraSekhara Rao, new to this group. I would like to know how LOAD command was implemented in MySQL ? Day before yesterday i tried to load a data file of size 540 MB to a table. It took about only 2 min. But using PERL and PYTHON i tried to parse the same file into Lists at that time it took more than 1 hr. I surprised about this. My question is why PYTHON and PERL ( this took around 45 min to parse the same file) took lot of time and while MySQL took only 2 min and how the LOAD command in MySQL was implemented
View Replies !
LOAD DATA In MySql 4.0
I'm getting an error running LOAD DATA on server running version 4.024 MySql. I know it's part of 4.11 (it's in the documentation). Does anyone know if it is supported in 4.0?
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 !
MySQL Crashes When 98% Load On Server
When mysql load reaches 98% it's totaly hanged. MySQL is not sending any error messages to php and all website is down. When i disconnect php from mysql it's not helping, all the time mysql is 98% and i need to kill the mysql process to get my website to work again.
View Replies !
VB6 - MySQL 5 - Don't Load Some Field From The Table
I'm with a strange problem that occur in some fields of the table that are not being loaded in the form I'm usign the visual basic 6 as front end and MySQL 5. When I do in this way the form load the fields from the table. i.e: rsMySQL.Source = "Select * From tb_alunos But in this another form some field don't load. rsMySQL.Source = "Select A. *, B.*, C.*, E.* FROM tb_alunos AS A INNER JOIN tb_bairros AS B ON A.Id_Bairro = B.Id_Bairro INNER JOIN tb_cidades AS C ON A.Id_Cidade = C.Id_Cidade INNER JOIN tb_empresas AS E ON A.Id_Empresa = E.Id_Empresas" I have tried with LEFT JOIN and RIGHT JOIN, without success.
View Replies !
Load .sql File Into New MySql Database
I am not sure how to proceed with this problem. I have just installed MySql onto my machine and that seemed to go ok. I have created a database with a username and password also. I have a file from a friend (for real!). It is a .sql dump file?, which looks like it has several commands in it such as: CREATE TABLE `games`, INSERT INTO `games` VALUES("values"), CREATE TABLE `results`, and INSERT INTO `results` VALUES ("more values"). How do I load this file into my empty database?
View Replies !
Load Image File Into Mysql 3.23
I need to upload and image file (jpeg) to a database table called products. I have searched how to do it and can't find a simple way. Please show me the command to do it. The column in products table is called image1.
View Replies !
How Can I Load This Text File Into MySQL?
the following are the content of the txt file. It is 2 rows. There are much more rows like these in the txt file. I want to load every 1st line into my database. I used " load data infile ' file path/file name' into tablename fields terminated by ' ' lines starting 'r '; " to load the file. But there is error. I think "V2Dir " of 2nd line make the error. It conflicts with lines starting 'r '. Could somebody give me any solution for loading this text into my database? Code:
View Replies !
MYSQL Causing A Huge Load
I just got my new VPS with liquidweb up and running and I'm experiencing a huge amount of load, a lot i think is due to mysql queries, so I think I'm going to need to do some serious optimization of my queries. I'm pretty new to this stuff.. Can anyone make sense of this SHOW STATUS and SHOW VARIABLES info, and let me know what the issue may be? SHOW STATUS Variable_nameValue Aborted_clients0 Aborted_connects0 Binlog_cache_disk_use0 Binlog_cache_use0 Bytes_received13775592 Bytes_sent180075883 Com_admin_commands541 Com_alter_db0 Com_alter_table0 Com_analyze0 Com_backup_table0...............
View Replies !
|
TRACKER
