Re: Out of memory error
Out of your 4 gigabyte of memory, you allocate 2G to the innodb pool. Assuming you're using mostly innoDB, that's good. Say there's also about 300M allocated to the OS - assuming a dedicated server; that leaves about 1.7G for non-InnoDB operations. You have configured your server for 500 connections, and specified both join_buffer_size and sort_buffer_size as 8M. Every session that needs to do a sort allocates one sort buffer of the specified size. One join buffer of *minimum* this size is allocated for every full join between two tables, so multiple buffers may be needed for a single query. 8M * 500 connections = 2G. Do you see where this is going ? :-) Not every connection will need to allocate join or sort buffers, but it may be well worth to see what's happening on your server when you get these out of memory errors, and maybe you can optimize some queries to use indices for sorting, add indices to remove full joins, et cetera. I keep telling people this: set up Munin or Cacti, and graph anything you can find about your server. Trending gives you both the ability to look back at what was going on when a problem occured, and look forward to see problems coming ahead of time. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Out of memory error
upgrade to the lastest MySQL 5.1.x branch GA and try again. Best regards, Sharl.Jimh.Tsin (From China **Obviously Taiwan INCLUDED**) 2010/11/4 김수영 old...@posbank.co.kr: Dear MySQL users, I got an Out of memory error message on server yesterday. I restarted mysqld, and everything was go back to normal and now is fine. It happened already twice in this weekend even. But I don't know what's wrong. Server has QuadCore Xeon CPU, 4G ECC RAM, 2G x 49(ea) ibdata files, mostly based on using InnoDB typed tables. Below is server condition details. [r...@svr40 mysql]# ulimit -a core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 77824 max locked memory (kbytes, -l) 32 max memory size (kbytes, -m) unlimited open files (-n) 1024 pipe size (512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) 10240 cpu time (seconds, -t) unlimited max user processes (-u) 77824 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited [r...@svr40 mysql]# uname -a Linux svr40.hsl.co.kr 2.6.18-164.11.1.el5PAE #1 SMP Wed Jan 20 08:16:13 EST 2010 i686 i686 i386 GNU/Linux [r...@svr40 mysql]# cat /etc/redhat-release CentOS release 5.4 (Final) [r...@svr40 mysql]# rpm -qa | grep MySQL MySQL-devel-community-5.1.42-0.rhel5 MySQL-client-community-5.1.42-0.rhel5 MySQL-server-community-5.1.42-0.rhel5 [r...@svr40 mysql]# tail -50 svr40..err 101103 15:12:25 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2095132 bytes) 101103 15:12:25 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2095132 bytes) 101103 15:12:25 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2095132 bytes) 101103 15:12:25 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2095132 bytes) 101103 15:12:28 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2095116 bytes) 101103 15:12:38 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2095132 bytes) 101103 15:12:38 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2095132 bytes) 101103 15:12:42 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2095236 bytes) 101103 15:12:43 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2095584 bytes) 101103 15:12:45 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2095132 bytes) 101103 15:12:45 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2095132 bytes) 101103 15:12:47 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2095200 bytes) 101103 15:12:51 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2095116 bytes) 101103 15:13:12 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2095120 bytes) 101103 15:13:16 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2095132 bytes) 101103 15:13:16 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2095132 bytes) 101103 15:13:19 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2095120 bytes) 101103 15:13:22 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2095548 bytes) 101103 15:13:23 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2095132 bytes) 101103 15:13:23 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2095132 bytes) 101103 15:13:28 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2095548 bytes) 101103 15:13:32 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2095132 bytes) 101103 15:13:34 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2095500 bytes) 101103 15:13:38 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2095548 bytes) 101103 15:13:38 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2095392 bytes) 101103 15:13:40 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2095120 bytes) 101103 15:13:40 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2095120 bytes) 101103 15:13:41 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2095132 bytes) 101103 15:13:41 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2095132 bytes) 101103 15:13:45 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2095548 bytes) 101103 15:13:49 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2095200 bytes) 101103 15:13:51 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2095128 bytes) 101103 15:13:51 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2095284 bytes) 101103 15:13:52 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2095548 bytes) 101103 15:13:57 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2095128 bytes) 101103 15:13:57 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2095284 bytes) 101103 15:14:02 [Note] /usr/sbin/mysqld: Normal shutdown 101103 15:14:02 [Note] Event Scheduler: Purging the queue. 0 events 101103 15:14:04 InnoDB: Starting shutdown... 101103 15:14:08 InnoDB: Shutdown completed; log sequence number 10 437654676 101103 15:14:08 [Note] /usr/sbin/mysqld: Shutdown complete 101103 15:14:08 mysqld_safe mysqld from pid file /var/lib/mysql/svr40.xxx.pid ended
Re: “Out of memory; check if mysqld or some other process uses all available memory;” error
Hi, Configure my.cnf in such a way so that it uses 3.5 GB of physical memory. Please check max_connecions. Out of memory problem also occur due to lots of connections Thanks Krishna On 9/27/07, Amarnath Shivashankar [EMAIL PROTECTED] wrote: 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 Please help me to resolve this.
Re: Re: Out of memory; check if mysqld or some other process uses all available memory; error
Hi Mathieu, I found that innodb_buffer pool value isn't set..The whole innodb settings are commented. I found the below values from the my.cnf file: # Uncomment the following if you are using InnoDB tables #innodb_data_home_dir = /var/lib/mysql/ #innodb_data_file_path = ibdata1:10M:autoextend #innodb_log_group_home_dir = /var/lib/mysql/ #innodb_log_arch_dir = /var/lib/mysql/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high #innodb_buffer_pool_size = 256M #innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size #innodb_log_file_size = 64M #innodb_log_buffer_size = 8M #innodb_flush_log_at_trx_commit = 1 #innodb_lock_wait_timeout = 50 here is the memory settings: total used free shared buffers cached' mem: 4054 4038 15 0 6 1426 buffers/cache 2605 1448 swap: 4094 150 3943 Please help me out to change the parameter values Regards, Amarnath S Amarnath Shivashankar wrote : 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 Please help me to resolve this. Mathieu Bruneau wrote: This looks like the traditionnal 32 bits limitation ... You're using a 32 bits system right ? Because of many reasons (lots of documentation on the net about that) MySQL is in practice limited to about 2.4-2.6G of memory, thus the error you see. When I experienced this errors, I lowered the mysql_buffer and innodb_buffer so that mysql would stay below this limit and never had other issue with it. (It was crashing with an error 11 before). I kept this settings till I could upgrade to a 64 bits host. -- Mathieu Bruneau aka ROunofF Regards, Amarnath Shivashankar SQL Database Management The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com
Re: “Out of memory; check if mysq ld or some other process uses all availab le memory;” error
Amarnath Shivashankar a écrit : 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 Please help me to resolve this. This looks like the traditionnal 32 bits limitation ... You're using a 32 bits system right ? Because of many reasons (lots of documentation on the net about that) MySQL is in practice limited to about 2.4-2.6G of memory, thus the error you see. When I experienced this errors, I lowered the mysql_buffer and innodb_buffer so that mysql would stay below this limit and never had other issue with it. (It was crashing with an error 11 before). I kept this settings till I could upgrade to a 64 bits host. -- Mathieu Bruneau aka ROunofF === GPG keys available @ http://rounoff.darktech.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Out of memory (Needed xxx ...
As far as i remember you must set that variable in order to send huge blobs, and the error message can vary because it can means that the current memory space for that connection has been filled. Why not do you try to set it up, as mentioned in the manual and check the results. Im forwarding this to the java list, maybe Mark can reserve a liitle time from the Mysql Users Conference and give us some feedback. Carlos On 4/26/06, Robert DiFalco [EMAIL PROTECTED] wrote: It's not set, but I am streaming the LOG to the server, would max packet impact this situation? Also, wouldn't I get a different error, i.e. Packet Too Large? From: Carlos Proal [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 26, 2006 4:59 PM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: Out of memory (Needed xxx ... And whats the size of your max_allowed_packet variable ? Carlos On 4/26/06, Robert DiFalco [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: I am, the java heap is set to 2G. But I don't think it is my java process that is running out of memory, I believe it is the MySQL server. -Original Message- From: Carlos Proal [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 26, 2006 4:53 PM Cc: mysql@lists.mysql.com Subject: Re: Out of memory (Needed xxx ... Hi Robert, are you using the extended parameters to increase the jvm heap memory ? ie. java -Xms256m -Xmx512m Carlos On 4/26/06, Robert DiFalco [EMAIL PROTECTED] wrote: Getting this error from JDBC while inserting a VERY large VARBINARY or MEDIUMTEXT field, ~250MB. I'm guessing this is an error from the server? Is there a way to have the server start streaming to disk sooner with a LOB? Is there a property I'm not setting? TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: Out of memory (Needed xxx ...
Setting max_allowed_packet does not seem to make a difference on how large the blob to the database can be. The MySQL server still gives the OOM exception after streaming ~600mb to a LOB regardless of how max_allowed_packet is set. R. From: Carlos Proal [mailto:[EMAIL PROTECTED] Sent: Thursday, April 27, 2006 11:31 AM To: Robert DiFalco Cc: mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Out of memory (Needed xxx ... As far as i remember you must set that variable in order to send huge blobs, and the error message can vary because it can means that the current memory space for that connection has been filled. Why not do you try to set it up, as mentioned in the manual and check the results. Im forwarding this to the java list, maybe Mark can reserve a liitle time from the Mysql Users Conference and give us some feedback. Carlos On 4/26/06, Robert DiFalco [EMAIL PROTECTED] wrote: It's not set, but I am streaming the LOG to the server, would max packet impact this situation? Also, wouldn't I get a different error, i.e. Packet Too Large? From: Carlos Proal [mailto: [EMAIL PROTECTED] Sent: Wednesday, April 26, 2006 4:59 PM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: Out of memory (Needed xxx ... And whats the size of your max_allowed_packet variable ? Carlos On 4/26/06, Robert DiFalco [EMAIL PROTECTED] mailto: [EMAIL PROTECTED] wrote: I am, the java heap is set to 2G. But I don't think it is my java process that is running out of memory, I believe it is the MySQL server. -Original Message- From: Carlos Proal [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 26, 2006 4:53 PM Cc: mysql@lists.mysql.com Subject: Re: Out of memory (Needed xxx ... Hi Robert, are you using the extended parameters to increase the jvm heap memory ? ie. java -Xms256m -Xmx512m Carlos On 4/26/06, Robert DiFalco [EMAIL PROTECTED] wrote: Getting this error from JDBC while inserting a VERY large VARBINARY or MEDIUMTEXT field, ~250MB. I'm guessing this is an error from the server? Is there a way to have the server start streaming to disk sooner with a LOB? Is there a property I'm not setting? TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Out of memory (Needed xxx ...
Hi Robert, are you using the extended parameters to increase the jvm heap memory ? ie. java -Xms256m -Xmx512m Carlos On 4/26/06, Robert DiFalco [EMAIL PROTECTED] wrote: Getting this error from JDBC while inserting a VERY large VARBINARY or MEDIUMTEXT field, ~250MB. I'm guessing this is an error from the server? Is there a way to have the server start streaming to disk sooner with a LOB? Is there a property I'm not setting? TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: Out of memory (Needed xxx ...
I am, the java heap is set to 2G. But I don't think it is my java process that is running out of memory, I believe it is the MySQL server. -Original Message- From: Carlos Proal [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 26, 2006 4:53 PM Cc: mysql@lists.mysql.com Subject: Re: Out of memory (Needed xxx ... Hi Robert, are you using the extended parameters to increase the jvm heap memory ? ie. java -Xms256m -Xmx512m Carlos On 4/26/06, Robert DiFalco [EMAIL PROTECTED] wrote: Getting this error from JDBC while inserting a VERY large VARBINARY or MEDIUMTEXT field, ~250MB. I'm guessing this is an error from the server? Is there a way to have the server start streaming to disk sooner with a LOB? Is there a property I'm not setting? TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Out of memory (Needed xxx ...
And whats the size of your max_allowed_packet variable ? Carlos On 4/26/06, Robert DiFalco [EMAIL PROTECTED] wrote: I am, the java heap is set to 2G. But I don't think it is my java process that is running out of memory, I believe it is the MySQL server. -Original Message- From: Carlos Proal [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 26, 2006 4:53 PM Cc: mysql@lists.mysql.com Subject: Re: Out of memory (Needed xxx ... Hi Robert, are you using the extended parameters to increase the jvm heap memory ? ie. java -Xms256m -Xmx512m Carlos On 4/26/06, Robert DiFalco [EMAIL PROTECTED] wrote: Getting this error from JDBC while inserting a VERY large VARBINARY or MEDIUMTEXT field, ~250MB. I'm guessing this is an error from the server? Is there a way to have the server start streaming to disk sooner with a LOB? Is there a property I'm not setting? TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: Out of memory (Needed xxx ...
It's not set, but I am streaming the LOG to the server, would max packet impact this situation? Also, wouldn't I get a different error, i.e. Packet Too Large? From: Carlos Proal [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 26, 2006 4:59 PM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: Out of memory (Needed xxx ... And whats the size of your max_allowed_packet variable ? Carlos On 4/26/06, Robert DiFalco [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: I am, the java heap is set to 2G. But I don't think it is my java process that is running out of memory, I believe it is the MySQL server. -Original Message- From: Carlos Proal [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 26, 2006 4:53 PM Cc: mysql@lists.mysql.com Subject: Re: Out of memory (Needed xxx ... Hi Robert, are you using the extended parameters to increase the jvm heap memory ? ie. java -Xms256m -Xmx512m Carlos On 4/26/06, Robert DiFalco [EMAIL PROTECTED] wrote: Getting this error from JDBC while inserting a VERY large VARBINARY or MEDIUMTEXT field, ~250MB. I'm guessing this is an error from the server? Is there a way to have the server start streaming to disk sooner with a LOB? Is there a property I'm not setting? TIA, R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Out Of Memory problems: One MySQL user, 86 minutes sleeping
Hello.
1981 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:03.53 mysqld
1982 mysql 20 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
1983 mysql 17 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
1984 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
These are the threads of the same mysqld process which is using only
72m of virtual memory. May be you should find out other process
which is consuming memory. If you are sure that MySQL is guilty
than send to the list the output of 'SHOW VARIABLES' statement.
thomas Armstrong wrote:
Hi.
Using MySQL 4.1.9 on Linux FedoraCore2 (kernel 2.6.9),
I'm suffering several memory problems ('Out Of Memory'
problem) on my server.
Playing around with my server:
SHOW PROCESSLIST
Id | User | Host | db | Command | Time | State | Info
20138 | user1 | localhost | user1_db | Sleep | 5295 | NULL
(why is this user1 sleeping for 86 minutes?)
SHOW STATUS:
Qcache_free_blocks =0956
Qcache_free_memory =0911676280
Qcache_hits =09762140
Qcache_inserts =09109122
Qcache_lowmem_prunes =0912575
Qcache_not_cached =09897
Qcache_queries_in_cache =09644
Qcache_total_blocks =091582
-
[]# top
-
top - 12:55:40 up 1 day, 2:18, 2 users, load average: 0.39, 0.35, 0.29
Tasks: 88 total, 2 running, 86 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.0% us, 0.3% sy, 0.0% ni, 99.7% id, 0.0% wa, 0.0% hi, 0.0% s=
i
Mem:508072k total, 499980k used, 8092k free,69828k buffers
Swap: 1084376k total, 2176k used, 1082200k free, 116264k cached
PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND
1980 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:03.24 mysqld
1981 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:03.53 mysqld
1982 mysql 20 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
1983 mysql 17 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
1984 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
1985 mysql 17 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
1986 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:00.21 mysqld
1987 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:00.13 mysqld
1988 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
1989 mysql 16 0 72064 42m 6172 S 0.0 8.5 0:00.06 mysqld
14337 mysql 20 0 72064 42m 6172 S 0.0 8.5 0:00.00 mysqld
17323 apache15 0 42056 30m 16m S 0.0 6.1 0:02.44 httpd
2316 ogo 16 0 36092 28m 23m S 0.0 5.7 0:01.25 ogo-webui-1.0a
11228 apache16 0 36600 24m 18m S 0.0 4.9 1:51.20 httpd
14276 apache15 0 34408 24m 16m S 0.0 4.8 0:58.17 httpd
15737 apache15 0 34120 23m 16m S 0.0 4.8 0:32.92 httpd
-
[]# more /etc/my.conf
[mysqld]
datadir=3D/var/lib/mysql
socket=3D/var/lib/mysql/mysql.sock
query-cache-size=3D20M
query-cache-type=3D1
default-character-set=3Dutf8
# Slow queries log
log-slow-queries =3D /var/log/mysql/slow-queries.log
long_query_time =3D 5
log-long-format
[mysql.server]
user=3Dmysql
basedir=3D/var/lib
[safe_mysqld]
err-log=3D/var/log/mysqld.log
pid-file=3D/var/run/mysqld/mysqld.pid
---
[]# free -m
--
total used free sharedbuffers cached
Mem: 496487 8 0 68113
-/+ buffers/cache:305190
Swap: 1058 2 1056
---
I'm trying to find out the reason of my memory problems. I suspect this
sleeping user is to blame. Any suggestion?
Thank you very much.
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Out of memory (Needed 8164 bytes)
Hello. the problem is that after around 160,000 rows inserted with success , then i get Out of memory (Needed 8164 bytes) error many times. Does your program report this error or MySQL Server? Can someone give me an advice with some optimization or if something is wrong in mysql server configuration? At least check that memory which might be used by MySQL Server can't be more than amount of your RAM. See: http://dev.mysql.com/doc/mysql/en/memory-use.html Use a formula similar to this: min_memory_needed = global_buffers + (thread_buffers * max_connections) where thread_buffers includes the following: sort_buffer myisam_sort_buffer read_buffer join_buffer read_rnd_buffer thread_stack net_buffers and global_buffers includes: key_buffer innodb_buffer_pool innodb_log_buffer innodb_additional_mem_pool In case Andrei wrote: Hi list, i have a linux server with mysql 4.1.14 and i'm trying to execute a program wich read a file with 100's tohusands lines and for every line must do a SELECT and then an INSERT . the SELECT use 2 InnoDB tables and the INSERT use a MyISAM table. the problem is that after around 160,000 rows inserted with success , then i get Out of memory (Needed 8164 bytes) error many times. i will try to increase the RAM size but i don't know if this is the problem (for sure it will help , but ... ) so, i suspect that after these 160,000 rows ... the SELECT for the following records is not successfully ending but the INSERT is ok. Can someone give me an advice with some optimization or if something is wrong in mysql server configuration? #free total used free sharedbuffers cached Mem:514460 238972 275488 0 28824 131024 -/+ buffers/cache: 79124 435336 Swap: 1975912 200521955860 VARIABLES: +-+-+ | Variable_name | Value +-+-+ | back_log| 50 | basedir | /opt/sql/mysql-4.1.14/ | binlog_cache_size | 32768 | bulk_insert_buffer_size | 8388608 | character_set_client| latin1 | character_set_connection| latin1 | character_set_database | latin1 | character_set_results | latin1 | character_set_server| latin1 | character_set_system| utf8 | character_sets_dir | /opt/sql/mysql-4.1.14/share/mysql/charsets/ | collation_connection| latin1_swedish_ci | collation_database | latin1_swedish_ci | collation_server| latin1_swedish_ci | concurrent_insert | ON | connect_timeout | 5 | datadir | /opt/sql/mysql-data/ | date_format | %Y-%m-%d | datetime_format | %Y-%m-%d %H:%i:%s | default_week_format | 0 | delay_key_write | ON | delayed_insert_limit| 100 | delayed_insert_timeout | 300 | delayed_queue_size | 1000 | expire_logs_days| 0 | flush | OFF | flush_time | 0 | ft_boolean_syntax | + -()~*:| | ft_max_word_len | 84 | ft_min_word_len | 4 | ft_query_expansion_limit| 20 | ft_stopword_file| (built-in) | group_concat_max_len| 1024 | have_archive| NO | have_bdb| NO | have_blackhole_engine | NO | have_compress | YES | have_crypt | YES | have_csv| NO | have_example_engine | NO | have_geometry | YES | have_innodb | YES | have_isam | NO | have_ndbcluster | NO | have_openssl| NO | have_query_cache| YES | have_raid | NO | have_rtree_keys | YES | have_symlink| YES | init_connect| | init_file | | init_slave | | innodb_additional_mem_pool_size | 20971520 | innodb_autoextend_increment | 8 | innodb_buffer_pool_awe_mem_mb | 0 | innodb_buffer_pool_size | 167772160 | innodb_data_file_path | ibdata1:10M:autoextend | innodb_data_home_dir| /opt/sql/mysql-data/ | innodb_fast_shutdown| ON | innodb_file_io_threads | 4 | innodb_file_per_table | OFF | innodb_flush_log_at_trx_commit | 1 |
Re: Out of memory (Needed 8164 bytes)
On Monday 10 October 2005 13:42, Hugh Sasse wrote: On Mon, 10 Oct 2005, Andrei wrote: Hi list, Hi, i have a linux server with mysql 4.1.14 and i'm trying to execute a program wich read a file with 100's tohusands lines and for every line must do a SELECT and then an INSERT . Written in what language? C What does it do about clearing up after each line read? good question ! that helped ! How much does it read in at once? one row Does it rely on garbage collection? Can you run it on a different machine from the database? yes ... but the same error. I think people will need more concrete information to help you debug this. Hugh Thanks, Andrei -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Out of memory, mysqld got signal 6;
Hello. Switch to the latest release 4.1.12 (or 4.0.24). It is strongly recommended to use official binaries. Andrew Hill [EMAIL PROTECTED] wrote: Hi all, Over the last couple of days, a MySQL 4.0.21 server of ours has crashed a couple of times, with the error message listed below. It has previously been running without problems since being installed (about 150 days so far). I've not had much luck in tracking down what signal 6 means - the references I can find on the 'net seem to be from people running MySQL on a *BSD machine, while ours is on a RHEL3 server. Can anyone please point me in the right direction to track down what this might be? The server has 10GB of RAM, and MySQL has been compiled with -fomit-frame-pointer. I can re-compile MySQL without this option if it's absolutely necessary. Thanks, 050602 17:18:47 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 mysqld got signal 6; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=3D8388608 read_buffer_size=3D2093056 max_used_connections=3D235 max_connections=3D800 threads_connected=3D190 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections =3D 3281785 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=3D0xc271010 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... frame pointer (ebp) is NULL, did you compile with -fomit-frame-pointer? Aborting backtrace! Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x1a70a218 is invalid pointer thd-thread_id=3D1642298 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Fatal signal 11 while backtracing -- Andrew Hill Software Developer m3 Media Services Limited Kirkman House, 12-14 Whitfield Street, London W1T 2RF T: +44 (0)20 7299 7370 F: +44 (0)20 7299 7371 IRC: #max on freenode.net=20 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Out of memory, mysqld got signal 6;
-Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: 03 June 2005 12:29 To: mysql@lists.mysql.com Subject: Re: Out of memory, mysqld got signal 6; Switch to the latest release 4.1.12 (or 4.0.24). It is strongly recommended to use official binaries. Sure, can do - but is this a known issue with 4.0.21? I wasn't able to find anything in the bug tracker... TIA, -- Andrew Hill Software Developer m3 Media Services Limited Kirkman House, 12-14 Whitfield Street, London W1T 2RF T: +44 (0)20 7299 7370 F: +44 (0)20 7299 7371 IRC: #max on freenode.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Out of memory, mysqld got signal 6;
Hello. This could be not exactly 4.0.21 issue, but issue of a compiler, say, if you've built MySQL manually. Sure, can do - but is this a known issue with 4.0.21? I wasn't able to find anything in the bug tracker... TIA, -- Andrew Hill Software Developer m3 Media Services Limited Kirkman House, 12-14 Whitfield Street, London W1T 2RF T: +44 (0)20 7299 7370 F: +44 (0)20 7299 7371 IRC: #max on freenode.net =20 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Out of memory, but plenty of swap space left
Hello. Does the problem remains if you're executing the query just after the 'FLUSH TABLES' statement. Jon Wagoner [EMAIL PROTECTED] wrote: Recently I've been getting error 1041 at times, which gives the message to update the ulimit or add swap space. =20 This is a dedicated MySQL server, so I have no ulimit set for MySQL. According to /proc/meminfo I have over 1GB of swap free. Mem: 2118533120 2067628032 509050880 19677184 1659768832 Swap: 1998733312 686882816 1311850496 MemTotal: 2068880 kB MemFree: 49712 kB MemShared: 0 kB Buffers: 19216 kB Cached:1320684 kB SwapCached: 300184 kB Active: 943996 kB Inactive: 1008424 kB HighTotal: 1179484 kB HighFree: 2044 kB LowTotal: 889396 kB LowFree: 47668 kB SwapTotal: 1951888 kB SwapFree: 1281104 kB The mysqld section of my.cnf contains the following: log-bin server-id =3D 106 basedir =3D /usr datadir =3D /var/lib/mysql tmpdir =3D /tmp language=3D /usr/share/mysql/english skip-locking set-variable=3D key_buffer=3D512M set-variable=3D max_allowed_packet=3D1G set-variable=3D table_cache=3D3072 set-variable=3D sort_buffer=3D2M set-variable=3D record_buffer=3D2M set-variable=3D thread_cache=3D8 set-variable=3D thread_concurrency=3D8 set-variable=3D myisam_sort_buffer_size=3D64M set-variable=3D thread_stack=3D128K set-variable=3D open_files_limit=3D8192 set-variable=3D tmp_table_size=3D50M max_tmp_tables =3D 100 innodb_data_home_dir =3D /var/lib/mysql/ innodb_data_file_path =3D ibdata1:10M:autoextend innodb_log_group_home_dir =3D /var/lib/mysql/ innodb_log_arch_dir =3D /var/lib/mysql/ set-variable =3D innodb_buffer_pool_size=3D384M set-variable =3D innodb_additional_mem_pool_size=3D20M set-variable =3D innodb_log_file_size=3D5M set-variable =3D innodb_log_buffer_size=3D8M innodb_flush_log_at_trx_commit=3D1 set-variable =3D innodb_lock_wait_timeout=3D50 long_query_time=3D30 query_cache_limit=3D1M query_cache_size=3D64M query_cache_type=3D1 max_connections=3D200 Does anyone have any suggestions as to why I'm getting out of memory errors? Do I have some of the settings wrong? If it matters, I have about 50GB worth of data, split between InnoDB and MyISAM tables. I last got the error updating records in one of the MyISAM tables which was about 1MB in size. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Out of memory, but plenty of swap space left
Heikki, Yes, I'm running MySQL on 32-bit Linux. I think maybe something had just gotten corrupted. MySQL restarted itself yesterday, with the following in the error log: 050407 16:24:49 [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 mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=536870912 read_buffer_size=2093056 max_used_connections=201 max_connections=200 threads_connected=49 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1342686 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x59d064a0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... frame pointer (ebp) is NULL, did you compile with -fomit-frame-pointer? Aborting backtrace! Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x59f907f8 is invalid pointer thd-thread_id=68571 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. 050407 16:24:51 InnoDB: Database was not shut down normally! Unless you tell me different, I'll just plan on upgrading to 4.1.11 (I'm still running 4.1.8) -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sent: Friday, April 08, 2005 12:24 AM To: mysql@lists.mysql.com Subject: Re: Out of memory, but plenty of swap space left John, are you running on a 32-bit computer? Then, normally, the process size is limited to 2 GB. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Original Message - From: Jon Wagoner [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, April 07, 2005 7:31 PM Subject: Out of memory, but plenty of swap space left Recently I've been getting error 1041 at times, which gives the message to update the ulimit or add swap space. =20 This is a dedicated MySQL server, so I have no ulimit set for MySQL. According to /proc/meminfo I have over 1GB of swap free. Mem: 2118533120 2067628032 509050880 19677184 1659768832 Swap: 1998733312 686882816 1311850496 MemTotal: 2068880 kB MemFree: 49712 kB MemShared: 0 kB Buffers: 19216 kB Cached:1320684 kB SwapCached: 300184 kB Active: 943996 kB Inactive: 1008424 kB HighTotal: 1179484 kB HighFree: 2044 kB LowTotal: 889396 kB LowFree: 47668 kB SwapTotal: 1951888 kB SwapFree: 1281104 kB The mysqld section of my.cnf contains the following: log-bin server-id =3D 106 basedir =3D /usr datadir =3D /var/lib/mysql tmpdir =3D /tmp language=3D /usr/share/mysql/english skip-locking set-variable=3D key_buffer=3D512M set-variable=3D max_allowed_packet=3D1G set-variable=3D table_cache=3D3072 set-variable=3D sort_buffer=3D2M set-variable=3D record_buffer=3D2M set-variable=3D thread_cache=3D8 set-variable=3D thread_concurrency=3D8 set-variable=3D myisam_sort_buffer_size=3D64M set-variable=3D thread_stack=3D128K set-variable=3D open_files_limit=3D8192 set-variable=3D tmp_table_size=3D50M max_tmp_tables =3D 100 innodb_data_home_dir =3D /var/lib/mysql/ innodb_data_file_path =3D ibdata1:10M:autoextend innodb_log_group_home_dir =3D /var/lib/mysql/ innodb_log_arch_dir =3D /var/lib/mysql/ set-variable =3D innodb_buffer_pool_size=3D384M set-variable =3D innodb_additional_mem_pool_size=3D20M set-variable =3D innodb_log_file_size=3D5M set-variable =3D innodb_log_buffer_size=3D8M innodb_flush_log_at_trx_commit=3D1 set-variable =3D innodb_lock_wait_timeout=3D50 long_query_time=3D30 query_cache_limit=3D1M query_cache_size=3D64M query_cache_type=3D1 max_connections=3D200 Does anyone have any suggestions as to why I'm getting out of memory errors? Do I have some of the settings wrong? If it matters, I have about 50GB worth of data, split between InnoDB and MyISAM tables. I last got the error updating records in one of the MyISAM tables which was about 1MB in size. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe
Re: Out of memory, but plenty of swap space left
John, are you running on a 32-bit computer? Then, normally, the process size is limited to 2 GB. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Original Message - From: Jon Wagoner [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, April 07, 2005 7:31 PM Subject: Out of memory, but plenty of swap space left Recently I've been getting error 1041 at times, which gives the message to update the ulimit or add swap space. =20 This is a dedicated MySQL server, so I have no ulimit set for MySQL. According to /proc/meminfo I have over 1GB of swap free. Mem: 2118533120 2067628032 509050880 19677184 1659768832 Swap: 1998733312 686882816 1311850496 MemTotal: 2068880 kB MemFree: 49712 kB MemShared: 0 kB Buffers: 19216 kB Cached:1320684 kB SwapCached: 300184 kB Active: 943996 kB Inactive: 1008424 kB HighTotal: 1179484 kB HighFree: 2044 kB LowTotal: 889396 kB LowFree: 47668 kB SwapTotal: 1951888 kB SwapFree: 1281104 kB The mysqld section of my.cnf contains the following: log-bin server-id =3D 106 basedir =3D /usr datadir =3D /var/lib/mysql tmpdir =3D /tmp language=3D /usr/share/mysql/english skip-locking set-variable=3D key_buffer=3D512M set-variable=3D max_allowed_packet=3D1G set-variable=3D table_cache=3D3072 set-variable=3D sort_buffer=3D2M set-variable=3D record_buffer=3D2M set-variable=3D thread_cache=3D8 set-variable=3D thread_concurrency=3D8 set-variable=3D myisam_sort_buffer_size=3D64M set-variable=3D thread_stack=3D128K set-variable=3D open_files_limit=3D8192 set-variable=3D tmp_table_size=3D50M max_tmp_tables =3D 100 innodb_data_home_dir =3D /var/lib/mysql/ innodb_data_file_path =3D ibdata1:10M:autoextend innodb_log_group_home_dir =3D /var/lib/mysql/ innodb_log_arch_dir =3D /var/lib/mysql/ set-variable =3D innodb_buffer_pool_size=3D384M set-variable =3D innodb_additional_mem_pool_size=3D20M set-variable =3D innodb_log_file_size=3D5M set-variable =3D innodb_log_buffer_size=3D8M innodb_flush_log_at_trx_commit=3D1 set-variable =3D innodb_lock_wait_timeout=3D50 long_query_time=3D30 query_cache_limit=3D1M query_cache_size=3D64M query_cache_type=3D1 max_connections=3D200 Does anyone have any suggestions as to why I'm getting out of memory errors? Do I have some of the settings wrong? If it matters, I have about 50GB worth of data, split between InnoDB and MyISAM tables. I last got the error updating records in one of the MyISAM tables which was about 1MB in size. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Out of memory
See what happens if you try it with the --quick option. This will prevent mysqldump from writing to memory before writing to the dump file. Hi, yes, it seems to work so far. Thanks for your quick reply. -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Out of memory
Philippe Poelvoorde wrote: Hi, mysqldump returns with this error on one of my nightly batch : mysqldump: Out of memory (Needed 8164 bytes) mysqldump: Got error: 2008: MySQL client run out of memory when retrieving data from server Fri Dec 24 00:00:22 CET 2004 Is there any way to avoid this particular error ? (bear I mind that I don't have any access to my.cnf on this environnement!) The last table in the dump (and not dump, it stops just after the CREATE TABLE) is not that big (less than 8Mo on disk). See what happens if you try it with the --quick option. This will prevent mysqldump from writing to memory before writing to the dump file. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Out of memory on INSERT
On Tue, 2004-06-15 at 08:08, J S wrote: Hi, I have a perl script which runs a bulk insert. When I run an insert with about 100,000 lines it keels over with the following message: DBD::mysql::st execute failed: Out of memory (Needed 6100848 bytes) at ./parse.pl line 227, line 15. There is 8GB of memory on the box so I'm sure there is enough memory there. Is there a setting in my.cnf which I need to tweak? How large is the data? How much of that 8GB is used by other processes? Have you watched the output of 'top' while the script is running? MySQL has tweakable limits on how large a particular insert can be, but this error looks like perl is truely running out of memory, rather than being denied by MySQL. -- . Garth Webb . [EMAIL PROTECTED] . . shoes * * schoenen * * chaussures * zapatos . Schuhe * * pattini * * sapatas * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: out of memory error and update problem
Chip Wiegand wrote: When running mysql from the command line (Putty terminal) and trying to do some updates the updates fail. First the query I am running - mysql update warranty_temp, warranty_old set warranty_old.OwnerName=warranty_temp.OwnerName where warranty_old.WarrantyID between 75 and 100 AND warranty_temp.WarrantyID between 75 and 100; Query OK, 119 rows affected (50.97 sec) Rows matched: 14641 Changed: 119 Warnings: 0 This is not a join, it is a cartesian product of the 2 table ranges. The results of this query are that the OwnerName in record 75 is copied into the OwnerName for records 75 thru 100. That shouldn't be, there should be a differant OwnerName for each row being copied into each row of the target table. Now the table has 119 rows with the same OwnerName. mysql update warranty_temp, warranty_old set warranty_old.OwnerName=warranty_temp.OwnerName where warranty_old.WarrantyID=warranty_temp.WarrantyID and warranty_old.WarrantyID between 75 and 100 ; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: out f memory error
# [EMAIL PROTECTED] / 2003-06-04 16:37:08 +0300: I have a program that runs a loop,in every loop I execute a query. the query itself is very efficient, the result of the query is 4 rows at most but... after a while I get the following error: Out of memory! Bus error (core dumped) how can I increase the memory that is used for the query? Thanks Dana most probably it's your program that's getting out of memory, and the problem is completely unrelated to MySQL. -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Out of memory on simple question.
Thanks for this... So I think I need some joins! Simon -Original Message- From: Roger Baklund [mailto:[EMAIL PROTECTED]] Sent: 24 June 2002 12:19 To: Mysql (E-mail) Cc: Simon Green Subject: Re: Out of memory on simple question. * Simon Green After running this simple question I get this error. I have looked at the my.cnf file and all looks fine and the system has 1.256 G of memory. Can some one please tell me where I have gone wrong. select in_names.Username from in_names, in_names2 where in_names.Username != in_names2.Username; ./mysql: Out of memory (Needed 8164 bytes) ERROR 2008: MySQL client run out of memory If you have many names in these tables, this will consume a lot of memory, yes. You are asking for all combinations of names where the name is not the same. This is an example with only five names in each table: mysql create table in_names (Username varchar(30)); Query OK, 0 rows affected (0.02 sec) mysql insert into in_names values(aaa),(bbb),(ccc),(ddd),(eee); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql create table in_names2 select * from in_names; Query OK, 5 rows affected (0.03 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql select in_names.Username,in_names2.Username - from in_names, in_names2 - where in_names.Username != in_names2.Username; +--+--+ | Username | Username | +--+--+ | bbb | aaa | | ccc | aaa | | ddd | aaa | | eee | aaa | | aaa | bbb | | ccc | bbb | | ddd | bbb | | eee | bbb | | aaa | ccc | | bbb | ccc | | ddd | ccc | | eee | ccc | | aaa | ddd | | bbb | ddd | | ccc | ddd | | eee | ddd | | aaa | eee | | bbb | eee | | ccc | eee | | ddd | eee | +--+--+ 20 rows in set (0.00 sec) I selected both names, so that you can see what is going on. Each name in one table is matched with _every_ name in the other table, except the one that is the same... just as you asked for. :) -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Out of memory on simple question.
* Simon Green After running this simple question I get this error. I have looked at the my.cnf file and all looks fine and the system has 1.256 G of memory. Can some one please tell me where I have gone wrong. select in_names.Username from in_names, in_names2 where in_names.Username != in_names2.Username; ./mysql: Out of memory (Needed 8164 bytes) ERROR 2008: MySQL client run out of memory If you have many names in these tables, this will consume a lot of memory, yes. You are asking for all combinations of names where the name is not the same. This is an example with only five names in each table: mysql create table in_names (Username varchar(30)); Query OK, 0 rows affected (0.02 sec) mysql insert into in_names values(aaa),(bbb),(ccc),(ddd),(eee); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql create table in_names2 select * from in_names; Query OK, 5 rows affected (0.03 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql select in_names.Username,in_names2.Username - from in_names, in_names2 - where in_names.Username != in_names2.Username; +--+--+ | Username | Username | +--+--+ | bbb | aaa | | ccc | aaa | | ddd | aaa | | eee | aaa | | aaa | bbb | | ccc | bbb | | ddd | bbb | | eee | bbb | | aaa | ccc | | bbb | ccc | | ddd | ccc | | eee | ccc | | aaa | ddd | | bbb | ddd | | ccc | ddd | | eee | ddd | | aaa | eee | | bbb | eee | | ccc | eee | | ddd | eee | +--+--+ 20 rows in set (0.00 sec) I selected both names, so that you can see what is going on. Each name in one table is matched with _every_ name in the other table, except the one that is the same... just as you asked for. :) -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: out of memory
Hi, David! Try to write your query without order by ... Sort operations need a lot of memory. But, if you want to use sorting, try to split your big table to smaller tables. Dmitri Lubinski -Original Message- From: David Keeney [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 10, 2001 6:14 AM To: MySQL List Subject: out of memory Sorry. I omitted an important detail from the previous post: I have a database of almost 6 million records, and 1.5 Gbyte table size. I need to iterate over the table with a Perl script, examining every record. This code : $sth_gnr = $dbh-prepare( 'SELECT SQL_BIG_RESULT * FROM Tiger_main '. WHERE rdid '' . 'ORDER BY rdid ' #. ) or die 'bad prepare gnr'; $sth_gnr-execute; dies (during the execute statement) with the error : Out of Memory: Killed process 31666 (temp.pl). Killed The same query executed by the mysql client dies with an almost identical error message. Is there a fast way to iterate over the database with a succession of 'Select ... ' queries? A statement like 'Select * from Tiger_main limit 1,1' is very slow, and iterating over the entire table would take weeks. I am using MySQL server version 3.23.36 under RH Linux 7.1. Thanks for any support you can offer. David Keeney --- End of forwarded message --- -- David Keeney [EMAIL PROTECTED] Travel By Road http://www.travelbyroad.net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: out of memory
On Mon, Jul 09, 2001 at 08:14:24PM -0600, David Keeney wrote: dies (during the execute statement) with the error : Out of Memory: Killed process 31666 (temp.pl). Killed Look for 'SQL_BIG_TABLES' in the Docs... `SQL_BIG_TABLES = 0 | 1' If set to `1', all temporary tables are stored on disk rather than in memory. This will be a little slower, but you will not get the error `The table tbl_name is full' for big `SELECT' operations that require a large temporary table. The default value for a new connection is `0' (i.e., use in-memory temporary tables). -- Brian 'you Bastard' Reichert[EMAIL PROTECTED] 37 Crystal Ave. #303Daytime number: (603) 434-6842 Derry NH 03038-1713 USA Intel architecture: the left-hand path - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
