0
votes
2answers
38 views

Building a dedicated physical MySQL server [closed]

I have a database that I use to mine a "primary" 20-column table with 18 million rows and growing by about 2 million a week, and that may accelerate. I am working to pre-process data by running PHP ...
-5
votes
1answer
87 views

Recommendation for mysql please :MySQLTuner [closed]

>> MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options ...
1
vote
0answers
128 views

MySQL server taking up 100% CPU

I'm managing a site that does streaming video, the site is running off: Intel(R) Xeon(R) CPU E31220 @ 3.10GHz 16 GB RAM 2 x 2TB RAID1 1gbit unmetered bandwidth is not an issue as the ...
1
vote
2answers
67 views

Do Inactive MySQL Databases Consume Memory?

I have inactive databases that are on the server just for archive purposes. I know I can do mysqldump for all of them, but I am not 100% sure they are inactive. When I started my system I didn't ...
1
vote
1answer
38 views

Adding new tables — memory usage increases

I added more tables to my mysql server. 20% increase in tables that are all innodb. I have 200 + databases that use the same schema. Does adding more tables increase memory usage? I am using about ...
0
votes
0answers
55 views

MariaDB Galera setup with drastically different amounts of memory used on cluster nodes - why?

We have a 3 node MariaDB Galera cluster. All nodes are identical and the config on each is identical. They have 64GB of RAM each. The output of the following query is 28: SELECT ...
1
vote
1answer
168 views

MySQL not releasing memory

MySQL seems to want to keep an entire table in cache (table size = ~20GB) after any large inserts or select statements are performed on it. Right now my innodb buffer pool is 20GB. Total RAM is 32GB. ...
3
votes
2answers
175 views

MySQL runs out of memory when importing innodb database

I have a small VPS with 512MB RAM and no swap (can't have any). I am importing a 1.5GB innodb database with mysql -u -p < database.sql Before the operation can finish it says : Lost connection ...
0
votes
2answers
196 views

Is there a way to enable MariaDB MEMORY storage engine for every table?

I am new to MariaDB and interested in using it because I want to use MEMORY storage engine for every table. I read MEMORY storage engine on mariadb.com and it seems like I had to specify storage ...
1
vote
3answers
2k views

How long will a temporary MEMORY table persist if I don't drop it (MySQL)

I'm using a recursive stored procedure in MySQL to generate a temporary table called id_list, but I must use the results of that procedure in a follow up select query, so I can't DROP the temporary ...
0
votes
0answers
27 views

How to avoid hitting the memory ceiling with temporary table?

I running a MySql DB at a webhosting company. The size limit of a schema/DB is set to 100Mb. I'm using temporary tables in my query like: CREATE TEMPORARY TABLE t_foo SELECT * FROM Foo INNER JOIN ...
1
vote
2answers
176 views

How much RAM should you allocate a MySQL replication slave?

Our MySQL master is allocated 24GB of RAM (innodb_buffer_pool_size = 24GB), but was curious if there was some standard amount to allocate to a replication slave? Right now the slave is just used to ...
0
votes
1answer
144 views

Does MySQL load the entire row into memory, or just the columns used in WHERE, ORDER BY and GROUP BY?

I have a table that isn't very large row wise (in the 100ks range), but that contains a lot of raw data that is very large in size. Despite having a relatively little number of rows, it's around ...
0
votes
1answer
212 views

Get memory usage of mysql query during runtime

Is possible to obtain used memory in a running query? Something like: mysql> SELECT MEMORY_PEAK as memory; #return memory usage by mysql before run query mysql> SELECT id FROM table WHERE ...
0
votes
2answers
50 views

Is it safe for me to switch from an 8GB server to a 4GB server? (Stats inside)

We are looking to cut some of our server costs and the only place I think it might be possible is our db server. We're currently running an 8GB rackspace cloud that houses a mysql instance and I ...
3
votes
1answer
1k views

Maximum possible memory usage: 16.2G (874% of installed RAM)

Why the MySQL is saying that can use about 16GB of RAM even if my server has only about 2GB? # ./mysqltuner.pl >> MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net> >> Bug ...
0
votes
2answers
53 views

MySQL using swap during conversion to InnoDB

I'm trying to convert some MyISAM tables in my database to InnoDB (MySQL 5.1). The server has quite a bit of memory (96G), and the tables that are giving me problems can be on the order of 50 millions ...
0
votes
0answers
80 views

How much RAM do I need?

Let's assume a query like the following as typical: select A.`date` as `date`, count(*) as N from A as A join B as B on B.id = A.b_id join C as C on C.id = B.c_id join D as D on D.id = ...
0
votes
0answers
64 views

MySQL overtaking system memory and forced to restart

I'm facing a frustrating issue. I have a MySQL Server 5.5 running on top of Ubuntu Server 12.04.3 LTS (VMWare guest w/ 16GB RAM - 8 Cores) mysqltuner.pl says [OK] Maximum possible memory usage: ...
0
votes
1answer
45 views

Query when index is ready

To improve performance of a query I set up an in-memory table and fill it with data. Of course to increase speed I will use an index on the data stored in the in-memory table. create table helper ( ...
1
vote
2answers
273 views

MySQL memory usage issues?

I have a MySQL instance running on my Ubuntu server that is eating up most of the memory on the machine. I currently have 2 GB of ram installed on the machine, and MySQL is using 1983 MB. Most of my ...
1
vote
1answer
784 views

MySQL: sysbench test - InnoDB vs Memory tables

I've done some tests in order to investigate performance issue on the new HP Gen8 server (Intel(R) Xeon(R) CPU E5-2630 0 @ 2.30GHz) I've created two tables, first one is using InnoDB storage engine ...
1
vote
0answers
170 views

MySql Very Slow Writes & Out of memory error

I am having a rds db instance in AWS which is used by Drupal 7 instatllation. I am getting the below error messages in the log: > 130912 11:57:39 [ERROR] /rdsdbbin/mysql/bin/mysqld: Out of memory ...
1
vote
3answers
125 views

in-memory SQL database system [closed]

In an application, I am using mysql's TEMPORARY TABLE. Since this application does not need permanent tables, I wonder if I can find an alternative RDMS designed for memory tables. I hope to find an ...
3
votes
1answer
265 views

Finding swap causes of MySQL

In my centos 6.3 server I have a MySQL 5.5.33 database. It has 17 tables (15 InnoDB, 2 MyISAM) and total records 6.7M rows. I refactored my schemas and added indexes for my slow logs. My average query ...
0
votes
1answer
224 views

How can lots of ram (128 GB) be used to improve MySQL 5.5 index creation?

This is similar to another post of mine, but now I have a different table structure, and it's still unclear what MySQL parameters should be changed to take advantage of the extra ram my machine ...
0
votes
1answer
304 views

MySQL Memory Grows Over Time

I've got a server running a LAMP stack with 1GB RAM that is maxed out on RAM usage. What I'm observing is that directly after a reboot, it drops to 25% usage, and then slowly climbs over time so that ...
1
vote
2answers
782 views

Why would increase in innodb_buffer_pool_size slow down MySQL?

5.1.68-cll - MySQL Community Server on CentOS The system has 32GB of RAM. I increased innodb_buffer_pool_size from 10240M to 15360M (10GB -> 15GB). Time taken for a series of identical operations ...
0
votes
1answer
181 views

Does MySQL reserve memory for MEMORY tables based on max_heap_table_size

Currently I have a set of large MEMORY tables, and have previously calculated how much space I believe each will require and assigned max_heap_table_size accordingly so that the sum is not greater ...
1
vote
1answer
714 views

Can putting mysql DB into memory or having the innodb_buffer_pool_size match the size of DB increase performance?

We have a mysql database that has roughly 80 gigabytes (GB) of data using inno_db engine. We are looking to increase performance on the database (we looked at the slow log and gone through and ...
3
votes
1answer
448 views

Can memcached improve performance for InnoDB in mem with a large innodb_buffer_pool_size?

I learned that innodb_buffer_pool_size makes innodb behave like an in memory db. If I give innodb_buffer_pool_size enough ram for the entire db could memcached improve further the performance, or ...
3
votes
4answers
702 views

Stress Test to Saturate Memory for MySQL InnoDB

I'm investigating windows 32bit memory problems (the 2G limit) and am after a test which will max out MySQL's memory including InnoDB Buffer Pool, per connection memory and/or any other uses of ...
1
vote
0answers
51 views

memcached+mysql cluster maximum memory

I'm deploying a system with memcached and mysql cluster. The command line is: memcached -E lib/ndb_engine.so -e "connectstring=maddy:1186;role=dev" I can't specify the -m argument since I used -E ...
4
votes
2answers
2k views

Is it bad to create many mysql temporary tables simultaneously?

I need to make heavy statistical analysis to deliver some data to users. Currently, I catch the data from mysql and process them via PHP arrays. However, mysql temporary tables are quite better ...
1
vote
1answer
162 views

Why does a MySQL MEMORY-backed table take so much more space than on disk?

I have a single InnoDB table on disk, with ~1,107,423 rows and taking up 45.6 MiB disk space. The structure is: messageid int(11) signature varchar(255) When I create a MEMORY-backed version of ...
6
votes
2answers
16k views

How do I shrink the innodb file ibdata1 without dumping all databases?

InnoDB stores all tables in one big file ibdata1. After dropping a big table, the file is keeping its size no matter how big the table was. How can I shrink that file without having to dump and ...
0
votes
2answers
1k views

MySQL how to release memory used up by SELECT

I executed an INSERT IGNORE combined with SELECT from large table. The SELECT took up 16GB of memory and went into SWAP before I terminated it with CTRL+C. INSERT IGNORE INTO sid(sid) SELECT sid ...
2
votes
3answers
2k views

Memory problems with MySQLWorkbench

I'm currently using MySQLWorkbench 5.2.40 on my PC (8GB rRM, Corei7). I have a table with almost 1.5 million records, which I need to visually look over. Surprisingly, the total size does not exceed ...
3
votes
3answers
1k views

Optimizing DELETE Query on MySQL MEMORY Table

I run a large forum which maintains a MySQL database for backend data storage. The 'session' table tracks logged in users & guests. It is currently about 100k records, so not really that large. ...
3
votes
3answers
8k views

How to use insert delay with the InnoDB engine and use less connection for insert statements?

I'm working on an application which involves a lot of database writes, approximately ~70% inserts and 30% reads. This ratio would also include updates which I consider to be one read and one write. ...
7
votes
2answers
9k views

Why is MySQL is creating so many temporary tables on disk?

Can any configuration mistake lead to creating too many temp tables by mysql..mysql tuner shows Current max_heap_table_size = 200 M Current tmp_table_size = 200 M Of 17158 temp tables, 30% were ...
2
votes
2answers
578 views

Out of memory mysql was aborted

I am using windows 2003 server, with 8G of RAM and MySQL was installed on It. I am getting error as follows out of memory.[ERROR] mysqld: Out of memory (Needed 523712 bytes). The configuration ...
0
votes
1answer
163 views

memcached service stopped unexpectedly

I am working with MySQL Cluster 7.2.4 memcached 1.6 I started memcached service with ndb-caching option: memcached -E /usr/local/mysql/lib/ndb_engine.so -e ...
3
votes
2answers
3k views

mysql out of available memory

We have a dedicated MySQL server with 16GB of RAM that's serving 8 web workers with relatively high traffic (over 1000 requests per second at peak traffic) At peak we're seeing the following errors ...
0
votes
2answers
91 views

MySQL still holding memory after I shut it down?

I used this command to shutdown MySQL, however it seems the amount of free memory on my box has not changed? That seems to odd. /usr/local/webserver/mysql/bin/mysqladmin -u ${mysql_username} ...
4
votes
3answers
3k views

How much memory will a MEMORY table take up?

Let's say I have a MyISAM table with a data length of 4.8GB and an index length of 6.2GB. So, a total data size of eleven gig. How much memory would this require, were I to convert it to a MEMORY ...
3
votes
1answer
3k views

I have lots of free memory. How do I use it to increase performance?

I am running Litespeed (with suExec, PHP SAPI), MySQL, DirectAdmin, named on a VPS with a 16-core Xeon CPU and 2GB RAM. Currently, only 300 MB out of 2048 are used. It might sound a stupid, but how ...
4
votes
2answers
6k views

Is InnoDB Engine up to speed against Memory Engine?

I am exploring the efficiency of different database engines in MySQL version 5.5.18 to see which is best suited for use with a range query on a dataset of 5 million rows: SELECT P.col1, P.col2, ...
4
votes
1answer
2k views

Mysql Memory table getting many locks

On my site I log every pageview (date, ip, referrer, page, etc) in a simple mysql table. This table has the following activity: 1 SELECT per minute 1 DELETE per minute no UPDATE queries Lots of ...
0
votes
1answer
358 views

InnoDB table vs MEMORY table for a mysql environment with many inserts

I run my sites all on InnoDB tables which is working really well so far. Now I like to know what is going on in real-time on my sites, so I store each pageview (page, referrer, IP, hostname, etc) in ...