MySQL : Open-Source, Relational Database Management System
10
votes
1answer
11k views
Multi cores and MySQL Performance
Importance of RAM is an established fact but far less material is available about the importance of cores and multithreading when it comes to the usage of CPU by MySQL. I am talking about the ...
10
votes
3answers
542 views
Where is the INNODB Performance Leak?
I have a weird problem which I cannot seem to fix. I'm more an web programmer than a Server/DB Admin, so I hope someone here can help me.
The Situation
I am working on a system which handles a lot ...
10
votes
3answers
424 views
Use MySQL to regularly do multi-way joins on 100+ GB tables?
Background:
I’ve created a web application that I would like to be able to scale reasonably well. I know I'm not Google or Twitter, but my app uses a fairly large amount of data for each user and thus ...
9
votes
3answers
2k views
Why does importing a 12 GB .sql file take more than 36 hours?
I've been waiting now for 36 hours for a 12 GB .sql file to be imported with a simple type site.sql | mysql command. I can see the ibdata1 is growing still, currently nearly 40 GB.
Considering the ...
9
votes
3answers
11k views
Why does InnoDB store all databases in one file?
It was convenient that MyISAM used to store each table in a corresponding file. InnoDB has made advancements in many aspects, but I wonder why InnoDB stores all databases in one file (ibdata1 by ...
9
votes
5answers
1k views
Is it unwise to run replication on same physical server?
I'm contemplating setting up a Master-Slave replication for my database. The slave server will be used for redundancy and possibly a reports server. However, one of the biggest issues I'm running into ...
9
votes
5answers
8k views
MySQL server has gone away obstructing import of large dumps
I am trying to import a large sql dump (2GB) to my local mysql on my mac. I have been able to do this in the past (I was using MAMP), but now I get a
ERROR 2006 (HY000) at line 7758: MySQL server ...
9
votes
2answers
5k views
Is MySql's LAST_INSERT_ID() function guaranteed to be correct?
When I do a single row INSERT to a table that has an AUTO_INCREMENT column I'd like to use the LAST_INSERT_ID() function to return the new AUTO_INCREMENT'ed value stored for that row.
As many SQL ...
9
votes
4answers
3k views
Are there any MySQL Benchmarking tools?
I've heard a long time ago that there is this tool that helps you tweek mysql settings for better performance but i can't seam to find it. I am aware that I can use ab for apache to simulate high ...
9
votes
5answers
4k views
Splitting Tables in MySQL. Good practice?
I have started working on an existing project and the previous developer had split up a table into 10 separate tables with identical schemas but different data.
The tables look like this:
...
9
votes
2answers
10k views
How to SELECT from SHOW TABLE STATUS results
I'd like to limit the rows and columns that come back from the
SHOW TABLE STATUS
command in MySQL 5.1. Is there a way to get this same information through a SELECT statement so I can manipulate ...
9
votes
3answers
844 views
Why don't my .my.cnf settings work?
I have a .my.cnf file in my home directory on my desktop that includes:
[dbid]
user = myusername
password = mypassword
database = dbname
host = server.location.com
If I recall correctly, before ...
9
votes
3answers
7k views
MySQL table_cache and Opened_tables
I have seen people use the comparison of Open_tables and Opened_tables to assess whether the table_cache is too small in MySQL. However, I believe that Opened_tables is cumulative across uptime, so ...
9
votes
2answers
2k views
Is it common practice to mix InnoDB and MyISAM tables on same server?
I've got a single database of about 4.5GB running on a server with 8GB RAM. The vast majority of the tables are MyIsam (about 4.3GB), but I'm soon going to be converting some of them to InnoDB. (It's ...
9
votes
1answer
167 views
Power went out - Did query finish?
Is there a way to check and see if a query finished? I ran 3 very long running update queries (+/- 25 hours each) as I went out the door for vacation last week. Unfortunately, somewhere during the ...
9
votes
1answer
3k views
Order by column should have index or not?
I have added indexes to table which are used for searching result. I am showing results by ASC or DESC order. So that column should have index or not? I have 2 more indexes on that table. How ...
9
votes
3answers
1k views
How to create a conditional index on MySQL?
How to create an index to filter a specific range? AFAIK it's impossible to create directly but I think it's possible simulate this feature.
Example: I wanna create an index for NAME column just to ...
9
votes
1answer
929 views
MySQL table with 100,000 records queried often
I have a single database of about 100 tables to store various kinds of information.
The most important table is our order table which is used to store customers orders and is over 100000 records as ...
9
votes
3answers
1k views
Best way to design tournament database
I'm creating a webpage for placing bets on all matches of the upcoming Euro 2012 football tournament. Need some help deciding what approach to take for the knockout phase.
I have created a mockup ...
9
votes
1answer
164 views
Calculate MATCH() AGAINST() scores from UNIFIED QUERY not FOR EACH TABLE
I am trying to have a score for entire section of SELECT statements
SELECT *,MATCH(`result`) AGAINST('keyword') as `score` FROM `table1` WHERE MATCH(`result`) AGAINST('keyword')
UNION
SELECT ...
9
votes
1answer
278 views
Disable explicit commits in JDBC, detect them in SQL, or put the database in a readonly state
Background: I'm working on http://sqlfiddle.com (my site), and am trying to prevent one avenue of abuse possible there. I'm hoping that by asking about a problem I'm currently addressing, I don't ...
9
votes
5answers
689 views
Infrastructure for Highly Concurrent, High Write DB
My requirements are:
3000 Connections
70-85% Write vs Read
Currently, we are maxing out a High-CPU, Extra Large Instance at 700 connections. All 8 cores are maxed. We think it's the number of ...
9
votes
3answers
5k views
Access denied for user 'root'@'%'
I used to access the root user in MySQL just fine. But recently, I am no longer able to.
I am able to login fine :
mysql -u root -p
Here is the mysql status after login :
mysql> status
...
8
votes
9answers
921 views
Which DBMS are fast enough for an online game (few thousand players)?
I'm currently making an MMORPG game, which could have a few thousand players online at the same time (probably not; just wishful thinking). First we wanted to use MySQL, but I have heard it isn't fast ...
8
votes
5answers
2k views
How to manage millions of users?
I am about to launch something really big. I need to prepare my server and database.
I would like to group each set of 100,000 users in separate user tables but I do not know how to associate one ...
8
votes
3answers
19k views
Which is faster, InnoDB or MyISAM?
How can MyISAM be "faster" than InnoDB if
MyISAM needs to do disk reads for the data?
InnoDB uses the buffer pool for indexes and data, and MyISAM just for the index?
8
votes
3answers
5k views
Why is 'LOAD DATA INFILE' faster than normal INSERT statements?
I've read an article that mentioned we can achieve 60,000 inserts per second by LOAD DATA IN FILE statement that read from csv files and insert it into database.
Why should it differ from normal ...
8
votes
3answers
746 views
How is LIKE implemented?
Can anyone explain how the LIKE operator is implemented in current database systems (e.g. MySQL or Postgres)? or point me to some references that explain it?
The naive approach would be to inspect ...
8
votes
3answers
3k views
Where can I find example databases for MySQL, SQL Server and Oracle?
I want to experiment with the 3 large database management systems.
For this purpose it would be cool to have the same database (the same tables, etc.) for each of those.
Where can I find sql scripts ...
8
votes
1answer
5k views
How is INFORMATION_SCHEMA implemented in MySQL?
The INFORMATION_SCHEMA is, in theory, a set of views specified in the SQL standard that allow the user to inspect the system's metadata. How is it this implemented in MySQL?
When I connect to a fresh ...
8
votes
5answers
8k views
Why do MySQL threads often show “freeing items” status when the query cache is disabled?
When I run SHOW PROCESSLIST, there are often a large number of INSERT/UPDATE threads in the "freeing items" state.
The MySQL manual suggests that at least part of the reason for a thread to be in ...
8
votes
2answers
3k views
MySQL Sharding vs MySQL Cluster
Considering performance only, can a MySQL Cluster beat a custom data sharding MySQL solution? sharding = horizontal partitioning
When I refer to sharding, I'm considering sharding made in the ...
8
votes
2answers
4k views
kill all queries - MySQL
Sometimes during a SNAFU I have to run kill query xxxxxxx twenty or thirty times. Any sort of kill all command I am missing?
On account of how I don't like typing.
8
votes
4answers
962 views
How can I optimize this MySQL query further?
I have a query that is taking a particularly long time to run (15+ seconds) and it is only getting worse with time as my dataset grows. I have optimized this in the past, and have added indices, ...
8
votes
2answers
3k views
move data folder and drive mysql centos
i need to move the data to a different folder and drive on my cent os db server.
i added new hard drive (since we ran low on space),
i have a 1tb of hard drive and i have a new 2tb i want to put the ...
8
votes
2answers
3k views
PostgreSQL vs. MySQL - Advantages / Disadvantages with a spatial component
We are the in the process of building out a web application that has a spatial data component. In the beginning our spatial data comparisons will take a given point and return matched overlapping ...
8
votes
3answers
1k views
MySQL High Performance for Lots of SELECTs/INSERTs/UPDATEs/DELETEs
I am creating a module where every user often gets a record into a table for 10 to 300 seconds.
When the time expires a record gets deleted. The case is: there will be a lot of users and records will ...
8
votes
4answers
101 views
What are the trade offs for incrementing ids vs. fulltext keys for foreign key designs?
In a lot of relational database designs there are fields that are referenced in other tables.
For example consider a user table with a unique username and a second table storing address data.
One ...
8
votes
3answers
2k views
Monitor MySQL activity per database?
What is the best way to monitor activity on each database of my server ?
I tried mtop/mytop which display activity in real time but aren't very practical to see all the queries executed on it.
I ...
8
votes
1answer
2k views
Reasons for occasionally slow queries?
We are running MySQL 5.1 on Windows Server 2008 R2.
We have been doing some diagnostics on our database of late and have found some disturbing artifacts which we can't explain. We added some code to ...
8
votes
4answers
6k views
Alternatives to MySQL Workbench
I've been running into some problems with MySQL Workbench that I'm not sure will be able to be fixed (namely, what appears to be an old bug that may have resurfaced), so I'd like to be able to find a ...
8
votes
1answer
3k views
Clarification on MySQL innodb_flush_method variable
Let me begin by admitting that I'm very ignorant of the inner workings of hard disks. So when I read over the manual for the variable innodb_flush_method, it confused me. Can I get an explanation in ...
8
votes
3answers
3k views
Audit logins on MySQL database
Is there a way to audit logins to MySQL? I'd like to be able to create a username for each employee and thereby create an audit trail of logins. However, googling has turned up no good results.
The ...
8
votes
3answers
1k views
MySQL LDAP authentication
Is LDAP authentication and authorization in MySQL possible? What are the requirements (MySQL version, third-party software etc.)? How it's organized?
Thanks.
8
votes
4answers
2k views
Adding an index very slow…is there a mysql cmd to get an ETA or show progress?
I'm currently running an alter query on my table (20M entries)
in order to add an index. it's running for more than 3 days already
(stuck on 'copy to tmp table').
is there a way I can see the ...
7
votes
4answers
493 views
How to create a database for unknown kinds of data?
I am in the process of designing a database for a new PHP/MySql-based application.
My problem is that I do not and cannot represent what should be saved in the database because it is unlimited and ...
7
votes
4answers
961 views
Benefits of using backtick (`) in MySQL queries?
In MySQL we can create queries with or without the backtick (`) symbol. Example:
SELECT * FROM TEST;
SELECT * FROM `TEST`;
Both works fine in mysql-console.
Is there any technical difference ...
7
votes
2answers
309 views
Data Compression Through Base36 Encoding
I'm trying to store a lot of large numbers that truncate when stored as INT values. Unfortunately they are too large for this type.
I'm thinking about using Base36 encoding to accomplish this. Are ...
7
votes
3answers
3k views
check constraint does not work?
I have the following table.
create table test (
id smallint unsigned AUTO_INCREMENT,
age tinyint not null,
primary key(id),
check (age<20)
);
The problem is that the CHECK ...
7
votes
4answers
475 views
Is splitting a 'users' table for authentication purposes a good idea?
Suppose I have a user table in my site in which there are around 2-3 Million users (records) in the table.
For speeding up my login process, is it a good approach to split my user table, one for ...
