I have not specified any values for join_buffer_size and sort_buffer_size. What should be the optimal values for this?
-Karthik From: Krishna Chandra Prajapati [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 14, 2008 10:03 AM To: Karthik Pattabhiraman Cc: mysql@lists.mysql.com Subject: Re: Trouble with large data in MySql Hi Karthik, You have many columns in a single table (although you can). But, if you break the below table into two tables ( Vertical partitioning). It will help you to run your query faster. You haven't shown join_buffer_size and sort_buffer_size. Secondly, check your all the queries with explain. On Wed, Oct 8, 2008 at 4:00 PM, Karthik Pattabhiraman <[EMAIL PROTECTED]> wrote: Hi, We have 4 tables in which we have approximately 40 Million records per month. We are having trouble getting results from MySql as it takes about 4-5 hours to complete for each query. We are using this primarily for reporting purposes. My table schema is as follows SMAS Table: Column Name Type Key adnetwork adnetworkResponse campaignName clientRequest logkey loggingTime logmodule pageName propertyName requestId requestSystemId serverResponse sessionId siteName sucess systemId varchar(3999) text varchar(3999) varchar(3999) varchar(3999) timestamp varchar(3999) varchar(3999) varchar(3999) varchar(3999) varchar(3999) text varchar(256) varchar(3999) int(11) varchar(3999) MUL MUL Currently, SMAS table has 40Million records and our query takes 5 hours to execute. My my.cnf file is as follows and all tables are InnoDB. [mysqld] datadir=/mnt/data-store/mysql/data socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid innodb_data_home_dir=/mnt/data-store/mysql/data innodb_data_file_path=ibdata1:15G:autoextend innodb_buffer_pool_size=3G max_connections=200 tmpdir=/mnt/data-store/tmp [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid My query is as follows on SMAS table select campaignName, siteName, adnetwork,date_format(loggingTime ,'%d/%m/%Y') logDate, count(distinct requestid) adpages from SMAS where sucess = 1 GROUP BY 1,2,3,4; Any help will be highly appreciated. -Karthik -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 500003 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: [EMAIL PROTECTED]
